Visual CSharp 1002005 A Developers Notebook [Electronic resources] نسخه متنی

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.

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).

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.