Cancellable SQL Server Query

• Download demo - 23.9 KB

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
StartQueryExecution	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 List<Object> parameter returned by QueryChunkCompleted is cleared or a new list is created once the event has been handled.

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() and EndExecuteReader() 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 ClearObjectsOnChunkCompleted property and ensured the SqlDataReader and SqlConnection objects were closed when the query is cancelled.