For the insertion and update of massive data, ADO.NET is indeed not as good as JDBC. JDBC has a unified model for batch operations. It is very convenient to use:
PreparedStatement ps = conn.prepareStatement("insert or update arg1,args2....");
then you can
for(int i=0;i<1000000000000000;i++){
ps.setXXX(realArg);
.....
ps.addBatch();
if(i%500==0){ //Suppose five hundred items are submitted once
ps.executeBatch();
//clear Parame Batch
}
}
ps.executeBatch();
Such an operation not only brings extremely high performance, but is also very convenient. Ordinarily, in ADO.NET, to implement such a function, the Addbat and CommitBat APIs should be provided directly in the Command interface or the DataAdapter interface, but ADO.NET's But it is not implemented so simply, but requires developers to go through complex workarounds.
For a large number of insert operations, you can use an empty DataTable to add the rows to be inserted, and then clear the table after a certain number of submissions.
It’s not too complicated to implement:
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);
For this test, it took me 28 seconds to insert 100,000 pieces of data. The performance is quite impressive. But for batch updates and examples of searching around the world, the records are filled into the DataSet and then the rows are extracted.
To update, as far as my test with a small amount of data is concerned, filling 100,000 pieces of data into the DataSet no longer works. If it is millions, how do you operate it? Do you have to first get the records to be batched into the DataSet? Medium? In other words, which records I want to update must be selected to query these records?
So I still use an empty DataTable to add the records to be updated:
sd.SelectCommand = new SqlCommand("select devid,data_time,data_value from CurrentTest where 1=0", conn);
//The condition of 1=0 guarantees an empty table.
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]);
Try updating 300 records first. If it succeeds, it will update all records in a loop, but it will prompt that the insertion operation requires InsertCommand because it is an empty table and then Add Row operation. At this time, RowState is Added.
If Update is sent to the database at this time, the insertion operation is performed and cannot be updated. Change to:
for(int i=0;i<300;i++){
............................
row = {fill in initialized value};
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]);
First insert data into the DataTable, then use AcceptChanges() to modify the RowState to UnChanged, and then modify the data in the table to change the UnChanged state.
Change the DataTable from the Current state to Original, and then update the Row of the DataTable, you can use
Update is successful. But it is really inconvenient to do so.
Adjust the idea, first take 200 entries from the database (the size of the batch update), and directly get an Original DataTable.
sd.SelectCommand = new SqlCommand("select top 200 devid,data_time,data_value from CurrentTest", conn);
DataSet dataset = new DataSet();
sd.Fill(dataset);
Use these 200 spaces to put other data to be updated and see:
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);//Update records from DEVID10000 to DEVID10200
dataset.Tables[0].Rows[i].EndEdit();
}
sd.Update(dataset.Tables[0]);
OK, success, haha. Keep filling the data to be updated into this space, and submit it when it is full. In this way, it only takes a few cycles to update 100,000 pieces of data.
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
http://www.cnblogs.com/Seabiscuit/archive/2010/05/25/1743341.html