新增、查詢、修改、刪除 SQLite 資料庫資料範例 ( WinForm CRUD Template for SQLite)

本程式示範新增、查詢、修改、刪除 SQLite 資料庫資料的語法, 使用 System.Data.SQLite v 1.0.112 (32 bits) 於 Visual Studio 2015 中開發, 作為語法細節參考.
原始碼: 下載
資料庫與測試資料: 下載

補充:
SQLite 歷史悠久, 從2000年開放至今, 非常適用於本機資料庫的需求應用, 執行效能高. 讀取資料可以多工執行, 但是寫入資料庫時, 則需依序鎖定執行.
SQLite 從 v 1.0.112 開始, 已不支援 Visual Studio 2015 從 NuGet 直接下載的方式.
以本程式開發環境為 x86為例, 若仍要以 Visual Studio 2015 開發 SQLite 的程式, 則必須到 SQLite Download, 或附檔下載所需的檔案 sqlite-netFx46-binary-bundle-Win32-2015-1.0.112.0.zip, 自行加入 reference 中.
Visual Studio 2015 已漸漸被忽略, 該是放棄的時候了 !
執行畫面:

新增

iAffected = 0;
sCmd = "INSERT INTO TConfig (FParentID, FSeqNo, FKey, FValue, FValueB, FReadonly, FNote) VALUES (@FParentID, @FSeqNo, @FKey, @FValue, @FValueB, @FReadonly, @FNote)";
try
{
	using (SQLiteConnection cn1 = new SQLiteConnection(_sConnectionString))
	using (SQLiteCommand command = cn1.CreateCommand())
	{
		command.CommandText = sCmd;
		command.Parameters.AddWithValue("@FParentID", FParentID.ZToObject_DBNull());
		command.Parameters.AddWithValue("@FSeqNo", FSeqNo.ZToObject_DBNull());
		command.Parameters.AddWithValue("@FKey", txtFKey.Text.ZToObject_DBNull());
		command.Parameters.AddWithValue("@FValue", txtFValue.Text.ZToObject_DBNull());
		command.Parameters.AddWithValue("@FValueB", txtFValueB.Text.ZToObject_DBNull());
		command.Parameters.AddWithValue("@FReadonly", chkReadonly.Checked.ZToObject_DBNull());
		command.Parameters.AddWithValue("@FNote", txtFNote.Text.ZToObject_DBNull());
		cn1.Open();
		iAffected = command.ExecuteNonQuery();
	}
}
catch (Exception e1)
{
	MessageBox.Show(e1.Message);
	return;
}
if (iAffected != 1)
{
	MessageBox.Show("Fail.");
	return;
}

查詢

DataRow DBReadRow_ID(long lID)
{
	try
	{
		using (SQLiteConnection cn1 = new SQLiteConnection(_sConnectionString))
		using (SQLiteCommand command = cn1.CreateCommand())
		{
			command.CommandText = "Select * from TConfig where FID=@FID";
			command.Parameters.AddWithValue("@FID", lID);
			cn1.Open();
			using (SQLiteDataAdapter adapter1 = new SQLiteDataAdapter(command))
			{
				DataSet ds1 = null;
				ds1 = new DataSet();
				adapter1.Fill(ds1);
				if (ds1.Tables[0].Rows.Count < 1)
					return null;

return ds1.Tables[0].Rows[0];
			}
		}
	}
	catch (Exception ex1)
	{
		MessageBox.Show(ex1.Message + _sConnectionString);
	}
	return null;
}
void EntryRead(long lID)
{
	DataRow row1 = DBReadRow_ID(lID);
	if (row1 == null)
	{
		EntryClear();
		MessageBox.Show($"No data found. ID={lID}.");
		return;
	}

// Fetch to buffers.
	txtFID.Text = row1["FID"].ZToLong().ZToString();
	txtFParentID.Text = row1["FParentID"].ZToLong().ZToString();
	txtFSeqNo.Text = row1["FSeqNo"].ZToLong().ZToString();
	txtFKey.Text = row1["FKey"].ZToString();
	txtFValue.Text = row1["FValue"].ZToString();
	txtFValueB.Text = row1["FValueB"].ZToString();
	chkReadonly.Checked = row1["FReadonly"].ZToLong() == 1;
	txtFNote.Text = row1["FNote"].ZToString();
	lblFCreateTime.Text = row1["FCreateTime"].ZToDateTime().ZToString_MsDash();
	lblFUpdateTime.Text = row1["FUpdateTime"].ZToDateTime().ZToString_MsDash();
}

修改

int iAffected = 0;
string sCmd = "Update TConfig set FParentID=@FParentID, FSeqNo=@FSeqNo, FKey=@FKey, FValue=@FValue, FValueB=@FValueB, FReadonly=@FReadonly, FNote=@FNote, FUpdateTime=(datetime('now', 'localtime')) where FID=@FID and FUpdateTime=@FUpdateTime";
try
{
	using (SQLiteConnection cn1 = new SQLiteConnection(_sConnectionString))
	using (SQLiteCommand command = cn1.CreateCommand())
	{
		command.CommandText = sCmd;
		command.Parameters.AddWithValue("@FParentID", FParentID.ZToObject_DBNull());
		command.Parameters.AddWithValue("@FSeqNo", FSeqNo.ZToObject_DBNull());
		command.Parameters.AddWithValue("@FKey", txtFKey.Text.ZToObject_DBNull());
		command.Parameters.AddWithValue("@FValue", txtFValue.Text.ZToObject_DBNull());
		command.Parameters.AddWithValue("@FValueB", txtFValueB.Text.ZToObject_DBNull());
		command.Parameters.AddWithValue("@FReadonly", chkReadonly.Checked.ZToObject_DBNull());
		command.Parameters.AddWithValue("@FNote", txtFNote.Text.ZToObject_DBNull());
		command.Parameters.AddWithValue("@FID", FID.ZToObject_DBNull());
		command.Parameters.AddWithValue("@FUpdateTime", FUpdateTime.ZToObject_DBNull());
		cn1.Open();
		iAffected = command.ExecuteNonQuery();
	}
}
catch (Exception e1)
{
	MessageBox.Show(e1.Message);
	return;
}
if (iAffected != 1)
{
	MessageBox.Show("Fail.");
	return;
}

刪除

Log
20200301, 011netservice@gmail.com, Create.

WebTool