Lesson 3: Performing Asynchronous Processing

Estimated lesson time: 30 minutes

Asynchronous processing enables you to perform operations in parallel. This means that one operation can begin before the other has completed. ADO.NET enables you to execute two commands asynchronously. This capability can be especially useful when you need to perform operations against two different databases. It can also be effective in ASP.NET applications in which one set of data can be rendered to the client before all the operations have completed.

Asynchronous processing is supported through the SqlClient provider, but other providers could add the capabilities if warranted. This section will focus on executing commands asynchronously using the System.Data.SqlClient namespace. Implemented through the Command object, it can be accomplished using any of the following command operations: ExecuteReader, ExecuteXmlReader, ExecuteScalar, and ExecuteNonQuery.

Exam Tip

If you search on the MSDN Web site for "asynchronous processing," you might see papers that reference a new feature called Service Broker. Service Broker is a server-side platform that can be used to build asynchronous programs. For this exam, you only need to be aware of how to use ADO.NET to execute commands asynchronously.

Asynchronous processing can provide a performance advantage for your applications. It is especially useful in situations where a long-running database operation would slow down the processing of another operation. However, enabling asynchronous processing does result in additional overhead. Therefore, it should only be used if your application would benefit by allowing one operation to begin before another has completed.

Using Asynchronous Processing

Enabling asynchronous processing through ADO.NET requires that you first add directives to the Microsoft.Data.SqlClient namespace and potentially the System.Threading namespace as well. You will also need to add the Asynchronous Processing=True keyword to your connection string. For example, the following connection string could be used to enable asynchronous processing:

 //C# //Connect to the database and enable asynchronous processing SqlConnection conn = new SqlConnection(); String connStr = @"Data Source=.\SQL2005STD;" +     "Initial Catalog=AdventureWorks;" +     "Integrated Security=SSPI;" +     "Asynchronous Processing=True;"; conn.ConnectionString = connStr; conn.Open(); 'VB 'Connect to the database and enable asynchronous processing Dim conn As New SqlConnection Dim connStr As String = "Data Source=.\SQL2005STD;" + _     "Initial Catalog=AdventureWorks;" + _     "Integrated Security=SSPI;" + _     "Asynchronous Processing=True;" conn.ConnectionString = connStr conn.Open()

Once asynchronous processing has been enabled, you can access certain asynchronous methods through the Command object. Each operation includes a Begin and End method. For example, if you want to use ExecuteReader to populate a DataReader object asynchronously, you would use the BeginExecuteReader and EndExecuteReader methods instead.

The BeginExecuteReader method will return an IAsynchResult object, which will be used to track the progress of the operation. You can then poll the IsCompleted property to determine when the operation has completed. Once complete, you can call the associated End method. For example, the following code can be used to execute a query asynchronously:

 //C# SqlCommand cmd = new SqlCommand("Select * from Person.Contact", conn); IAsyncResult result = cmd.BeginExecuteReader; while (! result.IsCompleted) {     //Perform additional processing } cmd.EndExecuteReader(result); 'VB Dim cmd As New SqlCommand("Select * from Person.contact", conn) Dim result As IAsyncResult = cmd.BeginExecuteReader While Not result.IsCompleted     'Perform additional processing End While cmd.EndExecuteReader(result)

There are several methods of handling the results of this type of processing. The IAsynchResult object has an AsyncWaitHandle property that enables you to issue a block until the operation completes. Finally, you could use a callback function that executes only when the operation is complete.

In some cases, it might be necessary to cancel an executing command. The Command object provides a Cancel method for accomplishing this. In terms of error handling, it is possible you could receive errors on both the Begin and End methods. You might have to make special considerations if certain operations are dependent on others, such as rolling back a previous transaction.

Lab: Performing Asynchronous Processing

Lab 3 contains one exercise in which you create a Windows-based application that is used to perform asynchronous processing.

The completed code examples, in both Visual Basic and C#, are available in the \Labs\Chapter 04 folder on the companion CD.

Important

Lab requirements

You will need to have SQL Server 2005 installed before you can complete this lab. Refer to the Introduction for setup instructions.

Exercise 1: Use the Callback Feature

In this exercise, you create a simple Windows application that has one button. The button will perform an asynchronous query using the callback feature. The code in the callback feature will simply return a message box that displays the amount of time the query took to execute.

