C# sql batch updates

Active Oldest Votes. SId ; command. ExecuteNonQuery ;. Jeff Sternal Jeff Sternal I like this approach. Arvand How do you address this? Arvayne Depends on what you are doing, but transactions usually are a good choice. Use a StringBuilder System.

ToString ; cmd. Rob Rob Not if its just integer values — Gunnar Steinn. Not really a SQL Injection issue in this case, but a bad practice. Create a method named BatchUpdate in BatchUpdate. Since we are showing at most ten rows per page, the GridView s Rows collection will have no more than ten items. The four TemplateField input controls are programmatically referenced and their values assigned to the ProductsRow instance s properties.

The batch update algorithm used for this tutorial updates each row in the ProductsDataTable that corresponds to a row in the GridView, regardless of whether the product s information has been changed. While such blind updates aren't usually a performance issue, they can lead to superfluous records if you re auditing changes to the database table. Back in the Performing Batch Updates tutorial we explored a batch updating interface with the DataList and added code that would only update those records that were actually modified by the user.

Feel free to use the techniques from Performing Batch Updates to update the code in this tutorial, if desired. When binding the data source to the GridView through its smart tag, Visual Studio automatically assigns the data source s primary key value s to the GridView s DataKeyNames property.

The code used in BatchUpdate is similar to that used in the BLL s UpdateProduct methods, the main difference being that in the UpdateProduct methods only a single ProductRow instance is retrieved from the architecture.

The code that assigns the properties of the ProductRow is the same between the UpdateProducts methods and the code within the foreach loop in BatchUpdate , as is the overall pattern. To complete this tutorial, we need to have the BatchUpdate method invoked when either of the Update Products buttons is clicked. Create event handlers for the Click events of these two Button controls and add the following code in the event handlers:. First a call is made to BatchUpdate.

Next, the ClientScript property is used to inject JavaScript that will display a messagebox that reads The products have been updated. Take a minute to test out this code. Visit BatchUpdate. Assuming there are no input validation errors, you should see a messagebox that reads The products have been updated. Then from BatchUpdate. This should result in an error when clicking Update Products with the other changes during that batch operation rolled back to their original values.

The BatchUpdate method we just examined retrieves all of the products from the BLL s GetProducts method and then updates just those records that appear in the GridView. This approach is ideal if the GridView does not use paging, but if it does, there may be hundreds, thousands, or tens of thousands of products, but only ten rows in the GridView.

In such a case, getting all of the products from the database only to modify 10 of them is less than ideal. For those types of situations, consider using the following BatchUpdateAlternate method instead:. After the foreach loop completes, products contains one ProductsRow instance for each row in the GridView. Since each of the ProductsRow instances have been added to the products instead of updated , if we blindly pass it to the UpdateWithTransaction method the ProductsTableAdapter will try to insert each of the records into the database.

Instead, we need to specify that each of these rows has been modified not added. The GridView provides built-in per-row editing capabilities, but lacks support for creating fully editable interfaces. As we saw in this tutorial, such interfaces are possible, but require a bit of work. To create a GridView where every row is editable, we need to convert the GridView s fields into TemplateFields and define the editing interface within the ItemTemplate s. In the next tutorial we'll see how to create an interface for batch deleting.

If the row had been modified, it called the appropriate UpdateCommand , InsertCommand , or DeleteCommand , depending on the value of the RowState property for that row. Every row update involved a network round-trip to the database. Starting with ADO. NET 2. Setting the UpdateBatchSize to a positive integer value causes updates to the database to be sent as batches of the specified size. For example, setting the UpdateBatchSize to 10 will group 10 separate statements and submit them as single batch.

Setting the UpdateBatchSize to 0 will cause the DataAdapter to use the largest batch size that the server can handle. Setting it to 1 disables batch updates, as rows are sent one at a time. Executing an extremely large batch could decrease performance.

Therefore, you should test for the optimum batch size setting before implementing your application. The following procedure demonstrates the use of the UpdateBatchSize property. ProductCategory table, and an integer representing the batch size the number of rows in the batch.

Upload the data to the temporary table, then perform the SqlBulkCopy update. Using SqlBulkCopy , upload the datatable's data to the temporary table. Then execute a SQL command to update the main table's data from the temporary table.