MARS(Multiple Active Result Set) 多动结果集是 ADO.NET 提供的一个新功能 , MARS 可以在同一连接上处理多个活动的结果集 , 因为重用了连接 , 减少了数据库的访问 , 从而大大的提高了性能 . 这个功能结合另一新功能 : 对异步进程的支持 是一个很强大的功能~~~
需要注意的是 : 使用 ADO.NET 2.0 的异步进程 , 数据库连接字符串要添加 Asynchronous Processing=true 如果执行多条命令连接字符串还要添加 MultipleActiveResultSets=true , 否则会抛出异常 : 此连接不支持 MultipleActiveResultSets。
这里使用的数据库及表为 MS Sql Server 2000 的 NorthWind 的 Customers 表 和 Orders 表 , 页面上只需拖放相应的GridView 控件。
先看看异步回调 , 这里只执行一条命令 :
string
strSelectCmdOrders
=
@"
SELECT TOP 5 C.CustomerID,CompanyName,ContactName,OrderID,OrderDate,ShipCity
FROM Customers C ,Orders O WHERE C.CustomerID = O.CustomerID
ORDER BY CompanyName,ContactName " ;
// 以此变量判断异步执行是否完成
bool isComplete = false ;
protected void Page_Load( object sender, EventArgs e)
{
if ( ! IsPostBack )
{
SqlConnection sqlCon = new SqlConnection( " Data Source=.;Initial Catalog=NorthWind;Integrated Security=True;Asynchronous Processing=true " );
SqlCommand sqlCmdOrders = new SqlCommand( strSelectCmdOrders , sqlCon );
sqlCon.Open( );
sqlCmdOrders.BeginExecuteReader( new AsyncCallback( callBackOrders ) , sqlCmdOrders , CommandBehavior.CloseConnection );
// 如果异步执行完成则结束该事件处理
while ( ! isComplete )
{
System.Threading.Thread.Sleep( 1 );
}
}
}
private void callBackOrders ( IAsyncResult asyncResult )
{
using ( SqlCommand sqlCmdOrders = asyncResult.AsyncState as SqlCommand )
{
using ( SqlDataReader sqlReader = sqlCmdOrders.EndExecuteReader( asyncResult ) )
{
GridViewOrders.DataSource = sqlReader;
GridViewOrders.DataBind( );
isComplete = true ;
}
}
}
FROM Customers C ,Orders O WHERE C.CustomerID = O.CustomerID
ORDER BY CompanyName,ContactName " ;
// 以此变量判断异步执行是否完成
bool isComplete = false ;
protected void Page_Load( object sender, EventArgs e)
{
if ( ! IsPostBack )
{
SqlConnection sqlCon = new SqlConnection( " Data Source=.;Initial Catalog=NorthWind;Integrated Security=True;Asynchronous Processing=true " );
SqlCommand sqlCmdOrders = new SqlCommand( strSelectCmdOrders , sqlCon );
sqlCon.Open( );
sqlCmdOrders.BeginExecuteReader( new AsyncCallback( callBackOrders ) , sqlCmdOrders , CommandBehavior.CloseConnection );
// 如果异步执行完成则结束该事件处理
while ( ! isComplete )
{
System.Threading.Thread.Sleep( 1 );
}
}
}
private void callBackOrders ( IAsyncResult asyncResult )
{
using ( SqlCommand sqlCmdOrders = asyncResult.AsyncState as SqlCommand )
{
using ( SqlDataReader sqlReader = sqlCmdOrders.EndExecuteReader( asyncResult ) )
{
GridViewOrders.DataSource = sqlReader;
GridViewOrders.DataBind( );
isComplete = true ;
}
}
}
注意 BeginExecuteReader 方法的第2个参数 , 该参数接收一个传递给回调方法的定制对象 , 在回调方法里可以通过 AsyncState 获取该对象
通过跟踪程序 结合使用 SQL SERVER 事件探察器发现在调用 BeginExecuteReader 方法时执行了 SQL 语句.
再来看看 MARS 和 异步处理的结合:
string
strSelectCmdOrders
=
@"
SELECT TOP 5 C.CustomerID,CompanyName,ContactName,OrderID,OrderDate,ShipCity
FROM Customers C ,Orders O WHERE C.CustomerID = O.CustomerID AND CompanyName = 'Alfreds Futterkiste'
ORDER BY CompanyName,ContactName " ;
string strSelectCmdCustomers = " SELECT * FROM Customers WHERE CompanyName = 'Alfreds Futterkiste' " ;
protected void Page_Load( object sender, EventArgs e)
{
if ( ! IsPostBack )
{
using ( SqlConnection sqlCon = new SqlConnection( " Data Source=.;Initial Catalog=NorthWind;Integrated Security=True;Asynchronous Processing=true;MultipleActiveResultSets=true " ) )
{
sqlCon.Open( );
SqlCommand sqlCmdCustomer = new SqlCommand( strSelectCmdCustomers , sqlCon );
IAsyncResult asyncResultCustomer = sqlCmdCustomer.BeginExecuteReader( );
SqlCommand sqlCmdOrder = new SqlCommand( strSelectCmdOrders , sqlCon );
IAsyncResult asyncResultOrder = sqlCmdOrder.BeginExecuteReader( );
System.Threading.WaitHandle waitHandleCustomer = asyncResultCustomer.AsyncWaitHandle;
System.Threading.WaitHandle waitHandleOrder = asyncResultOrder.AsyncWaitHandle;
System.Threading.WaitHandle[] waitHandles = new System.Threading.WaitHandle[] { waitHandleCustomer , waitHandleOrder };
for ( int i = 0 ; i < waitHandles.Length ; i ++ )
{
switch ( System.Threading.WaitHandle.WaitAny( waitHandles ) )
{
case 0 :
SqlDataReader sqlReaderCustomer = sqlCmdCustomer.EndExecuteReader( asyncResultCustomer );
GridView1.DataSource = sqlReaderCustomer;
GridView1.DataBind( );
break ;
case 1 :
SqlDataReader sqlReaderOrder = sqlCmdOrder.EndExecuteReader( asyncResultOrder );
GridView2.DataSource = sqlReaderOrder;
GridView2.DataBind( );
break ;
}
}
sqlCmdCustomer.Dispose( );
sqlCmdOrder.Dispose( );
}
}
// System.Threading.WaitHandle.WaitAll( wailtHandles );
// SqlDataReader sqlReaderCustomer = sqlCmdCustomer.EndExecuteReader( asyncResultCustomer );
// GridView1.DataSource = sqlReaderCustomer;
// GridView1.DataBind( );
// SqlDataReader sqlReaderOrder = sqlCmdOrder.EndExecuteReader( asyncResultOrder );
// GridView2.DataSource = sqlReaderOrder;
// GridView2.DataBind( );
}
注意这里的数据库连接字符串加上了
MultipleActiveResultSets=true , 还有点是这个功能目前只支持 MS SQL SERVER 2005 及以上版本 FROM Customers C ,Orders O WHERE C.CustomerID = O.CustomerID AND CompanyName = 'Alfreds Futterkiste'
ORDER BY CompanyName,ContactName " ;
string strSelectCmdCustomers = " SELECT * FROM Customers WHERE CompanyName = 'Alfreds Futterkiste' " ;
protected void Page_Load( object sender, EventArgs e)
{
if ( ! IsPostBack )
{
using ( SqlConnection sqlCon = new SqlConnection( " Data Source=.;Initial Catalog=NorthWind;Integrated Security=True;Asynchronous Processing=true;MultipleActiveResultSets=true " ) )
{
sqlCon.Open( );
SqlCommand sqlCmdCustomer = new SqlCommand( strSelectCmdCustomers , sqlCon );
IAsyncResult asyncResultCustomer = sqlCmdCustomer.BeginExecuteReader( );
SqlCommand sqlCmdOrder = new SqlCommand( strSelectCmdOrders , sqlCon );
IAsyncResult asyncResultOrder = sqlCmdOrder.BeginExecuteReader( );
System.Threading.WaitHandle waitHandleCustomer = asyncResultCustomer.AsyncWaitHandle;
System.Threading.WaitHandle waitHandleOrder = asyncResultOrder.AsyncWaitHandle;
System.Threading.WaitHandle[] waitHandles = new System.Threading.WaitHandle[] { waitHandleCustomer , waitHandleOrder };
for ( int i = 0 ; i < waitHandles.Length ; i ++ )
{
switch ( System.Threading.WaitHandle.WaitAny( waitHandles ) )
{
case 0 :
SqlDataReader sqlReaderCustomer = sqlCmdCustomer.EndExecuteReader( asyncResultCustomer );
GridView1.DataSource = sqlReaderCustomer;
GridView1.DataBind( );
break ;
case 1 :
SqlDataReader sqlReaderOrder = sqlCmdOrder.EndExecuteReader( asyncResultOrder );
GridView2.DataSource = sqlReaderOrder;
GridView2.DataBind( );
break ;
}
}
sqlCmdCustomer.Dispose( );
sqlCmdOrder.Dispose( );
}
}
// System.Threading.WaitHandle.WaitAll( wailtHandles );
// SqlDataReader sqlReaderCustomer = sqlCmdCustomer.EndExecuteReader( asyncResultCustomer );
// GridView1.DataSource = sqlReaderCustomer;
// GridView1.DataBind( );
// SqlDataReader sqlReaderOrder = sqlCmdOrder.EndExecuteReader( asyncResultOrder );
// GridView2.DataSource = sqlReaderOrder;
// GridView2.DataBind( );
}
如果要取消异步进程 , 则只需在调用相应的 Command 对象的 Cancel 方法 , 如果想要 Rollback 已由 Command 对象完成的处理 , 则在执行命令前提供一个定制事务 .
可以看出 MARS 和异步命令处理一起使用是很强大的~
相关文章: Multiple Active Result Sets (MARS)