深入研究SQL SERVER 2005和多活动结果集（MARS）

SQL SERVER 2005引入了在单一连接上对多活动结果集（也称为MARS）或多个请求的支持。通过在与SQL SERVER 2005的连接上启用这一特性，当存在与Sqlconnection相关联的开放式SqlDataReader时，连接将不会中断。即使尚未关闭当前打开的SqlDataReader，也仍然能够在Sqlconnection上执行其他查询比如：SELECT，UPDATE，CREATETABLE等等。

 

举个简单的例子，就是在Northwind数据库里的订单表里取订单数据，而且把对应的子订单明细数据一起取出。

 

C#示例代码：<激光传真机>

string strSQL; SqlConnectionStringBuilder ssb = new SqlConnectionStringBuilder(); ssb.DataSource = "."; ssb.InitialCatalog = "Northwind"; ssb.UserID = "sa"; ssb.Password = "********"; ssb.MultipleActiveResultSets = true; SqlConnection cn = new SqlConnection(ssb.ConnectionString); SqlCommand cmdOrders, cmdDetials; SqlParameter pCustID, pOrderID; SqlDataReader rdrOrders, rdrDetials; cn.Open(); strSQL = "SELECT OrderID,OrderDate FROM Orders WHERE CustomerID = @CustomerID"; cmdOrders = new SqlCommand(strSQL, cn); pCustID = cmdOrders.Parameters.Add("@CustomerID", SqlDbType.NChar, 5); strSQL = "SELECT ProductID,Quantity,UnitPrice FROM [ORDER dETAiLS] WHERE OrderID = @OrderID"; cmdDetials = new SqlCommand(strSQL, cn); pOrderID = cmdDetials.Parameters.Add("@OrderID", SqlDbType.NChar, 5); pCustID.Value = "ALFKI"; Console.WriteLine("Orders for {0}", pCustID.Value); Console.WriteLine("================="); rdrOrders = cmdOrders.ExecuteReader(); while (rdrOrders.Read()) { Console.WriteLine("OrderID: {0} OrderDate:{1:d}", rdrOrders["OrderID"], rdrOrders["OrderDate"]); pOrderID.Value = rdrOrders["OrderID"]; rdrDetials = cmdDetials.ExecuteReader(); while (rdrDetials.Read()) { Console.WriteLine(" ProductID:{0} Quantity:{1} UnitPrice:{2:c}", rdrDetials["ProductID"], rdrDetials["Quantity"], rdrDetials["UnitPrice"]); } rdrDetials.Close(); } rdrOrders.Close(); cn.Close();

 

执行结果图片：<激光一体机>

 

 

推荐站点:长宸办公