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;

}

}

}