In my last couple articles (part I, part II) we looked at basic ways to have long-running i/o bound process be handled by the ThreadPool using the asynchronous programming model. In this article we’ll take a look at coding async calls against SqlServer.
After the db is set up we need to create a connection string that declares that we are going to be making asynchronous calls. In our connection string we need to add: “Asynchronous Processing=true “.
When coding asynchronous methods, we need to make sure we don’t close the connection or dispose of the connection or command until we have received the callback otherwise our callback will throw an exception or just return nothing so if we do the following our model breaks down:
// THIS WONT WORK:
using(SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString))
using (SqlCommand command = new SqlCommand("SELECT value FROM tblTable", connection))
{
connection.Open();
command.BeginExecuteReader(EndDatabaseCall, command);
}
Do you see why? The answer is because we use the “using” blocks in the code above, the IDisposible.Dispose() method is called when the thread leaves the “using” block scope. This closes our connection and prevents us from getting the results of the command.
So we have to be aware of what we are doing during the initial calls. All of the cleanup must happen in result method being called asynchronously (in the response). Here is an example of a method that will work asynchronously:
public static void BeginDataBaseCall()
{
Console.WriteLine("BeginDataBaseCall()");
SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString);
SqlCommand command = new SqlCommand("SELECT value FROM tblTable", connection);
connection.Open();
command.BeginExecuteReader(EndDatabaseCall, command);
}
The response method (EndDatabaseCall()) will now be responsible for all the cleanup. This is probably best handled in a finally block to make sure everything is cleaned up after the db call even if an exception is thrown.
public static void EndDatabaseCall(IAsyncResult result)
{
Console.WriteLine("EndDatabaseCall");
StringBuilder builder = new StringBuilder();
builder.Append("DbCall Results: ");
SqlCommand command = result.AsyncState as SqlCommand;
try
{
using(SqlDataReader reader = command.EndExecuteReader(result))
{
while (reader.Read())
{
builder.Append(reader[0]).Append(" ");
}
}
}
catch
{
throw;
}
finally
{
#region Cleanup
command.Connection.Close();
command.Connection.Dispose();
command.Dispose();
#endregion
}
Console.WriteLine(builder.ToString());
}
As you can see, it is pretty easy making asynchronous calls to SqlServer. This means our main thread is no longer waiting on a response from a remote sql box and can continue processing other instructions which will make our application much more scalable.
In the sample code for this article I also have an implementation with an anonymous method used as a callback which behaves the same as the one here, but both the call and response are in the same method.
In my next article, we’ll look at performing async file system reads and writes and start getting into how to build a system to manage a asynchronous architecture using events.
Until next time,
Happy Coding