Welcome to AspAdvice Sign in | Join | Help

AzamSharp

Some day I will know everything I hope that day never comes

Syndication

Tags

Navigation

Executing a Query Asynchronously in .NET 2.0

Everyone hates waiting right! Waiting for new console games, waiting for the pizza delivery and specially waiting for the database task to be completed before starting a new task. 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 asynchrnously hence not waiting.

Let's take a simple example. Suppore I want to fetch the results from two different databases and present it to the user. The first database is contains the table "Articles" which contains thousands of rows and the other database contains a table "Products" which contains fewer number of rows.

Users should be able to view the data as soon as it is available this means that they should not wait until all the rows are fetched. Let's see how this can be performed.

Check out the complete GetData method which is used to perform the task:

private void GetData()

{

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

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

SqlConnection gvgConnection = new SqlConnection(gvgConnectionString);

SqlConnection northwindConnection = new SqlConnection(northwindConnectionString);

SqlCommand gvgCommand, northwindCommand;

IAsyncResult[] iasyncresults = new IAsyncResult[2];

WaitHandle[] waitHandles = new WaitHandle[2];

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();

}

If you look closely then you will note that the connection string contains an additional attribute "Asynchronous Processing = true" which activates the async processing for the queries.

I created separate SqlConnection and SqlCommand objects so that I can access both the databases. Also notice that I am using a WAITFOR  DELAY in the query. This will stop the query for the time specified in this case 20 seconds and 5 seconds respectively. Offcouse the results of the query that was halted for 5 seconds will return before the one with 20 seconds and so we want to display those results to the user.  

Finally, I iterated through the waitHandles collection to find which of the task has been completed. Once, I get the task I load the respective data using either LoadProducts or LoadArticles.

If you run this sample the products will be loaded before the articles since they are only halted for 5 seconds. Once, they are loaded they are instantly displayed on the screen in the DataGridView control. After some time (after 19 seconds) the articles will be displayed on the page in a separate DataGridView control.

Although this works but there is one big problem. During this asynchronous fetching the user screen is freezed. This means that the user cannot perform interaction with any of the controls on the interface. To solve this freezing problem you will have to load the data in the background process or in other words on a separate thread. Let's see how this can be done.

The BackgroundWorker class is responsible for running the background process. It has several different events but the most important is the DoWork event which is used to perform expensive operations. You can simply drag and drop the BackgroundWorker control from the toolbox on the windows form.

Now, when the button is clicked you can run the BackgroundWorker asynchronously. Take a look at the following code:

private void button3_Click(object sender, EventArgs e)

{

// Get all the data in the background process

backgroundWorker1.RunWorkerAsync();

}

And here is the DoWork event:

private void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e)

{

// do the background work

GetData();

}

private void backgroundWorker1_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)

{

backgroundWorker1.Dispose();

}

The DoWork simply calls the GetData which fetches the data from different sources. Now, when you run the application the asynchronous processes will run without freezing the windows form.

:)

References:

Programming Microsoft ADO.NET 2.0 Core Reference by David Sceppa

 UPDATE:

 I found a problem in the above application. When the first part meaning products are loaded you cannot use the scroll bar to view the products meaning the new thread is still frozen and waiting for the other task to complete. The problem was also caused by refreshing or updating the UI from the worker thread. This will cause an exception since the UI was not created by the worker thread but by the main application thread. There is a way to fix this problem by using the Control.Invoke method which calls the delegated method on the main thread to update the UI. Take a look at the complete code.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Runtime.InteropServices;
using AgentServerObjects;
using System.Data.SqlClient;
using System.Threading;



namespace MyWindowsApplication
{
    public partial class Form1 : Form
    {
        DateTime startTime;


        private delegate void UpdateProductsDelegate(List<Product> products);
        private delegate void UpdateArticlesDelegate(List<Article> articles);     

        private BackgroundWorker worker;

