Click here to Skip to main content
Click here to Skip to main content

ADO.NET Asynchronous SQL Calls

, 10 Sep 2009 CPOL
Rate this:
Please Sign up or sign in to vote.
How to use asynchronous calls to SQL to receive progress information

Introduction

This article will show you how to use asynchronous calls in ADO.NET to receive progress messages from long running stored procedure calls or any other SQL command that raises messages back to the client.

My original idea was to show how you can echo to the client the SQL BACKUP statement messages, but if you don't have a database big enough, you will not notice the issue I am about to show. There is no real difference from executing the stored procedure and the BACKUP command, both send their information messages as errors (which they are NOT). Both use RAISERROR with NOWAIT from what I can detect. I am expecting under the hood that BACKUP has a different method to return information messages. For stored procedures, we are limited to RAISERROR command.

This is fine as there is a severity flag as part of the call, and any severity less than ten is used for information only (see MSDN Documentation). So we can make use of severity one for our purposes.

Why Would You Want To Do This?

Good question! I normally use this for one off stored procedures that heavily modify the database, upgrading the database or BACKUPs that are run from a client utility, where they are expecting some form of progress to show it has not stopped!

Stored Procedure Being Used

The following stored procedure will be used during the demos:

CREATE PROCEDURE AdoProcess_Test1
AS
BEGIN
	SET NOCOUNT ON 	-- Used for performance and making sure 
			-- we don't send back unneeded information.
	DECLARE @time VARCHAR(16)
	SET @time = CONVERT(VARCHAR(16),GETDATE(),114)
	RAISERROR( 'Completed 25%% At %s', 2, 1, @time) WITH NOWAIT
	WAITFOR DELAY '00:00:03'
	SET @time = CONVERT(VARCHAR(16),GETDATE(),114)
	RAISERROR( 'Completed 50%% At %s', 2, 2, @time) WITH NOWAIT
	WAITFOR DELAY '00:00:03'
	SET @time = CONVERT(VARCHAR(16),GETDATE(),114)
	RAISERROR( 'Completed 75%% At %s', 1, 3, @time) WITH NOWAIT
	WAITFOR DELAY '00:00:03'
	SET @time = CONVERT(VARCHAR(16),GETDATE(),114)
	RAISERROR( 'Completed 100%% At %s', 1, 4, @time) WITH NOWAIT
END;

The most important lines are those which start with RAISERROR. For this demo, I have hard coded the message strings within the stored procedure. The normal recommendation is to add a user message to the database and reference the message number in RAISERROR, see MSDN Documentation. The message string follows the same syntax as C printf function. That is the reason for the double %% in the string.

Break down of the RAISERROR line is as follows:

RAISERROR( <Message/MessageId>, <Severity>, <State>[, <Arguments>]) [WITH <Options>]
  • Message/MessageId - The actual message to be returned to the client, can use printf place holders for variable placement.
  • Severity of the message - Less than ten for information messages.
  • State - This is a unique number to identify where in the stored procedure the RAISERROR was fired.
  • Arguments - Optional arguments for the Message if printf place holders are used.
  • Options - Optional options, in our case we want to send back the message as soon as we raised it so the NOWAIT option is used. You can send the messages to the event log if required!

The demo has a couple of helper functions that connect to the database and create/drop the stored procedure for you automatically in the tempdb. Therefore you need CREATE/DROP PROCEDURE permission in the tempdb.

Connection String

The connection string is one the most important parts of the whole setup. For the demo, I have hard coded it inside the C# class, but normally it would be placed in the application/web config file.

private const string ConnectionStr = "Data Source=(local);
    Initial Catalog=tempdb;Trusted_Connection=SSPI;Asynchronous Processing=true";

