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