Open Microsoft Visual Studio 2005.
If you completed the practice from Lesson 1, skip to step 5.
Click File, New, Project.
In the New Project dialog box, expand the Other Project Types node, and select Visual Studio Solutions. Type TK442Chapter4 for the name of your blank solution, and place it in a directory of your choosing. A new solution file will be created, and you can now add multiple projects to this solution. You will add one project for each lab included in this chapter.
Select File, Add, New Project. Select Windows Application as the template, and type Lab3 as the project name. Set the language by selecting Visual Basic or Visual C# from the language drop-down list box. By default, Visual Studio will select the language specified when it was first configured.
From the Toolbox, drag one label control onto the Default design surface. Use the following property value for this control:
```
 Name = lblInstance Text = "Server Instance:" 
```
From the Toolbox, drag one textbox control onto the Default design surface. Use the following property value for this control:
```
 Name = txtInstance 
```
Right-click the Form1 file from Solution Explorer, and select View Code. Add the following directives to the top of the file:
```
 //C# using System.Data.SqlClient; using System.Threading; 'VB Imports System.Data.SqlClient Imports System.Threading 
```

Now add the following code before the constructor:

 //C# private int count; private SqlCommand cmd; 'VB Dim count As Int16 Dim cmd As SqlCommand

Now add the following code beneath the Load method for the Form1:

 //C# private void btnQuery_Click(object sender, EventArgs e) {      //Change the cursor to a wait cursor and get start time      this.Cursor = Cursors.WaitCursor;      DateTime startTime = DateTime.Now;      //Connect to the database and enable asynchronous processing      SqlConnection conn = new SqlConnection();      String connStr = @"Data Source=" + txtInstance.Text + ";" +        "Initial Catalog=AdventureWorks;" +        "Integrated Security=SSPI;" +        "Asynchronous Processing=True;";      conn.ConnectionString = connStr;      conn.Open();      //Create our command      cmd = new SqlCommand("SELECT Count(*) FROM Person.Contact", conn);      //Reference the callback procedure      IAsyncResult result = cmd.BeginExecuteReader(arCallBackMethod, null);      while (!result.IsCompleted)      {          Thread.Sleep(1000);  //Pause the thread for a second          count += count;      }      //Change the cursor back to default and get end time      this.Cursor = Cursors.Default; } private void arCallBackMethod(IAsyncResult ar) {      SqlDataReader dr = cmd.EndExecuteReader(ar);      //We could display the results or process them at this point      dr.Read();      MessageBox.Show(dr[0].ToString() + " records were processed." +         " The query took approximately " + count + " seconds to process"); } 'VB Private Sub btnAsynch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAsynch.Click         'Change the cursor to a wait cursor and get start time         Me.Cursor = Cursors.WaitCursor         Dim startTime As DateTime         startTime = Now()         'Connect to the Ddtabase and enable asynchronous processing         Dim conn As New SqlConnection         Dim connStr As String = "Data Source=" + txtInstance.Text + ";" + _             "Initial Catalog=AdventureWorks;" + _             "Integrated Security=SSPI;" + _             "MultipleActiveResultSets=True;"         conn.ConnectionString = connStr         conn.Open()         'Create our command         cmd = New SqlCommand("SELECT Count(*) FROM Person.Contact", conn)         'Reference the callback procedure         Dim result As IAsyncResult = cmd.BeginExecuteReader(AddressOf arCallbackMethod, Nothing)         While Not result.IsCompleted             Thread.Sleep(1000)  'Pause the thread for a second             count += count         End While         'Change the cursor back to default and get end time         Me.Cursor = Cursors.Default     End Sub     Private Sub arCallbackMethod(ByVal ar As IAsyncResult)         Dim dr As SqlDataReader = cmd.EndExecuteReader(ar)         'We could display the results or process them at this point         dr.Read()         MessageBox.Show(dr(0).ToString() + " records were processed." + _             " The query took approximately " + count + " seconds to process")     End Sub

Save the Lab3 project by clicking File, and then Save All.
Right-click the Lab3 project from Solution Explorer, and select Set As Startup project.
Press Ctrl+F5 to build the project without debugging. Ensure that the project builds successfully. Form1 should appear after the project compiles and executes. You should see one button and a text box. Type the name of the instance of SQL Server 2005 in which you have the AdventureWorks database installed. The cursor should change to an hourglass, and in a second or less, you should receive a message box with the results.