ADO.NET2.0增加了一些新的特性,其中就包括异步查询。这个特点在需要执行多个查询的时候,或者查询过程比较常的时候就很有用。而默认情况下是不开启异步查询的,必须要在连接字符串中显示的打开,如下图,图中划线的就是需要显示制定的,后面一个是显示的打开MARS。
下面的代码包括了异步查询的三种方法,所以代码都可以测试运行,数据库是NorthWind.
其中一个运行页面如图:
首先是页面源码:
<
form
id
="form1"
runat
="server"
>
< div >
< asp:GridView ID ="GridView1" runat ="server" BackColor ="White" BorderColor ="#CCCCCC"
BorderStyle ="None" BorderWidth ="1px" CellPadding ="3" >
< FooterStyle BackColor ="White" ForeColor ="#000066" />
< RowStyle ForeColor ="#000066" />
< SelectedRowStyle BackColor ="#669999" Font-Bold ="True" ForeColor ="White" />
< PagerStyle BackColor ="White" ForeColor ="#000066" HorizontalAlign ="Left" />
< HeaderStyle BackColor ="#006699" Font-Bold ="True" ForeColor ="White" />
</ asp:GridView >
</ div >
< asp:GridView ID ="GridView2" runat ="server" BackColor ="White" BorderColor ="#336666"
BorderStyle ="Double" BorderWidth ="3px" CellPadding ="4" GridLines ="Horizontal" >
< FooterStyle BackColor ="White" ForeColor ="#333333" />
< RowStyle BackColor ="White" ForeColor ="#333333" />
< SelectedRowStyle BackColor ="#339966" Font-Bold ="True" ForeColor ="White" />
< PagerStyle BackColor ="#336666" ForeColor ="White" HorizontalAlign ="Center" />
< HeaderStyle BackColor ="#336666" Font-Bold ="True" ForeColor ="White" />
</ asp:GridView >
</ form >
< div >
< asp:GridView ID ="GridView1" runat ="server" BackColor ="White" BorderColor ="#CCCCCC"
BorderStyle ="None" BorderWidth ="1px" CellPadding ="3" >
< FooterStyle BackColor ="White" ForeColor ="#000066" />
< RowStyle ForeColor ="#000066" />
< SelectedRowStyle BackColor ="#669999" Font-Bold ="True" ForeColor ="White" />
< PagerStyle BackColor ="White" ForeColor ="#000066" HorizontalAlign ="Left" />
< HeaderStyle BackColor ="#006699" Font-Bold ="True" ForeColor ="White" />
</ asp:GridView >
</ div >
< asp:GridView ID ="GridView2" runat ="server" BackColor ="White" BorderColor ="#336666"
BorderStyle ="Double" BorderWidth ="3px" CellPadding ="4" GridLines ="Horizontal" >
< FooterStyle BackColor ="White" ForeColor ="#333333" />
< RowStyle BackColor ="White" ForeColor ="#333333" />
< SelectedRowStyle BackColor ="#339966" Font-Bold ="True" ForeColor ="White" />
< PagerStyle BackColor ="#336666" ForeColor ="White" HorizontalAlign ="Center" />
< HeaderStyle BackColor ="#336666" Font-Bold ="True" ForeColor ="White" />
</ asp:GridView >
</ form >
后台代码:
public
string
ConStr
=
ConfigurationManager.ConnectionStrings[
"
NorthwindConnectionString
"
].ConnectionString;
protected void Page_Load( object sender, EventArgs e)
... {
if (!IsPostBack)
...{
BindData();
//BindMultiData();
//BindMultiDataArray();
//BindDataWithCallBack();
}
}
// 异步查询的poll方式,最普通的方式
private void BindData()
... {
string QueryStr = "SELECT * FROM customers";
using (SqlConnection Con = new SqlConnection(ConStr))
...{
SqlCommand Cmd = new SqlCommand(QueryStr, Con);
IAsyncResult ASynResult;
SqlDataReader Da;
try
...{
Con.Open();
//begin方法返回一个IAsyncResult对象,用来检查执行是否完成
ASynResult = Cmd.BeginExecuteReader(CommandBehavior.CloseConnection);
while (!ASynResult.IsCompleted)
...{
Response.Write("异步查询</br>");
ASynResult.AsyncWaitHandle.WaitOne(3000, true);
//System.Threading.Thread.Sleep(10);
}
Da = Cmd.EndExecuteReader(ASynResult);
GridView1.DataSource = Da;
GridView1.DataBind();
}
catch (Exception ex)
...{
Response.Write(ex.Message);
}
}
}
// 异步查询的wait方式,使用多个等待句柄来异步查询,必须等待所有进程完成处理结果集
private void BindMultiData()
... {
string CusQueryStr = "SELECT * FROM customers WHERE CompanyName = 'Alfreds Futterkiste'";
string SupQueryStr = "SELECT Customers.CompanyName, Customers.ContactName, " +
"Orders.OrderID, Orders.OrderDate, " +
"Orders.RequiredDate, Orders.ShippedDate " +
"FROM Orders, Customers " +
"WHERE Orders.CustomerID = Customers.CustomerID " +
"AND Customers.CompanyName = 'Alfreds Futterkiste' " +
"ORDER BY Customers.CompanyName, Customers.ContactName";
using (SqlConnection MyCon = new SqlConnection(ConStr))
...{
SqlCommand CusCmd = new SqlCommand(CusQueryStr, MyCon);
SqlCommand SupCmd = new SqlCommand(SupQueryStr, MyCon);
SqlDataReader CusDr;
SqlDataReader SupDr;
IAsyncResult CusIsynResult;
IAsyncResult SupIsynResult;
//创建句柄数组
System.Threading.WaitHandle[] WHandles = new System.Threading.WaitHandle[2];
System.Threading.WaitHandle CusHandle;
System.Threading.WaitHandle SupHandle;
MyCon.Open();
CusIsynResult = CusCmd.BeginExecuteReader(CommandBehavior.CloseConnection);
SupIsynResult = SupCmd.BeginExecuteReader(CommandBehavior.CloseConnection);
CusHandle = CusIsynResult.AsyncWaitHandle;
SupHandle = SupIsynResult.AsyncWaitHandle;
//将等待句柄赋给句柄数组
WHandles[0] = CusHandle;
WHandles[1] = SupHandle;
//将数组传给waitall方法,等待所以的异步查询完成
System.Threading.WaitHandle.WaitAll(WHandles);
CusDr = CusCmd.EndExecuteReader(CusIsynResult);
SupDr = SupCmd.EndExecuteReader(SupIsynResult);
GridView1.DataSource = CusDr;
GridView1.DataBind();
GridView2.DataSource = SupDr;
GridView2.DataBind();
MyCon.Dispose();
CusCmd.Dispose();
SupCmd.Dispose();
}
}
// 采用WaitAny方式,优点是不用等待所有进程都完成才处理结果集
private void BindMultiDataArray()
... {
string CusQueryStr = "SELECT * FROM customers WHERE CompanyName = 'Alfreds Futterkiste'";
string SupQueryStr = "SELECT Customers.CompanyName, Customers.ContactName, " +
"Orders.OrderID, Orders.OrderDate, " +
"Orders.RequiredDate, Orders.ShippedDate " +
"FROM Orders, Customers " +
"WHERE Orders.CustomerID = Customers.CustomerID " +
"AND Customers.CompanyName = 'Alfreds Futterkiste' " +
"ORDER BY Customers.CompanyName, Customers.ContactName";
using (SqlConnection MyCon = new SqlConnection(ConStr))
...{
SqlCommand CusCmd = new SqlCommand(CusQueryStr, MyCon);
SqlCommand SupCmd = new SqlCommand(SupQueryStr, MyCon);
SqlDataReader CusDr;
SqlDataReader SupDr;
IAsyncResult CusIsynResult;
IAsyncResult SupIsynResult;
System.Threading.WaitHandle[] WHandles = new System.Threading.WaitHandle[2];
System.Threading.WaitHandle CusHandle;
System.Threading.WaitHandle SupHandle;
int WHindex;
MyCon.Open();
CusIsynResult = CusCmd.BeginExecuteReader(CommandBehavior.CloseConnection);
SupIsynResult = SupCmd.BeginExecuteReader(CommandBehavior.CloseConnection);
CusHandle = CusIsynResult.AsyncWaitHandle;
SupHandle = SupIsynResult.AsyncWaitHandle;
WHandles[0] = CusHandle;
WHandles[1] = SupHandle;
for (int i = 0; i < WHandles.Length; i++)
...{
//waitany好处在于不必等待所有异步操作完成
WHindex = System.Threading.WaitHandle.WaitAny(WHandles);
switch (WHindex)
...{
case 0:
CusDr = CusCmd.EndExecuteReader(CusIsynResult);
GridView1.DataSource = CusDr;
GridView1.DataBind();
break;
case 1:
SupDr = SupCmd.EndExecuteReader(SupIsynResult);
GridView2.DataSource = SupDr;
GridView2.DataBind();
break;
}
}
MyCon.Dispose();
CusCmd.Dispose();
SupCmd.Dispose();
}
}
// 通过回调来实现异步查询
private void BindDataWithCallBack()
... {
string SupQueryStr = "SELECT Customers.CompanyName, Customers.ContactName, " +
"Orders.OrderID, Orders.OrderDate, " +
"Orders.RequiredDate, Orders.ShippedDate " +
"FROM Orders, Customers " +
"WHERE Orders.CustomerID = Customers.CustomerID " +
"AND Customers.CompanyName = 'Alfreds Futterkiste' " +
"ORDER BY Customers.CompanyName, Customers.ContactName";
using (SqlConnection MyCon = new SqlConnection(ConStr))
...{
SqlCommand SupCmd = new SqlCommand(SupQueryStr, MyCon);
SqlDataReader SupDr;
IAsyncResult SupIsynResult;
MyCon.Open();
AsyncCallback Callback = new AsyncCallback(CallBackMethod);
SupIsynResult = SupCmd.BeginExecuteReader(Callback, SupCmd,CommandBehavior.CloseConnection);
System.Threading.Thread.Sleep(100);
MyCon.Dispose();
SupCmd.Dispose();
}
}
// 回调方法
public void CallBackMethod(IAsyncResult IResult)
... {
SqlCommand Command = (SqlCommand)IResult.AsyncState;
SqlDataReader dr = Command.EndExecuteReader(IResult);
GridView1.DataSource = dr;
GridView1.DataBind();
}
}
protected void Page_Load( object sender, EventArgs e)
... {
if (!IsPostBack)
...{
BindData();
//BindMultiData();
//BindMultiDataArray();
//BindDataWithCallBack();
}
}
// 异步查询的poll方式,最普通的方式
private void BindData()
... {
string QueryStr = "SELECT * FROM customers";
using (SqlConnection Con = new SqlConnection(ConStr))
...{
SqlCommand Cmd = new SqlCommand(QueryStr, Con);
IAsyncResult ASynResult;
SqlDataReader Da;
try
...{
Con.Open();
//begin方法返回一个IAsyncResult对象,用来检查执行是否完成
ASynResult = Cmd.BeginExecuteReader(CommandBehavior.CloseConnection);
while (!ASynResult.IsCompleted)
...{
Response.Write("异步查询</br>");
ASynResult.AsyncWaitHandle.WaitOne(3000, true);
//System.Threading.Thread.Sleep(10);
}
Da = Cmd.EndExecuteReader(ASynResult);
GridView1.DataSource = Da;
GridView1.DataBind();
}
catch (Exception ex)
...{
Response.Write(ex.Message);
}
}
}
// 异步查询的wait方式,使用多个等待句柄来异步查询,必须等待所有进程完成处理结果集
private void BindMultiData()
... {
string CusQueryStr = "SELECT * FROM customers WHERE CompanyName = 'Alfreds Futterkiste'";
string SupQueryStr = "SELECT Customers.CompanyName, Customers.ContactName, " +
"Orders.OrderID, Orders.OrderDate, " +
"Orders.RequiredDate, Orders.ShippedDate " +
"FROM Orders, Customers " +
"WHERE Orders.CustomerID = Customers.CustomerID " +
"AND Customers.CompanyName = 'Alfreds Futterkiste' " +
"ORDER BY Customers.CompanyName, Customers.ContactName";
using (SqlConnection MyCon = new SqlConnection(ConStr))
...{
SqlCommand CusCmd = new SqlCommand(CusQueryStr, MyCon);
SqlCommand SupCmd = new SqlCommand(SupQueryStr, MyCon);
SqlDataReader CusDr;
SqlDataReader SupDr;
IAsyncResult CusIsynResult;
IAsyncResult SupIsynResult;
//创建句柄数组
System.Threading.WaitHandle[] WHandles = new System.Threading.WaitHandle[2];
System.Threading.WaitHandle CusHandle;
System.Threading.WaitHandle SupHandle;
MyCon.Open();
CusIsynResult = CusCmd.BeginExecuteReader(CommandBehavior.CloseConnection);
SupIsynResult = SupCmd.BeginExecuteReader(CommandBehavior.CloseConnection);
CusHandle = CusIsynResult.AsyncWaitHandle;
SupHandle = SupIsynResult.AsyncWaitHandle;
//将等待句柄赋给句柄数组
WHandles[0] = CusHandle;
WHandles[1] = SupHandle;
//将数组传给waitall方法,等待所以的异步查询完成
System.Threading.WaitHandle.WaitAll(WHandles);
CusDr = CusCmd.EndExecuteReader(CusIsynResult);
SupDr = SupCmd.EndExecuteReader(SupIsynResult);
GridView1.DataSource = CusDr;
GridView1.DataBind();
GridView2.DataSource = SupDr;
GridView2.DataBind();
MyCon.Dispose();
CusCmd.Dispose();
SupCmd.Dispose();
}
}
// 采用WaitAny方式,优点是不用等待所有进程都完成才处理结果集
private void BindMultiDataArray()
... {
string CusQueryStr = "SELECT * FROM customers WHERE CompanyName = 'Alfreds Futterkiste'";
string SupQueryStr = "SELECT Customers.CompanyName, Customers.ContactName, " +
"Orders.OrderID, Orders.OrderDate, " +
"Orders.RequiredDate, Orders.ShippedDate " +
"FROM Orders, Customers " +
"WHERE Orders.CustomerID = Customers.CustomerID " +
"AND Customers.CompanyName = 'Alfreds Futterkiste' " +
"ORDER BY Customers.CompanyName, Customers.ContactName";
using (SqlConnection MyCon = new SqlConnection(ConStr))
...{
SqlCommand CusCmd = new SqlCommand(CusQueryStr, MyCon);
SqlCommand SupCmd = new SqlCommand(SupQueryStr, MyCon);
SqlDataReader CusDr;
SqlDataReader SupDr;
IAsyncResult CusIsynResult;
IAsyncResult SupIsynResult;
System.Threading.WaitHandle[] WHandles = new System.Threading.WaitHandle[2];
System.Threading.WaitHandle CusHandle;
System.Threading.WaitHandle SupHandle;
int WHindex;
MyCon.Open();
CusIsynResult = CusCmd.BeginExecuteReader(CommandBehavior.CloseConnection);
SupIsynResult = SupCmd.BeginExecuteReader(CommandBehavior.CloseConnection);
CusHandle = CusIsynResult.AsyncWaitHandle;
SupHandle = SupIsynResult.AsyncWaitHandle;
WHandles[0] = CusHandle;
WHandles[1] = SupHandle;
for (int i = 0; i < WHandles.Length; i++)
...{
//waitany好处在于不必等待所有异步操作完成
WHindex = System.Threading.WaitHandle.WaitAny(WHandles);
switch (WHindex)
...{
case 0:
CusDr = CusCmd.EndExecuteReader(CusIsynResult);
GridView1.DataSource = CusDr;
GridView1.DataBind();
break;
case 1:
SupDr = SupCmd.EndExecuteReader(SupIsynResult);
GridView2.DataSource = SupDr;
GridView2.DataBind();
break;
}
}
MyCon.Dispose();
CusCmd.Dispose();
SupCmd.Dispose();
}
}
// 通过回调来实现异步查询
private void BindDataWithCallBack()
... {
string SupQueryStr = "SELECT Customers.CompanyName, Customers.ContactName, " +
"Orders.OrderID, Orders.OrderDate, " +
"Orders.RequiredDate, Orders.ShippedDate " +
"FROM Orders, Customers " +
"WHERE Orders.CustomerID = Customers.CustomerID " +
"AND Customers.CompanyName = 'Alfreds Futterkiste' " +
"ORDER BY Customers.CompanyName, Customers.ContactName";
using (SqlConnection MyCon = new SqlConnection(ConStr))
...{
SqlCommand SupCmd = new SqlCommand(SupQueryStr, MyCon);
SqlDataReader SupDr;
IAsyncResult SupIsynResult;
MyCon.Open();
AsyncCallback Callback = new AsyncCallback(CallBackMethod);
SupIsynResult = SupCmd.BeginExecuteReader(Callback, SupCmd,CommandBehavior.CloseConnection);
System.Threading.Thread.Sleep(100);
MyCon.Dispose();
SupCmd.Dispose();
}
}
// 回调方法
public void CallBackMethod(IAsyncResult IResult)
... {
SqlCommand Command = (SqlCommand)IResult.AsyncState;
SqlDataReader dr = Command.EndExecuteReader(IResult);
GridView1.DataSource = dr;
GridView1.DataBind();
}
}
Trackback: http://tb.blog.csdn.net/TrackBack.aspx?PostId=1828642