If you haven't yet read the article "Scalable Apps with Asynchronous Programming in ASP.NET" by Jeff Prosise or seen his presentation at TechEd then you should cetainly take the time to do so, however I'll summarize the key points briefly here. Basically, there is a finite number of Threads available to ASP.Net for request handling, and by making database calls the way that most textbooks and articles recommend, many of the available threads that should be handling requests are actually tapping their feet waiting for your database request to complete, before it can serve your page and return to the thread pool. When all the threads are busy, incoming requests are queued, and if that queue becomes too long then users start to see HTTP 503 Service unavailable errors. In other words there is a glass ceiling to scalability, with synchronous IO requests in asp.net.
To make optimum use of the thread pool all IO requests that you know could take a second or more to process should be made asynchronously and the links above will give you plenty of examples of how you should do this. The purpose of this article is to demonstrate how you can return a DataSet asynchronously, which is not something I could find an example of anywhere. Another thing I could not find in my research was how to use asynchronous database calls when you have a datalayer, as opposed to a page or usercontrol that contacts the database directly, and I will provide you with both here.
If you have looked at examples of asynchronous database calls elsewhere on the web before arriving here you have probably become familair with the BeginExecuteReader and EndExecuteReader methods of the SQLClient namespace, but where is the BeginExecuteDataSet method? If you need a dataset, why should that have to be a synchronous request? I could not find any asynchronous methods for the DataAdapter and while I did find ways to call a WebMethod or WebService that returns a dataset asynchronously, why should you have to break your methods that return datasets out to webservices? I also found some examples of how to create delegates or use System.Threading.Thread.Start to create datasets in their own thread, but, according to Mr. Prosise these are worthless in ASP.Net because both of these methods actually steal threads for the same thread pool
ASP.Net is using anyway! So by using System.Threading.Thread.Start to create your dataset all you are doing is returning a thread to the threadpool and immediately grabbing another one. So
how can you do it?
For this example I borrowed the Datalayer from the Job Site Starter Kit and added some Asynchronous methods to it. Here is my BeginExecuteDataSet method:
Public Function BeginExecuteDataSet(ByVal callback As System.AsyncCallback, _
ByVal stateObject As Object, ByVal behavior As CommandBehavior) As System.IAsyncResult
Dim res As IAsyncResult = Nothing
res = cmd.BeginExecuteReader(callback, stateObject, behavior)
But how is that different to BeginExecuteReader? It is not it is exactly the same,
I don't see the need to rewrite the DataAdapter class from scratch to support
Asynchronous functions when I can simply use a datareader to populate a dataset. The key differences are in the EndExecuteDataSet Method:
Public Function EndExecuteDataSet(ByVal asyncResult As System.IAsyncResult) As DataSet
Dim ds As Dataset = Nothing
Dim rdr As SqlClient.SqlDataReader = cmd.EndExecuteReader(asyncResult)
Dim dt As DataTable = New DataTable()
ds = New DataSet
Calling these methods is therefore no different to calling BeginExecuteReader. For
example the following code would work from both an .aspx page or an .ascx
Dim db As Classes.Data.DAL
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
'Append async attribute to connection string
db = New Classes.Data.DAL(String.Concat _
db.CommandText = "asyncTest"
' Launch data request asynchronously using page async task
Page.RegisterAsyncTask(New PageAsyncTask(New BeginEventHandler(AddressOf BeginGetData), _
New EndEventHandler(AddressOf EndGetData), New EndEventHandler(AddressOf GetDataTimeout), _
Function BeginGetData(ByVal sender As Object, ByVal e As EventArgs, _
ByVal cb As AsyncCallback, ByVal state As Object) As IAsyncResult
Return db.BeginExecuteDataSet(cb, state)
Sub EndGetData(ByVal ar As IAsyncResult)
gv1.DataSource = db.EndExecuteDataSet(ar)
Catch ex As Exception
lblMsg.Text = ex.ToString()
Sub GetDataTimeout(ByVal ar As IAsyncResult)
lblMsg.Text = "Async connection timed out"
There you have it, a DataSet returned asynchronously, from a Data Access Layer. Download the entire Data Access Layer (zip file contains both Visual Basic and C# versions - 3.05 kb).