【转】ADO.NET2.0新特性-异步查询

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>

 

后台代码:

 

    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(
3000true);
                    
//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


posted on 2007-10-17 11:22  reagan  阅读(649)  评论(1编辑  收藏  举报