Asynchronous command execution in ADO.NET 2.0

Kishork80
Posted by in ADO.NET category on for Beginner level | Views : 10065 red flag
Rating: 3 out of 5  
 1 vote(s)

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 !!


 

Page copy protected against web site content infringement by Copyscape

About the Author

Kishork80
Full Name: kishor kumar
Member Level: Starter
Member Status: Member
Member Since: 7/1/2010 2:49:48 AM
Country: India
kishor kumar
http://www.dotnetfunda.com
Having 6 pyears of exp in dot net and still counting...

Login to vote for this post.

Comments or Responses

Posted by: Arefin on: 8/28/2011 | Points: 25
Nice article. Thanks for sharing.

Login to post response

Comment using Facebook(Author doesn't get notification)