Execute Long Running SQL Query Or Stored Procedure From .NET

The following 2 main issues arise when your application is intended to deal with huge data-

• SQL Server takes significant time to process (long-running SQL statements) which leads to blocking the execution of your .NET code.
• Main thread or UI thread also gets blocked till the response from the SQL server.

These issues are some serious issues while building interactive applications. User patience is an unpredictable parameter and the user’s reaction against a long waiting screen is uncertain. At least UI shouldn't be frozen to engage the user and make him wait for the result.

 

Since transactional SQL statements will definitely take time to process the things, the quickest solution sought is on the application programming level. Also, it is known that MS SQL Server takes each & every call as synchronous, even if you change the connection string property AsynchronousProcessing to true. It is A client application (C#, .net) which gets affected, so the following are some widely used solutions-

1. Cancellation Token mechanism: So that users can cancel ongoing longer execution if they are unwilling to wait.
2. Callback mechanism: So that UI thread can't get blocked.

Cancellation mechanism

 

It has many limitations, for example, a fired SQL command can be canceled by firing SqlCommand.Cancel() but for this, that command object must persist. SqlReader's Close or Read is designed to ignore this Cancel() call if this call is not placed before them and so on.

 

Callback mechanism

 

It is a great programming style that solves many issues and helps you to build more interactive UI based applications. Instead of holding execution on a long-running statement(s), it allows you to carry on to the next line of code. In this post, we will implement this functionality to execute SQL queries asynchronously.

 

In our sample project which is a WPF application, we will use the Callback mechanism. Let's add the following controls on the Window-

1. Button    ( btnExecuteQuery )
2. ListBox   ( listboxResult )
3. TextBox ( textboxQuery )

Idea is to fire asynchronous call to MS SQL for the T-SQL statement written in textboxQuery on the btnExecuteQuery click event.

 

For executing MS SQL statements in asynchronous, we have to-

1. Set the Asynchronous Processing property of ConnectionString to true.
2. Use command.BeginExecuteNonQuery() and command.EndExecuteNonQuery().

At the connection level, a specification is required in the connection string for asynchronous processing. 

 

Example: AsynchronousProcessing = true

 

At SqlCommand execution level, instead of ExecuteReader() we need to use BeginExecuteReader() and EndExecuteReader() methods to achieve asynchronous execution. BeginExecuteReader takes the following 2 arguments:

1. A callback procedure/method
2. User-state (called 'StateObject' which holds the status of an asynchronous operation)

Example

 

 

Generally, the state-object is the object which will be required in the callback method to process the things. Here, we are passing the SqlCommand object because our SQL query will be executed in different functions inside different worker threads. Our SqlCommand object is already initialized and ready to use by a callback function.

 

Let’s focus on our callback function, which has been passed to AsyncCallback() inside BeginExecuteReader(). It has the following syntax:

 

 

Please note the IAsyncResult type, which is required to hold the status of an asynchronous operation.

 

To un-box the AsynState back to the SqlCommand, we need to typecast as below-

 

 

So far, we understood the required logic. Let's collaborate the things together. Our button click event handler code would look like the following:

private void btnExecuteQuery_Click(object sender, RoutedEventArgs e)
{
    SqlConnectionStringBuilder connectionBuilder
        = new SqlConnectionStringBuilder("Network Address=localhost; Initial Catalog=DemoDatabase; Integrated Security=true;")
    {
        ConnectTimeout = 4000,
        AsynchronousProcessing = true
    };

    SqlConnection conn = new SqlConnection(connectionBuilder.ConnectionString);
    SqlCommand cmd = new SqlCommand(textboxQuery.Text, conn);
    try
    {
        conn.Open();

        //The actual T-SQL execution happens in a separate worker thread.
        cmd.BeginExecuteReader(new AsyncCallback(MyCallbackFunction), cmd);
    }
    catch (SqlException se)
    {
        //ToDo: Swallow exception log
    }
    catch (Exception ex)
    {
        //ToDo: Swallow exception log
    }
}

And the callback function looks like the following:

private void MyCallbackFunction(IAsyncResult result)
{
 try
  {
    //un-box the AsynState back to the SqlCommand
    SqlCommand cmd = (SqlCommand)result.AsyncState;
    SqlDataReader reader = cmd.EndExecuteReader(result);
    while (reader.Read())
     {
       Dispatcher.BeginInvoke( new delegateAddTextToListbox(AddTextToListbox),
       reader.GetString(0));
     }

    if (cmd.Connection.State.Equals(ConnectionState.Open))
     {
       cmd.Connection.Close();
     }
  }
 catch (Exception ex)
  {
   //ToDo: Swallow exception log
  }
}

Great! We have implemented it! But wait, we need a few more tackle, since our sample project is a Windows-based WPF application, forms controls & methods are not accessible other than from the main UI thread. Thus, accessing Windows Forms controls listboxResult will produce exception (InvalidOperationException) - { "The calling thread cannot access this object because a different thread owns it." }

 

 

So, we need a different approach to access Windows Form controls & associated methods i.e. Delegate. We will shift listboxResult accessing code inside a function and we will call that function through a delegate.

 

Let’s create a delegate that can point out any function that will accept one string argument and return void.

 

 

Next, is to create a method that will accept a string and add that to Windows Form control Listbox-

 

 

Now, we can use delegate in our callback function to add the result into Listbox as in the following:

 

 

Done! Now, the worker thread will able to add the result to the controls by the method through delegate call. Dispatcher.BeginInvoke() is used to execute delegate asynchronously with the specified arguments. This method is under System.Windows.Threading namespace.

 

Summary

 

C# 5.0 introduces a new asynchronous programming model i.e. async & await. It no longer requires pair codes like BeginExecute & EndEquecute. A task is declared for asynchronous programming and the rest is done automatically by the runtime. For example, 

var task = await cmd.ExecuteReaderAsyc();

And method containing this task-based statement (s) must be decorated with async.

 

Example:

private async void MyAsyncFunction()   { … }