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.
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 = (int)reader["ArticleID"];
article.Title = (string)reader["Title"];
article.Description = (string)reader["Description"];
articles.Add(article);
}
UpdateArticles(articles);
}
}
Both the load methods are used to retrieve the information from the database. The method UpdateArticles and UpdateProducts are used to update the UI and populate the DataGridView control. I will explain these methods later in the article.
Implementing the Button Click Code:
The last part is to implement the button click code which will trigger the asynchronous processing.
private void button3_Click(object sender, EventArgs e)
{
GetData();
}
The Button click simply calls the GetData method. Run the code and see what happens.
UI Freeze and Delay:
If you are reading this then I assume that you have experienced a UI freeze and delay in displaying the data. Let’s talk about UI Freeze. Since, we are fetching the data on the same thread the thread will freeze until the data is retrieved. Now, let’s talk about why the products were not displayed before articles. Again, since we are working on a single thread it will be busy until all the tasks are performed on that thread. This means that even though the products were fetched in 5 seconds they had to wait for the articles which took 20 seconds. This problem can be solved if we can execute the data fetching on a separate thread. Luckily, BackgroundWorker class can be used for this purpose. Let’s take a look at the code below:
private BackgroundWorker worker;
private void button3_Click(object sender, EventArgs e)
{
worker = new BackgroundWorker();
InitializeWorker();
worker.RunWorkerAsync();
}
I removed the GetData method from the Button click event and replaced it will the creation of the BackgroundWorker instance. The InitializeWorker method will attach the necessary events to the BackgroundWorker instance.
private void InitializeWorker()
{
worker.DoWork+=new DoWorkEventHandler(worker_DoWork);
worker.RunWorkerCompleted += new RunWorkerCompletedEventHandler(worker_RunWorkerCompleted);
}
The DoWork event is the main event which is used to perform the lengthy task.
void worker_DoWork(object sender, DoWorkEventArgs e)
{
GetData();
}
Now, the GetData method will be fired on a separate thread hence not freezing up the form.
Populating the DataGridView:
We are not over yet! The running of the process on a different thread will cause another problem. This problem will be caused when you try to assign a value to the controls displayed on the page. This is because you can only assign the values to the controls if you are using the thread which created the control. Unfortunately, in our case the new thread is not the parent thread of the controls and hence will not be able to assign any values to the control.
Invoking Controls Created on Main Thread:
You can use the Controls Invoke method to invoke a method on the control. For this you first need to define the delegates which will be executed by the Invoke method.
private delegate void UpdateProductsDelegate(List<Product> products);
private delegate void UpdateArticlesDelegate(List<Article> articles);
Now, we need to define the method with the same signature as the delegates.
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;
}
The statement this.Invoke will invoke the controls on the main thread. The main thread is the thread on which the controls were initially created. Now, you can assign the DataSource of the DataGridView control to the collection.
If you run the application you will see that products are first displayed on the windows form and later articles are populated. The user interface will not freeze as we are using a separate thread to fetch the data and the controls are also updated properly as we are calling the controls from the main thread.
Here is the screen shot of the application when the data is fetched from both the sources.
I hope you liked the article, happy coding!