C# 批量插入和更新数据

 对于大量的插入操作,可以利用一个空的DataTable加入要插入的行,达到一定数量提交后清空该表就行了,
实现起来并不算复杂:

          

3. DateTime begin = DateTime.Now;
4. string connectionString = ......;
5. using(SqlConnection conn = new SqlConnection(connectionString))...{
6.     conn.Open();
7.     SqlDataAdapter sd = new SqlDataAdapter();
8.     sd.SelectCommand = new SqlCommand("select devid,data_time,data_value from CurrentTest", conn);
9.     sd.InsertCommand = new SqlCommand("insert into CurrentTest (devid,data_time,data_value) "
10.                     + " values (@devid,@data_time,@data_value);", conn);
11.     sd.InsertCommand.Parameters.Add("@devid", SqlDbType.Char, 18, "devid");
12.     sd.InsertCommand.Parameters.Add("@data_time", SqlDbType.Char, 19, "data_time");
13.     sd.InsertCommand.Parameters.Add("@data_value", SqlDbType.Int, 8, "data_value");
14.     sd.InsertCommand.UpdatedRowSource = UpdateRowSource.None;
15.     sd.UpdateBatchSize = 0;
17.     DataSet dataset = new DataSet();
18.     sd.Fill(dataset);
19.     Random r = new Random(1000);
20.     for (int i = 0; i < 100000; i++) ...{
21.         object[] row = ...{"DEVID"+i,DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"),r.Next(1,1000) };
22.         dataset.Tables[0].Rows.Add(row);
23.         if (i % 300 == 0) ...{
24.             sd.Update(dataset.Tables[0]);
25.             dataset.Tables[0].Clear();
26.         }
27.     }
28.     sd.Update(dataset.Tables[0]);
29.     dataset.Tables[0].Clear();
30.     sd.Dispose();
31.     dataset.Dispose();
32.     conn.Close();
33.    
34. }
35. TimeSpan ts = DateTime.Now - begin;
36. MessageBox.Show("ts = " + ts.TotalMilliseconds);

对于这个测试我插入10万条数据用时28秒.性能还算可圈可点.但是对于批量更新,搜遍全球的例子,都是把记录Fill到DataSet中然后牧举rows
来更新,就我这个小数据量的测试而言,把10万条数据Fill到DataSet中已经不能工作,如果是百万,千万如何操作?难道一定先把要批操作的记录
先获取到DataSet中?也就是我要更新哪些记录就要选查询这些记录?

 于是我仍然利用一个空的DataTable来加入要更新的记录:

 

1.  sd.SelectCommand = new SqlCommand("select devid,data_time,data_value from CurrentTest where 1=0", conn);
2.  //1=0的条件保证取一个空表.
3.  sd.UpdateCommand = new SqlCommand("update CurrentTest set data_time = @data_time,data_value = @data_value where devid = @devid", conn);
4.         sd.UpdateCommand.Parameters.Add("@data_time", SqlDbType.Char, 19, "data_time");
5.         sd.UpdateCommand.Parameters.Add("@data_value", SqlDbType.Int, 4, "data_value");
6.         sd.UpdateCommand.Parameters.Add("@devid", SqlDbType.Char, 20, "devid");
7.         sd.UpdateCommand.UpdatedRowSource = UpdateRowSource.None;
8.         sd.UpdateBatchSize = 0;
10.  for(int i=0;i<300;i++){
11.   ..............................
12.   dataset.Tables[0].Rows.Add(row);
13.  }
14.  sd.Update(dataset.Tables[0]);

 先更新300条试试,如果成功再循环更新所有记录,但提示插入操作需要InsertCommand,因为一个空表然后Add Row操作.这时RowState是Added,如果这时Update到数据库,执行的就是插入操作而无法更新.

 改成:

1.  for(int i=0;i<300;i++){
2.   ..............................
4.  row = {填入初始化的值};
5.   dataset.Tables[0].Rows.Add(row);
6.  }
7.  dataset.AcceptChanges();
8.  for(int i=0;i<300;i++){
9.   ..............................
10.   dataset.Tables[0].Rows[i][x] = "xxxxxxx";
11.   ..............................
12.  }
13.  sd.Update(dataset.Tables[0]);

 先在DataTable中插入数据,然后用AcceptChanges(),修改RowState为UnChanged,再修改表中数据希望改变UnChanged状态,即将DataTable从Current状态改为Original,然后再对DataTable的Row进行更新,就能使

Update成功.但这样做确实不方便.

 调整思路,先从数据库中取200条(批更新的Size大小),直接得到一个Original的DataTable.
 sd.SelectCommand = new SqlCommand("select top 200 devid,data_time,data_value from CurrentTest", conn);
 DataSet dataset = new DataSet();
        sd.Fill(dataset);
 用这200个空间来放要更新的其它数据看看:
 

1.                     for (int i = 0; i < 100; i++)
2.                     {
3.                         dataset.Tables[0].Rows[i].BeginEdit();
4.                         dataset.Tables[0].Rows[i]["data_time"] = "2222-22-22 22:22:22";
5.                         dataset.Tables[0].Rows[i]["data_value"] = 100;
6.                         dataset.Tables[0].Rows[i]["devid"] = "DEVID"+(i+10000);//更新DEVID10000到DEVID10200的记录
7.                         dataset.Tables[0].Rows[i].EndEdit();
8.                     }
9.                     sd.Update(dataset.Tables[0]);

 OK,成功,哈哈.把要更新的数据不断往这个空间填,填满就提交,这样更新100000条数据只要几个循环就行了.        

 

1. DateTime begin = DateTime.Now;
2.             string connectionString = "";
3.             using(SqlConnection conn = new SqlConnection(connectionString))...{
4.                 conn.Open();
6.                 SqlDataAdapter sd = new SqlDataAdapter();
7.                 sd.SelectCommand = new SqlCommand("select top 200 devid,data_time,data_value from CurrentTest", conn);
9.                 DataSet dataset = new DataSet();
10.                 sd.Fill(dataset);
11.                 Random r = new Random(1000);
13.                 sd.UpdateCommand = new SqlCommand("update CurrentTest "
14.                                 + " set data_time = @data_time,data_value = @data_value where devid = @devid", conn);
15.                 sd.UpdateCommand.Parameters.Add("@data_time", SqlDbType.Char, 19, "data_time");
16.                 sd.UpdateCommand.Parameters.Add("@data_value", SqlDbType.Int, 4, "data_value");
17.                 sd.UpdateCommand.Parameters.Add("@devid", SqlDbType.Char, 20, "devid");
18.                 sd.UpdateCommand.UpdatedRowSource = UpdateRowSource.None;
19.                 sd.UpdateBatchSize = 0;
20.                 for (int count = 0; count < 100000;)
21.                 ...{
23.                     for (int i = 0; i < 200; i++,count++)
24.                     ...{
25.                         dataset.Tables[0].Rows[i].BeginEdit();
26.                         dataset.Tables[0].Rows[i]["data_time"] = "2222-22-22 22:22:22";
27.                         dataset.Tables[0].Rows[i]["data_value"] = 100;
28.                         dataset.Tables[0].Rows[i]["devid"] = "DEVID"+count;
29.                         dataset.Tables[0].Rows[i].EndEdit();
30.                     }
31.                     sd.Update(dataset.Tables[0]);
32.                 }
33.   
35.                 dataset.Tables[0].Clear();
36.                 sd.Dispose();
37.                 dataset.Dispose();
38.                 conn.Close();
39.                
40.             }
41.             TimeSpan ts = DateTime.Now - begin;
42.             MessageBox.Show("ts = " + ts.TotalMilliseconds);

 注意上面的更新操作是指在一个十万,百万,千万条记录中我要不断更新其中的记录,这些要更新的记录并不是从头

到尾这样的顺序,只是不断地根据条件更新任何记录,我不可能把成百上千万记录先Fill到ds中然后在ds中Select到

这条记录然后更新,所以每200次更新操作填入一次DataTable中提交,就实现了JDBC的addBat和executeBat操作.

 这个操作更新10万条用了32秒,还算勉强吧.

 KAO,没有更优雅的方法了.只好将就这样用了.