你的位置:首页 > ASP.net教程

[ASP.net教程]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