Using SqlCommandBuilder for batch updates

In general, if you use the designer to drag SqlDataAdapter to the page, there will be no error when sqldataadapter.update (DS) updates occur because the system automatically generates SqlDataAdapter property commands, such as:.  Updatecommane InsertCommand SelectCommand and so on.   But some programmers do not like to use the designer, or some places do not need to drag SqlDataAdapter such a huge object to implement, then SqlDataAdapter will not automatically generate related queries or UPDATE statements. So when the Sqldataadapter.update (DS) statement is executed, the SqlDataAdapter Bridge does not know which table to update. No error.

Workaround:

Using SqlCommandBuilder for batch updates

1. Function:

You can do whatever you do to the dataset at the UI layer and throw it directly to this method, which automatically updates your changes to the database without having to update it every time.

Database

  2. Use method
  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;
}  &NBSP
catch (Exception err)
{ 
   throw new Businessexception (ERR);
}
}

Call this method directly, the point is that the select * from "+strtblname is a must,
Role everyone should also think of, mainly to tell SqlDataAdapter update which table

3. When do I use it?

A. Sometimes when you need to cache, for example, in a product selection interface, select a good product, and edit/delete/update,

Finally, the database is given to the database, not every step, because the customer chooses the product may be edited/deleted n Times

Update operation, if committed every time, not only can cause database conflicts, throw an error, and when the amount of data is very large when the user executes

It's getting a little slow in efficiency.

B. Some interface is such that some interface is such that the requirements must be implemented with the cache, confirm that the previous operation is not submitted to the library, click

The product selection information and other information about the product are submitted when the page is specifically submitted. I've always had this situation.

C. In some cases only updates to the database, not read. That is, not read from the database, SqlDataAdapter also do not know is
Which table has been updated, it is likely that the call to update will go wrong. In this case, you can use SqlCommandBuilder.

4.
Note the point:
1. Only one table can be updated and two or more associated tables cannot be updated
2. A primary key must be in the table
3. Fields in the updated table cannot have the image type

5. Advantages:

Save time by saving code, this method can replace all: Update/delete/INSERT Action statements

6. Disadvantages:
Access two databases (SELECT * TableName, this is the sentence, to confirm which table, unless it is a large amount of data,
Generally not felt), the efficiency is somewhat slow

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

Using SqlCommandBuilder for batch updates