Asynchronous call to MSSQL

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”.