淺談多重作用結果集（Multiple Active Result Set，MARS）
MARS搭配SQL Server 2005以上的版本，讓前端的.NET程式可以在同一個連線中同時進行多個批次查詢。
- 下列程式示範未啟用MARS，因此會收到下圖的錯誤訊息。
1: SqlConnectionStringBuilder conbd = new SqlConnectionStringBuilder();
2: conbd.InitialCatalog = "Northwind";
3: conbd.IntegratedSecurity = true;
4: conbd.DataSource = ".";
5: using (SqlConnection con = new SqlConnection(conbd.ToString()))
6: {
7: if (con.State != ConnectionState.Open) con.Open();
8: try
9: {
10: using (TransactionScope trans = new TransactionScope())
11: {
12: using (SqlCommand cmd = new SqlCommand("select c1,c2 from t1", con))
13: {
14: using (SqlDataReader reader = cmd.ExecuteReader())
15: {
16: while (reader.Read())
17: {
18: using (SqlCommand cmdInsert = new SqlCommand("insert into t2 values(@c1,@c2)", con))
19: {
20: cmdInsert.Parameters.Clear();
21: cmdInsert.Parameters.AddRange(new SqlParameter[] { new SqlParameter("@c1", SqlDbType.Int), new SqlParameter("@c2", SqlDbType.VarChar, 10) });
22: cmdInsert.Parameters[0].Value = reader[0];
23: cmdInsert.Parameters[1].Value = reader[1];
24: cmdInsert.ExecuteNonQuery();
25: }
26: }
27: }
28: cmd.CommandText = "delete from t1";
29: cmd.ExecuteNonQuery();
30: }
31: trans.Complete();
32: }
33: }
34: catch (Exception ex)
35: {
36: MessageBox.Show(ex.Message);
37: }
38: }
要解決上述問題，只要啟用MARS即可順利在同一個連線中從SqlDataReader讀出資料並新增和刪除資料（設定方式如下）。
1: conbd.MultipleActiveResultSets = true;
特別強調MARS是用在單一連線上建立邏輯連接，來處理多個SQL命令，以SqlClient為例，僅限於SqlConnection的共用，SqlCommand則必須各自宣告，否則會收到如下圖的錯誤訊息。
- 下列程式嘗試於開啟MARS後共用相同sqlCommand物件來同時處理SqlDataReader及進行Insert動作，在SqlDataReader尚未關閉前，執行ExecuteNonQuery方法將會發生例外。
1: SqlConnectionStringBuilder conbd = new SqlConnectionStringBuilder();
2: conbd.InitialCatalog = "Northwind";
3: conbd.IntegratedSecurity = true;
4: conbd.DataSource = ".";
5: conbd.MultipleActiveResultSets = true;
6: using (SqlConnection con = new SqlConnection(conbd.ToString()))
7: {
8: if (con.State != ConnectionState.Open) con.Open();
9: try
10: {
11: using (TransactionScope trans = new TransactionScope())
12: {
13: using (SqlCommand cmd = new SqlCommand("select c1,c2 from t1", con))
14: {
15: using (SqlDataReader reader = cmd.ExecuteReader())
16: {
17: while (reader.Read())
18: {
19: cmd.CommandText = "insert into t2 values(@c1,@c2)";
20: cmd.Parameters.Clear();
21: cmd.Parameters.AddRange(new SqlParameter[] { new SqlParameter("@c1", SqlDbType.Int), new SqlParameter("@c2", SqlDbType.VarChar, 10) });
22: cmd.Parameters[0].Value = reader[0];
23: cmd.Parameters[1].Value = reader[1];
24: cmd.ExecuteNonQuery();
25: }
26: }
27: cmd.CommandText = "delete from t1";
28: cmd.ExecuteNonQuery();
29: }
30: trans.Complete();
31: }
32: }
33: catch (Exception ex)
34: {
35: MessageBox.Show(ex.Message);
36: }
37: }
參考資料：