Frank Wang's inspirations on .NET

IEnumerable<Inspiration> inspirations = from i in DataContext.Inspirations where i.Sharable == true select i

Execute Transact-SQL statement asynchronously

Saturday, July 5, 2008 3:33 PM

Processing a large amount of data from SQL server in .NET applications can be challenging some times. A common concern that people have is it just takes too much time to get the data from SQL. Waiting for the entire result set to return and then handling it require a great amount of RAM usage. That's not the worst thing yet. Imagine how frustrated your users can get when they have to wait for minutes after they click a button. A better designed application should utilize the asynchronous pattern to the data retrieval and process the data as soon as it becomes available in the callback function.

.NET developers used to write their own asynchronous code to execute T-SQL statements back in the .NET 1.0 & 1.1 days. The System.Data.SqlClient namespace in .NET 2.0 shipped with a number of out-of-box asynchronous methods for T-SQL execution, including ExecuteReader, ExecuteNonQuery, ExecuteXmlReader etc. These methods strictly follow the standard MS naming conventions for asynchronous methods. Take ExecuteReader as an example, BeginExecuteReader and EndExecuteReader are provided. The only caveat, if any, is that the asynchronous processing of T-SQL requires SQL Server 2005 or higher versions. If you haven't paid attention yet, the Management Studio for SQL 2005 or 2008 execute "select" queries asynchronously, as you can see in the screen shot below. I am selecting 4.5 million records from a table. The query comes back in less than 2 seconds with the first set of records. At the same time, the Management Studio keeps rendering the rest of the records behind the scene.

image

To demonstrate how the asynchronous operation works with large result sets, we are going to create a similar UI to the query window you just saw above.

Let's start off with creating a new Windows Form project named AsyncReader. Go ahead and add the controls to the Windows Form as shown below.

image 

The end goal of the example is: when I click on the Execute button, the application connects to the Northwind database, executes the T-SQL statement entered in the text box, and displays the result set in the ListBox at the bottom asynchronously.

Double click on the Execute button to bring up the code editor. We are going to implement the OnClick event of this button.

private void button1_Click(object sender, EventArgs e)
{
    using (SqlConnection conn = new SqlConnection("Network Address=(local), Initial Catalog=Northwind; Integrated Security=true; Asynchronous Processing=true"))
    {
        using (SqlCommand cmd = new SqlCommand(tbQuery.Text, conn))
        {
            try
            {
                conn.Open();
                cmd.BeginExecuteReader(new AsyncCallback(QueryCallback), cmd);
            }
            catch (SqlException se)
            {
                MessageBox.Show(se.Message);
                conn.Close();
            }
        }
    }
}

The code above follows the standard procedure for reading data from a SQL database in a .NET application, except two things:

1.  There's an additional name-value pair in the connection string. "Asynchronous Processing=true" tells the SQL Server (2005 or above) that this application will be executing T-SQL statements asynchronously. This name-value pair MUST be included in the connection string to enable asynchronous operations. Otherwise, an InvalidOperationException will be thrown when the BeginExecuteReader is called.

2. We used BeginExecuteReader method instead of ExecuteReader. This is the key. We are not going to wait until the entire result set returns. A callback delegate "QueryCallback" was passed as the first parameter. This means we are going to process the result set in "QueryCallback". The second parameter is called "stateObject" and it is of the System.Object type. It really can be boxed from any type of object holding the user state that is passed into the callback procedure. In our example, we pass the SqlCommand as the state object because we will need it to get the actual reference of the SqlDataReader.

The execution may take 2 seconds, 2 minutes, or probably 2 hours, but we have just solved two problems here by doing things asynchronously. First of all, the UI will NOT freeze because the BeginExecuteReader fires the query off and returns to the UI thread IMMEDIATELY. The actual T-SQL execution happens in a separate work thread. We will demonstrate this in a moment. Secondly, the query may take hours to complete but we can start processing the result set as soon as the data comes back in our callback method. Let's take a look at what's going to happen in the callback method.

private void QueryCallback(IAsyncResult result)
{
    try
    {
        SqlCommand cmd = (SqlCommand)result.AsyncState;
        SqlDataReader reader = cmd.EndExecuteReader(result);
        while (reader.Read())
        {
             Invoke(new AddDataToResultDelegate(AddDataToResult), reader.GetString(0));
        }
        if (cmd.Connection.State.Equals(ConnectionState.Open))
        {
            cmd.Connection.Close();
        }
    }
    catch (Exception ex)
    {
        Invoke(new AddDataToResultDelegate(AddDataToResult), "Error: " + ex.Message);
    }
}

The first thing we do is un-box the AsynState back to the SqlCommand, which was supplied as the state object from BeginExecuteReader.  Then we finish the asynchronous reading by calling the EndExecuteReader, which returns the requested SqlDataReader. Remember that calling EndExecuteReader is NOT optional. When you call BeginExecuteReader to execute a T-SQL statement, you MUST call EndExecuteReader in order to complete the operation. If the process of executing the command has not yet finished, this method blocks until the operation is complete. Now that we've got hold of the reference to the SqlDataReader, we can do anything we want with the data it brings back.  In this case, we are going to display the data in the ListBox control named lbResult we placed on the form earlier.

private delegate void AddDataToResultDelegate(string text);
 
