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

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

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

2.SqlCommandBuilder    update 批量修改的问题

               DataSet ds = new DataSet();
             ds.Tables.Add(table);
             string _tableName = table.TableName;
             int result = ;
            using (SqlConnection sqlconn = new SqlConnection(db.ConnectionString))
             {
                 sqlconn.Open();  

                 //使用加强读写锁事务
                SqlTransaction tran = sqlconn.BeginTransaction(IsolationLevel.ReadCommitted);
                 try
                 {  

                     ds.Tables[].AcceptChanges();
                     foreach (DataRow dr in ds.Tables[].Rows)
                     {
                        //所有行设为修改状态
                         dr.SetModified();
                    }
                    //为Adapter定位目标表      

                     SqlCommand cmd = new SqlCommand(string.Format("select * from {0} where {1}", _tableName, " 1=2"), sqlconn, tran);
                    SqlDataAdapter da = new SqlDataAdapter(cmd);
                    SqlCommandBuilder sqlCmdBuilder = new SqlCommandBuilder(da);
                    sqlCmdBuilder.ConflictOption = ConflictOption.OverwriteChanges;
                     da.AcceptChangesDuringUpdate = false;
                    string columnsUpdateSql = "";
                    SqlParameter[] paras = new SqlParameter[table.Columns.Count];
                    int parasIndex = ;
                     //需要更新的列设置参数是,参数名为"@+列名"
                     for (int i = ; i < table.Columns.Count; i++)
                    {
                        //此处拼接要更新的列名及其参数值
                         columnsUpdateSql += ("[" + table.Columns[i].ColumnName + "]" + "=@" + table.Columns[i].ColumnName + ",");
                         if (table.Columns[i].DataType.Name == "DateTime")
                        {
                             paras[i] = new SqlParameter("@" + table.Columns[i].ColumnName, SqlDbType.DateTime, , table.Columns[i].ColumnName);
                         }
                        else if (table.Columns[i].DataType.Name == "Int64")
                        {
                             paras[i] = new SqlParameter("@" + table.Columns[i].ColumnName, SqlDbType.NVarChar, , table.Columns[i].ColumnName);
                        }
                        else
                        {
                             paras[i] = new SqlParameter("@" + table.Columns[i].ColumnName, SqlDbType.NVarChar, , table.Columns[i].ColumnName);
                        }
                     }
                    if (!string.IsNullOrEmpty(columnsUpdateSql))
                     {
                        //此处去掉拼接处最后一个","
                        columnsUpdateSql = columnsUpdateSql.Remove(columnsUpdateSql.Length - );
                     }
                     //此处生成where条件语句
                    string limitSql = ("[" + table.Columns[].ColumnName + "]" + "=@" + table.Columns[].ColumnName);
                   SqlCommand updateCmd = new SqlCommand(string.Format(" UPDATE [{0}] SET {1} WHERE {2} ", _tableName, columnsUpdateSql, limitSql));
                     //不修改源DataTable
                    updateCmd.UpdatedRowSource = UpdateRowSource.None;
                    da.UpdateCommand = updateCmd;
                     da.UpdateCommand.Parameters.AddRange(paras);
                     //da.UpdateCommand.Parameters.Add("@" + table.Columns[0].ColumnName, table.Columns[0].ColumnName);
                     //每次往返处理的行数
                    da.UpdateBatchSize = table.Rows.Count;
                     result = da.Update(ds, _tableName);
                     ds.AcceptChanges();
                     tran.Commit();  

                 }
                catch(Exception ex)
                 {
                    tran.Rollback();
                    throw ex;
                }
                 finally
                {
                     sqlconn.Dispose();
                     sqlconn.Close();
                }
           }  

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