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

本程式示範新增、查詢、修改、刪除 SQL Server 資料庫資料的語法, 使用 .Net Framewrok 原生的 System.Data.SqlClient 於 Visual Studio 2015 中開發, 作為語法細節參考.
原始碼: 下載
資料庫: 下載
測試資料: 下載

執行畫面:

新增

iAffected = 0;
sCmd = "INSERT [dbo].[TConfig] ([FParentID], [FSeqNo], [FKey], [FValue], [FValueB], [FReadonly], [FNote]) VALUES (@FParentID, @FSeqNo, @FKey, @FValue, @FValueB, @FReadonly, @FNote)";
try
{
	using (SqlConnection cn1 = new SqlConnection(_sConnectionString))
	using (SqlCommand 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(int iID)
{
	try
	{
		using (SqlConnection cn1 = new SqlConnection(_sConnectionString))
		using (SqlCommand command = cn1.CreateCommand())
		{
			command.CommandText = "Select * from TConfig where FID=@FID";
			command.Parameters.AddWithValue("@FID", iID);
			cn1.Open();
			using (SqlDataAdapter adapter1 = new SqlDataAdapter(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(int iID)
{
	DataRow row1 = DBReadRow_ID(iID);
	if (row1 == null)
	{
		EntryClear();
		MessageBox.Show($"No data found. ID={iID}.");
		return;
	}

// Fetch to buffers.
	txtFID.Text = row1["FID"].ZToInt().ZToString();
	txtFParentID.Text = row1["FParentID"].ZToInt().ZToString();
	txtFSeqNo.Text = row1["FSeqNo"].ZToInt().ZToString();
	txtFKey.Text = row1["FKey"].ZToString();
	txtFValue.Text = row1["FValue"].ZToString();
	txtFValueB.Text = row1["FValueB"].ZToString();
	chkReadonly.Checked = row1["FReadonly"].ZToInt() == 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 [dbo].[TConfig] set [FParentID]=@FParentID, [FSeqNo]=@FSeqNo, [FKey]=@FKey, [FValue]=@FValue, [FValueB]=@FValueB, [FReadonly]=@FReadonly, [FNote]=@FNote, [FUpdateTime]=getdate() where [FID]=@FID and [FUpdateTime]=@FUpdateTime";
try
{
	using (SqlConnection cn1 = new SqlConnection(_sConnectionString))
	using (SqlCommand 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
20200116, 011netservice@gmail.com, Create.

WebTool