Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Asynchrous Execution with C#

By Keith Henry,

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: 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:

A Brief Intro to Delegates

When you create an .aspx page in Visual Studio it is created with a method called Page_Load already written for you and a region called "Web Form Designer generated code". Towards the bottom of this region is a line like this:
this.Load += new System.EventHandler(this.Page_Load);
This line is sometimes described as "subscribing" to an event. I'm going to break it down into three parts:
  • this.Load is the event fired by the System.Web.UI.Page class when it is about to load.
  • new System.EventHandler is the delegate for the method required to handle this event.
  • this.Page_Load is the method in our page that will be executed when the event fires.

System.EventHandler is a delegate, which describes what the method should look like, what it should return and what parameters it should expect.
Page_Load is going to handle the event System.Web.UI.Page.Load which is of delegate type System.EventHandler, so Page_Load has to return the same thing (void) and take the same parameters (object sender and System.EventArgs e) as System.EventHandler

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

1: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfSystemDataSqlClientSqlConnectionClassTopic.asp
2: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfSystemDataSqlClientSqlCommandClassTopic.asp
Total article views: 6337 | Views in the last 30 days: 1
 
Related Articles
FORUM

Event class in Trace

Event Class in Trace

BLOG

New Immersion Event Classes at SQLskills

There are three new upcoming Immersion Event classes from SQLskills, including one that I will be te...

BLOG

Undocumented Capabilities of Extended Event Objects

Undocumented Capabilities of Extended Event Objects The extended event objects (objects exposed by ...

FORUM

AUDIT_SCHEMA_OBJECT_ACCESS_EVENT Produces Failed and Successful Event

AUDIT_SCHEMA_OBJECT_ACCESS_EVENT Produces Failed and Successful Event for a failed event

ARTICLE

VBScript Classes to Query SQL Server for Backup Information

A VBscript class is created that can be used to query the maintenance plans on an SQL server to dete...

Tags
miscellaneous    
programming    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones