Asynchronous access to data can greatly improve the performance or perceived performance (responsiveness) of your application. With asynchronous access, multiple commands can be executed simultaneously and notification of command completion can be accomplished by either polling, using WaitHandles, or delegates.
Synchronous vs. Asynchronous Access
Commands are normally executed synchronously, which causes the command to "block" program execution until the command has completed. Blocking execution keeps the program from continuing until the command has finished executing. This simplifies the writing of the code because the developer simply thinks about code execution in a rather procedural, step-by-step fashion, as shown in Figure 4-4. The problem arises with long-running commands because blocking inhibits the program's ability to do other work, such as performing additional commands or, more important, allowing the user to abort the command.
Figure 4-4: Synchronous data access
Asynchronous command execution does not block program execution because it takes place on a new thread. The new thread represents a new program execution path, which means the original thread can continue executing while the new thread is waiting for its command to complete, as shown in Figure 4-5. The original thread is free to repaint the screen or listen for other events, such as button clicks.
Figure 4-5: Asynchronous data access
Synchronous code is considered easier to write than asynchronous code because of its procedural nature, but the extra effort to write asynchronous code gives your users a much more responsive system.
To demonstrate the difference between synchronous and asynchronous data access, the following code uses synchronous data access. This code simulates a long-running query and then places a message in a label on the form.
Visual Basic
Private Sub Button1_Click( _ ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button1.Click Dim ver As String = nothing Dim cnSettings As SqlConnectionStringBuilder cnSettings = New SqlConnectionStringBuilder(_ "Data Source=.;" _ + "Database=PUBS;" _ + "Integrated Security=True;" _ + "Max Pool Size=5") Using cn As SqlConnection = New SqlConnection(cnSettings.ConnectionString) cn.Open() Using cmd As SqlCommand = cn.CreateCommand() cmd.CommandText = "WaitFor Delay '00:00:15' Select @@Version" ver = CType(cmd.ExecuteScalar(), String) End Using End Using Label1.Text = ver End Sub
C#
private void button1_Click(object sender, EventArgs e) { string ver = null; SqlConnectionStringBuilder cnSettings; cnSettings = new SqlConnectionStringBuilder( @"Data Source=.;" + "Database=PUBS;" + "Integrated Security=True;" + "Max Pool Size=5"); using (SqlConnection cn = new SqlConnection(cnSettings.ConnectionString)) { cn.Open(); using (SqlCommand cmd = cn.CreateCommand()) { cmd.CommandText = "WaitFor Delay '00:00:15' Select @@Version"; ver = (string)cmd.ExecuteScalar(); } } label1.Text = ver; }
In this code, if you attempt to move or resize the window while the query is executing, the window does not respond. While you're waiting, if you want to look at another application, such as Microsoft Internet Explorer, you can open it on top of this application. If you minimize Internet Explorer while the query is still running, the application's form does not repaint until the query has completed.
To implement this application using asynchronous code, you must first set the connection string to have Asynchronous Processing=true or async=true. Otherwise, any attempt to execute a command asynchronously throws an exception. Next, one of the command object's Begin methods must be executed. The synchronous example just shown uses the ExecuteScaler method. Because there is no asynchronous version of this method, we'll use the closest equivalent, BeginExecuteReader, for our example. (The SqlCommand object provides the BeginExecuteNoQuery, BeginExecuteReader, and BeginExecuteXmlReader methods.) The following code shows the asynchronous implementation.
Visual Basic
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Hand les Button2.Click Dim cnSettings as SqlConnectionStringBuilder cnSettings = new SqlConnectionStringBuilder(_ "Data Source=.;" _ + "Database=PUBS;" _ + "Integrated Security=True;" _ + "Max Pool Size=5;" _ + "async=true") Dim cn as new SqlConnection(cnSettings.ConnectionString) cn.Open() Dim cmd as SqlCommand = cn.CreateCommand() cmd.CommandText = "WaitFor Delay '00:00:15' Select @@Version" cmd.BeginExecuteReader(new AsyncCallback(AddressOf ProcessResult),cmd) End Sub public Sub ProcessResult(ar as IAsyncResult) Dim cmd as SqlCommand = ctype(ar.AsyncState, SqlCommand) using cmd.Connection using cmd dim ver as string = nothing dim rdr as SqlDataReader = cmd.EndExecuteReader(ar) if (rdr.Read()) then ver = ctype(rdr(0),string) label1.BeginInvoke(new LabelHandler(AddressOf UpdateLabel), ver) end if end using end using End Sub public delegate sub LabelHandler(text as string) public sub UpdateLabel(text as string) label1.Text = text End Sub
C#
private void button2_Click(object sender, EventArgs e) { SqlConnectionStringBuilder cnSettings; cnSettings = new SqlConnectionStringBuilder( @"Data Source=.;" + "Database=PUBS;" + "Integrated Security=True;" + "Max Pool Size=5;" + "async=true"); SqlConnection cn = new SqlConnection(cnSettings.ConnectionString); cn.Open(); SqlCommand cmd = cn.CreateCommand(); cmd.CommandText = "WaitFor Delay '00:00:15' Select @@Version"; cmd.BeginExecuteReader(new AsyncCallback(ProcessResult),cmd); } public void ProcessResult(IAsyncResult ar) { SqlCommand cmd = (SqlCommand)ar.AsyncState; using (cmd.Connection) { using (cmd) { string ver = null; SqlDataReader rdr = cmd.EndExecuteReader(ar); if (rdr.Read()) { ver = (string)rdr[0]; label1.BeginInvoke(new LabelHandler(UpdateLabel), ver); } } } } public delegate void LabelHandler(string text); public void UpdateLabel(string text) { label1.Text = text; }
As you can see, the label is populated with the result of the query. The problem is that properties on Windows form controls must be set using the same thread as the thread that created the control. The ProcessResult method is always running with a different thread, so the result must be marshaled back to the original thread. You do this by executing the BeginInvoke method on the label and passing the text that needs to be placed into the label. The BeginInvoke method requires a delegate, so the delegate called LabelHandler is created and used. The LabelHandler delegate points to the UpdateLabel method, which is called by the thread that created the label.
In this asynchronous example, when the command is executed the form can still be resized or moved, and if another window temporarily covers this form, the form will be repainted as soon as the form is uncovered.
