近日由于项目要求在需要实现中型数据的批量插入和更新，晚上无聊，在网上看到看到这样的一个实现方法，特摘抄过来，以便以后可能用到参考。
一.数据的插入
DateTime begin = DateTime.Now; string connectionString = ......; using(SqlConnection conn = new SqlConnection(connectionString)){ conn.Open(); SqlDataAdapter sd = new SqlDataAdapter(); sd.SelectCommand = new SqlCommand("select devid,data_time,data_value from CurrentTest", conn); sd.InsertCommand = new SqlCommand("insert into CurrentTest (devid,data_time,data_value) " + " values (@devid,@data_time,@data_value);", conn); sd.InsertCommand.Parameters.Add("@devid", SqlDbType.Char, 18, "devid"); sd.InsertCommand.Parameters.Add("@data_time", SqlDbType.Char, 19, "data_time"); sd.InsertCommand.Parameters.Add("@data_value", SqlDbType.Int, 8, "data_value"); sd.InsertCommand.UpdatedRowSource = UpdateRowSource.None; sd.UpdateBatchSize = 0; DataSet dataset = new DataSet(); sd.Fill(dataset); Random r = new Random(1000); for (int i = 0; i < 100000; i++) { object[] row = {"DEVID"+i,DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"),r.Next(1,1000) }; dataset.Tables[0].Rows.Add(row); if (i % 300 == 0) { sd.Update(dataset.Tables[0]); dataset.Tables[0].Clear(); } } sd.Update(dataset.Tables[0]); dataset.Tables[0].Clear(); sd.Dispose(); dataset.Dispose(); conn.Close(); } TimeSpan ts = DateTime.Now - begin; MessageBox.Show("ts = " + ts.TotalMilliseconds);
二.数据的更新
作者原话引出：
对于这个测试我插入10万条数据用时28秒.性能还算可圈可点.但是对于批量更新,搜遍全球的例子,都是把记录Fill到DataSet中然后牧举rows
来更新,就我这个小数据量的测试而言,把10万条数据Fill到DataSet中已经不能工作,如果是百万,千万如何操作?难道一定先把要批操作的记录
先获取到DataSet中?也就是我要更新哪些记录就要选查询这些记录?
于是我仍然利用一个空的DataTable来加入要更新的记录:
sd.SelectCommand = new SqlCommand("select devid,data_time,data_value from CurrentTest where 1=0", conn);
//1=0的条件保证取一个空表.
sd.UpdateCommand = new SqlCommand("update CurrentTest set data_time = @data_time,data_value = @data_value where devid = @devid", conn);
sd.UpdateCommand.Parameters.Add("@data_time", SqlDbType.Char, 19, "data_time");
sd.UpdateCommand.Parameters.Add("@data_value", SqlDbType.Int, 4, "data_value");
sd.UpdateCommand.Parameters.Add("@devid", SqlDbType.Char, 20, "devid");
sd.UpdateCommand.UpdatedRowSource = UpdateRowSource.None;
sd.UpdateBatchSize = 0;
for(int i=0;i<300;i++){
..............................
dataset.Tables[0].Rows.Add(row);
}
sd.Update(dataset.Tables[0]);
先更新300条试试,如果成功再循环更新所有记录,但提示插入操作需要InsertCommand,因为一个空表然后Add Row操作,这时RowState是Added,
如果这时Update到数据库,执行的就是插入操作而无法更新. 改成:
for(int i=0;i<300;i++){
..............................
row = {填入初始化的值};
dataset.Tables[0].Rows.Add(row);
}
dataset.AcceptChanges();
for(int i=0;i<300;i++){
..............................
dataset.Tables[0].Rows[i][x] = "xxxxxxx";
..............................
}
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个空间来放要更新的其它数据看看:
for (int i = 0; i < 100; i++)
{
dataset.Tables[0].Rows[i].BeginEdit();
dataset.Tables[0].Rows[i]["data_time"] = "2222-22-22 22:22:22";
dataset.Tables[0].Rows[i]["data_value"] = 100;
dataset.Tables[0].Rows[i]["devid"] = "DEVID"+(i+10000);//更新DEVID10000到DEVID10200的记录
dataset.Tables[0].Rows[i].EndEdit();
}
sd.Update(dataset.Tables[0]);
OK,成功,哈哈.把要更新的数据不断往这个空间填,填满就提交,这样更新100000条数据只要几个循环就行了.
DateTime begin = DateTime.Now; string connectionString = ""; using(SqlConnection conn = new SqlConnection(connectionString)){ conn.Open(); SqlDataAdapter sd = new SqlDataAdapter(); sd.SelectCommand = new SqlCommand("select top 200 devid,data_time,data_value from CurrentTest", conn); DataSet dataset = new DataSet(); sd.Fill(dataset); Random r = new Random(1000); sd.UpdateCommand = new SqlCommand("update CurrentTest " + " set data_time = @data_time,data_value = @data_value where devid = @devid", conn); sd.UpdateCommand.Parameters.Add("@data_time", SqlDbType.Char, 19, "data_time"); sd.UpdateCommand.Parameters.Add("@data_value", SqlDbType.Int, 4, "data_value"); sd.UpdateCommand.Parameters.Add("@devid", SqlDbType.Char, 20, "devid"); sd.UpdateCommand.UpdatedRowSource = UpdateRowSource.None; sd.UpdateBatchSize = 0; for (int count = 0; count < 100000;) { for (int i = 0; i < 200; i++,count++) { dataset.Tables[0].Rows[i].BeginEdit(); dataset.Tables[0].Rows[i]["data_time"] = "2222-22-22 22:22:22"; dataset.Tables[0].Rows[i]["data_value"] = 100; dataset.Tables[0].Rows[i]["devid"] = "DEVID"+count; dataset.Tables[0].Rows[i].EndEdit(); } sd.Update(dataset.Tables[0]); } dataset.Tables[0].Clear(); sd.Dispose(); dataset.Dispose