Batch update using SqlCommandBuilder

This is generally the case. If you use the designer to drag the SqlDataAdapter to the page, the SqlDataAdapter will not appear. an error occurred during Update (ds) Update because the system automatically generates the attribute command of SqlDataAdapter, for example :. updateCommane insertCommand selectCommand. However, some programmers do not like to use the designer, or do not need to drag SqlDataAdapter to implement such a large thing in some places. Therefore, SqlDataAdapter will not automatically generate related query or update statements. therefore, when SQL dataadapter is executed. when the Update (ds) Statement is run, the SqlDataAdapter bridge does not know which table to Update. no error is reported.

Solution:

Batch update using SqlCommandBuilder

1. functions:

After you perform any operations on DataSet on the UI Layer, you can directly drop this method. This method can automatically update your modifications to the database, instead of updating them to the database every time.

Database

2. Usage

Copy to ClipboardReference: [www.bkjia.com] public int UpdateByDataSet (DataSet ds, string strTblName, string strConnection)
{
Try
{
SqlConnection conn = new SqlConnection (strConnection ));
SqlDataAdapter myAdapter = new SqlDataAdapter ();
SqlCommand myCommand = new SqlCommand ("select * from" + strTblName), (SqlConnection) this. conn );
MyAdapter. SelectCommand = myCommand;
SqlCommandBuilder myCommandBuilder = new SqlCommandBuilder (myAdapter );
MyAdapter. Update (ds, strTblName );
Return 0;
}
Catch (BusinessException errBU)
{
Throw errBU;
}
Catch (Exception err)
{
Throw new BusinessException (err );
}
}

You can call this method directly. It indicates that select * from "+ strTblName is a must. You should also think about the function, mainly to tell SqlDataAdapter which table to update.

3. When can I use it?

A. When caching is required, for example, on the product selection page, select a product and edit, delete, or update the product,

The database is finally handed over to the database, instead of accessing the database for each step, because the customer may make n edits/deletes the selected item.

The update operation, if submitted every time, not only may cause database conflicts and errors, but also when the data volume is large

Efficiency also slows down

B. Some interfaces are like this, And the requirements must be implemented using the cache. confirm that the previous operations are not submitted to the database, and click

When the buttons are submitted on the page, the product selection information and other information are submitted.

C. In some cases, I only Update the database and do not read it. That is to say, if I do not read it from the database, the SqlDataAdapter does not know which table to Update. Therefore, an error may occur when I call Update. In this case, you can use SqlCommandBuilder.

4. Note: 1. Only one table can be updated. Two or more associated tables cannot be updated. 2. The table must have a primary key. 3. The fields in the updated table cannot have an image type.

5. Advantages:

This method can replace all the update/delete/insert operation statements to save the amount of code and time.

6. disadvantage: the efficiency is a little slow when you access the database twice (select * TableName, which is the table to be determined, unless it is a large amount of data, which is generally invisible ).