This month, my article will talk about 2 topics related to accessing data using specifically ADO.Net 2.0. I will first introduce you to MARS which stands for Multiple Active Result Set allowing you to have more then one data reader on a single connection at a specific time. My second topic will be on the asynchronous features of ADO.Net 2.0 without using threading. Well almost without threading as you will discover later!

MARS

When you started using ADO.Net (probably using the .Net Framework 1.x), you were surely told that using a DataReader is more efficient then using a DataSet when you need nothing more then going through all the records returns by your query and populating some kind of structure like a treeview control. So you probably start using a DataReader up to the day when you received this exception: There is already an open DataReader associated with this Connection which must be closed first. This error has occurred because you tried to open a second DataReader on the same connection while the first DataReader was still used. Does this sound familiar to you?

This is what MARS is all about. You can now open more then one DataReader on a single connection. To use MARS, you need 3 things:

• You need to use ADO.Net 2.0
• You need to use a capable data provider (there is only the SQLClient provider against a SQL Server 2005 database and the OracleClient managed provider against any version of Oracle database that are currently supporting this feature). Notice that I will only talk about SQL Server in this article.
• You are finally required to modify your connection string.

We now need to modify the connection string by adding an attribute to it:

    MultipleActiveResultSets=True

Notice that by default, this attribute is set to False. You might be tempted to add to your global connection string and that’s a bad idea because this attribute adds some overhead to your database connections. I suggest that you activate this feature only when required (that’s what my demo application is doing).

I won’t copy all the code of my demo application because it is really straightforward. When you look at the GUI (of the fMars form), you see 2 buttons and a treeview. The goal of this demo is to fill a treeview (should sounds familiar for regular readers!). The first level is filled with the categories of the NorthWind database retrieved into a DataReader object. While reading each category, a new DataReader is trying to retrieve all the products of the current category. The first button modifies the connection string by adding the new attribute with a False value to simulate the error. The second button adds the new attribute to the connection string with a True value to prove that it is working.

Notice that my demo application is using the ExecuteReader of a SQLCommand object. You can also use ExecuteNonQuery and/or ExecuteScalar as well.

That’s it for that topic. You can now review your code where you had to find a workaround (I often saw people opening 2 connections) to the problem that was problematic in the previous version of ADO.Net.

Asynchronous command execution

In this section I will show you how to run multiple queries at the same time without using threading features. Notice that this feature is supported by the ADO.Net SQLClient provider and not by the database itself (that means that you can use these techniques on SQL 7, 2000, and/or 2005).

Before going into the code, I need to ensure that you exactly know what I (and ADO.Net) mean by asynchronous execution.

First consider figure 1 which shows the sequence for synchronous query. The client (say a SQLCommand in your application) on the left prepares a query and sends it (by the mean of calling a method like ExecuteReader). The database executes the query and returns the result to the calling application. Only then, the next line of code (the line that follows the call to the ExecuteReader method) is executed. In synchronous execution, each command must complete before the next command begins executing.

Figure 1: Synchronous queries

When we are using an asynchronous technique, the same steps are done but this time, the line of code that follows the call to the ExecuteReader method gets executed before the database as completely returned the results. This is where we can get performance by doing something else while waiting that the database returns results.

Figure 2: Asynchronous queries

But the thing is: how will I know when my results are ready to be retrieved? I will show you 3 methods here. The steps to use asynchronous commands are the following:

• Create a connection string with a special attribute
• Create a regular SQLCommand object
• Start the SQLCommand using one of the BeginExecuteXXX methods
• Call the according EndExecuteXXX methods

In my demo application, the fADOAsync form is the form that contains all the code shown here (I will not show all the code here so you better download the example).

The first step is to create a connection string that will indicate that our application want to do some asynchronous commands. This is done by adding a new attribute to our connection string:

Asynchronous Processing=True

Async=True

The second step is to create a command. This command has nothing special. Nothing says that this command will be used asynchronously later. In fact, the same SQLCommand could be used synchronously or asynchronously without any changes up to here. For example, the SQLCommand used by the first method in my demo application looks like this:

strSQL = "Waitfor delay '00:00:10';" + _
         "Select Count(*) as EmployeesCount from Employees"
mcmEmployees = New SqlCommand(strSQL, mcnNorthwind)

The third step is to call one of the BeginExecuteXXX methods. The 3 available methods are BeginExecuteNonQuery, BeginExecuteReader and BeginExecuteXMLReader. You have surely found that these 3 methods looks like the ExecuteXXX methods you are already familiar to. But wait a minute, there is no BeginExecuteScalar method! Microsoft thought that it was not required because it could be easily replaced with a BeginExecuteReader followed by a single read. When you call one of these 2 methods, you must also decide if you want to use callbacks or not to be warned when your results are ready. My first example will show you how to use callbacks while the 2 others won’t make use of callbacks. So read on!

