Asynchronous operations in ADO.Net

Asynchronous programming helps you to improve the overall performance and responsiveness of your application. In this article we will explore the basics of asynchronous programming and write code to perform asynchronous operations in ADO.Net and ADO.Net Entity Framework.

Why the need of asynchrony?

Threads are expensive as they consume a lot of resources in your system for initialization, switching contexts, and releasing the resources they occupy. Normally, when a thread is performing an I/O operation (file handling, database operation or accessing the network resources, etc.) the thread is blocked by the operating system until the I/O operation is complete. The thread will resume its CPU operation after its I/O operation is complete. Since the blocked thread is not returned to the thread pool, this forces the thread pool to create new threads to serve incoming requests — the incoming requests are queued. To make matters worse, incoming requests may even be rejected primarily because the thread pool may not have available threads with it to handle an incoming request.

In using asynchronous programming, you would need fewer threads and hence avoid reaching the maximum available thread limit. You can also process multiple I/O bound operations in parallel — this would enhance the responsiveness of the application.

With .Net Framework 4.5, you have a much simplified approach towards asynchronous programming — you now have two keywords async and await those you can use to implement asynchrony in your applications.

Note that the async and await keywords doesn’t create any additional threads and the currently executing thread is not blocked. Also, an asynchronous method never runs on its own thread — it runs on the current synchronization context.

The “async” and “await” keywords

You should mark your method with the async keyword to enable asynchronous capabilities. When the async keyword is used in the method signature, the method can use the await keyword to indicate suspension points — it indicates to the runtime that the async method will be suspended and will be able to resume execution only after the awaited asynchronous operation is complete. Note that when you suspend an asynchronous method using the await keyword, the control will not come out of the method — it would wait for the awaited operation to complete its execution. So, if you have a try — catch — finally block with a call to an awaited operation inside the try block, the finally block will not be executed until the awaited operation has completed its execution.

Support for Asynchronous Programming in ADO.Net and Entity Framework

Microsoft .Net Framework 4.5 comes up with better support for asynchronous database operations. Until .Net Framework 4.5, asynchronous programming support in ADO.Net was incapable of handling all possible blocking scenarios. As an example, there were no asynchronous methods available in the earlier versions of .Net Framework to open a database connection in ADO.Net.

Note that prior to .Net Framework 4.5 you have had to specify “Asynchronous Processing=true” in your connection string to leverage asynchronous ADO.Net calls. With .Net Framework 4.5, you no longer need to specify this in the connection string. With the advent of .Net Framework 4.5 you can now optimize the data access performance using the asynchronous methods like NextResultAsync, ReadAsync, IsDBNullAsync, and GetFieldValueAsync, etc.

The following piece of code illustrates how you can implement asynchronous operations in ADO.Net.

   using (SqlConnection connection = new SqlConnection(connectionString)       { await connection.OpenAsync();            //Some code             await reader.ReadAsync();             //Other code     }

Note the usage of the OpenAsync, ReadAsync, and NextResultAsync methods in the code snippet shown earlier.

The newer versions of Entity Framework also provide excellent support for asynchronous programming. The following snippet of code illustrates how you can retrieve a list of customers from the Northwind database asynchronously. Note how the await keyword has been used as a suspension point. The async keyword is used to indicate that the ReadAsync() method is an asynchronous method. The “async” suffix is a convention that is followed to name an asynchronous method. If an asynchronous method doesn’t contain the await keyword, the method would be executed as a normal synchronous method.

The following asynchronous method illustrates how you can insert a customer object to the database asynchronously.

public static async Task<bool> InsertAsync(Customer obj)         {             try             {                 using (NorthwindEntities context = new NorthwindEntities())                 {                     context.Customers.Add(obj);                     await context.SaveChangesAsync();                     return true;                 }             }             catch (Exception ex)             {                 Console.WriteLine(ex.Message);             }                 return false; }

You should remember that asynchronous programming though a great mechanism to boost the scalability and responsiveness of an application, should be used judiciously. You should not just convert one synchronous operation to an asynchronous operation — this would degrade the performance because of the additional constraints involved for the asynchronous operation to complete. Use asynchronous programming approach if you have long running I/O operations that have less or no dependency on the caller. You should refrain from using asynchronous methods for simple CPU intensive operations.