Asynchronous Data Access

• Download source files - 20.4 Kb

Introduction

I felt stupid. It was in Barcelona last week, at an outdoor café, when I was waiting for my tapas to be served. The first day at Microsoft’s Tech Ed had been exhausting. I had been running all over the place, filling up my backpack with free coke cans, mars bars, donuts and cookies wherever I went. I’m sure I actually lost some weight, carrying that food around all day. So after the final sessions, I was tired and cranky, and complaining to myself about the waitress. She forgot what I had ordered, kept me waiting for too long, and seemed more eager to socialize with half of her family who were passing by. Her customers, consisting as I suspected of the other half of her family and me, were hardly of her concern.

But then it dawned on me that she was actually doing lots of things simultaneously. I thought of her as an overworked database server, though with smaller memory and more hair, switching between many requests. And I was just this minor thread, impatiently waiting for its request to be serviced. It wouldn’t have been much of a problem if I had had other things to do at the time. I could have been busy telling silly jokes, while not really listening to the jokes of others because mine would have been funnier. And then I wouldn’t have cared so much about the waitress taking so long to do her job, if at all.

But I was all alone. Nobody was with me. (No please, I can explain.) And this thread was just waiting. It was doing nothing else. And it hated the server for it.

Then it struck me that this was exactly what I had been doing all my life. Not as a customer of outdoor cafés, mind you, but as a programmer. I was always programming my threads to wait for servers. How much more interesting life could be, when my threads were doing other stuff! Wouldn’t it be good if, from now on, I would refuse to wait for database servers to come up with their answers? My threads could invite other threads. They could be telling each other jokes! And they would let the server come up with data at its own pace.

I felt stupid for always having created lonely threads, waiting for servers to respond, while they could have had so much fun, and their lives were just ticking away. I had been such a cold-blooded programmer, and I had never realized it. So I decided to better myself, again. And coming home from Tech Ed, I started to work on it right away.

Creating the new method

I already had a data access class lying around. I had taken it from Microsoft’s site where it is called the Data Access Application Block (DAAB). I had improved upon it earlier this year by hiding SQL Server dependencies, adding exception handling, altering the way tables are filled and adding support for updates, among other things. It was the least I could do to make the thing actually useable. Now I’m going to take that updated class and I will add asynchronous method calls for each of the normal (synchronous) calls in there. Don’t worry if you have not seen the DAAB before, either in my version or in Microsoft’s half-finished version. I won’t talk about the code inside the synchronous methods. You can even mimic the steps I make with empty methods yourself. It doesn’t matter. If you want you can always check out Microsoft’s philosophy behind the DAAB later. (Just make sure you will use my code afterwards, even though I’m not even calling it a pattern or practice!)

The Delegate types

Microsoft’s DAAB (and my class too) contains nine overloads for a number of methods that interact with a database. As an example, I’ve included the overloads for my FillDataTable method in fragment 1.

public static void FillDataTable(ref DataTable dt,
      string connectionString, CommandType commandType,
      string commandText)
 
public static void FillDataTable(ref DataTable dt,
      string connectionString, CommandType commandType,
      string commandText, params IDataParameter[] commandParameters)
 
public static void FillDataTable(ref DataTable dt,
      string connectionString, string spName,
      params object[] parameterValues)
 
public static void FillDataTable(ref DataTable dt,
      IDbConnection connection, CommandType commandType,
      string commandText)
 
public static void FillDataTable(ref DataTable dt,
      IDbConnection connection,
      CommandType commandType, string commandText,
      params IDataParameter[] commandParameters)
 
public static void FillDataTable(ref DataTable dt,
      IDbConnection connection,
      string spName, params object[] parameterValues)
 
public static void FillDataTable(ref DataTable dt,
      IDbTransaction transaction, CommandType commandType,
      string commandText)
 
public static void FillDataTable(ref DataTable dt,
      IDbTransaction transaction, CommandType commandType,
      string commandText, params IDataParameter[] commandParameters)
 
public static void FillDataTable(ref DataTable dt,
      IDbTransaction transaction, string spName,
      params object[] parameterValues)

Fragment 1

