Widgets Asynchronous DataSet


I dream in code

About the author

Robert Williams is an internet application developer for the Salem Web Network.
E-mail me Send mail
Go Daddy Deal of the Week: 30% off your order at! Offer expires 11/6/12

Recent comments




Code Project Associate Logo


The opinions expressed herein are my own personal opinions and do not represent my employer's view in anyway.

Asynchronous DataSet

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

 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)
             Return res
       End Function

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
             Return ds
       End Function

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

    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 _
        (ConfigurationManager.ConnectionStrings("MyDB")ConnectionString, ";async=true"))
        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), _
    End Sub
    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)  
    End Function
    Sub EndGetData(ByVal ar As IAsyncResult)
           gv1.DataSource = db.EndExecuteDataSet(ar)
        Catch ex As Exception
           lblMsg.Text = ex.ToString()
        End Try
    End Sub
    Sub GetDataTimeout(ByVal ar As IAsyncResult)
        lblMsg.Text = "Async connection timed out"
    End Sub

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

Posted by Williarob on Wednesday, December 19, 2007 8:07 AM
Permalink | Comments (0) | Post RSSRSS comment feed
blog comments powered by Disqus