Execute an asynchronous query

Some ADO.NET classes supports built-in asynchronous query behavior. Some aren’t. In this post, we’ll revise the former classes, like SqlDataReader. However, if you are familiar with multithread computing (particularly, in the .NET Framework) you can easily implement asynchronous execution in classes which don’t support it, such as the TableAdapter.

Before you’d do anything, you should introduce a new name-value pair into your connection string: Async=true or Asynchronous Processing=true. Including it, you enable asynchronous queries to be executed on the database server. This was the easy part.

Writing your asynchronous data access class is a little bit more complicated. If you are familiar with delegates and the classes of System.Threading, then you are ready to go. But if you’d like to rely on built-in logic, consider using the DbCommand’s BeginExecuteReader and EndExecuteReader. First the code, then the explanation:

SqlConnection theConnection = new SqlConnection(“connectionString”);
SqlCommand theCommand = new SqlCommand(“command”, theConnection);
theConnection.Open();
IAsyncResult theResult = theCommand.BeginExecuteReader(new AsyncCallback(DataComplete), theCommand);
public static void DataComplete(IAsyncResult result)
{
SqlCommand theCommand = (SqlCommand)result.AsyncState;
SqlDataReader theReader = theCommand.EndExecuteReader(result);
if(theReader.HasRows)
{
//Deal with the results.
}
}

As you can see, we’ve created an IAsyncResult object by using the DbCommand.BeginExecuteReader method, passed a callback method and the command itself as parameters. Then we’ve created the method, which accepts the IAsyncResult object. Because we passed the command object as the state object, we are able to retrieve it by casting the IAsyncResult.AsyncState property into a SqlCommand. Then we call the EndExecuteReader method of the command, passing the IAsyncResult object as parameter, and building our DataReader with the help of it. Then we’re ready to iterate over the returned data.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s