You can see that the nine versions of FillDataTable each have a slightly different way of filling an existing DataTable object with data. Some use a connection string, others a connection object or even a transaction object. Some leave the client with the choice of a command type and command text, with or without parameter variables, while others simply need the name of a stored procedure and optionally a couple of parameter constants. But what they all have in common is that they will start waiting for the database server to return the complete table. The clients using these methods must obviously have nothing better to do than to wait, sometimes almost endlessly, for a possibly overheated server to finish its job. (And in the process, hoping it’s not being needlessly chatty with relatives.) What a sad bunch of threads that is.

When you want your threads to have a life, doing smart stuff or socializing with others and such, you will need asynchronous versions of your data access methods. The first thing you should do is to create a delegate type for each of your methods and each of your overloads (if any). A delegate is an official representative of some other entity, which is actually responsible for anything the delegate does. Just like my Tech Ed badge said, I was a delegate of my company, so I had all the fun while my company was responsible for all the food that got away in my backpack. The delegates you create will have the fun of taking parameters your client threads will give them, while your original data access methods will remain the ones responsible for doing the actual work.

Create the delegate types

1. Copy the signatures (not the body) of your data access methods.
2. Change the access modifiers (such as in my case public static) into private delegate.
3. Change the names by adding Delegate, or anything else you prefer.
4. Oh, and don’t forget the semi-colon at the end.

private delegate void FillDataTableDelegate1(ref DataTable dt,
      string connectionString, CommandType commandType,
      string commandText);
 
private delegate void FillDataTableDelegate2(ref DataTable dt,
      string connectionString, CommandType commandType,
      string commandText, params IDataParameter[] commandParameters);
 
private delegate void FillDataTableDelegate3(ref DataTable dt,
      string connectionString, string spName,
      params object[] parameterValues);
 
private delegate void FillDataTableDelegate4(ref DataTable dt,
      IDbConnection connection, CommandType commandType,
      string commandText);
 
private delegate void FillDataTableDelegate5(ref DataTable dt,
      IDbConnection connection, CommandType commandType,
      string commandText, params IDataParameter[] commandParameters);
 
private delegate void FillDataTableDelegate6(ref DataTable dt,
      IDbConnection connection, string spName,
      params object[] parameterValues);
 
private delegate void FillDataTableDelegate7(ref DataTable dt,
      IDbTransaction transaction, CommandType commandType,
      string commandText);
 
private delegate void FillDataTableDelegate8(ref DataTable dt,
      IDbTransaction transaction, CommandType commandType,
      string commandText, params IDataParameter[] commandParameters);
 
private delegate void FillDataTableDelegate9(ref DataTable dt,
      IDbTransaction transaction, string spName,
      params object[] parameterValues);

Fragment 2

If you have multiple overloads of the same method, like I do, you must create a delegate type for each overload and give it a unique name. I did it by adding a number, as you can see in fragment 2. Yes, I know, it looks weird. But they’re private. Mind your own business. Delegate types cannot have the same names with different signatures. It's because they are actually compiled into classes. The people that created the compiler didn't bother to solve this. Maybe Anders Hejlsberg just needed a day off. I don’t know. Ask Microsoft.

The Begin methods

The delegate types are the ones taking your calls and turning them into asynchronous calls to the original methods. Now we need a way to expose them to your client threads, but your clients will not use them directly. You will supply wrappers for them to work with. (Just like I was wrapped with a badge, login account and a t-shirt for Microsoft to be able to deal with me at Tech Ed.)

public static IAsyncResult BeginFillDataTable
      (ref DataTable dt, string connectionString, CommandType commandType,
           string commandText, AsyncCallback ac, Object state,
           params IDataParameter[] commandParameters)
{
      FillDataTableDelegate2 d = new FillDataTableDelegate2(FillDataTable);
      IAsyncResult result = d.BeginInvoke(ref dt, connectionString,
           commandType, commandText, commandParameters, ac, state);
      return result;
}

Fragment 3

Create the Begin wrappers

