Click here to Skip to main content
15,868,128 members
Articles / Programming Languages / C#
Tip/Trick

Execute Long Running SQL Statements Asynchronously from .NET

Rate me:
Please Sign up or sign in to vote.
4.21/5 (8 votes)
21 Oct 2015CPOL5 min read 37.7K   429   23   6
A beginner's tutorial for understanding Asynchronous execution of SQL statements using a simple application.

Introduction

This tip aims at understanding the fundamental concept of asynchronous execution, i.e., how to use worker thread in colloboration with ADO.NET's BeginExecute & EndExecute feature to avoid UI freeze.

Background

Below are the 2 main issues that 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 the serious issues while building interactive applications. User patience is an unpredictable parameter and user’s reaction against long waiting screen is uncertain. At-least UI shouldn't freeze 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 client application (C#, .NET) which gets affected. So, below are some widely used solutions.

  1. Cancellation Token mechanism - so that user 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 cancelled by firing SqlCommand.Cancel() but for this, that command object must be 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 get execute SQL query asynchronously.

Using the Code

In our sample project which is a WPF application, we will use 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 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 connection level, a specification is required in connection string for asynchronous Processing. Example: AsynchronousProcessing = true

C#
SqlConnectionStringBuilder connectionBuilder = 
             new SqlConnectionStringBuilder( "Network Address=localhost;
			Initial Catalog=DB_EINV;Integrated Security=true;")
  {
     ConnectTimeout = 4000,
     AsynchronousProcessing = true
  };

SqlConnection conn = new SqlConnection(connectionBuilder.ConnectionString);

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

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

Example:

C#
//The actual T-SQL execution happens in a separate work thread.
cmd.BeginExecuteReader(new AsyncCallback(MyCallbackFunction), cmd);

Generally, state-object is the object which will be required in the callback method to process the things. Here, we are passing 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 callback function.

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

C#
private void MyCallbackFunction(IAsyncResult asyncResult)
 {
          
 }

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

C#
//un-box the AsynState back to the SqlCommand
SqlCommand cmd = (SqlCommand)asyncResult.AsyncState;
SqlDataReader reader = cmd.EndExecuteReader(asyncResult);

So far, we have understand the required logic & way. Let's wire the things together. Our Button Click Event Handler Code would look like:

C#
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 work 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:

C#
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 few more tackle. Since, our sample project is a Windows based WPF application, form’s controls & method are not accessible from other than main UI thread. Thus, accessing Windows Form’s controls listboxResult will produce exception (InvalidOperationException) - { "The calling thread cannot access this object because a different thread owns it." }

clip_image010

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

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

clip_image012

Next is to create a method that will accept string and add that to Windows Form’s control listbox:

C#
private void AddTextToListbox(string Text)
 {
    listboxResult.Items.Add(Text);
 }

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

C#
while (reader.Read())
 {
    Dispatcher.BeginInvoke(new delegateAddTextToListbox(AddTextToListbox), reader.GetString(0));
 }

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

clip_image018

Summary

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

C#
var task = await cmd.ExecuteReaderAsyc();

And method containing these task based statement(s) must must be decorated with async. For example:

C#
private async void MyAsyncFunction() {…}

Points of Interest

Since C# 5.0 has introduced a new approach using async await & task based programming, so many people will think that this tip is outdated. But the idea behind writing this tip is to target those developers who are still developing or maintaining applications in old .NET Frameworks. Also, it will help to understand the basic mechanism behind the modern Task based asynchronous approach.

History

  • 21st October, 2015 : First version

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer (Senior)
India India
Anil Kumar has good exposure on Automobile, Airways, and Financial domain in diverse technologies. Now he is primarily focus on Microsoft Technologies (ASP.Net, C#, ADO.Net, WCF, MVC, Ajax, SQL Server ..). He contributes in technical forums along with education and Career Advice on many websites. He has been awarded with C# Corner MVP for year 2012-13, and 2013-14. You can contact Anil Kumar on his email: Anil[dot]Kumar[at]CodePattern.net , follow him @AnilAwadh , +AnilKumarMVP

blog: http://codePattern.net

Comments and Discussions

 
QuestionGood Explanation Pin
Veeru Dada22-Oct-15 22:57
Veeru Dada22-Oct-15 22:57 
Suggestionoutdated Pin
Tevita Wainiqolo22-Oct-15 12:29
professionalTevita Wainiqolo22-Oct-15 12:29 
GeneralRe: outdated Pin
Anil Kumar @AnilAwadh22-Oct-15 18:18
professionalAnil Kumar @AnilAwadh22-Oct-15 18:18 
GeneralRe: outdated Pin
kiquenet.com23-Mar-19 9:19
professionalkiquenet.com23-Mar-19 9:19 
GeneralMy vote of 5 Pin
Humayun Kabir Mamun21-Oct-15 18:04
Humayun Kabir Mamun21-Oct-15 18:04 
GeneralRe: My vote of 5 Pin
Anil Kumar @AnilAwadh21-Oct-15 18:28
professionalAnil Kumar @AnilAwadh21-Oct-15 18:28 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.