Executing Asynchronous Database Commands

ADO.NET 2.0 supports asynchronous database commands. Normally, when you execute a database command, the thread that is executing the command must wait until the command is finished before executing any additional code. In other words, normally, when you execute a database command, the thread is blocked.

When you take advantage of asynchronous commands, on the other hand, the database command is executed on another thread so that the current thread can continue performing other work. For example, you can use the current thread to execute yet another database command.

There are two reasons that you might want to use asynchronous database commands when building an ASP.NET page. First, executing multiple database commands simultaneously can significantly improve your application's performance. This is especially true when the database commands are executed against different database servers.

Second, the ASP.NET Framework uses a limited thread pool to service page requests. When the ASP.NET Framework receives a request for a page, it assigns a thread to handle the request. If the ASP.NET Framework runs out of threads, the request is queued until a thread becomes available. If too many threads are queued, then the framework rejects the page request with a 503Server Too Busy response code.

If you execute a database command asynchronously, then the current thread is released back into the thread pool so that it can be used to service another page request. While the asynchronous database command is executing, the ASP.NET framework can devote its attention to handling other page requests. When the asynchronous command completes, the framework reassigns a thread to the original request and the page finishes executing.

Note

You can configure the ASP.NET thread pool with the httpRuntime element in the web configuration file. You can modify the appRequestQueueLimit, minFreeThreads, and minLocalRequestFreeThreads attributes to control how many requests the ASP.NET Framework queues before giving up and sending an error.

There are two parts to this task undertaken in this section. A data access component that supports asynchronous ADO.NET methods must be created, as well as an ASP.NET page that executes asynchronously.

Using Asynchronous ADO.NET Methods

ADO.NET 2.0 introduces asynchronous versions of several of its methods. These methods come in pairs: a Begin and End method. For example, the SqlCommand object supports the following asynchronous methods:

BeginExecuteNonQuery()
EndExecuteNonQuery()
BeginExecuteReader()
EndExecuteReader()
BeginExecuteXmlReader()
EndExecuteXmlReader()

The idea is that when you execute the Begin method, the asynchronous task is started on a separate thread. When the method finishes executing, you can use the End method to get the results.

To use these asynchronous methods, you must use a special attribute in your connection string: the Asynchronous Processing=true attribute.

The data access component in Listing 16.34 contains a BeginGetMovies() and EndGetMovies() method that fetches movies from the Movies database table asynchronously. These methods use the ADO.NET BeginExecuteReader() and EndExecuteReader() to fetch a DataReader asynchronously.

Listing 16.34. `App_Code\AsyncDataLayer.vb`

[View full width]

Imports System Imports System.Data Imports System.Data.SqlClient Imports System.Web.Configuration Imports System.Collections.Generic Public Class AsyncDataLayer     Private Shared ReadOnly _connectionString As String     Private _cmdMovies As SqlCommand     Public Function BeginGetMovies(ByVal callback As AsyncCallback, ByVal state As Object)  As IAsyncResult         Dim con As New SqlConnection(_connectionString)         _cmdMovies = New SqlCommand("SELECT Title,Director FROM Movies", con)         con.Open()         Return _cmdMovies.BeginExecuteReader(callback, state, CommandBehavior.CloseConnection)     End Function     Public Function EndGetMovies(ByVal result As IAsyncResult) As List(Of AsyncDataLayer .Movie)         Dim results As New List(Of AsyncDataLayer.Movie)()         Dim reader As SqlDataReader = _cmdMovies.EndExecuteReader(result)         While reader.Read()             Dim NewMovie As New AsyncDataLayer.Movie()             NewMovie.Title = CType(reader("Title"), String)             NewMovie.Director = CType(reader("Director"), String)             results.Add(NewMovie)         End While         Return results     End Function     Shared Sub New()         _connectionString = WebConfigurationManager.ConnectionStrings("Movies") .ConnectionString         _connectionString &= ";Asynchronous Processing=true"     End Sub     Public Class Movie         Private _title As String         Private _director As String         Public Property Title() As String             Get                 Return _title             End Get             Set(ByVal Value As String)                 _title = Value             End Set         End Property         Public Property Director() As String             Get                 Return _director             End Get             Set(ByVal Value As String)                 _director = Value             End Set         End Property     End Class End Class

Using Asynchronous ASP.NET Pages

When you take advantage of asynchronous ADO.NET methods, you must also enable asynchronous ASP.NET page execution. You enable an asynchronous ASP.NET page by adding the following two attributes to a page directive:

<%@ Page Async="true" AsyncTimeout="8" %>

