Evjen c08.tex V2 - 01/28/2008 2:05pm Page 436
Chapter 8: Data Management with ADO.NET
Method Description
WaitAll (waitHandles
,
milliseconds
,
exitContext)
This overloaded method receives the time-out value in the form of
milliseconds and a Boolean value specifying whether the method requires
asynchronous context. It should be set to
False
for asynchronous
processing.
WaitAll (waitHandles
,
timeSpan,
exitContext)
This overloaded method receives the time-out value in the form of
TimeSpan
object. The second parameter receives a Boolean value specifying
whether the method requires asynchronous context. It should be set to
False
for asynchronous processing.
Close ( )
This method releases all wait handles and reclaims their resources.
Now that you understand asynchronous methods added to the
SqlCommand
and how to properly interact
with them, you can write some code to see the asynchronous processing in action.
Approaches of Asynchronous Processing in ADO.NET

You can process asynchronous commands in t hree distinct ways. One approach is to start t he asyn-
chronous process and start polling the
IAsyncResult
object to see when the process has finished. The
second approach is to provide a callback method while starting the asynchronous pro cess. This approach
enables you to perform other tasks in parallel. When the asynchronous process finishes, it fires the
callback method that cleans up after the process and notifies other parts of the program that the asyn-
chronous process has finished. The third and most elegant method is to associate a wait handle with the
asynchronous process. Using this approach, you can start all the asynchronous processing you want and
then wait for all or any of them to finish so that you can process them accordingly.
The Poll Approach
The code shown in Listing 8-31 creates an inline SQL statement to retrieve the top five records from
the Orders table from the Northwind database. It starts the asynchronous process by calling the
Begin-
ExecuteReader
. After the asynchronous process has started, it uses a
while
loop to wait for the process
to finish. While waiting, the main thread sleeps for 10 milliseconds after checking the status of the asyn-
chronous process. After the process has finished, it retrieves the result using the
EndExecuteReader
method.
Listing 8-31: The Poll approach to working with asynchronous commands
VB
<
%@ Page Language="VB" %
>
<
%@ Import Namespace="System.Data" %
>

<
%@ Import Namespace="System.Data.SqlClient" %
>
<
%@ Import Namespace="System.Configuration" %
>
<
script runat="server"
>
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)
Dim DBCon As SqlConnection
Dim Command As SqlCommand = New SqlCommand()
Dim OrdersReader As SqlDataReader
Continued
436
Evjen c08.tex V2 - 01/28/2008 2:05pm Page 437
Chapter 8: Data Management with ADO.NET
Dim ASyncResult As IAsyncResult
DBCon = New SqlConnection()
DBCon.ConnectionString = _
ConfigurationManager.ConnectionStrings("DSN_NorthWind").ConnectionString
Command.CommandText = _
"SELECT TOP 5 Customers.CompanyName, Customers.ContactName, " & _
"Orders.OrderID, Orders.OrderDate, " & _
"Orders.RequiredDate, Orders.ShippedDate " & _
"FROM Orders, Customers " & _
"WHERE Orders.CustomerID = Customers.CustomerID " & _
"ORDER BY Customers.CompanyName, Customers.ContactName"
Command.CommandType = CommandType.Text
Command.Connection = DBCon

DBCon.Open()
’ Starting the asynchronous processing
ASyncResult = Command.BeginExecuteReader()
’ This loop with keep the main thread waiting until the
’ asynchronous process is finished
While Not ASyncResult.IsCompleted
’ Sleeping current thread for 10 milliseconds
System.Threading.Thread.Sleep(10)
End While
’ Retrieving result from the asynchronous process
OrdersReader = Command.EndExecuteReader(ASyncResult)
’ Displaying result on the screen
gvOrders.DataSource = OrdersReader
gvOrders.DataBind()
’ Closing connection
DBCon.Close()
End Sub
<
/script
>
<
html xmlns=" />>
<
head id="Head1" runat="server"
>
<
title
>
The Poll Approach
<

/title
>
<
/head
>
<
body
>
<
form id="form1" runat="server"
>
<
div
>
<
asp:GridView ID="gvOrders" runat="server"
AutoGenerateColumns="False" Width="100%"
>
<
Columns
>
<
asp:BoundField HeaderText="Company Name"
DataField="CompanyName"
><
/asp:BoundField
>
<
asp:BoundField HeaderText="Contact Name"
DataField="ContactName"

