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(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();
    }
}