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
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;
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 )
{
Console.WriteLine("Received {0} messages", e.Errors.Count);
foreach( SqlError info in e.Errors )
{
if ( info.Class > 9 )
{
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
