Execute Long Running SQL Statements Asynchronously from .NET


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.


Below are the 2 main issues that arise when your application is intended to deal with huge data:

  1. SQL Server takes significant time to process (long running SQL statements) which leads to blocking the execution of your .NET code.

  2. 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.

Cancellation Token mechanism – so that user can cancel ongoing longer execution if they are unwilling to wait.

Callback mechanism – so that UI thread can’t get blocked

Read Full Article – Click

Hope this will help.

Jay Ganesh !!!!!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s