Asynchronous Data Access using Callback Model

Ability to execute several tasks on different threads at the same time.Through Asynchronous call the main thread never blocked and anotherthread calls the method and gives the output to the main thread.

The problem with the ADO.Net 1.x is that onethread has to wait for the other thread to complete. So to overcomethis problem Microsoft has introduced Asynchronous data access, throughwhich one can execute multiple threads at a time.Asynchronous access to the results of multiple commands over the sameConnection instance, plus the ability to open connectionsasynchronously. A database operation is normally a synchronousoperation, meaning that the caller regains the control of theapplication only when the interaction with the database has completed.This way of working may pose performance and scalability issues incases of lengthy operations. ADO.NET 2.0 provides true asynchronoussupport for two specific scenarios: opening connections and executingcommands. Leveraging these features, you can open a connection andpopulate the command object while the connection is physicallyestablished. This is a clear performance advantage, because it reallygives you a bit of parallelism if SQL Server lives on a remote machine.

Although asynchronous execution can be a nicefeature, it should not be used gratuitously; only use it if you knowthe command can run for a long time and also that you have somethinguseful to do at the same time. The Windows thread scheduler in theWindows NT family of operating systems (the feature is not available onWindows 9x and ME clients) takes overhead of its own to switch betweenthreads. Also bear in mind that some .NET libraries arethread-sensitive; using asynchrony, the thread that you use to startthe operation won't necessarily be the same thread it finishes on. Notonly can asynchronous operation be effective for multiple actionstatements and stored procedure execution, when used with the MultipleActive Result Sets (MARS) feature in SQL Server 2005, you can multiplexasynchronous SELECT statements using a single database connection.

Asynchronous code is difficult to write and debug, more advanced coding skills are required.

Executing Multiple Commands SequentiallyAn alternative situation is when you use several object instances toaccess data (here multiple DataReader instances, but it could be a mixof DataReader and DataSet instances) over the same connection. Again,only one of these can be executing a query over the single connectionat a time. But because the ADO.NET code you write will usually callmethods on the DataReader or other objects sequentially and will blockon each method call until it is complete, there is no problem withusing the same connection. Figure below shows this scenario.

In this case, however, there are two areas where performance and usability issues can arise:
1. If you open a rowset with a DataReader over the connection, you mustclose it before you attempt to open another DataReader or executeanother command?if not, you?ll get a ?Connection is busy . . .? error.To be able to open more than one rowset concurrently in ADO.NET version1.x requires that each command have its own separate connection to thedatabase. Because the number of database connections available islimited and they are expensive in terms of resource usage, this oftenisn?t a feasible approach.
2. If one of the commands you?re executing takes a long time to returnresults, the code in the application will block and wait until thequery 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 inthe .NET Framework, with similar functionality. Consistency is animportant thing in large frameworks
Asynchronous Methods
All command execution APIs are in the Command object in ADO.NET,including ExecuteReader, ExecuteNonQuery, ExecuteXmlReader andExecuteScalar. We decided to minimize the API surface that we added forthis feature, so we added asynchronous versions only for the methodsthat couldn't be adapted from other methods: ExecuteReader,ExecuteNonQuery and ExecuteXmlReader. ExecuteScalar is simply a shortform of ExecuteReader + fetch first row/first column + close thereader, so we didn't include an asynchronous version of it.
Following the asynchronous API pattern already in use in the .NETFramework, each existing synchronous method has now an asynchronouscounterpart that's split into two methods; a begin part that starts thework, and an end part that completes it. The table below summarizes thenew methods in the command object:
Table 1. New asynchronous methods available in ADO.NET 2.0

The asynchronous pattern models methods, sothe begin method takes all the input parameters, and the end methodprovides all the output parameters, as well as the return value. Forexample, here is what an asynchronous invocation of ExecuteReader lookslike.
The "async" Connection String Keyword
In order to use asynchronous commands, the connections on which thecommands will be executed must be initialized with async=true in theconnection string. An exception will be thrown if any of theasynchronous methods are called on a command with a connection thatdoesn't have async=true in its connection string.

BeginExecuteReader
Starts an asynchronous query to the data source that is expected toreturn some rows. The return value is a reference to an object thatimplements the IAsyncResult interface, in this case an instance of theSqlAsyncResult class, which is used to monitor and access the processas it runs and when it is complete.
Ex : async-result = command.BeginExecuteReader(callback, state)
It takes an AsyncCallback instance that specifies the callback routineto be executed when the process is complete, plus an Object thatdefines state information for the process.

EndExecuteReader
Once the command execution started by a BeginExecute Reader call hascompleted, this method is called to access the results. The singleparameter is a reference to the SqlAsyncResult for the command, and themethod returns a DataReader that references the rowset(s) returned bythe query (in the same way the ExecuteReader method does forsynchronous 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 morecommands is through the polling model. It simply involves starting offthe and then repeatedly checking the IsCompleted property of theSqlAsyncResult instance until it returns True.Of course, the code can go off and do other things between checking tosee whether the process is complete. However, this approach is notrecommended unless it is a simple and "tight" loop that handles aspecific required task and uses only minimal processing time. If thereare large or unrelated tasks to accomplish, you should consider usingthe callback or wait models instead.

2. The Asynchronous Callback Model
Thesecond approach to asynchronous execution involves providing a routinein the code that will act as a callback. It will be executed when theaction you specify occurs, rather like the way that an event handler iscalled to handle a u

ser?s
interaction with an application.To specify the callback routine you create a new instance of the AsyncCallback class, providing the name of that routine as the parameter,and pass this AsyncCallback instance into the method you use to startexecution of the command. Asynchronous technique employs the familiarasynchronous programming model using the AsyncCallback delegate in.NET, and so includes the SqlAsyncResult class to implement theIAsyncResult interface. While this feature works only for SqlClient atthe moment.IAsyncResult Interface : Represents the status of an asynchronousoperation.The IAsyncResult interface is implemented by classes containing methodsthat can operate asynchronously. An object that supports theIAsyncResult interface stores state information for an asynchronousoperation, and provides a synchronization object to allow threads to besignaled when the operation completes.

3. The Asynchronous Wait Model
The mostcomplex of the asynchronous methods is also the most efficient if allyou want to do is start some commands running against one or more datasources (they can all use separate connections and therefore differentdatabases if required) and not execute other code in the meantime. Yousimply want to wait until one, more, or all of the commands havecompleted and then perhaps display some results. In this case, youstart each process in the same way as the previous examples but thenuse the AsyncResult to create a WaitHandle that you use to monitor eachprocess.

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 ofmultiple commands over the same Connection instance, plus the abilityto 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

Twitter Digg Delicious Stumbleupon Technorati Facebook Email

No comments yet... Be the first to leave a reply!