For a complete list of all the options that you can have in a SQL connection, see MSDN Documentation. Break down of the options I am using are as follows:

  • Data Source - This is the server that you wish to connect to. I am using my own local one. If you installed SQL Express edition with Visual Studio, then you will need to change the (local) to (local)\SQLEXPRESS instance name.
  • Initial Catalog - This is the name of the actual database you're be using, that is hosted in SQL server. If you don't have the CREATE/DROP permissions in tempdb, then change it to database that you do have.
  • Trusted_Connection - This tells the SQL client software you are using your Windows credentials for Single Sign On options. The recommended is to use SSPI instead of true.
  • Asynchronous Processing - To have ADO.NET do asynchronous processing (calling the Begin methods on the command object), this must be true or else you will get InvalidOperationException in the demo.

For real life applications, I normally add 'Application Name' and 'Workshare Id' options to the connection. This is so that the DBA knows which application/machine is causing an issue with the database (keeps the DBA friendly!).

Setting up Asynchronous Processing

In the demo, I have created two methods that show how to do this. The only difference between them is the actual SQL command method being used.

To start with, we need a way to signal that the Stored Procedure has finished processing and we can continue with the execution of our code. To do this, we will use the handle ManualResetEvent class. The reason to use this class is that once it is signalled, I want it to stay signalled. I have declared it as a class member.

private static readonly ManualResetEvent _reset = new ManualResetEvent(false);

Setup the connection and command objects for use.

using (SqlConnection connection = new SqlConnection(ConnectionStr))
{
    using (SqlCommand cmd = new SqlCommand("AdoProcess_Test1", connection))
    {
        .....
    }
} 

As the SQL command is a stored procedure without parameters, we just need to initialise the command some more. If you think the command will take longer than 30 seconds, then you need to adjust the command timeout property as well. The connection timeout property just handles how long it takes to connect to the database, and not how long it takes to run a command/query.

cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = 30; 	// Default of 30 seconds, 
			// only showing how to set no need to set it.

Now we need to tell the connection which method to call when it receives messages from the database. The signature for the method is void <MethodName>(object sender, SqlInfoMessageEventArgs e).