><
/asp:BoundField
>
Continued
437
Evjen c08.tex V2 - 01/28/2008 2:05pm Page 438
Chapter 8: Data Management with ADO.NET
<
asp:BoundField HeaderText="Order Date"
DataField="orderdate" DataFormatString="{0:d}"
><
/asp:BoundField
>
<
asp:BoundField HeaderText="Required Date" DataField="requireddate"
DataFormatString="{0:d}"
><
/asp:BoundField
>
<
asp:BoundField HeaderText="Shipped Date" DataField="shippeddate"
DataFormatString="{0:d}"
><
/asp:BoundField
>
<
/Columns
>
<
/asp:GridView

>
<
/div
>
<
/form
>
<
/body
>
<
/html
>
C#
<
%@ Page Language="C#" %
>
<
%@ Import Namespace="System.Data" %
>
<
%@ Import Namespace="System.Data.SqlClient" %
>
<
%@ Import Namespace="System.Configuration" %
>
<
script runat="server"
>
protected void Page_Load(object sender, EventArgs e)

{
SqlConnection DBCon;
SqlCommand Command = new SqlCommand();
SqlDataReader OrdersReader;
IAsyncResult ASyncResult;
DBCon = new SqlConnection();
DBCon.ConnectionString =
ConfigurationManager.ConnectionStrings["DSN_NorthWind"].ConnectionString;
Command.CommandText =
"SELECT TOP 5 Customers.CompanyName, Customers.ContactName, " +
"Orders.OrderID, Orders.OrderDate, " +
"Orders.RequiredDate, Orders.ShippedDate " +
"FROM Orders, Customers " +
"WHERE Orders.CustomerID = Customers.CustomerID " +
"ORDER BY Customers.CompanyName, Customers.ContactName";
Command.CommandType = CommandType.Text;
Command.Connection = DBCon;
DBCon.Open();
// Starting the asynchronous processing
ASyncResult = Command.BeginExecuteReader();
// This loop with keep the main thread waiting until the
// asynchronous process is finished
while (!ASyncResult.IsCompleted)
{
// Sleeping current thread for 10 milliseconds
System.Threading.Thread.Sleep(10);
}
Continued
438
Evjen c08.tex V2 - 01/28/2008 2:05pm Page 439

Chapter 8: Data Management with ADO.NET
// Retrieving result from the asynchronous process
OrdersReader = Command.EndExecuteReader(ASyncResult);
// Displaying result on the screen
gvOrders.DataSource = OrdersReader;
gvOrders.DataBind();
// Closing connection
DBCon.Close();
}
<
/script
>
Ifyousetabreakpointatthe
while
loop, you will be able to see that the code execution continues
after calling the
BeginExecuteReader
method. The code then continues to loop until the asynchronous
execution has finished.
The Wait Approach
The most elegant of the three approaches is neither the poll approach nor the callback approach. The
approach that provides the highest level of flexibility, efficiency, and (admittedly) a bit more complexity
is the wait approach. Using this approach, you can write code that starts multiple asynchronous processes
and waits for any or all the processes to finish running. This approach allows you to wait for only those
processes that are dependent on each other and to proceed with the ones that don’t. This approach, by
its design, requires you to think about asynchronous processes in great detail. You must pick a good
candidate for running in parallel and, most importantly, determine how different processes depend
on each other. The complexity of this approach requires you to understand its details and design the
code accordingly. The end result is, typically, a very elegant code design that makes the best use of
synchronous and asynchronous processing models.

The code shown in Listing 8-32 uses the
WaitOne
method of the
WaitHandle
class. This method causes
the program execution to wait until the asynchronous process has finished running.
Listing 8-32: The wait approach to handling a single asynchronous process
VB
<
%@ Page Language="VB" %
>
<
%@ Import Namespace="System.Data" %
>
<
%@ Import Namespace="System.Data.SqlClient" %
>
<
%@ Import Namespace="System.Configuration" %
>
<
script runat="server"
>
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)
Dim DBCon As SqlConnection
Dim Command As SqlCommand = New SqlCommand()
Dim OrdersReader As SqlDataReader
Dim ASyncResult As IAsyncResult
Dim WHandle As Threading.WaitHandle
DBCon = New SqlConnection()

