Section 5.5. Batch Updates to Improve Performance

5.5. Batch Updates to Improve Performance

With .NET 2.0, SqlDataAdapter is upgraded to support the use of batch updates. This can dramatically reduce the number of round trips to the database server, and it can reduce the load on the database, greatly improving overall performance.

---

Note: In .NET 2.0 you can update the database in batches, reducing the load on your database server.

---

5.5.1. How do I do that?

To turn on batch updating, change the UpdateBatchSize property of SqlDataAdapter from the default value of 1 to a higher value. This will allow SqlDataAdapter to group its commands into batches.

To begin, create a new Windows application with controls, as shown in Figure 5-24.

Figure 5-24. The Test Batch Updates dialog

The complete program is shown in Example 5-4.

Example 5-4. Batch-update test code

 #region Using directives      using System; using System.Collections; using System.ComponentModel; using System.Data; using System.Data.SqlClient; using System.Drawing; using System.Windows.Forms;      #endregion      namespace DataStats {    partial class Form1 : Form    {       public Form1( )       {          InitializeComponent( );       }            private void ModifyEachRow(DataSet ds)       {          foreach (DataRow row in ds.Tables["Orders"].Rows)          {             row["OrderDate"] = DateTime.Now;          }       }            private void ShowStatistics(SqlConnection conn, ListBox lbox)       {          IDictionary dict = conn.RetrieveStatistics( );          foreach (string key in dict.Keys)          {             lbox.Items.Add(key + " = " + dict[key].ToString( ));          }            }                 private void button1_Click(object sender, EventArgs e)       {          string connString = "Data Source=localhost; Initial Catalog=Northwind;";                connString += "Integrated Security=SSPI";          SqlConnection conn = new SqlConnection(connString);          conn.StatisticsEnabled = true;          conn.Open( );          SqlCommand cmd = new SqlCommand( );          cmd.Connection = conn;               cmd.CommandText = "Select * from Orders";          SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);          SqlCommandBuilder bldr = new SqlCommandBuilder(dataAdapter);          DataSet dataset = new DataSet( ); ;          dataAdapter.Fill(dataset, "Orders"); // create orders table          ModifyEachRow(dataset);          conn.ResetStatistics( ); // start statistics clean          dataAdapter.Update(dataset, "Orders"); // update from the db          ShowStatistics(conn, this.lbNoBatch);          dataAdapter.UpdateBatchSize = 10;          ModifyEachRow(dataset);          conn.ResetStatistics( );          dataAdapter.Update(dataset, "Orders");          ShowStatistics(conn, this.lbBatch);               conn.Close( );            }    } }

5.5.2. What just happened?

The logic behind this code is to retrieve the complete listing from the Orders database into a data set:

cmd.CommandText = "Select * from Orders"; SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd); SqlCommandBuilder bldr = new SqlCommandBuilder(dataAdapter); DataSet dataset = new DataSet( ); ; dataAdapter.Fill(dataset, "Orders"); // create orders table

The SqlCommandBuilder class builds simple update/delete commands for the tables. You will modify each row in the data set by calling ModifyEachRow:

private void ModifyEachRow(DataSet ds) {    foreach (DataRow row in ds.Tables["Orders"].Rows)    {       row["OrderDate"] = DateTime.Now;    } }

This updates OrderDate to the current time. Now you can reset your statistics and see how long it takes dataAdapter to update the data set:

conn.ResetStatistics( );  dataAdapter.Update(dataset, "Orders");  ShowStatistics(conn, this.lbNoBatch);

You pass the statistics to the first listbox. Then you modify the data again, but this time you set UpdateBatchSize to 10, allowing dataAdapter to update the database with batches:

dataAdapter.UpdateBatchSize = 10; ModifyEachRow(dataset); conn.ResetStatistics( ); dataAdapter.Update(dataset, "Orders"); ShowStatistics(conn, this.lbBatch);

This results in far fewer round trips to the database, as shown in Figure 5-25 (the round-trips statistic has been highlighted).

Figure 5-25. Batch updates enabled

5.5.3. Where can I learn more?

To learn more about improving efficiency using batch updates, see the article "ADO.NET 2.0 Feature Matrix" in the MSDN Library.