private static void ConnectionInfoMessage(object sender, SqlInfoMessageEventArgs e)
{
    if ( e.Errors.Count > 0 )
    {
        // Check to make sure we are information only messages
        Console.WriteLine("Received {0} messages", e.Errors.Count);
        foreach( SqlError info in e.Errors )
        {
            if ( info.Class > 9 ) // Severity
            {
                Console.WriteLine("Error Message : {0} : 
			State : {1}", info.Message, info.State );
            }
            else
            {
                Console.WriteLine("Info Message : {0} : 
			State : {1}", info.Message, info.State);
            }
        }
    }
    else
    {
        Console.WriteLine("Received Connection Info Message : {0}", e.Message);     
    }
}

As you can see, I am checking the Class property on the SqlError instance. This is the actual severity that RAISERROR method fired with. The State property has the same name as the State option in the RAISERROR call, why could not the ADO team keep the Severity name as well. The SqlInfoMessageEventArgs.Message is the combination, with a new line as the separator, of error collection if present.

Now we have the method we need to wire it up to the connection object.

cmd.Connection.InfoMessage += ConnectionInfoMessage;

So when an asynchronous operation completes, we need a call back method to process the results and signal to the rest of the application that the operation has finished. We need to create a method that will support IAsyncResult interface, the signal for the method is void <MethodName>(IAsyncResult result).

As I demonstrate both Non Query execution and Reader execution, I have to create two call back methods, one for each type of command being used. Their names will be NonQueryCallBack and ReaderCallBack:

private static void NonQueryCallBack(IAsyncResult result)
{
    SqlCommand command = (SqlCommand) result.AsyncState;
    try
    {
        if (command != null)
        {
            Console.WriteLine("Waiting for completion of the Async call");
            command.EndExecuteNonQuery(result);
        }
    }
    catch (SqlException ex)
    {
        Console.WriteLine("Problem with executing command! - [{0}]", ex.Message);
    }
    finally
    {
        Console.WriteLine
		("Completed call back so signal main thread to continue....");
        _reset.Set();
    }
}

When the command.EndExecuteNonQuery(result) is executed, it will wait on the call back thread until the command has completed or there has been an exception. Either way, when it completes we want to signal to the main thread that we have finished, so in the finally block, we call Set method on the ManualResetEvent instance.

Let's wire it and execute the command by passing in SqlCommand object as the Async State and the callback method.

AsyncCallback result = NonQueryCallBack;
cmd.Connection.Open();
cmd.BeginExecuteNonQuery(result, cmd);
Console.WriteLine("Waiting for completion of executing stored procedure....");
_reset.WaitOne();

Once execution started, we can either continue with other work (don't close the current connection or else the command will cancel) or wait until it has completed, which is what we are doing in this demo.

Now there is an issue with Execute Non Query command in that it will wait until the end before sending back any messages, as the output from this method shows:

Waiting for completion of executing stored procedure....
Waiting for completion of the Async call
Received 4 messages
Info Message : Completed 25% At 15:23:19:697 : State : 1
Info Message : Completed 50% At 15:23:22:697 : State : 2
Info Message : Completed 75% At 15:23:25:697 : State : 3
Info Message : Completed 100% At 15:23:28:697 : State : 4
Completed call back so signal main thread to continue....
Completion of Non Execute Method....

When I originally started to use this method for backing up databases before upgrading, I did not notice this effect until the backup started to take more than a couple of seconds (as my test databases were empty, they only took about a second to do!). I assumed as I was not returning data sets I should have used the ExecuteNonQuery method, this is incorrect. This method is optimized internal (I believe) so it will not process any messages until the end, and if you think about it, you can see that the design is correct.

Now to overcome this issue, you need to use the BeginExecuteReader and EndExecuteReader methods instead. In the demo, I have created another set of methods that use these, so let's see what its output is.

Waiting for completion of executing stored procedure....
Waiting for completion of the Async call
Received 1 messages
Info Message : Completed 25% At 15:32:48:410 : State : 1
Received 1 messages
Info Message : Completed 50% At 15:32:51:410 : State : 2
Received 1 messages
Info Message : Completed 75% At 15:32:54:410 : State : 3
Received 1 messages
Info Message : Completed 100% At 15:32:57:410 : State : 4
Completed call back so signal main thread to continue....
Completion of Execute Reader Method....

As you can see, the ConnectionInfoMessage method is fired every time a message is received and not at the end.

I have also provided another method to show how to start a backup and to show that it returns the same sort of messages. I do not automatically execute this method as it tries to backup the msdb database and you may not have access to it.

History

  • 10th September 2009 - Initial release

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

gbd77rc
Database Developer Workshare
United States United States
I have been in computer industry since 1984 and have seen a lot changes during this time. I have a very board experience of client and server technologies, so basically a 'jack of all trades, master of none' Smile | :)

Comments and Discussions

 
QuestionADO.NET Asynchronous SQL Calls in vb.net Pinmembermohitkhandelwal19-Jun-14 22:24 
AnswerRe: ADO.NET Asynchronous SQL Calls in vb.net [modified] Pinmembergersis7618-Nov-14 5:32 
GeneralMy vote of 5 Pinmemberdeepakdynamite26-Mar-14 22:22 
QuestionWaitOne() Pinprofessionaltesnep16-Oct-13 15:24 
GeneralMy vote of 5 PinprofessionalAmir Mohammad Nasrollahi11-Aug-13 21:18 
SuggestionFireInfoMessageEventOnUserErrors not to be forgotten PinmemberMark Donskoy22-May-12 9:13 
GeneralVery nice, but I need a trigger-message Pinmembermy_mimuel15-Oct-09 22:46 
GeneralRe: Very nice, but I need a trigger-message Pinmembergbd77rc19-Oct-09 9:39 
GeneralReally good! Pinmemberilog18-Sep-09 3:48 
GeneralThanks PinmemberKoltz10-Sep-09 21:06 
GeneralRe: Thanks Pinmembergbd77rc10-Sep-09 21:10 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.1411023.1 | Last Updated 10 Sep 2009
Article Copyright 2009 by gbd77rc
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid