Recently a colleague of mine shared a code that he was working on where he was playing around with Asynch ADO.NET. He had a simple WinForm application. On the click of a button, he was making calls to the DB to get data. To try out the asynch features, he had introduced delays in the code.
The code looked something like below
private void button1_Click(object sender, EventArgs e)
{
//start with empty text boxes and these get populated once the DB query returns
textBox1.Text = string.Empty;
textBox2.Text = string.Empty;
textBox3.Text = string.Empty;
SqlCommand com1 = null; SqlCommand com2 = null; SqlCommand com3 = null;
SqlConnection con1 = null; SqlConnection con2 = null; SqlConnection con3 = null;
SqlDataReader dr1 = null; SqlDataReader dr2 = null; SqlDataReader dr3 = null;
string str1 = string.Empty;
try
{
string connection = "Data Source=.;Asynchronous Processing=true;Initial Catalog = pubs;Integrated Security=True";
con1 = new SqlConnection(connection);
con1.Open();
com1 = new SqlCommand("SELECT * FROM authors", con1);
con2 = new SqlConnection(connection);
con2.Open();
com2 = new SqlCommand("waitfor delay '00:00:05';SELECT * FROM authors", con2);
con3 = new SqlConnection(connection);
con3.Open();
com3 = new SqlCommand("waitfor delay '00:00:10';SELECT * FROM authors", con3);
IAsyncResult ar1 = com1.BeginExecuteReader();
IAsyncResult ar2 = com2.BeginExecuteReader();
IAsyncResult ar3 = com3.BeginExecuteReader();
WaitHandle[] handles = new WaitHandle[3];
handles[0] = ar1.AsyncWaitHandle;
handles[1] = ar2.AsyncWaitHandle;
handles[2] = ar3.AsyncWaitHandle;
for (int results = 0; results < handles.GetLength(0); results++)
{
// wait for any handle, then process results as they come
int index = WaitHandle.WaitAny(handles, -1, false); // infinite secs
//depending on which call completed, update the appropriate text box
if (index == 0)
{
dr1 = com1.EndExecuteReader(ar1);
while (dr1.Read())
{
str1 = str1 + dr1[0] + " " + dr1[1] + " " + dr1[2] + "\n";
}
textBox1.Text = str1;
}
else if (index == 1)
{
dr2 = com2.EndExecuteReader(ar2);
while (dr2.Read())
{
str1 = str1 + dr2[0] + " " + dr2[1] + " " + dr2[2] + "\n";
}
textBox2.Text = str1;
}
else if (index == 2)
{
dr3 = com3.EndExecuteReader(ar3);
while (dr3.Read())
{
str1 = str1 + dr3[0] + " " + dr3[1] + " " + dr3[2] + "\n";
}
textBox3.Text = str1;
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
con1.Close();
con2.Close();
con3.Close();
}
}
Is this the best code that could be written is debatable. One could write separate handlers to handle the completion of each of the Asynch queries. But that's not the point. Let's for the sake of argument assume there is a specific need to have written the code in this manner. When you run this code, you would get some unexpected results
1. If after return of all 3 queries, you click the button again, you would expect the text boxes to clear up and then get filled up again when the queries return after 5 and 10 secs. But that doesn't happen and the text boxes don't seem to clear up.
2. Each time you re-run the application, the way in which the results get populated in the text boxes is unpredictable. Sometimes, it will appear to work find while in most cases, the data for all three text boxes gets displayed at one shot (essentially after the last query has also returned)
So what's happening here? If you have experience only with the current .net programming only, it will be difficult to understand. Those of you who have programmed for Windows applications using the Win32 APIs directly like say with Visual C++ 1.5 or 2.0 or even with MFC would recall some of the key concepts there
1. Windows relies heavily on messaging infrastructure. Actions on the windows/controls etc generate messages that the code handles by picking messages from a queue and then passing them to a windows procedure. While processing of a message is happening, other messages will wait in the queue to be picked up.
2. The messages in the queue have different priorities. Those with higher priority even if come in while some messages are already in the queue, will get pushed foward and will be processed before the low priority messages.
3. The visual rendering of windows and controls there-in happens by processing the WM_PAINT message. During the processing of this message, the code uses the device context provided to it (which is usually the display device context) and draws the contents on it, which show up on the desktop display
4. Each time a control is invalidated (property change) like say text is entered into a text box, it needs to redraw itself so that it can show the currently set text. This is done by calling a Win32 API to invalidate itself, which causes a WM_PAINT message to be posted to the message queue. When the application gets a chance to process the message, the text box will update itself and show the new text.
The key to the issue with the above code is related to all these points with specific focus on the last point. In the sample code above, the text box has been updated by setting its Text property. It would have in turn generated a WM_PAINT message, but since the code above is in a loop, it essentially doesn't allows the processing of the message queue and hence the WM_PAINT message isn't processed on time.
It is in such scenarios that the Application.DoEvents API is useful. As you would see in the documentation, it mentions that a call to this allows Windows to immediately process all messages in the queue. So if we add this call at appropriate places (as shown below in bold face), the behavior of the UI will be more in line with what we expect.
private void button1_Click(object sender, EventArgs e)
{
//start with empty text boxes and these get populated once the DB query returns
textBox1.Text = string.Empty;
textBox2.Text = string.Empty;
textBox3.Text = string.Empty;
Application.DoEvents();
SqlCommand com1 = null; SqlCommand com2 = null; SqlCommand com3 = null;
SqlConnection con1 = null; SqlConnection con2 = null; SqlConnection con3 = null;
SqlDataReader dr1 = null; SqlDataReader dr2 = null; SqlDataReader dr3 = null;
string str1 = string.Empty;
try
{
string connection = "Data Source=.;Asynchronous Processing=true;Initial Catalog = pubs;Integrated Security=True";
con1 = new SqlConnection(connection);
con1.Open();
com1 = new SqlCommand("SELECT * FROM authors", con1);
con2 = new SqlConnection(connection);
con2.Open();
com2 = new SqlCommand("waitfor delay '00:00:05';SELECT * FROM authors", con2);
con3 = new SqlConnection(connection);
con3.Open();
com3 = new SqlCommand("waitfor delay '00:00:10';SELECT * FROM authors", con3);
IAsyncResult ar1 = com1.BeginExecuteReader();
IAsyncResult ar2 = com2.BeginExecuteReader();
IAsyncResult ar3 = com3.BeginExecuteReader();
WaitHandle[] handles = new WaitHandle[3];
handles[0] = ar1.AsyncWaitHandle;
handles[1] = ar2.AsyncWaitHandle;
handles[2] = ar3.AsyncWaitHandle;
for (int results = 0; results < handles.GetLength(0); results++)
{
// wait for any handle, then process results as they come
int index = WaitHandle.WaitAny(handles, -1, false); // infinite secs
//depending on which call completed, update the appropriate text box
if (index == 0)
{
dr1 = com1.EndExecuteReader(ar1);
while (dr1.Read())
{
str1 = str1 + dr1[0] + " " + dr1[1] + " " + dr1[2] + "\n";
}
textBox1.Text = str1;
Application.DoEvents();
}
else if (index == 1)
{
dr2 = com2.EndExecuteReader(ar2);
while (dr2.Read())
{
str1 = str1 + dr2[0] + " " + dr2[1] + " " + dr2[2] + "\n";
}
textBox2.Text = str1;
Application.DoEvents();
}
else if (index == 2)
{
dr3 = com3.EndExecuteReader(ar3);
while (dr3.Read())
{
str1 = str1 + dr3[0] + " " + dr3[1] + " " + dr3[2] + "\n";
}
textBox3.Text = str1;
Application.DoEvents();
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
con1.Close();
con2.Close();
con3.Close();
}
}
A quick look into the .net code us