The fourth and last step is to call the according EndExecuteXXX method. These methods retrieve the data returned by the database and store it into a DataReader object. What you absolutely need to know is that these 3 EndExecuteXXX methods are synchronous. Otherwise said, the execution is blocking on this line until the database gives results so it serves nothing to find BeginExecuteXXX method on a line of code immediately followed by an EndExecuteXXX method. That would mean that you are using asynchronous feature in a synchronous way. So we will need a way do something else before effectively calling the EndExecuteXXX method and that’s why my 3 examples will be showing next.

Asynchronous queries – Method 1

The first method (the CallBack button) will use a callback to be warned when the results are ready. For those who are not familiar with callbacks, you can think of them as a kind of events (like the Click of button).

When I am ready to start the query, I can use this syntax:

mcmEmployees.BeginExecuteReader(AddressOf QueryCallbackMethod, Nothing)

Immediately after the BeginExecuteXXX method has been launched, the following line is also executed (which in my case is a timer that gets started to display the hour in a label):

Timer1.Start()

The QueryCallBackMethod is a method that must exist (otherwise it won’t compile). This method gets automatically called when the database returns the result. The results are stored into the ar argument. In my demo application, this method has this code that displays the result of my SELECT COUNT(*) query:

Sub QueryCallbackMethod(ByVal ar As IAsyncResult)
    'Stops the timer 
    Timer1.Stop()
    Try
        'Retrieves the data from the AR argument
        Dim drEmp As SqlDataReader = mcmEmployees.EndExecuteReader(ar)
        'Read the first row of the result set
        drEmp.Read()
        'Displays the value returned
        MessageBox.Show(drEmp("EmpCount").ToString + " rows found")
    Catch ex As Exception
        'Display a message if there is an exception
        MessageBox.Show(ex.Message)
    Finally
        'Close the connection
        mcnNorthwind.Close()
    End Try
End Sub

Asynchronous queries – Method 2

You may not like the fact that a callback is used. You may prefer having a more linear application but still have the asynchronous feature. That’s not a problem. You can use this yet simpler syntax (from my second button IsCompleted):

Dim arAuthors As IAsyncResult = cmAuthors.BeginExecuteReader()

If you simply omit the callback reference of the BeginExecuteXXX, no callbacks will be used but you then need another way of knowing when the results are ready. This second example is using the IsCompleted property of the IAsyncResult object (arAuthors) in a loop to know if results are ready (letting us doing something else meanwhile). And because this example has 2 commands that are launched at the same time, the IsCompleted property of both IAsyncResult objects are monitored. Also notice that both queries contain a Waitfor operator (for 10 and 5 seconds). Without asynchronous queries, this process would last 15 seconds but it actually takes about 10 seconds.

Once the IsCompleted property is true for both objects, we can call the EndExecuteXXX method of each object to retrieve the results.

Asynchronous queries – Method 3

Instead of waiting after all queries to complete, what if we wanted to process each DataReader as they return? That’s exactly what my third example is showing. I could have developed something using the IsCompleted property to know when all and do something else to detect when all objects have been processed.

Instead, I will use a special class found into the System.Threading namespace. This class is named WaitHandle and is very helpful in helping us knowing when objects complete their tasks.

Each IAsyncResult objects returns a handle to implement the WaitHandle mechanism. After the BeginExecuteReader have been called, you can store the handles into an array:

Dim arrWaitHandles(1) As WaitHandle
arrWaitHandles(0) = arAuthors.AsyncWaitHandle
arrWaitHandles(1) = arEmployees.AsyncWaitHandle

Then, in a loop, you can call the shared WaitAny method of the WaitHandle that will wait until one of the handles completes its task or a timeout occurs (1 second in my example):

intIndex = WaitHandle.WaitAny(arrWaitHandles, 1000, False)

The integer value returned by the WaitAny method is either the index of element that returns a complete state or the special WaitTimeOut value. You use this value to call the appropriate EndExecuteXXX method:

Select Case intIndex
    Case WaitHandle.WaitTimeout
        lblAsyncResult.Text = Date.Now.ToLongTimeString
        Application.DoEvents()
    Case 0
        Dim drAuthors As SqlDataReader
        drAuthors = cmAuthors.EndExecuteReader(arAuthors)
        Do While (drAuthors.Read())
            lstAuthors.Items.Add(drAuthors("au_fname"))
        Loop
        intCount += 1
    Case 1
        Dim drEmployees As SqlDataReader
        drEmployees = cmEmployees.EndExecuteReader(arEmployees)
        Do While (drEmployees.Read())
            lstEmployees.Items.Add(drEmployees("FirstName"))
        Loop
        intCount += 1
End Select

Conclusion

These methods shouldn’t stop you trying to optimize your query to ensure performance. These methods should be used when optimized queries slow down the application caused by slow networks or because your optimizes query are long to execute.

I hope you appreciated the topic and see you next month.