c# - MultipleActiveResultSets 启用但不工作

Question

我启用了 MARS 但仍然无法在一个连接中执行两个 SqlDataReader，我搜索了一个解决方案以使用 RegEdit 默认启用 MARS 找不到任何解决方案并且仍然收到该错误:已经有一个与此命令关联的打开的 DataReader必须先关闭。

代码如下:

public partial class student_Courses : System.Web.UI.Page
{
    string connectionString = "";
    string query1 = "";
    string query2 = "";
    string courseName = "";
    string chapterName = "";
    string chapterVideoName = "";
    string courseValue = "";
    SqlDataReader sr2 = null;

    protected void Page_Load(object sender, EventArgs e)
    {
        query1 = "SELECT * FROM Courses";
        query2 = "SELECT * FROM Chapters WHERE value='" + courseValue + "'";
        connectionString = "Data Source=Prince-PC;" + 
                            "Initial Catalog=Elearning;" + 
                            "Integrated Security=True;"+ 
                            "MultipleActiveResultSets=True";
        SqlConnection con = new SqlConnection(connectionString);
        SqlCommand command1 = new SqlCommand(query1, con);
        SqlCommand command2 = new SqlCommand(query2, con);
        if (con.State == ConnectionState.Closed)
            con.Open();

        using (SqlDataReader sr1 = command1.ExecuteReader())
        {
            while (sr1.Read())
            {
                courseName = sr1["name"].ToString();
                courseValue = sr1["value"].ToString();
                sr2 = command2.ExecuteReader();
                while (sr2.Read())
                {
                    chapterName = TextBox3.Text =  sr2["name"].ToString();
                    chapterVideoName = Label2.Text =  sr2["video_name"].ToString();
                }
            }
        }
        con.Close();
    }
}

Answer 1

您需要像 this MSDN MARS example 中那样通过 using 语句处理 sr2

// The following line of code requires
// a MARS-enabled connection.
productReader = productCmd.ExecuteReader();
using (productReader)
{
  while (productReader.Read())
  {
    Console.WriteLine("  " +
      productReader["Name"].ToString());
  }
}