In ADO.NET 2.0 and higher versions, ADO.NET has enabled users to process database commands asynchronously. i.e. , we can now use a single connection for multiple, concurrent database access running them parallel
Introduction
When we process data using ADO or previous versions of ADO.NET, each command is executed sequentially i.e. Synchronously. The command code waits for each command to complete before the next one is processed.
The advantage in this process is when we use a single database instance, the sequential processing enables us to reuse the same connection object for all commands object in ADO.NET.
But in ADO.NET 2.0 and higher version, ADO.NET has enabled users to process database commands asynchronously. i.e. , we can now use a single connection for multiple, concurrent database access running them parallel.
This enables us to not only use the same connection, but also to use it in a parallel manner. The real advantage of asynchronous processing becomes apparent when we
are accessing multiple data tables independent of each other.
Code Sample:
The SqlCommand class provides a few additional methods that facilitate executing commands
asynchronously. Just keep this article I am taking one basic command as listed below.
|
Command |
Explanation |
|
BeginExecuteReader |
This method expects a query that returns a result set and starts it asynchronously. The return value is a reference to an object of SqlAsyncResult class that implements the IAsyncResult interface. The returned object can be used to monitor the process as it runs and as it is completed. |
|
EndExecuteReader |
This method is used to access the results from the BeginExecuteReader method. When calling this method, you are required to pass the same SqlAsyncResult object that you received when you called the BeginExecuteReader method. This method returns a SqlDataReader object containing the result of the SQL query. |
Code Sample:
First of all create a dummy sql table with some dummy data.
CREATE TABLE [detailpro].[tblDummyInfo](
[Name] [varchar](50) NULL,
[Place] [varchar](50) NULL
)
| Name |
Place |
| Kishor |
Bangalore |
| Shekhar |
Bangalore |
| Rohit |
Delhi |
Now create a console application with the code described below.
public static void functionAsynCommandSampleCode()
{
/*==============First of all create a sql connection string which points to the Database Instance.
You can use either Windows authentication or SQL.
I have taken SQL authetication here for this example.
!!!!!! NOTE:must set 'Asynchronous Processing=true' and 'MultipleActiveResultSets=true'; in connection string!!!!!*/
string strSqlConnectionString = "Data Source=Test;Initial Catalog=TestDB;User=test;pwd=test;Asynchronous Processing=true;MultipleActiveResultSets=true;";
/*===============Create an object of SqlConnection using the above sql connection string*/
SqlConnection con = new SqlConnection(strSqlConnectionString);
/*===============Create an object of SqlCommand now.*/
SqlCommand cmd = new SqlCommand();
SqlCommand cmd1 = new SqlCommand();
/*===============Create a proxy of IAsyncResult for further use in the code*/
IAsyncResult asynResult = null;
IAsyncResult asynResult1 = null;
/*===============Create an empty object of SqlDataReader .*/
SqlDataReader datareader = null;
SqlDataReader datareader1 = null;
/*===============Pass all the necessary fields to commands object*/
cmd.Connection = con;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT [Name] FROM [tblDummyInfo]";
//
cmd1.Connection = con;//!!! NOTE:using the same connection here.
cmd1.CommandType = CommandType.Text;
cmd1.CommandText = "SELECT [Place] FROM [tblDummyInfo]";
/*===============Open the connection now*/
con.Open();
/*===============Starting the asynchronous processing*/
asynResult = cmd.BeginExecuteReader();
asynResult1 = cmd1.BeginExecuteReader();
/*===============A Loop to keep the main thread waiting and the command object to get finished*/
/*===============Check if asynchronous process is finished*/
while (!asynResult.IsCompleted && !asynResult1.IsCompleted)
{
// Sleeping current thread for 10 milliseconds
System.Threading.Thread.Sleep(10);
}
string strOutput = string.Empty;
try
{
//===============Retrieving result from the asynchronous process
datareader = cmd.EndExecuteReader(asynResult);
datareader1 = cmd1.EndExecuteReader(asynResult1);
//===============Displaying result on the screen
while (datareader.Read())
{
strOutput += Convert.ToString(datareader[0])+",";
}
Console.WriteLine("Output is :{0} ", strOutput);
strOutput = string.Empty;
//===============Displaying result on the screen
while (datareader1.Read())
{
strOutput += Convert.ToString(datareader1[0])+",";
}
Console.WriteLine("Output is :{0} ", strOutput);
}
catch
{
//No exception
}
finally
{
datareader.Dispose();
cmd.Dispose();
// Closing and disposing connection
con.Close();
con.Dispose();
datareader1.Dispose();
cmd1.Dispose();
Now Call the method inside Main() method as per below code.
static void Main(string[] args)
{
//call the method
functionAsynCommandSampleCode();
Console.Read();
}
After successful execution get the output like
Output is :Kishor,Shekhar,Rohit,
Output is :Bangalore,Bangalore,Delhi,
Conclusion
Must include the attributes 'Asynchronous Processing=true;MultipleActiveResultSets=true;' in the sql connection string.which is mandatory to support asyn execution of the command code.
Happy coding ! cheers !!