如何使用SqlCommandBuilder來做到不寫T-SQL就可以對資料進行增刪修
在開始嘗試使用LINQ to Entities存取SQL Server前，都習慣使用SQLDataAdapter或TableAdapter搭配T-SQL或Strored Procedure來對資料進行CRUD，最近在論壇上看到有網友提出SqlCommandBuilder的問題，藉機會TRY了一下如何使用SqlCommandBuilder來對資料進行新增、修改和刪除。
- 首先先用SqlConnectionStringBuilder來建立連線字串：
1: static void Main(string[] args)
2: {
3: SqlConnectionStringBuilder scsb = new SqlConnectionStringBuilder();
4: scsb.DataSource = ".";
5: scsb.InitialCatalog = "Northwind";
6: scsb.IntegratedSecurity = true;
7: Console.ReadKey();
8: }
- 以下程式碼示範使用SqlCommandBuilder來進行資料新增：
1: private static void Create(SqlConnectionStringBuilder scsb)
2: {
3: using (SqlConnection con = new SqlConnection(scsb.ToString()))
4: {
5: using (SqlDataAdapter adapter = new SqlDataAdapter("select * from region", con))
6: {
7: using (SqlCommandBuilder scb = new SqlCommandBuilder(adapter))
8: {
9: DataTable dt = new DataTable();
10: adapter.Fill(dt);
11:
12: //新增
13: DataRow dr = dt.NewRow();
14: dr["RegionID"] = 7;
15: dr["RegionDescription"] = "111";
16: dt.Rows.Add(dr);
17: adapter.Update(dt);
18: }
19: }
20: }
21: }
SqlCommandBuilder建構時需傳入SqlDataAdapter，後續進行資料的增刪修需要用到Adapter的Update方法來同步回資料庫，另外資料表也必須要有Pirmary Key才可以，否則會遇到下列的錯誤訊息。
1: Unhandled Exception: System.InvalidOperationException: Dynamic SQL generation for2: the UpdateCommand is not supported against a SelectCommand that does not return3: any key column information.
以下為SqlCommandBuilder產生的Insert敘述：
1: exec sp_executesql N'INSERT INTO [region] ([RegionID], [RegionDescription]) VALUES (@p1, @p2)'2: ,N'@p1 int,@p2 nchar(3)'3: ,@p1=74: ,@p2=N'111'
- 以下程式碼示範使用SqlCommandBuilder來進行資料修改：
1: private static void Update(SqlConnectionStringBuilder scsb)
2: {
3: using (SqlConnection con = new SqlConnection(scsb.ToString()))
4: {
5: using (SqlDataAdapter adapter = new SqlDataAdapter("select * from region", con))
6: {
7: using (SqlCommandBuilder scb = new SqlCommandBuilder(adapter))
8: {
9: DataTable dt = new DataTable();
10: adapter.Fill(dt);
11:
12: //更新
13: DataRow[] drs = dt.Select("RegionID = 7");
14: if (drs.Count() > 0)
15: {
16: dt.Rows[dt.Rows.IndexOf(drs[0])]["RegionDescription"] = "222";
17: //dt.Rows[dt.Rows.IndexOf(drs[0])]["RegionID"] = "6"; //也可以更新Primary Key
18: }
19: adapter.Update(dt);
20: }
21: }
22: }
23: }
更新前必須先以DataSet的Select方法來找出符合的資料列陣列（第13列），接著再針對所要更新的欄位進行修改（第16列），最後則是呼叫Adapter的Update方法來將資料同步回資料庫。
以下為SqlCommandBuilder產生的Update敘述：
1: exec sp_executesql N'UPDATE [region] SET [RegionDescription] = @p1 WHERE (([RegionID] = @p2) AND ([RegionDescription] = @p3))'2: ,N'@p1 nchar(3),@p2 int,@p3 nchar(50)'3: ,@p1=N'222',@p2=7,@p3=N'111 '
- 以下程式碼示範使用SqlCommandBuilder來進行資料刪除：
1: private static void Delete(SqlConnectionStringBuilder scsb)
2: {
3: using (SqlConnection con = new SqlConnection(scsb.ToString()))
4: {
5: using (SqlDataAdapter adapter = new SqlDataAdapter("select * from region", con))
6: {
7: using (SqlCommandBuilder scb = new SqlCommandBuilder(adapter))
8: {
9: DataTable dt = new DataTable();
10: adapter.Fill(dt);
11:
12: //刪除
13: DataRow[] drs = dt.Select("RegionID in (6,7)");
14: foreach (DataRow dr in drs)
15: {
16: dt.Rows[dt.Rows.IndexOf(dr)].Delete();
17: }
18: adapter.Update(dt);
19: }
20: }
21: }
22: }
刪除前必須先以DataSet的Select方法來找出符合的資料列陣列（第13列），接著再呼叫DataTable的Delete方法來刪除資料（第16列），最後則是呼叫Adapter的Update方法來將資料同步回資料庫。
以下為SqlCommandBuilder產生的Delete敘述：
1: exec sp_executesql N'DELETE FROM [region] WHERE (([RegionID] = @p1) AND ([RegionDescription] = @p2))'
2: ,N'@p1 int,@p2 nchar(50)'
3: ,@p1=7,@p2=N'111 '
最後依實際需求呼叫增刪修的方法來維護資料，程式碼如下：
Create(scsb); //新增資料
Update(scsb); //更新資料
Delete(scsb); //刪除資料