1. For each original data access method, create a new method, but with this difference: it should have IAsyncResult as its return type and it should have two extra parameters, of type AsyncCallback and type Object respectively. You can decide for yourself where to put them. (Notice that, in the example in fragment 3, where I listed only the second of the nine overloads, the commandParameters parameter is still the last parameter in the list, as it was in the original data access method. That’s because no other parameters are permitted after the params keyword.)
2. It’s good practice to prefix the asynchronous versions of your data access methods with Begin. You could also use something else, like Donut, but that might make it less obvious to your clients.
3. The first thing this new method should do is to create a new delegate. It’s evident that you should use the delegate type you created specifically for this method overload. (If you don’t have overloads, you won’t have any trouble figuring out which one it is, since you have only one delegate type for each method.) In parenthesis you supply the name of the original data access method. Note that, although there are nine different overloads of FillDataTable, the compiler is able to figure out which one you mean, because it will have the same signature as the delegate type itself. (Anders Hejlsberg must have beamed with pride when he thought this up.)
4. The second thing the method should do is to invoke the delegate, using the BeginInvoke method, supplying the same parameters that you got from the client. The BeginInvoke method on a delegate type is generated automatically by the compiler behind the scenes, and it’s based on the parameters you supplied when you created the delegate type. (Note that it automatically appends the two extra parameters of type AsyncCallback and Object at the end, while we couldn’t do that because of our params keyword. The BeginInvoke method generated by the compiler doesn’t use a params parameter. It only accepts a single dimensional array, not a variable number of parameters. I’m sure Anders Hejlsberg’s had his reasons.)
5. The result of the BeginInvoke method is an object of type IAsyncResult. You should return this same object as the result of your wrapper method. After that, you’re done. With this part.

The wrapper method you have now is the one being called by the client thread. It creates a new delegate and then tells it to invoke your original data access method with the same parameters, but it will do so on a new thread, in its own time. The IAsyncResult object is just a reference to whatever it is the delegate is doing. With this object, you can check back occasionally and see if it has finished yet. That is, if you want. Your client thread can also ignore the result object all together and just continue telling jokes to other threads, or do the laundry or what else, until the delegate calls back to announce that it has finished and you can get your tapas, or your DataTable. That’s what the AsyncCallback parameter is all about. It’s for calling back to the client. But I’ll tell you about that in a moment.

The End method

When the asynchronous method call has completed the request, it is time for the client thread to collect its tapas, or its DataTable. You need to write a new method so that the client can get what it wants.

public static void EndFillDataTable(ref DataTable dt, IAsyncResult result)
{
      ...
      if ((((AsyncResult)result).AsyncDelegate).GetType() ==
           typeof(FillDataTableDelegate2))
      {
           FillDataTableDelegate2 d = (FillDataTableDelegate2)
                 ((AsyncResult)result).AsyncDelegate;
           d.EndInvoke(ref dt, result);
      }
      ...
}

Fragment 4

Create the End wrappers

1. For each of the original data access methods, you will also need to create a wrapper method that will return the results. It is common practice to prefix this method with End. As the return type, this End method should have the same type as the original data access method. In my case it was void, but for other methods, like ExecuteReader, it could be IDataReader or XmlReader or int or anything. The method will have at least one parameter for the IAsyncResult object that the client received back from the Begin method. This is not hard to understand as there may be ten different delegates working on retrieving results at the same time. This method will need to know which one of those delegates to resolve to. (Otherwise the client might end up with some else’s tapas, and in Barcelona that could very well mean snails!)
2. In my example in fragment 4 you can see that there’s also a DataTable parameter. That’s because in my original data access method, the DataTable dt is used as a by-reference parameter. I did not turn it into a function result. (I had very good reason to do so. Trust me.) So, we just use the same approach here and won’t give it a second thought.
3. Within this wrapper method you must retrieve the original delegate by casting the AsyncDelegate property of the IAsyncResult object to the proper delegate type.
4. Now you have the original delegate and you can ask it what it has been up to all this time, by calling EndInvoke on it. The EndInvoke method has been created by the compiler automatically, as you were able to see in figure 1, based on the parameters we used on the delegate type definition. Since we included a by-reference type in that definition (the DataTable of course) the compiler very wisely figured out that we probably wanted to have that one back at the end of the request. So it is not only included in the parameter list of BeginInvoke, but also in EndInvoke.

One thing that bothered me while creating the End methods, and I need to have this off of my chest, was how to deal with the nine different overloads in this case. If you don’t use overloads you can skip this part, but you’ll miss the joy of seeing me suffer.