The first attribute enables asynchronous page execution. The second attribute specifies a timeout value in seconds. The timeout value specifies the amount of time that the page gives a set of asynchronous tasks to complete before the page continues execution.

After you enable asynchronous page execution, you must set up the asychronous tasks and register the tasks with the page. You represent each asynchronous task with an instance of the PageAsyncTask object. You register an asynchronous task for a page by calling the Page.RegisterAsyncTask() method.

For example, the page in Listing 16.35 displays the records from the Movies database table in a GridView control. The database records are retrieved asynchronously from the AsyncDataLayer component created in the previous section.

Listing 16.35. `ShowPageAsyncTask.aspx`

[View full width]

<%@ Page Language="VB" Async="true" AsyncTimeout="5" Trace="true" %> <%@ Import Namespace="System.Threading" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"   "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <script runat="server">     Private dataLayer As New AsyncDataLayer()     Private Sub Page_Load()         ' Setup asynchronous data execution         Dim task As PageAsyncTask = New PageAsyncTask(AddressOf BeginGetData,AddressOf  EndGetData, AddressOf TimeoutData, Nothing, True)         Page.RegisterAsyncTask(task)         ' Fire off asynchronous tasks         Page.ExecuteRegisteredAsyncTasks()     End Sub     Private Function BeginGetData(ByVal sender As Object, ByVal e As EventArgs, ByVal  callback As AsyncCallback, ByVal state As Object) As IAsyncResult         ' Show Page Thread ID         Trace.Warn("BeginGetData: " & Thread.CurrentThread.GetHashCode())         ' Execute asynchronous command         Return dataLayer.BeginGetMovies(callback, state)     End Function     Private Sub EndGetData(ByVal ar As IAsyncResult)         ' Show Page Thread ID         Trace.Warn("EndGetDate: " & Thread.CurrentThread.GetHashCode())         ' Bind results         grdMovies.DataSource = dataLayer.EndGetMovies(ar)         grdMovies.DataBind()     End Sub     Private Sub TimeoutData(ByVal ar As IAsyncResult)         ' Display error message         lblError.Text = "Could not retrieve data!"     End Sub </script> <html xmlns="http://www.w3.org/1999/xhtml" > <head  runat="server">     <title>Show Page AsyncTask</title> </head> <body>     <form  runat="server">     <div>     <asp:Label                  Runat="server" />     <asp:GridView                  Runat="server" />     </div>     </form> </body> </html>

The page in Listing 16.35 creates an instance of the PageAsyncTask object that represents the asynchronous task. Next, the PageAsyncTask object is registered for the page with the Page.RegisterAsyncTask() method. Finally, a call to the Page.ExecuteRegisteredAsyncTasks() method executes the task. (If you don't call this method, any asynchronous tasks registered for the page are executed during the PreRender event automatically.)

The constructor for the PageAsyncTask object accepts the following parameters:

beginHandler The method that executes when the asynchronous task begins.
endHandler The method that executes when the asynchronous task ends.
timoutHandler The method that executes when the asynchronous task runs out of time according to the Page directive's AsyncTimeout attribute.
state An arbitrary object that represents state information.
executeInParallel A Boolean value that indicates whether multiple asynchronous tasks should execute at the same time or execute in sequence.

You can create multiple PageAsyncTask objects and register them for the same page. When you call the ExecuteRegisteredAsyncTasks() method, all the registered tasks are executed.

If an asynchronous task does not complete within the time alloted by the AsyncTimeout attribute, then the timoutHandler method executes. For example, the page in Listing 16.36 gives the asychronous tasks 5 seconds to execute. If the database SELECT command does not return a record within the 5 seconds, then the TimeoutData() method executes.

It is important to understand that the asynchronous task continues to execute even when the task executes longer than the interval of time specified by the AsyncTimeout attribute. The AsyncTimeout attribute specifies the amount of time that a page is willing to wait before continuing execution. An asynchronous task is not canceled if takes too long.

The page in Listing 16.36 has tracing enabled and it is sprinkled liberally with calls to trace.Warn() so that you can see when different events happen. The TRace.Warn() statements writes out the ID of the current Page tHRead. The Page thread ID can change between the BeginGetData() and EndGetData() methods (see Figure 16.19).

Figure 16.19. Trace information for a page executed asynchronously.

You can force the asynchronous task in Listing 16.35 to time out by adding a delay to the database command executed by the AsyncDataLayer.BeginGetMovies() method. For example, the following SELECT statement waits 15 seconds before returning results:

WAITFOR DELAY '0:0:15';SELECT Title,Director FROM Movies

If you use this modified SELECT statement, then the asychronous task times out and the TimeoutData() method executes. The TimeoutData() method simply displays a message in a Label control.