DBCon.ConnectionString = _
ConfigurationManager.ConnectionStrings("DSN_NorthWind").ConnectionString
Continued
439
Evjen c08.tex V2 - 01/28/2008 2:05pm Page 440
Chapter 8: Data Management with ADO.NET
Command.CommandText = _
"SELECT TOP 5 Customers.CompanyName, Customers.ContactName, " & _
"Orders.OrderID, Orders.OrderDate, " & _
"Orders.RequiredDate, Orders.ShippedDate " & _
"FROM Orders, Customers " & _
"WHERE Orders.CustomerID = Customers.CustomerID " & _
"ORDER BY Customers.CompanyName, Customers.ContactName"
Command.CommandType = CommandType.Text
Command.Connection = DBCon
DBCon.Open()
’ Starting the asynchronous processing
ASyncResult = Command.BeginExecuteReader()
WHandle = ASyncResult.AsyncWaitHandle
If WHandle.WaitOne = True Then
’ Retrieving result from the asynchronous process
OrdersReader = Command.EndExecuteReader(ASyncResult)
’ Displaying result on the screen
gvOrders.DataSource = OrdersReader
gvOrders.DataBind()
’ Closing connection
DBCon.Close()
Else
’ Asynchronous process has timed out. Handle this
’ situation here.

End If
End Sub
<
/script
>
<
html xmlns=" />>
<
head id="Head1" runat="server"
>
<
title
>
The Wait Approach
<
/title
>
<
/head
>
<
body
>
<
form id="form1" runat="server"
>
<
div
>
<

asp:GridView ID="gvOrders" runat="server"
AutoGenerateColumns="False" Width="100%"
>
<
Columns
>
<
asp:BoundField HeaderText="Company Name"
DataField="CompanyName"
><
/asp:BoundField
>
<
asp:BoundField HeaderText="Contact Name"
DataField="ContactName"
><
/asp:BoundField
>
<
asp:BoundField HeaderText="Order Date"
DataField="orderdate" DataFormatString="{0:d}"
><
/asp:BoundField
>
<
asp:BoundField HeaderText="Required Date" DataField="requireddate"
DataFormatString="{0:d}"
><
/asp:BoundField
>

Continued
440
Evjen c08.tex V2 - 01/28/2008 2:05pm Page 441
Chapter 8: Data Management with ADO.NET
<
asp:BoundField HeaderText="Shipped Date" DataField="shippeddate"
DataFormatString="{0:d}"
><
/asp:BoundField
>
<
/Columns
>
<
/asp:GridView
>
<
/div
>
<
/form
>
<
/body
>
<
/html
>
C#
<

%@ Page Language="C#" %
>
<
%@ Import Namespace="System.Data" %
>
<
%@ Import Namespace="System.Data.SqlClient" %
>
<
%@ Import Namespace="System.Configuration" %
>
<
script runat="server"
>
protected void Page_Load(object sender, EventArgs e)
{
SqlConnection DBCon;
SqlCommand Command = new SqlCommand();
SqlDataReader OrdersReader;
IAsyncResult ASyncResult;
System.Threading.WaitHandle WHandle;
DBCon = new SqlConnection();
DBCon.ConnectionString =
ConfigurationManager.ConnectionStrings["DSN_NorthWind"].ConnectionString;
Command.CommandText =
"SELECT TOP 5 Customers.CompanyName, Customers.ContactName, " +
"Orders.OrderID, Orders.OrderDate, " +
"Orders.RequiredDate, Orders.ShippedDate " +
"FROM Orders, Customers " +
"WHERE Orders.CustomerID = Customers.CustomerID " +

"ORDER BY Customers.CompanyName, Customers.ContactName";
Command.CommandType = CommandType.Text;
Command.Connection = DBCon;
DBCon.Open();
// Starting the asynchronous processing
ASyncResult = Command.BeginExecuteReader();
WHandle = ASyncResult.AsyncWaitHandle;
if (WHandle.WaitOne() == true)
{
// Retrieving result from the asynchronous process
OrdersReader = Command.EndExecuteReader(ASyncResult);
// Displaying result on the screen
gvOrders.DataSource = OrdersReader;
gvOrders.DataBind();
Continued
441
Evjen c08.tex V2 - 01/28/2008 2:05pm Page 442
Chapter 8: Data Management with ADO.NET
// Closing connection
DBCon.Close();
}
else
{
// Asynchronous process has timed out. Handle this
// situation here.
}
}
<
/script
>

