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

<
%@ 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

/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"

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

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.

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.

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
>

%@ 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 " +

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"
>

"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"

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

<
%@ 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 =

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