Sponsored By Aspose - File Format APIs for .NET

Aspose are the market leader of .NET APIs for file business formats – natively work with DOCX, XLSX, PPT, PDF, MSG, MPP, images formats and many more!

Enterprise Library 2.0 DAAB and ADO.NET 2.0 Batch Updates Feature

ADO.NET 2.0 Batch Updates is a new feature that helps eliminate the number of roundtrips taken by the SqlDataAdapter when it issues commands against the database.  Rather than the fixed one round trip per command in previous versions of ADO.NET, the SqlDataAdapter in ADO.NET 2.0 has a new property, called UpdateBatchSize, that allows you to specify the number of commands that will be packaged together and sent to the database in a single round trip.  In theory, this is supposed to increase the performance of your Data Access Layer by decreasing the number of roundtrips to the database ( Avoid Chatty Interfaces Between the Tiers in Your Application ).


Unfortunately, the Enterprise Library 2.0 Data Access Application Block doesn’t provide us a nice overload of the UpdateDataSet method where we can specify a value for UpdateBatchSize, because it works with the abstract DbDataAdapter Class, which doesn’t know anything about UpdateBatchSize.  Hence, UpdateDataSet will always use the chatty one round trip per command like in earlier versions of ADO.NET.


If you want to use the new Batch Updates feature, the only course of action is to access the SqlDataAdapter directly ( assuming you are using SQL Server ) as such:


 

Database northwind = DatabaseFactory.CreateDatabase();
SqlDataAdapter adapter
= (SqlDataAdapter)northwind.GetDataAdapter();
adapter.UpdateBatchSize
= 10;

 


and then use the SqlDataAdapter like normal to update the Database with the changes in the DataSet in question.


Here is an example that simulates a change to Orders in Northwind using an UpdateBatchSize = 2.


 

Database northwind = DatabaseFactory.CreateDatabase();

DbCommand command = northwind.GetSqlStringCommand
(
SELECT [OrderID],[OrderDate] FROM Orders WHERE CustomerID = @CustomerID);
northwind.AddInParameter(command,
@CustomerID, DbType.String, QUICK);

DataSet orders = new DataSet();

northwind.LoadDataSet(command, orders, orders);

// Simulate Change
for (int i = 0; i < orders.Tables[0].Rows.Count; i++)
{
DateTime orderDate
= (DateTime)orders.Tables[0].Rows[i][OrderDate];
orders.Tables[
0].Rows[i][OrderDate] = orderDate;
}





SqlDataAdapter adapter
= (SqlDataAdapter)northwind.GetDataAdapter();
adapter.UpdateBatchSize
= 2;

DbCommand updateCommand = northwind.GetSqlStringCommand(
Update [Orders] Set OrderDate = @OrderDate WHERE OrderID = @OrderID);

northwind.AddInParameter(updateCommand,
@OrderID, DbType.String,
OrderID, DataRowVersion.Current);

northwind.AddInParameter(updateCommand,
@OrderDate, DbType.DateTime,
OrderDate, DataRowVersion.Current);

updateCommand.UpdatedRowSource
= UpdateRowSource.None;

updateCommand.Connection = northwind.CreateConnection();

adapter.UpdateCommand = (SqlCommand)updateCommand;

adapter.Update(orders, orders);


 


It really isn’t that much more work, but a nice overload to UpdateDataSet accepting the UpdateBatchSize would have been ideal if they could have gotten it to work somehow.


 


Drinking:  Sencha Green Tea


 

This entry was posted in Uncategorized. Bookmark the permalink. Follow any comments here with the RSS feed for this post.

One Response to Enterprise Library 2.0 DAAB and ADO.NET 2.0 Batch Updates Feature

  1. sahilmalik says:

    David,

    Very good post. :)

    SM

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>