Naveen's Weblog

Bridge to future

Asynchronous call to MSSQL using ADO.Net

Posted by codingsense on December 22, 2008


Hi friends,

I was working on a small project that had a lot of computations happening in stored procedures.

In beginning all worked fine, when there was increase in data the stored procedure would take more time to complete the process and the application would raise command timeout, once i increased the command timeout of the connection it worked well but when again the data increased it failed i had to increase the timeout much more.

I thought it would not be feasible to increase the command timout to max, so was searching for some alternate and found a nice concept called “Asynchronous execution of the Transact-SQL” .

This worked very fine and i was able to overcome the following issues

  • No need to worry about command timeout
  • Other tasks can be done till MSSQL finishes executing the stored procedure

Here is the code i used for Asynchronous execution

string ConnectionString = @"Data Source=NAVEEN\SQLEXPRESS;Initial Catalog=Email;Integrated Security=True;Max Pool Size=5000;Connect Timeout=120; Asynchronous Processing=true";
using (SqlConnection sqlConn = new SqlConnection(ConnectionString))
{
sqlConn.Open();
SqlCommand command = new SqlCommand("ShiftNewEmails", sqlConn);
command.CommandType = CommandType.StoredProcedure;
IAsyncResult asyncResult = command.BeginExecuteNonQuery();
while (!asyncResult.IsCompleted)
{
}

MessageBox.Show(string.Format("Shifted {0} Email id's ", command.EndExecuteNonQuery(asyncResult)));
}

The key points to focus in the above code are

  • Asynchronous Processing=true -> in the connection string without this you cannot use Asynchronous execution.
  • command.BeginExecuteNonQuery() -> Starts the execution of the stored procedure in asyn mode.
  • asyncResult.IsCompleted -> Will be false till the mssql executes the command, once MSSQL finishes the value will be true.
  • command.EndExecuteNonQuery(asyncResult)-> Gets the result of the execution from MSSQL in the asyncResult parameter and completes the task.

I did the following task while MSSQL is executing the command, this gave the user an idea that the process is working fine.

int FixedString = lblProcessing.Text.Length;
int Dots = 0;
while (!asyncResult.IsCompleted)
{
if (Dots > 10)
Dots = 0;
lblProcessing.Text = "Processing details please wait". PadRight(FixedString + Dots,'.');
Dots++;
Application.DoEvents();
Thread.Sleep(100);
}

So you can use this feature to make your application more user friendly and avoid program form showing “NOT RESPONDING”.

Happy Learning 🙂

Advertisements

2 Responses to “Asynchronous call to MSSQL using ADO.Net”

  1. Tee Rinomhota said

    Wonderful, clear and straight to the point

  2. Aron said

    I have read somewhere that it should’nt use Application.DoEvents and thread.sleep instead use BackgroundWorker.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: