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

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

 SqlBulkCopy DTS = new System.Data.SqlClient.SqlBulkCopy(con);
                DTS.NotifyAfter = 1;
                DTS.DestinationTableName = datatable.name;
                DTS.BulkCopyTimeout = 60000000;
                DTS.WriteToServer(datatable);
                succ = dtSource.Rows.Count.ToString();

2.SqlCommandBuilder update 批量修改的问题

 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           }  

装载 http://blog.csdn.net/liudong8510/article/details/17000997