此文是接着我上篇文章写的，我在上篇文章中讲了异步查询数据库的方法，但例子写的有些草率，我只写了一次查询，没能表现出异步方式无阻塞的优点，因此根据园友的反映，我又对原来的代码做了些修改，增加普通和异步两种方式对数据库的查询操作的示例，希望对大家有所帮助。
主要代码如下：
1 ///2 /// 当点击执行查询时发生 3 /// 4 private void Button_DoSearch_Click(object sender, EventArgs e) 5 { 6 Application.DoEvents(); 7 DoSearchAsync(); 8 //DoSearchNormal(); 9 }
1 ///2 /// 异步方式查询Customers和Orders 3 /// 4 private void DoSearchAsync() 5 { 6 this.Text = "异步方式查询数据库"; 7 GetAllOrders(); 8 GetAllCustomers(); 9 } 10 11 ///12 /// 普通方式查询Customers和Orders 13 /// 14 private void DoSearchNormal() 15 { 16 this.Text = "普通方式查询数据库"; 17 mWatch.Start(); 18 GetAllCustomersNormal(); 19 GetAllOrdersNormal(); 20 mWatch.Stop(); 21 label_Time.Text = "查询耗时：" + mWatch.ElapsedMilliseconds.ToString() + "毫秒"; 22 }
View Code 1 ///2 /// 普通方式查询orders 3 /// 4 private void GetAllOrdersNormal() 5 { 6 try 7 { 8 mConnection = new SqlConnection(mConnectionString); 9 string sqlString = "select * from Orders"; 10 SqlCommand command = new SqlCommand(sqlString, mConnection); 11 mConnection.Open(); 12 SqlDataReader reader = command.ExecuteReader(); 13 DataTable dataTable = new DataTable(); 14 dataTable.Load(reader); 15 dgv_Data.DataSource = dataTable; 16 } 17 catch (Exception ex) 18 { 19 label_Time.Text = ex.Message; 20 } 21 finally 22 { 23 if (mConnection != null) 24 { 25 mConnection.Close(); 26 } 27 } 28 } 29 30 ///31 /// 普通方式查询Customers 32 /// 33 private void GetAllCustomersNormal() 34 { 35 try 36 { 37 mConnection2 = new SqlConnection(mConnectionString); 38 string sqlString = "select * from Customers"; 39 SqlCommand command = new SqlCommand(sqlString, mConnection2); 40 mConnection2.Open(); 41 SqlDataReader reader = command.ExecuteReader(); 42 DataTable dataTable = new DataTable(); 43 dataTable.Load(reader); 44 dgv_Customer.DataSource = dataTable; 45 } 46 catch (Exception ex) 47 { 48 label_Time2.Text = ex.Message; 49 } 50 finally 51 { 52 if (mConnection2 != null) 53 { 54 mConnection2.Close(); 55 } 56 } 57 } 58 59 ///60 /// 异步方式查询orders 61 /// 62 private void GetAllOrders() 63 { 64 mConnection = new SqlConnection(mConnectionString); 65 string sqlString = "select * from Orders"; 66 SqlCommand command = new SqlCommand(sqlString, mConnection); 67 mConnection.Open(); 68 mWatch.Start(); 69 AsyncCallback callBack = new AsyncCallback(HandleCallback);//注册回调方法 70 //开始执行异步查询，将Command作为参数传递到回调函数以便执行End操作 71 command.BeginExecuteReader(callBack, command); 72 } 73 74 ///75 /// 异步方式查询customers 76 /// 77 private void GetAllCustomers() 78 { 79 mConnection2 = new SqlConnection(mConnectionString); 80 string sqlString = "select * from Customers"; 81 SqlCommand command = new SqlCommand(sqlString, mConnection2); 82 mConnection2.Open(); 83 mCustomerWatch.Start(); 84 AsyncCallback callBack = new AsyncCallback(HandleCustomerCallback); 85 command.BeginExecuteReader(callBack, command); 86 } 87 88 ///89 /// 异步查询orders的回调方法 90 /// 91 /// 异步操作状态 92 private void HandleCallback(IAsyncResult MyResult) 93 { 94 try 95 { 96 SqlCommand command = (SqlCommand)MyResult.AsyncState; 97 SqlDataReader reader = command.EndExecuteReader(MyResult); 98 mWatch.Stop(); 99 string callBackTime = mWatch.ElapsedMilliseconds.ToString() + "毫秒"; 100 DataTable dataTable = new DataTable(); 101 dataTable.Load(reader); 102 this.Invoke(myTimeDelegate, callBackTime); 103 this.Invoke(myDataDelegate, dataTable); 104 } 105 catch (Exception MyEx) 106 { 107 this.Invoke(new DisplayInfoDelegate(DisplayTimeResults), String.Format(MyEx.Message)); 108 } 109 finally 110 { 111 if (mConnection != null) 112 { 113 mConnection.Close(); 114 } 115 } 116 } 117 118 ///119 /// 异步查询customers的回调方法 120 /// 121 /// 异步操作状态 122 private void HandleCustomerCallback(IAsyncResult MyResult) 123 { 124 try 125 { 126 SqlCommand command = (SqlCommand)MyResult.AsyncState; 127 SqlDataReader reader = command.EndExecuteReader(MyResult); 128 mCustomerWatch.Stop(); 129 string callBackTime = mCustomerWatch.ElapsedMilliseconds.ToString() + "毫秒"; 130 DataTable dataTable = new DataTable(); 131 dataTable.Load(reader); 132 this.Invoke(myCustomerTimeDelegate, callBackTime); 133 this.Invoke(myCustomerDelegate, dataTable); 134 } 135 catch (Exception MyEx) 136 { 137 this.Invoke(new DisplayInfoDelegate(DisplayTimeResults), String.Format(MyEx.Message)); 138 } 139 finally 140 { 141 if (mConnection2 != null) 142 { 143 mConnection2.Close(); 144 } 145 } 146 }
普通方式查询结果如图1所示：
图1
异步方式查询结果如图2所示：
图2
通过这两张图的对比，我们发现普通方式查询耗时是304毫秒，异步方式两张表的耗时加起来还不到10毫秒，速度差别是很明显的。因为普通方式执行查询的操作同在主线程上，两次操作需要排队，上一个操作未完成就会阻塞线程，这时程序是不能做任何事情的。而异步恰巧可以解决这个问题，两次查询操作都通过回调函数完成查询，程序不会阻塞或挂起线程。
其实，异步操作并没有实质上提高查询数据库的速度，只是发挥了并行的长处。当操作大量的数据时，对数据库的优化是必须的，建立索引，多使用存储过程对提高查询速度都有很大帮助。不得不说的是，异步确实还是有风险的，确定有必要使用时再用。
本文例子可以到这里下载：