It appeared that I wasn’t able to create nine overloads of EndFillDataTable as I was able to do with BeginFillDataTable. The reason being that the parameter lists in this case are all the same! I quickly discarded the idea of differentiating between the nine versions by using numbers or some other postfix characters. (I’m sure it wouldn’t have looked good on my resume.) So I cursed, I kicked my computer, and I set fire to my MCSD library. Finally I ended up creating nine if…else branches in the same method, each one checking for an actual type of the delegate. (I only included one branch in the example to save you from experiencing the same pain.) It’s still not pretty, but at least this way the ugliness is kept private. The client won’t care if there’s just one overload for EndFillDataTable compared to nine overloads for BeginFillDataTable. And I might just have some coke cans and mars bars left in my backpack that I’m willing to send to Anders Hejlsberg in exchange for a better solution.

Using the new methods

You are now ready to start using your new asynchronous methods. There are two ways of working with these methods, so your clients have a choice: a good one and a bad one. After the clients filed their requests with the Begin method, they can regularly check with the delegate for any results. That’s the bad choice. They can also just continue doing what they were doing until sooner or later the delegate shows up with the results. That’s the good choice. I’m going to show you that one first, and you might want to skip the other one since it’s actually still a bit stupid. And the other code might even be considered a bit rude.

With a callback method

When you want your client to be notified when the job is done, you should write a callback method (yes, yet another method), but this time on the client side. It will be a method that’s going to fetch the results.

private void FillDataTableCallback(IAsyncResult result)
{
      DataTable dt = null;
      SqlHelper.EndFillDataTable(ref dt, result);
}

Fragment 5

Create the callback methods

1. Write a new method with only one parameter, for the IAsyncResult object containing the delegate. Choose any name you like, but a postfix of Callback would be advisable.
2. In this method, call the End method you wrote earlier and supply it with the IAsyncResult object and, if needed, any other ref parameters that you must retrieve from that method. In other cases your End method might return a value as a function result, as is the case with my ExecuteReader. It all depends on what your original data access method looks like. The dog ate my crystal ball, so I cannot decide for you.
3. You can also use this new callback method to do any other additional processing of the results on the client side, like preparing some part of the user interface or feeding some business objects.

Now you’re ready to start calling your asynchronous data access methods. Don’t worry; you’ve already done most of the work. It’s just a few lines of code from here.

DataTable dt = new DataTable();
valuelist = new Object[1];
valuelist[0] = 2;
AsyncCallback callback = new AsyncCallback(FillDataTableCallback);
IAsyncResult result = SqlHelper.BeginFillDataTable(ref dt, connString,
      "TestFillDataTable", callback, null, valuelist);

Fragment 6

Write the calling code

1. In the calling code, you need to prepare the values you’re going to supply to the Begin methods, like I created the new DataTable and the valuelist in my example.
2. Then you should create a new AsyncCallback delegate. It will be a delegate for the callback method you wrote just a minute ago. This delegate will make sure that your callback method will be called when your asynchronous call is completed.
3. Now you’re ready to do the call. Use the Begin method to start the call, supplying all the parameters you would have supplied to your original (synchronous) data access method. But you will also include the callback delegate and, optionally, a state object. For the state object you can pass anything you like. I always use null since I’m just a poor guy with nothing else of interest to give.

You’re done! After calling the Begin method, your calling code can resume its work on any other matters that need attending. When the call completes, your callback method will be called automatically and your client will be able to process the results. If you like, you can do lots of simultaneous calls to the server. Your client can consume the results whenever they are delivered, provided it can handle more deliveries than I was able to in Barcelona. But of course, your client can also load the results up a backpack and consume them at its leisure. Just make sure they aren’t too heavy for your client to carry around all the time!

The example I gave here uses a connection string. Please take care when supplying a connection object. You should not use the same connection object for different asynchronous calls! ADO.NET can only have one DataReader working on a connection at a time. You might get errors at a very unpredictable rate when you try to reuse a connection that is already being used by another thread. (And keep in mind that just about anything uses DataReaders underneath…)

Without a callback method

I actually don’t want to show you this. You’ll be a nuisance if you are going to do it this way. I only want to show you how not to do an asynchronous call. If you actually design your client code this way, you will be questioning the delegates repeatedly for any updates. They won’t like you if you do that. (The waitress didn’t like me when I did that.) The delegates may even be slowed down because of it and you will get your tapas even later. Don’t say I didn’t warn you!