        public Form1()
        {
            InitializeComponent();
        }

        private void InitializeWorker()
        {
            worker.DoWork+=new DoWorkEventHandler(worker_DoWork);
            worker.RunWorkerCompleted += new RunWorkerCompletedEventHandler(worker_RunWorkerCompleted);
        }

        void worker_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {
          
        }

        void worker_DoWork(object sender, DoWorkEventArgs e)
        {
            GetData();   
        }
     

        private void button2_Click(object sender, EventArgs e)
        {
            MessageBox.Show("Hello World");
        }

        private void GetData()
        {

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

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

            SqlConnection gvgConnection = new SqlConnection(gvgConnectionString);

            SqlConnection northwindConnection = new SqlConnection(northwindConnectionString);

            SqlCommand gvgCommand, northwindCommand;

            IAsyncResult[] iasyncresults = new IAsyncResult[2];

            WaitHandle[] waitHandles = new WaitHandle[2];

            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();
        }

        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 UpdateArticles(List<Article> articles)
        {
            if (this.InvokeRequired)
            {
                this.Invoke(new UpdateArticlesDelegate(UpdateArticles), articles);
                return;
            }

            dataGridView1.DataSource = articles;

        }

        private void UpdateProducts(List<Product> products)
        {
            if (this.InvokeRequired)
            {
                this.Invoke(new UpdateProductsDelegate(UpdateProducts), products);
                return;
            }

            this.dataGridView2.DataSource = 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 = (int)reader["ArticleID"];
                    article.Title = (string)reader["Title"];
                    article.Description = (string)reader["Description"];
                    articles.Add(article);
                }

                UpdateArticles(articles);
            }           
        }

        private void button3_Click(object sender, EventArgs e)
        {
            worker = new BackgroundWorker();
            InitializeWorker();
            worker.RunWorkerAsync();

        }     

    }
}

Now, you can interact with the data as soon as it is loaded.

Enjoy!  

 

 

 

 

Published Thursday, April 05, 2007 6:26 PM by azamsharp

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

# Interesting finding - 04/06/2007 &laquo; Another .NET Blog @ Saturday, April 07, 2007 2:19 AM

PingBack from http://liangwu.wordpress.com/2007/04/07/interesting-finding-04062007/

Interesting finding - 04/06/2007 « Another .NET Blog

# Executing a Query Asynchronously in .NET 2.0 @ Tuesday, April 10, 2007 5:22 AM

怎样在ASP.NET 2.0中执行一个异步查询

ljianl

# Executing a Query Asynchronously in .NET 2.0 @ Wednesday, April 11, 2007 7:59 AM

You've been kicked (a good thing) - Trackback from DotNetKicks.com

DotNetKicks.com

# re: Executing a Query Asynchronously in .NET 2.0 @ Thursday, June 14, 2007 4:15 PM

I wish your posted code was formatted better on your blog :(

Josh Stodola

# re: Executing a Query Asynchronously in .NET 2.0 @ Thursday, June 14, 2007 4:27 PM

Looks good to me!

azamsharp

# 【收藏】本周ASP.NET英文技术文章推荐[04/01 - 04/07] @ Sunday, November 25, 2007 8:37 PM

摘要

本期共有6篇文章: 让UpdatePanel支持文件上传第一部分:开始

JSON攻击以及ASP.NETAJAX1.0是如何避免的

IIS7.0

在.NET2....

Jacky_xu

# Minocin @ Tuesday, October 05, 2010 2:00 AM

Minocin

vlfyxpjersqmwglhtrjnbqbjurxgunnlmeqsbjmlmfaedgxrnsybdjnscswqnopmlooiagnnvtpfyiwndzsdnxgdgirhycmmrjdlwqrbtlgavph

Minocin

# @ Thursday, October 07, 2010 12:09 PM

. . . . . . .

TrackBack

Leave a Comment

(required) 
required 
(required) 
Enter the code you see below