private void AddDataToResult(string Text)
{
    lbResult.Items.Add(Text);
}
 
private void DisplayInfo(SqlDataReader reader)
{
    Invoke(new AddDataToResultDelegate(AddDataToResult), reader.GetString(0));
}

We may not interact with the form and its contents from a different thread, and this callback procedure is all but guaranteed to be running from a different thread  than the form. Therefore you cannot simply call code that update the ListBox control, like this: AddDataToResult(reader.GetString(0)); Instead, we must call the procedure from the form's thread. One simple way to accomplish this is to call the Invoke method of the form, which calls the delegate AddDataToResultDelegatae supplied from the form's thread.

Up to this point, we have done all we need to do for this quick example. Compile it to make sure there're no errors, and then hit F5 to run the application. Enter "select * from customers" in the text box and click the Execute button.  So what did you get? Nothing!!! None of the records was added to the ListBox. We know there're 91 customers in the Northwind database. So what happened? Stop the application. Place a break point at the beginning of the QueryCallback method and run the application again. The program doesn't even stop at the breakpoint?!

Welcome to the asynchronous world, where nothing is what it seems. We know the problem already. The callback function expects the state object supplied by BeginExecuteReader, but it's not there any more. Take a closer look at the Execute button's OnClick event.

private void button1_Click(object sender, EventArgs e)
{
    using (SqlConnection conn = new SqlConnection("Network Address=(local), Initial Catalog=Northwind; Integrated Security=true; Asynchronous Processing=true"))
    {
        using (SqlCommand cmd = new SqlCommand(tbQuery.Text, conn))
        {
            try
            {
                conn.Open();
                cmd.BeginExecuteReader(new AsyncCallback(QueryCallback), cmd);
            }
            catch (SqlException se)
            {
                MessageBox.Show(se.Message);
                conn.Close();
            }
        }
    }
}

The two "using" statements are the first thing that came into our attention. We are supplying the SqlCommand instance as the object state to the callback function but it gets disposed by the "using" immediately after the asynchronous call is fired. If you scream "oh I should've thought of that", you are not alone. This is a common mistake that developers make from time to time in asynchronous programming. The state object are accessed by two threads (the form's thread and a work thread that executes the asynchronous T-SQL), so its reference needs to exist through the entire application context. This is why sometimes the state objects are also called "user context". The solution is obvious now - remove the "using" statements to make sure the SqlCommand lives the entire application's life cycle.

private void button1_Click(object sender, EventArgs e)
{
  SqlConnection conn = new SqlConnection("Network Address=localhost;Initial Catalog=Northwind;Integrated Security=true;Asynchronous Processing=true");
  SqlCommand cmd = new SqlCommand( tbQuery.Text, conn);
  try
  {
      conn.Open();
      cmd.BeginExecuteReader(new AsyncCallback(QueryCallback), cmd);
  }
  catch (SqlException se)
  {
      AddDataToResult(se.Message);
  }
  catch (Exception ex)
  {
      AddDataToResult(ex.Message);
  }
}

Run the program again. We got everything together this time. The form's UI is still responsive to the user interactions as the query executes in the background. The ListBox is filled up with all the 91 customer names after a slight delay.

image 

The demo code is all yours. You can download it here and play with it a little bit more. If you want to feel the true power of asynchronous processing, I suggest you connect this application to a database where you have millions of records.




Feedback

# re: Execute Transact-SQL statement asynchronously

Very good tip about "Using" statement.
Thanks for sharing. 1/6/2009 9:20 AM | Eloa

# re: Execute Transact-SQL statement asynchronously

Nice work Frank.Keep posting more tips on ADO.NET.
Thanks.

4/10/2009 3:19 AM | ray

# re: Execute Transact-SQL statement asynchronously

For performance, it's a good idea to have the Callback method read several lines at a time from the DataReader and then pass an array of items to add to the method invoked on the UI thread. This improves overall performance and reduces the perceived time to load.
An even better solution, however, is to bring back small volumes of data from SQL - that is - implement "data virtualization". This could mean more round-trips to the server, but generally results in reduced load both the server and the network. 9/15/2009 6:49 AM | Mark

# re: Execute Transact-SQL statement asynchronously

Thank you my friend , keep up doing the best work.
Keep it up.
Goo luck. 7/21/2011 1:32 AM | Xitij Thool

# re: Execute Transact-SQL statement asynchronously

really good sample.. thx 7/21/2011 4:24 AM | URVISH SUTHAR

# re: Execute Transact-SQL statement asynchronously

Awsome code. It was really helpful and I was looking for exactly similar code.Thank you very much. 1/12/2012 3:33 AM | Kalyan

# re: Execute Transact-SQL statement asynchronously

How to use this with oracle connection? 3/26/2012 5:23 AM | Dilip

# re: Execute Transact-SQL statement asynchronously

Thanks for the nice example, it really saved my life for long running SQL procedures.
however code link
http://www.dotnetinspirations.com/dotnetinspirations/sourcecode/asyncreader.zip
is not working.

i colud not understand the use of private void DisplayInfo(SqlDataReader reader)
{
Invoke(new AddDataToResultDelegate(AddDataToResult), reader.GetString(0));
},
where was it called 8/27/2012 1:20 AM | Sushil Agarwal

Post a comment