ADO.NET 2.0 數據異步處理改善用戶體驗

　　支持異步處理的提供程序有 SystemDataSqlClient在針對大批量數據插入過更新時使用異步處理方法可以不用等待多有數據更新完畢才能操作或者進行下步處理改善了用戶體驗  SqlCommand對象方法如下

　　異步方法 BeginExecuteNonQuery   EndExecuteNonQuery
           BeginExecuteXmlReader   EndExecuteXmlReader
           BeginExecuteReader   EndExecuteReader
  begin前綴的方法傳入參數end前綴的方法返回輸出參數和返回值
  begin前綴方法返回的是IAsyncResult  用於追蹤一步方法的執行狀態
   IAsyncResult AsnycState 用戶自定義的狀態對象
   IAsyncResult AsnycWaitHandle 呼叫代碼的等待形式是等其中的一個異步方法完成還是全部完成
   IAsyncResult CompletedSynchronously  獲取所有異步方法是否同時完成
   IAsyncResult Iscompleted 是否執行完畢可以根據此屬性進行下部動作
在連接字符串中加入async＝true
   如果所有的命令都是同步的建議在連接字符串中顯施加入async＝false
   如果有一部分命令是異步執行又有一部分是同步同步執行建議分別建立兩個連接對象
   如果async＝true時也可以執行同步命令但是會損失一些資源

　　/**///// obtain connection strings from configuration files or
            //// similar facility
            //// NOTE: these connection strings have to include async=true for
            //// example:
            //// server=myserver;database=mydb;integrated security=true;async=true
            //string connstrAccouting = GetConnString(accounting);
            //string connstrHR = GetConnString(humanresources);
            /**///// define two connection objects one for each database
            //using (SqlConnection connAcc = new SqlConnection(connstrAccounting))
            //using (SqlConnection connHumanRes = new SqlConnection(connstrHR))
            //{
            //    // open the first connection
            //    connAccOpen();
            //    // start the execution of the first query contained in the
            //    // employee_info storedprocedure
            //    SqlCommand cmdAcc = new SqlCommand(employee_info connAcc);
            //    cmdAccCommandType = CommandTypeStoredProcedure;
            //    cmdAccParametersAddWithValue(@empl_id employee_id);
            //    IAsyncResult arAcc = cmdAccBeginExecuteReader();
            //    // at this point the employee_info storedproc is executing on
            //    // the server and this thread is running at the same time
            //    // now open the second connection
            //    connHumanResOpen();
            //    // start the execution of the second storedproc against
            //    // the humanresources server
            //    SqlCommand cmdHumanRes = new SqlCommand(employee_hrinfo
            //                                            connHumanRes);
            //    cmdHumanResParametersAddWithValue(@empl_id employee_id);
            //    IAsyncResult arHumanRes = cmdHumanResBeginExecuteReader();
            //    // now both queries are running at the same time
            //    // at this point; more work can be done from this thread or we
            //    // can simply wait until both commands finish in our case well
            //    // wait
            //    SqlDataReader drAcc = cmdAccEndExecuteReader(arAcc);
            //    SqlDataReader drHumanRes = cmdHumanResEndExecuteReader(arHumanRes);
            //    // now we can render the results for example bind the readers to an ASPNET
            //    // web control or scan the reader and draw the information in a
            //    // WebForms form
            //}

　　string custid = ALFKI;
            string orderid = ;

　　// NOTE: connection strings denoted by connstring have to include
            // async=true for example:
            string connstring = server=(local);database=northwind;integrated security=true;async=true;
            // well use three connections for this
            using (SqlConnection c = new SqlConnection(connstring))
            using (SqlConnection c = new SqlConnection(connstring))
            using (SqlConnection c = new SqlConnection(connstring))
            {
                // get customer info
                cOpen();
                SqlCommand cmd = new SqlCommand(
                  SELECT CustomerID CompanyName ContactName FROM Customers WHERE CustomerID=@id c);
                cmdParametersAdd(@id SqlDbTypeChar )Value = custid;
                IAsyncResult arCustomer = cmdBeginExecuteReader();
                // get orders
                cOpen();
                SqlCommand cmd = new SqlCommand(SELECT * FROM Orders WHERE CustomerID=@id c);
                cmdParametersAdd(@id SqlDbTypeChar )Value = custid;
                IAsyncResult arOrders = cmdBeginExecuteReader();
                // get order detail if user picked an order
                IAsyncResult arDetails = null;
                SqlCommand cmd = null;
                if (null != orderid)
                {
                    cOpen();
                    cmd = new SqlCommand(SELECT * FROM [Order Details] WHERE OrderID=@id c);
                    cmdParametersAdd(@id SqlDbTypeInt)Value = intParse(orderid);
                    arDetails = cmdBeginExecuteReader();
                }
                // build the wait handle array for WaitForMultipleObjects
                WaitHandle[] handles = new WaitHandle[null == arDetails ? : ];
                handles[] = arCustomerAsyncWaitHandle;
                handles[] = arOrdersAsyncWaitHandle;
                if (null != arDetails)
                    handles[] = arDetailsAsyncWaitHandle;
                // wait for commands to complete and render page controls as we
                // get data back
                SqlDataReader r;
                DataTable dt;
                for (int results = (null == arDetails) ? : ; results < ; results++)
                {
                    // wait for any handle then process results as they come
                    int index = WaitHandleWaitAny(handles false); // secs
                    if (WaitHandleWaitTimeout == index)
                        throw new Exception(Timeout);
                    switch (index)
                    {
                        case : // customer query is ready
                            r = cmdEndExecuteReader(arCustomer);
                            if (!rRead())
                                continue;
                            lblCustomerIDText = rGetString();
                            lblCompanyNameText = rGetString();
                            lblContactText = rGetString();
                            rClose();
                            break;
                        case : // orders query is ready
                            r = cmdEndExecuteReader(arOrders);
                            dt = new DataTable();
                            dtLoad(r);
                            dgOrdersDataSource = dt; // databind to the orders grid
                            dgOrdersRefresh();
                            rClose();
                            break;
                        case : // details query is ready
                            r = cmdEndExecuteReader(arDetails);
                            dt = new DataTable();
                            dtLoad(r);
                            dgDetailsDataSource = dt; // databind to the details grid
                            dgDetailsRefresh();
                            rClose();
                            break;

　　}
                }
            }

From:http://tw.wingwit.com/Article/program/net/201311/11392.html