Asynchronous Data Access

 

Asynchronous Data Access

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.

image from book
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.

image from book
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.

image from book

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 
image from book

image from book

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; } 
image from book

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.

image from book

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 
image from book

image from book

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; } 
image from book

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.

 


Programming Microsoft ADO. NET 2.0 Applications. Advanced Topics
Linux Application Development (2nd Edition)
ISBN: 735621411
EAN: 2147483647
Year: 2004
Pages: 85
Authors: Michael K. Johnson, Erik W. Troan
BUY ON AMAZON

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net