Use sqlcommandbuilder to update a database

Address: http://blog.sina.com.cn/s/blog_5252e0900100aobl.html

generally, 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.

But someProgramUsers do not like to use the designer, or do not need to drag sqldataadapter to implement such a large thing in some places, then sqldataadapter will not automatically generate related query or update statements. therefore, when SQL dataadapter is executed. when updating (DS) Statements, 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 data base, there is no need to update the database every time.

2. Usage

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 to use

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, the sqldataadapter only updates data to the database and does not read data. That is to say, it does not read data from the database.
If you update a table, an error may occur when you call update. In this case, you can use sqlcommandbuilder.

4. Notes

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 be of the image type.

5. Advantages

SaveCodeThis method can replace all: update/delete/insert operation statements.

6. Disadvantages

To access the database twice (select * tablename, that is, to confirm the table, unless it is a large amount of data,
Generally, it cannot be felt.) The efficiency is slow.