If you set a break point and step through this code, you will notice that the program execution stops at the
WHandle.WaitOne
method call. The program automatically resumes when the asynchronous commands
finishes its execution.
Using Multiple Wait Handles
The real power of the wait approach doesn’t become apparent until you start multiple asynchronous
processes. The code shown in Listing 8-33 starts two asynchronous processes. One process queries a
database to get information about a specific customer and runs another query to retrieve all orders sub-
mitted by that the same customer. The code example shown in this listing creates two separate
Command
objects, Data Reader objects, and wait handles. However, it uses the same connection object for both
queries to demonstrate how well Multiple Active Result Set (MARS) supports work in conjunction with
the asynchronous processing.
Listing 8-33: Use of m ultiple wait handles in conjunction with MARS
VB
<
%@ Page Language="VB" %
>
<
%@ Import Namespace="System.Data" %
>
<
%@ Import Namespace="System.Data.SqlClient" %
>
<
%@ Import Namespace="System.Configuration" %
>
<
script runat="server"
>

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)
Dim DBCon As SqlConnection
Dim OrdersCommand As SqlCommand = New SqlCommand()
Dim CustCommand As SqlCommand = New SqlCommand()
Dim OrdersReader As SqlDataReader
Dim CustReader As SqlDataReader
Dim OrdersASyncResult As IAsyncResult
Dim CustAsyncResult As IAsyncResult
Dim WHandles(1) As System.Threading.WaitHandle
Dim OrdersWHandle As System.Threading.WaitHandle
Dim CustWHandle As System.Threading.WaitHandle
DBCon = New SqlConnection()
DBCon.ConnectionString = _
ConfigurationManager.ConnectionStrings("DSN_NorthWind").ConnectionString
Continued
442
Evjen c08.tex V2 - 01/28/2008 2:05pm Page 443
Chapter 8: Data Management with ADO.NET
CustCommand.CommandText = _
"SELECT * FROM Customers WHERE CompanyName = ’Alfreds Futterkiste’"
CustCommand.CommandType = CommandType.Text
CustCommand.Connection = DBCon
’ Selecting all orders for a specific customer
OrdersCommand.CommandText = _
"SELECT Customers.CompanyName, Customers.ContactName, " & _
"Orders.OrderID, Orders.OrderDate, " & _
"Orders.RequiredDate, Orders.ShippedDate " & _
"FROM Orders, Customers " & _
"WHERE Orders.CustomerID = Customers.CustomerID " & _
"AND Customers.CompanyName = ’Alfreds Futterkiste’ " & _

"ORDER BY Customers.CompanyName, Customers.ContactName"
OrdersCommand.CommandType = CommandType.Text
OrdersCommand.Connection = DBCon
DBCon.Open()
’ Retrieving customer information asynchronously
CustAsyncResult = CustCommand.BeginExecuteReader()
’ Retrieving orders list asynchronously
OrdersASyncResult = OrdersCommand.BeginExecuteReader()
CustWHandle = CustAsyncResult.AsyncWaitHandle
OrdersWHandle = OrdersASyncResult.AsyncWaitHandle
’ Filling Wait Handles array with the two wait handles we
’ are going to use in this code
WHandles(0) = CustWHandle
WHandles(1) = OrdersWHandle
System.Threading.WaitHandle.WaitAll(WHandles)
CustReader = CustCommand.EndExecuteReader(CustAsyncResult)
OrdersReader = OrdersCommand.EndExecuteReader(OrdersASyncResult)
gvCustomers.DataSource = CustReader
gvCustomers.DataBind()
gvOrders.DataSource = OrdersReader
gvOrders.DataBind()
DBCon.Close()
End Sub
<
/script
>
<
html xmlns=" />>
<
head id="Head1" runat="server"

>
Continued
443
Evjen c08.tex V2 - 01/28/2008 2:05pm Page 444
Chapter 8: Data Management with ADO.NET
<
title
>
Wait All Approach
<
/title
>
<
/head
>
<
body
>
<
form id="form1" runat="server"
>
<
div
>
<
asp:GridView ID="gvCustomers" Width="100%" runat="server"
><
/asp:GridView
>
<

