C # Use SqlDataAdapte to Perform Batch Data Operations on DataTable,

C # Use SqlDataAdapte to Perform Batch Data Operations on DataTable,

C # using SqlDataAdapte to Perform Batch Data Operations on DataTable can greatly simplify the amount of code used to operate data. We almost don't need to loop or care about whether users add or modify data, no need to write new, modify, or delete SQL statements. The adapters help us handle the SQL statements well in the background.

If you want to use DataAdapter to edit or delete data through the SQL Server Stored Procedure, make sure that you do not use SET NOCOUNT ON in the stored procedure definition. This will make the number of affected rows returned zero, and DataAdapter will interpret it as a concurrency conflict. In many cases, the order in which changes made via DataSet are sent to the data source is very important. For example, if the primary key value of an existing row is updated and a new row with the new primary key value as the foreign key is added, you must process the update before processing the insert operation. You can use the Select method of DataTable to return a DataRow array that only references a specific RowState. Then, you can pass the returned DataRow array to the Update method of DataAdapter to Process modified rows. You can control the order of insert, update, and delete operations by specifying the subset of rows to be updated. The following code processes deleted rows in the table, updated rows, and inserted rows first.

1 DataTable table = dataSet. tables ["MERs"]; 2 // The first step is to process the deletion. 3 adapter. update (table. select (null, null, DataViewRowState. deleted); 4 // process the Update 5 adapter. update (table. select (null, null, 6 DataViewRowState. modifiedCurrent); 7 // added 8 adapters for final processing. update (table. select (null, null, DataViewRowState. added ));

Note that calling AcceptChanges for DataSet, able, or DataRow will overwrite all Original values of DataRow by the Current value of DataRow. If you modify the field value that uniquely identifies the row, after you call AcceptChanges, the Original value does not match the value in the data source. AcceptChanges is automatically called for each row during the Update method of DataAdapter. During the Update method call, you can retain the original value by setting the AcceptChangesDuringUpdate attribute of DataAdapter to false or creating an event handler for the RowUpdated event and setting the Status to SkipCurrentRow. (From MSDN)

1. Strongly Typed Dataset

After the database table is designed, you can use VS to connect to the database and drag and drop the myUser table to the Dataset Designer (renamed as dsUser). By default, the data assembly automatically generates the Fill method, here we need to manually delete the table and only retain the table structure. We can customize the data query and operation logic. as shown in:

2. uidesign

Create a new form. After compilation, drag and drop the dsUser strong data set created in the previous step to the form from the toolbar, and add a BindingSource control (mainly used to bind the data of the database and DataSet in the DataGridView dview)

In BindingSource, select the data source DataSource as dsUser, and then select a table myUser in the dataset to bind:

Set the data source of the DataGridView to bindingSource. In this way, bindingSource1 is used to bind the data of the DataGridView and dsUser, that is, operate on the DataGridView interface, it can be synchronized to dsUser (mark each line)

3. Data Operation Method

You can use SqlDataAdapter to update databases in the able in batches. You can also delete, modify, and add databases in the table. In the following code, use the select * from {0} where 1 = 2 query statement to provide the architecture for SqlDataAdapter:

// Generate the schema string selectSQL = string. Format ("select * from {0} where 1 = 2", dt. TableName); SqlDataAdapter sda = new SqlDataAdapter (selectSQL, ConnectionString)

Then, use SqlCommandBuilder to automatically create new/delete/modify commands, so that you can use the sda. Update () method to batch operate data tables:

1 SqlCommandBuilder scb  =  new SqlCommandBuilder(sda);2 sda.Update(dt);

The complete code is as follows:

1 public static void UpdateDataSet (DataSet ds, string tableName) 2 {3 try 4 {5 if (tableName = "") 6 {7 throw new ArgumentNullException ("tableName cannot be blank"); 8} 9 // generate architecture 10 string selectSQL = string. format ("select * from {0} where 1 = 2", tableName); 11 using (SqlDataAdapter sda = new SqlDataAdapter (selectSQL, ConnectionString )) 12 {13 14 SqlCommandBuilder scb = new SqlCommandBuilder (sda); 15 16 sda. updateCommand = scb. getUpdateCommand (); 17 sda. insertCommand = scb. getInsertCommand (); 18 sda. deleteCommand = scb. getDeleteCommand (); 19 20 sda. update (ds, tableName); 21 ds. tables [tableName]. acceptChanges (); 22 DisposeCommand (sda. updateCommand); 23 DisposeCommand (sda. insertCommand); 24 DisposeCommand (sda. deleteCommand); 25 sda. dispose (); 26 scb. dispose (); 27} 28} 29 catch (SqlException e) 30 {31 throw e; 32} 33}View Code

To ensure that the data is synchronized to the dataset immediately after the user completes editing, you must call the EndEdit method of bindingSource to synchronize the data.

1 private void dataGridView1_CellEndEdit(object sender, DataGridViewCellEventArgs e)2 {3    this.bindingSource.EndEdit();4 }

When the form is loaded, the refresh method is called, and the program automatically binds the data to the DataGridView:

1 private bool fnRefresh()2 {3     string sql = string.Format("select * from myUser");4     CM.Products.DataAccess.SqlHelper.GetDataTableBySQL(this.dsUser.myUser, sql);5     return true;6 }

You can use the EndEdit method of the DataGridView to determine whether the current user has completed editing:

1 bool isEnd= this.dataGridView1.EndEdit();

In addition, you can use the GetChanges () method to obtain the modified data and return a DataTable.

1 DataTable dtModify = this.dsUser.myUser.GetChanges(DataRowState.Modified);

Call the UpdateTable method to batch process tables (one table here. If multiple tables exist, you need to specify the table name ):

1 private bool Save () 2 {3 try 4 {5 DataAccess. sqlHelper. updateDataSet (this. dsUser, "myUser"); 6 this. dsUser. myUser. acceptChanges (); // submit database 7 return true; 8} 9 catch (Exception ex) 10 {11 return false; 12} 13 14}

When you select a cell, you will get the index of the current row and then call the RemoveAt () method to delete it. Because bindingSource is bound, you can directly call save () method To save data:

 1 private void bindingNavigatorDeleteItem_Click(object sender, EventArgs e) 2 { 3     if (this.dataGridView1.CurrentRow != null) 4     { 5         int index = this.dataGridView1.CurrentRow.Index; 6         if (index > -1) 7         { 8             this.dataGridView1.Rows.RemoveAt(index); 9             Save();10         }11     }12 }

When you click the Add button, we create a strong myUserRow and attach the default value (especially the primary key) to it)

 1 //add row 2 private void toolStripButton1_Click(object sender, EventArgs e) 3 { 4     Common.dsUser.myUserRow dr = this.dsUser.myUser.NewmyUserRow(); 5     dr["ID"] = System.Guid.NewGuid().ToString(); 6     dr["Name"] = "Name"; 7     dr["AddTime"] = DateTime.Now.ToString(); 8  9     this.dsUser.myUser.AddmyUserRow(dr);10 }