Introduction
Ability to execute several tasks on different threads at the same time. Through Asynchronous call the main thread never blocked and another thread calls the method and gives the output to the main thread.
The problem with the ADO.Net 1.x is that one thread has to wait for the other thread to complete. So to overcome this problem Microsoft has introduced Asynchronous data access, through which one can execute multiple threads at a time.
Asynchronous access to the results of multiple commands over the same Connection instance, plus the ability to open connections asynchronously. A database operation is normally a synchronous operation, meaning that the caller regains the control of the application only when the interaction with the database has completed. This way of working may pose performance and scalability issues in cases of lengthy operations. ADO.NET 2.0 provides true asynchronous support for two specific scenarios: opening connections and executing commands. Leveraging these features, you can open a connection and populate the command object while the connection is physically established. This is a clear performance advantage, because it really gives you a bit of parallelism if SQL Server lives on a remote machine.
Although asynchronous execution can be a nice feature, it should not be used gratuitously; only use it if you know the command can run for a long time and also that you have something useful to do at the same time. The Windows thread scheduler in the Windows NT family of operating systems (the feature is not available on Windows 9x and ME clients) takes overhead of its own to switch between threads. Also bear in mind that some .NET libraries are thread-sensitive; using asynchrony, the thread that you use to start the operation won't necessarily be the same thread it finishes on. Not only can asynchronous operation be effective for multiple action statements and stored procedure execution, when used with the Multiple Active Result Sets (MARS) feature in SQL Server 2005, you can multiplex asynchronous SELECT statements using a single database connection.
Asynchronous code is difficult to write and debug, more advanced coding skills are required.
Executing Multiple Commands Sequentially
An alternative situation is when you use several object instances to access data (here multiple DataReader instances, but it could be a mix of DataReader and DataSet instances) over the same connection. Again, only one of these can be executing a query over the single connection at a time. But because the ADO.NET code you write will usually call methods on the DataReader or other objects sequentially and will block on each method call until it is complete, there is no problem with using the same connection. Figure below shows this scenario.
In this case, however, there are two areas where performance and usability issues can arise:
- If you open a rowset with a DataReader over the connection, you must close it before you attempt to open another DataReader or execute another command-if not, you'll get a "Connection is busy ..." error. To be able to open more than one rowset concurrently in ADO.NET version 1.x requires that each command have its own separate connection to the database. Because the number of database connections available is limited and they are expensive in terms of resource usage, this often isn't a feasible approach.
- If one of the commands you're executing takes a long time to return results, the code in the application will block and wait until the query process is complete before it can execute the next command.
So your code may be standing idle when it could be doing something else in the meantime.
New API Elements
We modeled the new ADO.NET asynchronous API after the existing APIs in the .NET Framework, with similar functionality. Consistency is an important thing in large frameworks
Asynchronous Methods
All command execution APIs are in the Command object in ADO.NET, including ExecuteReader, ExecuteNonQuery, ExecuteXmlReader and ExecuteScalar. We decided to minimize the API surface that we added for this feature, so we added asynchronous versions only for the methods that couldn't be adapted from other methods: ExecuteReader, ExecuteNonQuery and ExecuteXmlReader. ExecuteScalar is simply a short form of ExecuteReader + fetch first row/first column + close the reader, so we didn't include an asynchronous version of it.
Following the asynchronous API pattern already in use in the .NET Framework, each existing synchronous method has now an asynchronous counterpart that's split into two methods; a begin part that starts the work, and an end part that completes it. The table below summarizes the new methods in the command object:
Table 1. New asynchronous methods available in ADO.NET 2.0
|
Synchronous Method |
Asynchronous Method |
|
"Begin" part |
"End" part |
| ExecuteNonQuery |
BeginExecuteNonQuery |
EndExecuteNonQuery |
| ExecuteReader |
BeginExecuteReader |
EndExecuteReader |
| ExecuteXmlReader |
BeginExecuteXmlReader |
EndExecuteXmlReader |
The asynchronous pattern models methods, so the begin method takes all the input parameters, and the end method provides all the output parameters, as well as the return value. For example, here is what an asynchronous invocation of ExecuteReader looks like.
The "async" Connection String Keyword
In order to use asynchronous commands, the connections on which the commands will be executed must be initialized with async=true in the connection string. An exception will be thrown if any of the asynchronous methods are called on a command with a connection that doesn't have async=true in its connection string.
BeginExecuteReader
Starts an asynchronous query to the data source that is expected to return some rows. The return value is a reference to an object that implements the IAsyncResult interface, in this case an instance of the SqlAsyncResult class, which is used to monitor and access the process as it runs and when it is complete.
Ex : async-result = command.BeginExecuteReader(callback, state)
It takes an AsyncCallback instance that specifies the callback routine to be executed when the process is complete, plus an Object that defines state information for the process.
EndExecuteReader
Once the command execution started by a BeginExecute Reader call has completed, this method is called to access the results. The single parameter is a reference to the SqlAsyncResult for the command, and the method returns a DataReader that references the rowset(s) returned by the query (in the same way the ExecuteReader method does for synchronous processes).
Ex: data-reader = command.EndExecuteReader(async-result).
Models in Asynchronous
1. The Asynchronous Polling Model
The simplest approach to handling asynchronous execution of one or more commands is through the polling model. It simply involves starting off the and then repeatedly checking the IsCompleted property of the SqlAsyncResult instance until it returns True.
Of course, the code can go off and do other things between checking to see whether the process is complete. However, this approach is not recommended unless it is a simple and "tight" loop that handles a specific required task and uses only minimal processing time. If there are large or unrelated tasks to accomplish, you should consider using the callback or wait models instead.
2. The Asynchronous Callback Model
The second approach to asynchronous execution involves providing a routine in the code that will act as a callback. It will be executed when the action you specify occurs, rather like the way that an event handler is called to handle a user's interaction with an application.
To specify the callback routine you create a new instance of the Async Callback class, providing the name of that routine as the parameter, and pass this AsyncCallback instance into the method you use to start execution of the command.
Asynchronous technique employs the familiar asynchronous programming model using the AsyncCallback delegate in .NET, and so includes the SqlAsyncResult class to implement the IAsyncResult interface. While this feature works only for SqlClient at the moment.
IAsyncResult Interface : Represents the status of an asynchronous operation.
The IAsyncResult interface is implemented by classes containing methods that can operate asynchronously. An object that supports the IAsyncResult interface stores state information for an asynchronous operation, and provides a synchronization object to allow threads to be signaled when the operation completes.
3. The Asynchronous Wait Model
The most complex of the asynchronous methods is also the most efficient if all you want to do is start some commands running against one or more data sources (they can all use separate connections and therefore different databases if required) and not execute other code in the meantime. You simply want to wait until one, more, or all of the commands have completed and then perhaps display some results. In this case, you start each process in the same way as the previous examples but then use the AsyncResult to create a WaitHandle that you use to monitor each process.
The following code explains the usage of techniques of asynchronous programming and multithreading.
Asynchronous Callback Model Example:
using
System;
using System.Data;
using System.Data.SqlClient;
// You'll need this delegate in order to fill the grid from a thread other than the form's thread. See the Callback1 //procedure for more information.
private delegate void DelFillGrid(SqlDataReader reader);
// You'll need this delegate to update the status bar.
private delegate void DisplayStatusDelegate(string Text);
// It indicates whether asynchronous command is active or closed.
private bool isExecuting = false;
private SqlConnection conn = null;
private void DisplayStatus(string Text)
{
this.label1.Text = Text;
}
private void FillGrid(SqlDataReader dr)
{
try
{
DataTable dt = new DataTable();
dt.Load(dr);
this.dataGridView1.DataSource = dt;
DisplayStatus("Ready");
}
catch (Exception ex)
{
// Because you're guaranteed this procedure
// is running from within the form's thread,
// it can directly interact with members of the form.
DisplayStatus(string.Format("Ready (last attempt failed: {0})", ex.Message));
}
finally
{
// Don't forget to close the connection, as well.
if (dr != null)
{
dr.Close();
}
if (conn!= null)
{
conn.Close();
}
}
}
private void btnAsync_Click(object sender, System.EventArgs e)
{
if (isExecuting)
{
MessageBox.Show(this, "Already executing. Please wait until the current query " + "has completed.");
}
else
{
SqlCommand command = null;
try
{
DisplayStatus("Connecting...");
//To Execute the command asynchronously we need to make Asynchronous Processing=true
conn= new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=Northwind; Asynchronous Processing=true");
// To emulate a long-running query, wait for a few seconds before retrieving the real data.
command = new SqlCommand();
command.CommandText = "WAITFOR DELAY '00:00:05' : SELECT * FROM Customers" ;
command.Connection = conn;
conn.Open();
DisplayStatus("Executing...");
isExecuting = true;
//Passing the SQLCommand as a parameter makes easier to call EndExecuteReader();
AsyncCallback callback = new AsyncCallback(Callback1);
command.BeginExecuteReader(callback, command);
}
catch (Exception ex)
{
DisplayStatus("Error: " + ex.Message);
if (conn!= null)
{
conn.Close();
}
}
}
}
private void Callback1(IAsyncResult result)
{
try
{
// To retrieve the original command object.
SqlCommand command = (SqlCommand)result.AsyncState;
SqlDataReader dr = command.EndExecuteReader(result);
// To execute the code from a different thread instead of main thread.
DelFillGrid del = new DelFillGrid(FillGrid);
// To call the form's delegate.
this.Invoke(del, dr);
// Reader is to be closed at the end, as some thread may be using it. Use seperate procedure to close it
}
catch (Exception ex)
{
// We are running the code in a seperate thread so we need to catch the exception. Else we are unable to catch the exception anywhere.
this.Invoke(new DisplayStatusDelegate(DisplayStatus), "Error: " + ex.Message);
}
finally
{
isExecuting = false;
}
}
private void Form1_Load(object sender, System.EventArgs e)
{
this.btnAsync.Click += new System.EventHandler(this.btnAsync_Click);
this.FormClosing += new FormClosingEventHandler(Form1_FormClosing);
}
void Form1_FormClosing(object sender, FormClosingEventArgs e)
{
if (isExecuting)
{
MessageBox.Show(this, "Can't close the form until " + "the pending asynchronous command has completed. "); e.Cancel = true;
}
Summary
- Asynchronous access to the results of multiple commands over the same Connection instance, plus the ability to open connections asynchronously
- Main Features
- Ability to switch execution to another thread
- Ability to make calls to network resources in a way that does not block any threads
- Important for high-end 3-tier server applications