Asynchronous ADO.NET 2.0

Introduction

Desktop applications suffered a lot from long slow data operations that were blocking application execution till it returns rowsets or executing DML statements, and developers tend to create new thread using ThreadPool or Asynchrouns delegates for data access operations to save the main application thread free for other UI operations like showing progress bar progressing while fetching data, while dealing with threads is somehow easy in .NET world, ADO.NET 2.0 has a new Asynchronous Queries, you can send your query to DB and yet executing any other code blocks you want to, in the following small application I'll simply show you how to make Asynchronous DB Call to you DB, this is applicable to SQL 7 and later versions.

I/O Asynchronism

You should know that Asynchrouns calls are base on I/O true asynchronism rather than threading, I think this way it's faster and MS opt to do it like that to be away from Thread Synchronization issues.

Example

Here I included a button click handler method from simple winform that includes a button and a listbox, here you get results asynchronously from AdventureWorks sample database and while performing that you change the tiltle of the windows form, I included so many comments to make it self-descriptive.

private void button1_Click(object sender, EventArgs e)

{

// note the use of attribute async=True, it's mandatory to process asynchronous DB access.

string connectionString = @"server = KareemPC\sql2005; database = AdventureWorks;Integrated Security = True; async=True; connection timeout = 15;";

SqlConnection conn = new SqlConnection(connectionString);

SqlCommand cmd = new SqlCommand();

cmd.Connection = conn;

// here we'll use the table HumanResources.Employee of the sample AdventureWorks database

// if you are not familiar with this naming schema please read about Schemas in SQL Server 2005

cmd.CommandText = " SELECT NationalIdNumber FROM HumanResources.Employee";

cmd.CommandType = CommandType.Text;

try

{

conn.Open();

// begin to execute command returning to IAsyncResult type.

// note also the use of notation cmd.BeginXXXX which was always used by Async calls

// in .NET 1.x, we use IAsyncResult to check completion of command execution

// now you've actually sent command to DB and it's running asynchrounusly in it's own

// thread.

IAsyncResult result = cmd.BeginExecuteReader();

int counter = 0;

// using Is Completed property of IAsycnResult we check status of command execution.

while (!result.IsCompleted)

{

// simply here we just increment a dummy counter value to indicate that it's executing

// an activity while DB is running asynchrounsly.

counter += 1;

// here you reset the caption of the winform to this counter value

// note that this counter value is variant as DB access time will be always

// varying.

this.Text = counter.ToString();

}

// once command is completed you will move to this line and call EndExecuteReader

// method of the command object.

SqlDataReader reader = cmd.EndExecuteReader(result);

lstBooks.Items.Clear();

// then we can process results as usual using Read method of the DataReader

// we add values returned to listbox control.

while (reader.Read())

{

lstBooks.Items.Add(reader.GetString(0));

}

// close the reader to free resources.

reader.Close();

}

catch (Exception ex)

{

MessageBox.Show(ex.Message);

}

finally

{

// close connection

conn.Close();

}

}

Note: you can call EndExecuteReader anytime you like but it will not return untill all results are ready, and also you can use ADO.NET synchronously while supplying attribute "async=true", but this may slight performance degradation so it's advisable to create separate connection for asynchrouns and synchrouns calls.