Cancellable SQL Server Query
4.50/5 (3 votes)
A Cancellable threaded query for SQL Server using .net 3.5
Introduction
SQL Server Management Studio provides most of the basic GUI based functionality required to manage an instance of SQL Server on a daily basis quite easily, however there are a number of functions missing from this, such as the ability to search for text within a stored procedure or sort objects into custom folders.
Due to these short comings and a few others which are out of the scope of this article, i set about creating a clone of SSMS, both as a learning path and to add in some of the missing features.
The first issue I came across was that SSMS provided the ability to run queries asynchronously and cancel them at any time, this can be seen in action either by running a query in SSMS or expanding a node in the object Explorer.
So the first thing I had to do was come up with some code that run a query asynchronously against SQL Server and provide the ability to cancel the query.
Using the code
The below code can be used to load data into a standard .NET DataGridView
control.
public void RunQuery()
{
using (CancellableQuery Query = new CancellableQuery {
ConnectionString = MyConnectionString, SQL = MySqlString,
ReturnDataInChunks = true, QueryChunkSize = 500, ClearObjectsOnChunkCompleted = false })
{
Query.GetColumns += (sender, args) =>
{
foreach(QueryColumn Column in args.Columns)
{
if (gvMain.InvokeRequired)
{
MethodInvoker Invoker = () =>
{
gvMain.Columns.Add(Column.HeaderText, Column.HeaderText);
};
gvMain.Invoke(Invoker);
}
else
gvMain.Columns.Add(Column.HeaderText, Column.HeaderText);
}
};
Query.QueryCompleted += (sender, args) =>
{
foreach (Object[] Values in args.Results)
{
if (gvMain.InvokeRequired)
{
MethodInvoker Invoker = () =>
{
gvMain.Rows.Add(Values);
};
gvMain.Invoke(Invoker);
}
else
{
gvMain.Rows.Add(Values);
}
}
lblQueryStatus.Text = "Query Completed";
btnStopQuery.Enabled = false;
btnRunQuery.Enabled = true;
};
Query.QueryChunkCompleted += (sender, args) =>
{
foreach (Object[] Values in args.Results)
{
if (gvMain.InvokeRequired)
{
MethodInvoker Invoker = () =>
{
gvMain.Rows.Add(Values);
};
gvMain.Invoke(Invoker);
}
else
{
gvMain.Rows.Add(Values);
}
}
lblRowsReturned.Text = string.Format("{0} rows returned", gvMain.Rows.Count);
};
Query.OnQueryError += (sender, args) =>
{
MessageBox.Show(args.ex.ToString());
lblQueryStatus.Text = "Query Error";
};
Query.StartQueryExecution();
}
}
As you can see from the above code, there are at least four events you need to subscribe to within the
CancellableQuery class to make sure everything is hooked up correctly.
The CancellableQuery Class
The CancellableQuery class is really the main point of this article. It has three main objectives
- Query should be cancellable at any time.
- The query should return its results in chunks if required.
- It should be really simple to use.
So where do we start, well the first thing you will notice is that there are no special custom written collection or thread handling routines used, all objects used are common .NET objects.
The following public methods are available within this class:
| Method Name | Description |
|
|
Starts Executing the query in a Background Worker using the SQL text and datasource specified in the
ConnectionString property. |
CancelQuery |
Cancels the currently running query and causes the QueryCanelled
event to be fired if it has been subscribed to. |
The following public properties are available within this class
| Property Name | Description |
ConnectionString |
Gets or sets the ConnectionString property which identifies the SQL Server instance to connect to. |
SQL |
Gets or sets the SQL SELECT command to run against the server. |
ReturnDataInChunks |
Gets or sets a boolean value which indicates whether or not the query results are returned in chunks via the
QueryChunkCompleted Event. |
QueryChunkSize |
Gets or sets the value which indentifies the size of the chunks for which data should be returned when the
ReturnDataInChunks property is set to true. |
IsRunning |
Gets a boolean value indicating whether or not the query is running. |
ClearObjectsOnChunkCompleted |
Gets or sets a boolean value indicating whether or not the |
The following public events are available within the class
| Event Name | Description |
GetColumns |
Fires once the underlying SQLDataReader has completed it
ExecuteReader method and the underlying schema returned from the query has been read. |
QueryChunkCompleted |
This event is fired once the number of rows read is equal to the
QueryChunkSize property. |
QueryCompleted |
This event is fired once the query has completed and all data has been read from the SQL Server instance. |
QueryCancelled |
This event is fired when the CancelQuery() method is called. |
OnQueryError |
This event is fired when the query fails for whatever reason. |
The main processing functionality of the CancellableQuery class is done in the
StartQueryExecution method which we look at more closely.
public void StartQueryExecution()
{
// Before we do anything else it is best to check if the ConnectionString and SQL properties have been set.
if (ConnectionString == null || ConnectionString == string.Empty)
throw new Exception("ConnectionString property has not been set");
if (SQL == null || SQL == string.Empty)
throw new Exception("SQL property has not been set");
// set the IsRunning variable to true as we are affectively now in execution mode or running.
isRunning = true;
// Create an SqlDataReader object, if you dont want to connect
// to sql server then i see no reason why this cannot be changed to an
// OleDBDataReader object
SqlDataReader reader = null;
// Create an SqlConnection object, if you dont want to connect
// to sql server then i see no reason why this cannot be changed to an
// OleDBDConnection object
SqlConnection Connection = null;
// Create an SqlCommand object, if you dont want to connect
// to sql server then i see no reason why this cannot be changed to an
// OleDbCommand object
SqlCommand Command = null;
// List of objects that will store the physical field values
List<object[]> Results = new List<object[]>();
// A collection of columns within the dataset, unfortunately
// the DataColumnCollection does not really suit our needs, it has no
// constructor and inheriting from InternalDataCollectionBase is a bit of an over kill.
QueryColumnCollection Columns = new QueryColumnCollection();
try
{
// Create the BackgroundWorker object and set the
// WorkerSupportsCancellation Property to true so that we can cancel the worker when the
// CancelQuery Method is called.
Worker = new BackgroundWorker() { WorkerSupportsCancellation = true };
// Assigm our method stub to the DoWork event of the BackgroundWorker using a Lambada Expression.
// Some people like these, some don't, if you dont like them or you
// are using an older version of the .net framework, UnComment the RunQuery
/// method below and comment out this method.
Worker.DoWork += (s, e) =>
{
try
{
// Create the SqlConnection object assigning the ConnectionString
// of this class to the ConnectionString of the SQLConnection object.
Connection = new SqlConnection(ConnectionString);
// Open the connection string object. We do this right at the
// top here so that if an error occurs, the rest of the code is not run, saves
// a little bit of work on the side of the software.
Connection.Open();
// Create the Command object and assign the SQL property
// from this class to the CommandText Property of the SqlCommand object
Command = new SqlCommand(SQL);
// Assign the SqlConnection object to the command
Command.Connection = Connection;
// Execute the reader object
reader = Command.ExecuteReader();
// First thing we do is get the Columns returned by the underlying
// query, this enables us to instantly populate our control with the header
// columns from the Query
// The GetSchemaTable returns the column meta data from the underlying
// query which can be used to populate a grid with headers or populate
// a datatable object with headers.
using (DataTable dtSchema = reader.GetSchemaTable())
{
if (dtSchema != null)
{
foreach (DataRow drow in dtSchema.Rows)
{
QueryColumn MyColumn = new QueryColumn(
Convert.ToString(drow["ColumnName"]), (Type)(drow["DataType"]));
Columns.AddColumn(MyColumn);
}
}
if (GetColumns != null)
GetColumns(this, new GetColumnsEventArgs(Columns));
}
// create a local variable that keeps track of the CurrentRowCount,
// so that we can check if the QueryChunkSize has been reached.
int CurrentRowCount = 0;
// Loop through the datareader rows
while (reader.Read())
{
// Check if the query has been cancelled using the CancelQuery method
if (Cancelled)
{
// The query has been cancelled now fire the QueryCancelled Event if it has been attached to.
if (QueryCancelled != null)
QueryCancelled(this, EventArgs.Empty);
// set the internal isRunning variable to false
isRunning = false;
// jump out of the while loop, we no longer need to process any further rows.
break;
}
// create an Array of Objects to store the values that are reader from the SqlDataReader
Object[] values = new Object[reader.FieldCount];
// Read the values intot he array
reader.GetValues(values);
// Add the values to our results list.
Results.Add(values);
// Incremement the CurrentRowCount variable
CurrentRowCount++;
// Check if the ReturnDataInChunks property is set to
// true and if the CurrentRowcount equals the QueryChunkSize
if (ReturnDataInChunks && CurrentRowCount == QueryChunkSize)
{
// The CurrentRowCount equals the QueryChunkSize and
// the ReturnDataInChunks property is set to true.
// return the Object List back to the calling method
// if the QueryChunkCompleted event has been subscribed to.
if (QueryChunkCompleted != null)
QueryChunkCompleted(this, new QueryCompletedEventArgs(Results));
// reset the CurrentRowCount variable
CurrentRowCount = 0;
// Clear out the Object List as we dont need this
// internally any more as the calling code should of used them
// as required or cloned them if they dont need them.
Results.Clear();
}
}
}
catch (Exception ex)
{
// An exception has occoured somewhere, so raise
// the OnQueryError event if it has been subscribed to.
isRunning = false;
if (OnQueryError != null)
OnQueryError(this, new QueryErrorDelegate(ex));
// Set the isRunning varaible to false;
}
};
Worker.RunWorkerCompleted += (s, e) =>
{
// The query has completed fine, no errors have been
// reported so raise the QueryCompleted Event if it has been subscribed to.
// this will also return any remaining results as the
// QueryChunkCompleted Event will not be invoked if the CurrentRowCount does
// not equal the QueryChunkSize
if (QueryCompleted != null)
QueryCompleted(this, new QueryCompletedEventArgs(Results));
// Set the isRunning variable to false as we are
// theoretically no longer running any form of query operation.
isRunning = false;
};
// Run the background worker.
Worker.RunWorkerAsync();
}
catch (Exception ex)
{
// An exception has occoured somewhere, so raise
// the OnQueryError event if it has been subscribed to.
if (OnQueryError != null)
OnQueryError(this, new QueryErrorDelegate(ex));
// Set the isRunning varaible to false;
isRunning = false;
}
finally
{
// Do all the clean up required
if (Connection != null)
{
Connection.Close();
Connection.Dispose();
}
if (reader != null)
{
reader.Close();
reader.Dispose();
}
}
}
The first thing you will notice, is that the code is using a BackgroundWorker object rather than any of the
asynchronous methods provided by Microsoft such as BeginExcuteReader() and
EndExecuteReader(). There were two reason for this...
- Firstly and most importantly the asynchronous Data Access functionality built into .net adds a considerable overhead to your application and is much slower than no asyncronous access. For small amounts of data this is not really a problem, however when your querying millions of records you can shave minutes off the time (especially on slow servers) by not using the built in asynchronous functionality.
- Secondly the only really viable way of using the
BeginExecuteReader()andEndExecuteReader()functionality is via Callbacks which open a whole new level of threading issues, specifically when updating the GUI and if you remember from above, i wanted to keep this simple.
Getting the headers of the columns that will be returned from the Query
First thing is first, in order to display our data in the DataGridView, we need to provide it with the columns it needs to display the data.
This is done by the SqlDataReader.GetSchemaTable() method
using (DataTable dtSchema = reader.GetSchemaTable())
{
if (dtSchema != null)
{
foreach (DataRow drow in dtSchema.Rows)
{
QueryColumn MyColumn = new QueryColumn(Convert.ToString(drow["ColumnName"]),
(Type)(drow["DataType"]));
Columns.AddColumn(MyColumn);
}
}
if (GetColumns != null)
GetColumns(this, new GetColumnsEventArgs(Columns));
}
This is where things got quite irritating for me, i hate re-inventing the wheel and really wanted to use the
DataColumnCollection, but unfortunately it does not specify a constructor and worse still inherits from
InternalDataCollectionBase which is another class which introduces a considerable overhead, not really suited to our task at hand and thus a simple
QueryColumn and QueryCollumnCollection class were created.
Chunks of Data
One of the main things that SSMS does which i find really impressive is returning the data in chunks of 10,000 rows at a time.
This is where the ReturnDataInChunks and QueryChunkSize properties come in handy. Setting the
ReturnDataInChunks to true and the QueryChunkSize to a fairly large integer value such as 1000, will allow the
CancellableQuery to return the data in chunks back to the calling thread in the
QueryChunkCompleted event.
CurrentRowCount++;
// Check if the ReturnDataInChunks property is set to true and if the CurrentRowcount equals the QueryChunkSize
if (ReturnDataInChunks && CurrentRowCount == QueryChunkSize)
{
As you can see from above the code for this is quite simple, all we are doing is Keeping a count of the current row Count in the
CurrentRowCount variable, when we reach the QueryChunkSize we fire off the
QueryChunkCompleted event passing back our list of values which are stored in the Results variable which is a simple
List<object> (list of objects), which depending on the value of the ClearObjectsOnChunkCompleted property are either cleared out once the event has been fired (ClearObjectsOnChunkCompleted = true) or a new List<Object> is created (ClearObjectsOnChunkCompleted = false).
The code really is that simple.
The Demo Code
So lets take a closer look at the code above and what we are doing...
The first section of the code creates the CancellableQuery object and assigns the various properties to it.
Getting a list of Columns
The next lines deal with assigning the columns to DataViewGrid control we are going to use. I've called mine
gvMain.
Query.GetColumns += (sender, args) =>
{
foreach(QueryColumn Column in args.Columns)
{
if (gvMain.InvokeRequired)
{
MethodInvoker Invoker = () =>
{
gvMain.Columns.Add(Column.HeaderText, Column.HeaderText);
};
gvMain.Invoke(Invoker);
}
else gvMain.Columns.Add(Column.HeaderText, Column.HeaderText);
}
};
The GetColumnsEvent is of type QueryGetColumnsEventDelegate in
EventsAndDelegates.cs which provides us access to the underlying columns retrieved from the Query. the
args.Columns is a collection of QueryColumns which provide access to the Column Header via the
HeaderText and data type of the column via the ColumnType properties.
Reading the Data
Once we have got our list of columns and added them to the grid, we then need to read the data into the grid, this can be done by attaching to the
QueryChunkCompleted event and the QueryCompleted events.
Query.QueryChunkCompleted += (sender, args) =>
{
foreach (Object[] Values in args.Results)
{
if (gvMain.InvokeRequired)
{
MethodInvoker Invoker = () =>
{
gvMain.Rows.Add(Values);
};
gvMain.Invoke(Invoker);
}
else
{
gvMain.Rows.Add(Values);
}
}
lblRowsReturned.Text = string.Format("{0} rows returned", gvMain.Rows.Count);
};
This event will fire every time the number of rows read equals the value of the
QueryChunkSize property, assuming the ReturnDataInChunks property is set to true.
The QueryChunkCompleted event is again a custom delegate of type
QueryChunkCompletedEventDelegate which provides us with a list of values for each row that has been read via the
args.Results parameter.
I was initially going to use a DataTable as the args.Results property type, however the overhead used by this compared to a list of objects was to great, especially considering in theory you could be querying millions of rows.
The DataGridView, handily enough for us provides a nice little function that allows you to add a list of object values as row via the
Rows.Add() method.
Query Completed
Once the query has completed successfully we need to respond to the QueryCompleted event.
Query.QueryCompleted += (sender, args) =>
{
foreach (Object[] Values in args.Results)
{
if (gvMain.InvokeRequired)
{
MethodInvoker Invoker = () =>
{
gvMain.Rows.Add(Values);
};
gvMain.Invoke(Invoker);
}
else
{
gvMain.Rows.Add(Values);
}
}
lblQueryStatus.Text = "Query Completed";
btnStopQuery.Enabled = false;
btnRunQuery.Enabled = true;
};
If we have not set Enabled the returning of the data in chunks via the
ReturnDataInChunks property, the args parameter will contain all our data in the
args.Results property. If we have set the ReturnDataInChunks property to true, the
args.Results property will contain the remaining records that did not reach the
QueryChunkSize limit.
This is also a custom event delegate of type QueryCompletedEventDelegate located in
EventsAndDelegates.cs.
Query Error
If an error occurs during execution of the underlying query, we need to attach to the
OnQueryError event which provides us with the ability to respond to the underlying Exception object via the
args.ex property.
Query.OnQueryError += (sender, args) =>
{
MessageBox.Show(args.ex.ToString());
lblQueryStatus.Text = "Query Error";
};
When this event is fired, the IsRunning property is set to false.
Finally - Running or canalling the query
Once you have setup all the events required, the query can be invoked by simply calling the
StartQueryExecution() method.
To cancel the query, called the CancelQuery() method which will fire the
QueryCancelled event.
In Summary
This is my first article with the CodeProject, so firstly i open to all comments, good or bad and will update the article accordingly when someone points out a glaring omission on my part.
All queries against a Datasource whether SQL Server or not should in reality be run in there own thread to ensure that the main GUI stays responsive, the ability to cancel them however, really does depend on upon your business needs.
In my next article i plan on introducing the Microsoft Access \ SSMS style query builder i have put together.
History
- 23-07-2012: Initial release to CodeProject.com.
- 23-07-2012: Updated the article based up on some very valid comments by Mika Wendelius.
- 24-07-2012: Added the
ClearObjectsOnChunkCompletedproperty and ensured theSqlDataReaderandSqlConnectionobjects were closed when the query is cancelled.