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
