SqlCommandBuilder can add and modify data in batches

First, brief

In the project there is a need to import the packet, the packet is an XML file, which stores the data, then read the file form a DataTable, and then how to put the data of the DataTable into the database in bulk into it? With a loop? Although the cycle is simple, but summed up a bit awkward, so still use SqlCommandBuilder, sqldataadapter.update to BULK insert data bar.

Second, the content

First the original data of the database

Then code that inserts the data in bulk

    Private stringCon ="Data Source=.;i Nitial catalog=testdatabase;integrated security=true"; Private voiddatabatchupdate () {//false DataDataTable dt =NewDataTable (); Dt. Columns.Add ("ID",typeof(int)); Dt. Columns.Add ("testname"); DataRow Dr=dt.        NewRow (); dr["ID"] ="1"; dr["testname"] ="In vain"; Dt.        Rows.Add (DR); SqlConnection Sqlcon=NewSqlConnection (con); SqlDataAdapter SqlDa=NewSqlDataAdapter ("SELECT * from Testdatatable", Sqlcon); DataSet Sqlds=NewDataSet ();        Sqlda.fill (Sqlds); //Database DataDataTable databasedata = Sqlds. tables[0];        Databasedata.merge (DT); SqlCommandBuilder SQLCB=NewSqlCommandBuilder (sqlDa);    Sqlda.update (Databasedata); }

Data, there's a new piece of data in it.

The main point here is to merge the two table tables and then update to the data by SqlCommandBuilder and update (). However, the function update () updates the table according to the RowState of the DataTable. When merging two tables with merge, the RowState of the rows is like

Here, first add a few lines of code to the base of the original code

    Private stringCon ="Data Source=.;i Nitial catalog=testdatabase;integrated security=true"; Private voiddatabatchupdate () {//false DataDataTable dt =NewDataTable (); Dt. Columns.Add ("ID",typeof(int)); Dt. Columns.Add ("testname"); DataRow Dr=dt.        NewRow (); dr["ID"] ="1"; dr["testname"] ="In vain"; Dt.        Rows.Add (DR); SqlConnection Sqlcon=NewSqlConnection (con); SqlDataAdapter SqlDa=NewSqlDataAdapter ("SELECT * from Testdatatable", Sqlcon); DataSet Sqlds=NewDataSet ();        Sqlda.fill (Sqlds); //Database DataDataTable databasedata = Sqlds. tables[0];        Databasedata.merge (DT); //Show the status of a table without a rowDATABASEDATA.COLUMNS.ADD ("updatedsate");  for(inti =0; i < DatabaseData.Rows.Count; i++) {DataRow Updatedrow=Databasedata.rows[i]; updatedrow["updatedsate"] =updatedrow.rowstate; }        //SqlCommandBuilder SQLCB = new SqlCommandBuilder (sqlDa); //sqlda.update (databasedata);Griddata.datasource=Databasedata;    Griddata.databind (); }

The result shows that the last state is Added, so it is inserted into the database as new data.

So, if you're just inserting data in bulk,

New SqlDataAdapter ("", Sqlcon);

It is also possible to write this, that is, to get the structure of the database table.

New SqlDataAdapter ("select * from testdatatable where 1=2", Sqlcon);

So, if just a simple bulk INSERT database is much simpler, but the project's Import packet function, if the user is repeated import it? Or is there a few cells in the imported data that are different from the data in the database? How does this work? Well?? Write a few lines of code in the code to determine whether the imported data exists in the database table, the data in the database table is deleted, and then inserted into the new?  - - ! Obviously not reliable.

Later found that there is a very magical method, because the code of update () is based on RowState to determine whether or not to add and remove, the above only used the "increase", so long as the rowsstate is "change" on it.

First code

    Private stringCon ="Data Source=.;i Nitial catalog=testdatabase;integrated security=true"; Private voiddatabatchupdate () {//false DataDataTable dt =NewDataTable (); Dt. Columns.Add ("ID",typeof(int)); Dt. Columns.Add ("testname"); DataRow Dr=dt.        NewRow (); dr["ID"] ="1"; dr["testname"] ="In vain"; Dt.        Rows.Add (DR); SqlConnection Sqlcon=NewSqlConnection (con); SqlDataAdapter SqlDa=NewSqlDataAdapter ("SELECT * from Testdatatable", Sqlcon); DataSet Sqlds=NewDataSet ();        Sqlda.fill (Sqlds); //Database DataDataTable databasedata = Sqlds. tables[0]; Databasedata.primarykey=NewDatacolumn[] {databasedata.columns["ID"] };//set the primary key for a DataTabledatabasedata.merge (DT); //Show the status of a table without a rowDATABASEDATA.COLUMNS.ADD ("updatedsate");  for(inti =0; i < DatabaseData.Rows.Count; i++) {DataRow Updatedrow=Databasedata.rows[i]; updatedrow["updatedsate"] =updatedrow.rowstate; } SqlCommandBuilder SQLCB=NewSqlCommandBuilder (sqlDa);        Sqlda.update (Databasedata); Griddata.datasource=Databasedata;    Griddata.databind (); }

The key is to set the primary key for the DataTable, then the merge () will be automatically modified, its rowstate become "changed", and the name will change.

"= modified" = Before modification

This enables the automatic batch update of the data under the same ID. Note: When update data is updated, the structure of the database table must have a primary key.

SqlCommandBuilder can add and modify data in batches