Introduction:

ADO.NET 2.0 introduces a new model for executing queries. In ADO.NET 1.X we had to wait for the first query to finish the work before executing the new query. Well, fortunately that wait is now over since ADO.NET 2.0 introduces the asynchronous query execution model which allows the developer to execute multiple queries asynchronously; hence not waiting. In this article I will demonstrate how to execute queries using asynchronous model available in ADO.NET 2.0 framework.

Getting Started:

Let’s start by creating a simple windows form application which consists of two DataGridView controls and two buttons. Each DataGridView control is populated using a different database. The data will be populated asynchronously and the user interface will be updated instantly when the data is available. Take a look at the screen shot of the user interface given below:

Implementing the GetData Method:

The GetData method is used to retrieve the data from the databases. The first task is to create connection strings for the two databases.

string gvgConnectionString = "Server=localhost;Database=ArticleDatabase;Trusted_Connection=true;Asynchronous Processing = true";

            string northwindConnectionString = "Server=localhost;Database=Northwind;Trusted_Connection=true;Asynchronous Processing=true";

The connection strings contain the additional attribute “Asynchronous Processing = true” which activates the asynchronous execution of the queries. Next we create the SqlConnection and SqlCommand objects for the two connection strings.

SqlConnection gvgConnection = new SqlConnection(gvgConnectionString);

            SqlConnection northwindConnection = new SqlConnection(northwindConnectionString);

SqlCommand gvgCommand, northwindCommand;

Next, we will create the IAsyncResult and WaitHandle arrays.

IAsyncResult[] iasyncresults = new IAsyncResult[2];

WaitHandle[] waitHandles = new WaitHandle[2];

The IAsyncResult[] will hold the results of the query execution and the waitHandle[] will signal which operation has been completed.

Now, let’s execute the queries.

gvgConnection.Open();

            gvgCommand = new SqlCommand("WAITFOR DELAY '00:00:20'; SELECT * FROM Articles", gvgConnection);

            startTime = DateTime.Now;

            iasyncresults[0] = gvgCommand.BeginExecuteReader(null, gvgCommand, CommandBehavior.CloseConnection);

            waitHandles[0] = iasyncresults[0].AsyncWaitHandle;

            northwindConnection.Open();

            northwindCommand = new SqlCommand("WAITFOR DELAY '00:00:05'; SELECT * FROM Products", northwindConnection);

            iasyncresults[1] = northwindCommand.BeginExecuteReader(null, northwindCommand, CommandBehavior.CloseConnection);

            waitHandles[1] = iasyncresults[1].AsyncWaitHandle;

            for (int i = 0; i < waitHandles.Length; i++)

            {

                int index = WaitHandle.WaitAny(waitHandles);

                SqlCommand cmd = (SqlCommand)iasyncresults[index].AsyncState;

                // depending on the value of the index we know which one is which!

                switch (index)

                {

                    case 0:

                        // load the articles

                        LoadArticles(cmd, iasyncresults[index]);

                        break;

                    case 1:

                        // load the products

                        LoadProducts(cmd, iasyncresults[index]);

                        break;

                }

            }

            gvgConnection.Close();

            northwindConnection.Close();

        }

I have purposely included the DELAY in the queries to show the asynchronous processing clearly. From the DELAY you can see that the data from the ArticlesDatabase will be retrieved in 20 seconds while the data from the Northwind database is retrieved only in 5 seconds. Another important point to note is the use of SqlCommad.BeginExecuteReader method which performs the action asynchronously. The LoadProducts and the LoadArticles method are fired based on their completion status. Let’s take a look at the LoadProducts and LoadArticles implementation.

private void LoadProducts(SqlCommand cmd, IAsyncResult result)

        {

            DataGridView dgv = new DataGridView();

            List<Product> products = new List<Product>();

            using (SqlDataReader reader = cmd.EndExecuteReader(result))

            {

                while (reader.Read())

                {

                    Product product = new Product();

                    product.ProductID = (int) reader["ProductID"];

                    product.ProductName = (string) reader["ProductName"];                   

                    products.Add(product);

                }

                UpdateProducts(products);               

            }          

        }

private void LoadArticles(SqlCommand cmd, IAsyncResult result)

        {

            List<Article> articles = new List<Article>();

            using (SqlDataReader reader = cmd.EndExecuteReader(result))

            {

                while (reader.Read())

                {

                    Article article = new Article();

                    article.ArticleID