Bulk INSERT and update of massive data in C #

For the massive data inserts and updates, ADO. NET is not really as good as JDBC, JDBC has a unified model for batch operations. Use it.
Very convenient:
PreparedStatement PS = conn.preparestatement ("Insert or update arg1,args2 ...");
And then you can
for (int i=0;i<1000000000000000;i++) {
Ps.setxxx (REALARG);
.....
Ps.addbatch ();
if (i%500==0) {//Suppose 500 submit once
Ps.executebatch ();
Clear Parame Batch
}
}
Ps.executebatch ();

This operation not only brings extremely large performance, but also very convenient. Ordinarily, ADO. NET, you should implement this function directly in the command interface
or DataAdapter interface to provide Addbat and Commitbat APIs, but ado.net is not so simple to implement, but to require developers to pass
Complex workaround.
For a large number of inserts, you can use an empty datatable to add the rows you want to insert, and then empty the table after a certain number of submissions.
Implementation is not complicated:

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, "Devid");
Sd. INSERTCOMMAND.PARAMETERS.ADD ("@data_time", SqlDbType.Char, "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 I inserted 100,000 data for 28 seconds. The performance is fairly remarkable. But for batch updates, search all over the world for examples that fill the dataset and then herd rows.
To update, in my small data volume test, the 100,000 data into the dataset has not been able to work, if it is million, how to operate? must first take the record of the batch operation
Get to the dataset first? What records do I have to update to select these records?

So I still use an empty DataTable to add the record to be updated:

 sd. SelectCommand = new SqlCommand ("Select Devid,data_time,data_value from Currenttest where 1=0", conn); The
 //1=0 condition guarantees that an empty table is taken.
 sd. UpdateCommand = new SqlCommand ("update currenttest set data_time = @data_time, Data_value = @data_value where Devid = @devi D ", conn);
        SD. UPDATECOMMAND.PARAMETERS.ADD ("@data_time", SqlDbType.Char, "data_time");
        SD. UPDATECOMMAND.PARAMETERS.ADD ("@data_value", SqlDbType.Int, 4, "Data_value");
        SD. UPDATECOMMAND.PARAMETERS.ADD ("@devid", SqlDbType.Char, "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]);
First update 300 to try, if you successfully recycle all the records, but the prompt insert operation requires InsertCommand, because an empty table and then add row operation. Then RowState is added, and if you update to the database, you do the insert operation and you cannot update it. .

Change into:
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 the data in the DataTable, then use AcceptChanges (), modify RowState as unchanged, and then modify the data in the table to change the unchanged state. Changing the DataTable from current state to original and then updating the row of the DataTable enables

Update succeeded. But it's really inconvenient.

Adjust the idea, first from the database to take 200 (batch update size), directly to get a original DataTable.
Sd. SelectCommand = new SqlCommand ("Select Devid,data_time,data_value from Currenttest", conn);
DataSet DataSet = new DataSet ();
Sd. Fill (DataSet);
Use these 200 spaces to put the other data you want to update to see:

for (int i = 0; i < 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 DEVID10000 to DEVID10200 Records
DataSet. Tables[0]. Rows[i]. EndEdit ();
}
Sd. Update (DataSet. Tables[0]);
OK, success, haha. Keep the data to be updated into this space, fill it up and submit it, so you can update the 100,000 data with just a few loops.

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);

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)