C# DataTable 批量数据处理 增、删、改

1.批量新增，采用高效的SqlBulkCopy

[csharp] view plain copy print ?

1. using (SqlBulkCopy sbc = new SqlBulkCopy(db.ConnectionString))  
2.                 {  
3.                     sbc.DestinationTableName = dataTable.TableName;  
4.                     sbc.BatchSize = dataTable.Rows.Count;  
5.                     sbc.BulkCopyTimeout = 300;  
6.                     sbc.WriteToServer(dataTable);  
7.                 }  

2.批量新增、修改、删除，采用SqlDataAdapter，如下代码段可根据DataTable.RowState （关于RowState参考我的另一篇文章介绍）状态进行新增及删除，

但是发现 批量修改不好使，具体看第3点

[csharp] view plain copy print ?

1. SqlConnection conn = new SqlConnection(strConnection));           
2.   
3.       SqlCommand myCommand = new SqlCommand("select * from "+strTblName+" where 1=2"),(SqlConnection) conn);     
4.   
5.       SqlDataAdapter myAdapter = new SqlDataAdapter(myCommand );     
6.   
7.       SqlCommandBuilder myCommandBuilder = new SqlCommandBuilder(myAdapter);       
8.         
9.       myAdapter.InsertCommand = myCommandBuilder .GetInsertCommand();  
10.   
11.       myAdapter.UpdateCommand = myCommandBuilder .GetUpdateCommand();  
12.   
13.      myAdapter.DeleteCommand = myCommandBuilder .GetDeleteCommand();  
14.   
15.    try  
16.   
17.     {  
18.   
19.         lock(this)                             //处理并发情况(分布式情况)  
20.   
21.        {  
22.   
23.               conn.Open();  
24.   
25.               myAdapter.Update(ds,strTblName);    
26.   
27.                conn.Close();  
28.   
29.        }  

3.解决SqlDataAdapter.update 无法批量修改的问题

[csharp] view plain copy print ?

1. DataSet ds = new DataSet();  
2.             ds.Tables.Add(table);  
3.             string _tableName = table.TableName;  
4.             int result = 0;  
5.             using (SqlConnection sqlconn = new SqlConnection(db.ConnectionString))  
6.             {  
7.                 sqlconn.Open();  
8.   
9.                 //使用加强读写锁事务     
10.                 SqlTransaction tran = sqlconn.BeginTransaction(IsolationLevel.ReadCommitted);  
11.                 try  
12.                 {  
13.   
14.                     ds.Tables[0].AcceptChanges();  
15.                     foreach (DataRow dr in ds.Tables[0].Rows)  
16.                     {  
17.                         //所有行设为修改状态     
18.                         dr.SetModified();  
19.                     }  
20.                     //为Adapter定位目标表     
21.   
22.                     SqlCommand cmd = new SqlCommand(string.Format("select * from {0} where {1}", _tableName, " 1=2"), sqlconn, tran);  
23.                     SqlDataAdapter da = new SqlDataAdapter(cmd);  
24.                     SqlCommandBuilder sqlCmdBuilder = new SqlCommandBuilder(da);  
25.                     sqlCmdBuilder.ConflictOption = ConflictOption.OverwriteChanges;  
26.                     da.AcceptChangesDuringUpdate = false;  
27.                     string columnsUpdateSql = "";  
28.                     SqlParameter[] paras = new SqlParameter[table.Columns.Count];  
29.                     int parasIndex = 0;  
30.                     //需要更新的列设置参数是,参数名为"@+列名"  
31.                     for (int i = 0; i < table.Columns.Count; i++)  
32.                     {  
33.                         //此处拼接要更新的列名及其参数值  
34.                         columnsUpdateSql += ("[" + table.Columns[i].ColumnName + "]" + "=@" + table.Columns[i].ColumnName + ",");  
35.                         if (table.Columns[i].DataType.Name == "DateTime")  
36.                         {  
37.                             paras[i] = new SqlParameter("@" + table.Columns[i].ColumnName, SqlDbType.DateTime, 23, table.Columns[i].ColumnName);  
38.                         }  
39.                         else if (table.Columns[i].DataType.Name == "Int64")  
40.                         {  
41.                             paras[i] = new SqlParameter("@" + table.Columns[i].ColumnName, SqlDbType.NVarChar, 19, table.Columns[i].ColumnName);  
42.                         }  
43.                         else  
44.                         {  
45.                             paras[i] = new SqlParameter("@" + table.Columns[i].ColumnName, SqlDbType.NVarChar, 2000, table.Columns[i].ColumnName);  
46.                         }  
47.                     }  
48.                     if (!string.IsNullOrEmpty(columnsUpdateSql))  
49.                     {  
50.                         //此处去掉拼接处最后一个","  
51.                         columnsUpdateSql = columnsUpdateSql.Remove(columnsUpdateSql.Length - 1);  
52.                     }  
53.                     //此处生成where条件语句  
54.                     string limitSql = ("[" + table.Columns[0].ColumnName + "]" + "=@" + table.Columns[0].ColumnName);  
55.                     SqlCommand updateCmd = new SqlCommand(string.Format(" UPDATE [{0}] SET {1} WHERE {2} ", _tableName, columnsUpdateSql, limitSql));  
56.                     //不修改源DataTable     
57.                     updateCmd.UpdatedRowSource = UpdateRowSource.None;  
58.                     da.UpdateCommand = updateCmd;  
59.                     da.UpdateCommand.Parameters.AddRange(paras);  
60.                     //da.UpdateCommand.Parameters.Add("@" + table.Columns[0].ColumnName, table.Columns[0].ColumnName);  
61.                     //每次往返处理的行数  
62.                     da.UpdateBatchSize = table.Rows.Count;  
63.                     result = da.Update(ds, _tableName);  
64.                     ds.AcceptChanges();  
65.                     tran.Commit();  
66.   
67.                 }  
68.                 catch(Exception ex)  
69.                 {  
70.                     tran.Rollback();  
71.                     throw ex;  
72.                 }  
73.                 finally  
74.                 {  
75.                     sqlconn.Dispose();  
76.                     sqlconn.Close();  
77.                 }  
78.   
79.   
80.             }