Updating Data in Batches

In previous versions of ADO.NET, batch updates using DataSets weren't very efficient. The sequence of events using previous versions was as follows:

1.	Retrieve data from the database into a DataSet using the adapter's Fill method.
2.	Data in existing rows is modified, new rows are added, and yet other rows might be deleted.
3.	The adapter's Update() method is called. The adapter then runs through the entire list of rows in the given table(s). For each row that has been inserted, deleted, or updated since the last time the DataSet's AcceptChanges() method was called (or since creation), a single SQL statement is executed to perform the change on the server.

The problem with this scenario is in the case where there are large numbers of changed records in a DataSet. When this happens, the process of creating a new SQL statement, executing it, and then moving to the next updated record in the list adds unnecessary overhead to the update operation.

With the new ADO.NET 2.0 data adapters, you can specify the size of the command batch. If you specify a size of 0, the new data adapter will use the maximum batch size available. If you specify a value greater than 1, the data adapter will perform its update in batches containing the number of statements you indicated.

The only tricky thing to remember about batch updating is that you need to specify UpdateRowSource. None for the UpdatedRowSource property on all batch-involved commands, as shown in Listing 18.4.

Listing 18.4. Performing Batch Updates using a SqlDataAdapter

[View full width]

using System; using System.Data; using System.Data.SqlClient; using System.Collections.Generic; using System.Text; namespace BatchUpdate { class Program { static void Main(string[] args) {     SqlConnection conn = new SqlConnection(         "data source=localhost; initial catalog=SampleDB; Integrated Security=SSPI;");     conn.Open();     SqlCommand selectCmd = conn.CreateCommand();     selectCmd.CommandText = "SELECT ID, FirstName, LastName, MiddleInitial FROM Customers";     SqlCommand updateCmd = conn.CreateCommand();     updateCmd.CommandText = "UPDATE Customers SET FirstName = @FirstName, LastName =  @LastName " +         ", MiddleInitial = @MiddleInitial WHERE ID = @ID";     updateCmd.Parameters.Add(new SqlParameter("@FirstName", SqlDbType.VarChar, 50, "FirstName"));     updateCmd.Parameters.Add(new SqlParameter("@LastName", SqlDbType.VarChar, 50, "LastName"));     updateCmd.Parameters.Add(new SqlParameter("@MiddleInitial", SqlDbType.NChar, 1, "MiddleInitial"));     updateCmd.Parameters.Add(new SqlParameter("@ID", SqlDbType.Int, 4, "ID"));     updateCmd.UpdatedRowSource = UpdateRowSource.None;     SqlCommand insertCommand = conn.CreateCommand();     insertCommand.CommandText = "INSERT INTO Customers(FirstName, LastName, MiddleInitial) " +         "VALUES(@FirstName, @LastName, @MiddleInitial)";     insertCommand.Parameters.Add(new SqlParameter("@FirstName", SqlDbType.VarChar, 50, "FirstName"));     insertCommand.Parameters.Add(new SqlParameter("@LastName", SqlDbType.VarChar, 50, "LastName"));     insertCommand.Parameters.Add(new SqlParameter("@MiddleInitial", SqlDbType.NChar, 1, "MiddleInitial"));     insertCommand.UpdatedRowSource = UpdateRowSource.None; // required for batching     SqlDataAdapter da = new SqlDataAdapter(selectCmd) ;     da.UpdateCommand = updateCmd;     da.InsertCommand = insertCommand;     da.UpdateBatchSize = 10;     DataSet ds = new DataSet();     da.Fill(ds, "Customers");     // change the first customer     ds.Tables[0].Rows[0]["FirstName"] = "Modified!";     // add a couple customers     DataRow newCust = ds.Tables[0].NewRow();     newCust["FirstName"] = "Batch1";     newCust["LastName"] = "Batch";     newCust["MiddleInitial"] = 'Q';     ds.Tables[0].Rows.Add(newCust);     newCust = ds.Tables[0].NewRow();     newCust["FirstName"] = "Batch2";     newCust["LastName"] = "Batch";     newCust["MiddleInitial"] = 'j';     ds.Tables[0].Rows.Add(newCust);     da.Update(ds, "Customers");     Console.WriteLine("Batch update performed.");     Console.ReadLine(); } } } 

The preceding code modifies an existing row and then creates two additional rows. In previous versions of ADO.NET, this would generate three separate command executions, including the latency involved with starting and finishing each command. In ADO.NET 2.0, the preceding code will only issue one command to SQL Server. The command issued to SQL Server will contain one UPDATE statement and two INSERT statements. If you want to see how this works, you can easily watch how the statements are sent to SQL Server using SQL administration tools.