Introduction

Ever had an application where the Stored Procedure just takes too long? You've

done all the optimization you can but still takes a few minutes to run. Maybe a

search on a VLDB or building a complex report. Maybe you have a lot of little

processes that you run one after another. 

In this article we are going to build a web application that executes an

asynchronous process. This will allow us to return a web page so that the user

is not left there waiting with a blank screen, while our database is still

working in the background.

Section 1: Assumptions

This article is not an introduction to C#, and assumes that you are familiar

with the basic syntax. This article is not an introduction to the ADO.Net data

model, I'm going to assume that you are already familiar with the following

classes:

• System.Data.SqlClient.SqlConnection ¹

• System.Data.SqlClient.SqlCommand ²

To keep this article a reasonable length I'm going to look at a query that

doesn't return data, although you will be able to use what you learn here to

write an application that does.

We will use a test stored procedure that just takes a long time, but this

article can be adapted to any process. This is the stored procedure we will

use:

create proc waittest ( @iterations int = 10 )
as
begin
print 'Doing wait test'
print 'This will wait for 1 second ' + 
cast( @iterations as varchar ) + 
' times.'
declare @i int
set @i = 0

while @i < @iterations
begin
select @i = @i + 1
waitfor delay '000:00:01'
print 'You have reached step ' + 
cast( @i as varchar ) + 
'.  Here is a load of technical' +
' information about this step. ' + 
'blah blah blah blah blah blah'
end
print 'process complete'
end
go

Finally we will be building a web application, but this could be easily adapted

to a desktop solution. We will leave out error handleing and the like for now,

just to keep things simple.

Section 2: Getting Started

In order to execute our SqlCommand asynchronously we will build a new class. We

will start with our basic class declaration and constructor:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Threading; //this is our Threading namespace

namespace kh.SqlTools {
[Serializable]
public class AsyncCmd : System.Object , System.IDisposable {
//this will be the connection we execute against
public System.Data.SqlClient.SqlConnection Connection; 

//this will be the command we execute 
public System.Data.SqlClient.SqlCommand Command; 

public AsyncCmd() {}

public AsyncCmd(SqlConnection conn, SqlCommand comm) {
this.Connection = conn;
this.Command = comm;
}

//IDisposable requires this and it allows us to destroy our 
//connection and command objects
public void Dispose () {
try {
if (this.Connection != null) this.Connection.Dispose() ;
if (this.Command != null) this.Command.Dispose() ;
}
catch {}
}

//bool to show true once our process is complete.
public bool IsComplete = false;

Section 3: Delegates and Events

A delegate is a description of what a method will "look like"; what it

should return and what parameters it should take.  It allows you to call a

method in a distant class that probably isn't even created yet.

An event is a method in the local class that calls a method in the

distant class, both the local event and the distant method take the same

variables and return the same thing as the delegate.

Both delegates and events are seen as quite arcane, complex and

technical, but in reality if you have used .net then you have experience of

these already. See the cut-out below for an example of where you may have used

them before:

this.Load += new System.EventHandler(this.Page_Load);

We need a delegate and an event so that we can fire

events that our web page can handle:

public delegate void InfoMessage ( object sender, string Message );
public event InfoMessage OnInfo;

We also want to handle events from the objects we are using within this class:

public void InfoHandler(object sender, SqlInfoMessageEventArgs e) {
if (OnInfo != null) //check for subscriber
OnInfo(sender, 
"Info: " + 
e.Message);
}
public void ChangeHandler(object sender, StateChangeEventArgs e) {
if (OnInfo != null)  //check for subscriber
OnInfo(sender, 
"SqlConnection Change from " + 
e.OriginalState.ToString() + 
" to " + 
e.CurrentState.ToString());
}

These methods are going to handle events fired from the

System.Data.SqlClient.SqlConnection class and bubble them up to whatever

is calling this class. Now we will add a method that executes the process we

want to be done asynchronously:

public void ExecSql() {
if (OnInfo != null)
OnInfo(this, 
"AsyncCmd Starting");

if(this.Connection == null || this.Command == null)
throw new System.Exception( //fire error if objects not set
"Both Connection and Command values must be set!");

if (OnInfo != null) { //check for subscriber
this.Connection.InfoMessage +=  //bubble prints
new SqlInfoMessageEventHandler(InfoHandler);
this.Connection.StateChange +=  //bubble open|close
new StateChangeEventHandler(ChangeHandler);
}

this.Connection.Open();

this.Command.Connection = this.Connection;
this.Command.CommandTimeout = 0;

if (OnInfo != null)
OnInfo(this, 
"Executing SqlCommand");
this.Command.ExecuteNonQuery();
this.Connection.Close();

if (OnInfo != null)
OnInfo(this, 
"AsyncCmd Complete");

this.IsComplete = true;
}

I'm assuming here that you're pretty familiar with opening a connection and

executing a command against it, so I'm mainly going into detail on the

delegates here. When OnInfo is called it fires the event to a method of

the class that is calling this one and looks like the delegate InfoMessage.

Also here we have subscribed to the InfoMessage and StateChange events

of the SqlConnection object.

Section 4: Asynchronous Methods

The first thing we need to add is a thread which is going to be doing the

asynchronous work:

protected System.Threading.Thread _bldThrd;

We are going to create Start(), Stop() and Join() methods. Start() sets the

asynchronous process off, Join() allows us to re-join and wait for it to finish

synchronously and Stop() aborts the process.

//start async process
public void Start() {
//create new Thread and set ExecSql to the async 
//method using ThreadStart. 
_bldThrd = new System.Threading.Thread(
new System.Threading.ThreadStart( ExecSql ) );

_bldThrd.Start();
}

//join async process, if running
public void Join() {
if( _bldThrd != null ) //check Thread init
if ( _bldThrd.IsAlive ) //check running
_bldThrd.Join(); //join it
}

//stop async process, if running
public void Stop() {
if( _bldThrd != null ) //check Thread init
if ( _bldThrd.IsAlive ) //check running
_bldThrd.Abort(); //kill it

//it may have stopped, but wait for it
this.Join();
}
}
}