DataTable dt = new DataTable();
valuelist = new Object[1];
valuelist[0] = 2;
IAsyncResult result = SqlHelper.BeginFillDataTable(ref dt, connString,
      "TestFillDataTable3", null, null, valuelist);
while (! result.IsCompleted)
{
      Thread.Sleep(100);
}
SqlHelper.EndFillDataTable(ref dt, result);

Fragment 7

In this case, which I don’t want you to remember, you don’t use a callback method. You just pass null to the AsyncCallback parameter of the Begin method. (It’s like saying “don’t call me I’ll call you”. No wonder the delegates hate that.) It means your client code won’t be notified when the results are ready. Instead, your client code uses the IAsyncResult object that you get back from the Begin method to keep questioning it until it says the job is done. And until it is, the client code can just wait and sleep, as in my example. The End method is then invoked after IsCompleted returns True.

I don’t think I need to tell you that this approach completely defies the whole idea behind asynchronous calls. It leaves your client code as stupid as when it was doing simple synchronous calls. That’s why I don’t want you to do this. Forget about it. Erase anything you read for the last sixty seconds, and tear up this page!

Example

I’m going to leave you with another example that puts everything I told you together. This time I will use the ExecuteReader method with a transaction object and a simple SQL string as the command text.

Here’s the data access code:

//The original synchronous method
//(further details aren’t needed, just imagine it is your own method)
public static IDataReader ExecuteReader(IDbTransaction transaction,
      CommandType commandType, string commandText) { ... }
 
//The new private delegate type for the original method (same signature)
private delegate IDataReader ExecuteReaderDelegate(IDbTransaction
      transaction, CommandType commandType, string commandText);
 
//The Begin method that will start the asynchronous call using the delegate
public static IAsyncResult BeginExecuteReader
      (IDbTransaction transaction, CommandType commandType,
      string commandText, AsyncCallback ac, Object state)
{
      //create a new delegate and have it point to the synchronous method
      ExecuteReaderDelegate d = new ExecuteReaderDelegate(ExecuteReader);
      //invoke the delegate and give it the callback method from the client
      IAsyncResult result = d.BeginInvoke(transaction, commandType,
           commandText, ac, state);
      //return result object in case the client wants to check for progress
      return result;
}
 
//The End method that will end the asynchronous call
public static IDataReader EndExecuteReader(IAsyncResult result)
{
      //cast the delegate object in IAsyncResult to our own type
      ExecuteReaderDelegate d = (ExecuteReaderDelegate)
           ((AsyncResult)result).AsyncDelegate;
      //ask the delegate for any results and return them to the client
      return d.EndInvoke(result);
}

Fragment 8

And here’s the client code:

//The callback method that will process the results when they’re available
private void ExecuteReaderCallback(IAsyncResult result)
{
      //get the results from the data access code
      IDataReader dr = SqlHelper.EndExecuteReader(result);
      //do something useful with it
      while (dr.Read()) { ... };
      dr.Close();
}
 
//Part of the client code calling the asynchronous method
//Imagine this is all part of some transaction 
...
      //create a delegate for our callback method
      AsyncCallback callback = new AsyncCallback(ExecuteReaderCallback);
      //start working on it
      SqlHelper.BeginExecuteReader(transaction, CommandType.Text,
           "SELECT * FROM Test", callback, null);
...

Fragment 9

Conclusion

While at first I found the design pattern for asynchronous method calls a bit intimidating, it’s actually not that difficult to understand. It’s just a lot of wires to get a simple mechanism going. The new asynchronous versions of my data access methods required less than twenty extra lines of code each. Admittedly, the wiring can be a bit confusing at times, but it’s really not that hard once you get the hang of it. And the new methods don’t clash with the original ones. I’ve now actually enabled the client to choose between synchronous and asynchronous data access calls. With just a little effort I feel a lot smarter now. I might even go back to the tapas restaurant in Barcelona someday. And I won’t be bothered with the non-responsiveness of the waitress because I will make sure that I have lots of other things to do, while patiently waiting for my tapas. And I won’t feel stupid anymore. If it happens to be Anders Hejlsberg who’s having tapas with me, after kissing the ground that he walked on, I will thank him for bringing delegates to the world of .NET.

Download

The complete source code of my improved Data Access Application Block, including asynchronous versions for all methods, is available from my site. Feel free to comment on anything I could have done better. Just don’t send flowers. I hate flowers.