br /
><
br /
>
<
asp:GridView ID="gvOrders" Width="100%" AutoGenerateColumns="False"
runat="server"
>
<
Columns
>
<
asp:BoundField HeaderText="Company Name"
DataField="CompanyName"
><
/asp:BoundField
>
<
asp:BoundField HeaderText="Contact Name"
DataField="ContactName"
><
/asp:BoundField
>
<
asp:BoundField HeaderText="Order Date" DataField="orderdate"
DataFormatString="{0:d}"
><
/asp:BoundField
>
<

asp:BoundField HeaderText="Required Date" DataField="requireddate"
DataFormatString="{0:d}"
><
/asp:BoundField
>
<
asp:BoundField HeaderText="Shipped Date" DataField="shippeddate"
DataFormatString="{0:d}"
><
/asp:BoundField
>
<
/Columns
>
<
/asp:GridView
>
<
/div
>
<
/form
>
<
/body
>
<
/html
>
C#

<
%@ Page Language="C#" %
>
<
%@ Import Namespace="System.Data" %
>
<
%@ Import Namespace="System.Data.SqlClient" %
>
<
%@ Import Namespace="System.Configuration" %
>
<
script runat="server"
>
protected void Page_Load(object sender, EventArgs e)
{
SqlConnection DBCon;
SqlCommand OrdersCommand = new SqlCommand();
SqlCommand CustCommand = new SqlCommand();
SqlDataReader OrdersReader;
SqlDataReader CustReader;
IAsyncResult OrdersASyncResult;
IAsyncResult CustAsyncResult;
System.Threading.WaitHandle[] WHandles = new
System.Threading.WaitHandle[1];
System.Threading.WaitHandle OrdersWHandle;
System.Threading.WaitHandle CustWHandle;
DBCon = new SqlConnection();
DBCon.ConnectionString =

ConfigurationManager.ConnectionStrings["DSN_NorthWind"].ConnectionString;
Continued
444
Evjen c08.tex V2 - 01/28/2008 2:05pm Page 445
Chapter 8: Data Management with ADO.NET
CustCommand.CommandText =
"SELECT * FROM Customers WHERE CompanyName = ’Alfreds Futterkiste’";
CustCommand.CommandType = CommandType.Text;
CustCommand.Connection = DBCon;
// Selecting all orders for a specific customer
OrdersCommand.CommandText =
"SELECT Customers.CompanyName, Customers.ContactName, " +
"Orders.OrderID, Orders.OrderDate, " +
"Orders.RequiredDate, Orders.ShippedDate " +
"FROM Orders, Customers " +
"WHERE Orders.CustomerID = Customers.CustomerID " +
"AND Customers.CompanyName = ’Alfreds Futterkiste’ " +
"ORDER BY Customers.CompanyName, Customers.ContactName";
OrdersCommand.CommandType = CommandType.Text;
OrdersCommand.Connection = DBCon;
DBCon.Open();
// Retrieving customer information asynchronously
CustAsyncResult = CustCommand.BeginExecuteReader();
// Retrieving orders list asynchronously
OrdersASyncResult = OrdersCommand.BeginExecuteReader();
CustWHandle = CustAsyncResult.AsyncWaitHandle;
OrdersWHandle = OrdersASyncResult.AsyncWaitHandle;
// Filling Wait Handles array with the two wait handles we
// are going to use in this code
WHandles[0] = CustWHandle;

WHandles[1] = OrdersWHandle;
System.Threading.WaitHandle.WaitAll(WHandles);
CustReader = CustCommand.EndExecuteReader(CustAsyncResult);
OrdersReader = OrdersCommand.EndExecuteReader(OrdersASyncResult);
gvCustomers.DataSource = CustReader;
gvCustomers.DataBind();
gvOrders.DataSource = OrdersReader;
gvOrders.DataBind();
DBCon.Close();
}
<
/script
>
When you compile and execute the code shown in Listing 8-33, you see the result o n the screen, as shown
in Figure 8-25. This figure clearly shows two GridView controls that were used in the code example. The
GridView control on the top shows the result of executing a query that retrieved all information related
445