I'm not going too deep into the System.Threading namespace here (no space) but

these three methods are enough to build our application in this article.

Section 5: The Web Page

Now create a new web forms page (.aspx) We need a few basic methods that we are

going to use on the page:

//Adds a log item to the session
private void AppendSessionLog(string message) {
if (Session["log"] != null) 
Session["log"] += "|" + message;
else
Session["log"] = message;
}

//Writes the contents of the log to the page
private void DisplaySessionLog() {
if (Session["log"] != null) 
foreach(string s in Session["Log"].ToString().Split(
new char[] {'|'}))
Response.Write ( s + "<br>" );
}

//client side javascript that refreshes the page after 2 seconds
private void ClientSideReload() {
Response.Write("<script language=\"javascript\">\n" + 
" setTimeout(\"submit();\",2*1000);\n" + 
"</script>Please Wait...");
}

We also need the class we have just written:

public kh.SqlTools.AsyncCmd ac;

If we add a method which looks like our delegate (see InfoMessage

above) we will be able to handle events fired from this class:

//note how this returns void and takes object, string
//just like kh.SqlTools.AsyncCmd.InfoMessage
private void ac_OnInfo(object sender, string Message) {
this.AppendSessionLog( Message );
}

This method clears any old process and starts a new one.

private void StartProc () {
if ( ac != null ) //check we have an async cmd
ac.Dispose(); //dispose of it if we do

Session.RemoveAll(); //clear the session to start again

//create an instance of our async class
ac = new kh.SqlTools.AsyncCmd();
ac.Connection = new System.Data.SqlClient.SqlConnection(  ## Your connection string here! ##  );
ac.Command = new System.Data.SqlClient.SqlCommand("waittest");
ac.Command.CommandType = System.Data.CommandType.StoredProcedure ;
ac.Command.Parameters.Add("@iterations",50) ;

//subscribe to the delegate we created earlier
ac.OnInfo += new kh.SqlTools.AsyncCmd.InfoMessage(ac_OnInfo);
ac.Start();

//add our class to the session so that we can retrieve it
Session["ac"] = (object) ac;
}

All that remains is some events fired from the page to pick up our session

objects and write them to the page as appropriate

private void Page_Load(object sender, System.EventArgs e) {
// Get the stored Session variable
if (Session["ac"] != null) 
ac = (kh.SqlTools.AsyncCmd) Session["ac"]; 
}

private void Page_PreRender(object sender, EventArgs e) {
this.DisplaySessionLog();
if ( ac != null ) { //check we have an async cmd
if ( !ac.IsComplete  )  //if it's not finished
ClientSideReload(); //reload the page
else {
ac.Dispose();
ac = null;
Session.Remove("ac");
}
}
}

Now when we call the StartProc() (from a button click for instance) this page

will start to execute the Sql Command and keep refreshing every 2 seconds until

it has finished.

Conclusion

So put this all together and we have a page that can access a process that

takes a long time. We could execute two or more of these at the same time,

which give you the ability to balance the additional connections or server

resources used verses how quickly you need the results.

In order to keep this complicated article down to a reasonable length I've cut

out a few things:

1. Error Handling

   Unless you want lots of strange errors that you just can't find you should

   always handle all potential errors in asynchronous calls by firing an event to

   the calling class.  You also need to make sure that you kill any other

   Threads when an error occurs.

2. XML comments

   This is a fantastic feature of C# that makes your code self documenting (well,

   almost) and I've completely ignored it here for sake of brevity.

3. SqlClient objects not Thread Safe

   In the classes above it is possible to access the SqlConnection and SqlCommand

   objects that are being accessed by another thread by calling the Start() method

   and then just accessing them. This is a bad idea because they are not Thread

   safe, i.e. this behaviour will often crash the application and occasionally the

   whole server!

   You can do what I have here (which works and is safe but messy) by just not

   accessing them. However you should generate static copies of the SqlClient

   objects for use in the new thread.

4. Intellisense

   This is pretty useful for delegates in Visual Studio 2003. When you declare

   your event (e.g. "ac.OnInfo += ") hitting TAB generates the rest of the

   statement. Hitting TAB again generates the handler (e.g. "ac_OnInfo") method

   for you.

Resources

¹:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfSystemDataSqlClientSqlConnectionClassTopic.asp

²:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfSystemDataSqlClientSqlCommandClassTopic.asp