使用 2 个不同的命令时出错"There is already an open DataReader associated with this Command which must be closed

Question

我有这个遗留代码：

 private void conecta()
 {  
     if (conexao.State == ConnectionState.Closed)
         conexao.Open();
 }
 public List<string[]> get_dados_historico_verificacao_email_WEB(string email)
 {
     List<string[]> historicos = new List<string[]>();
     conecta();
     sql = 
         @"SELECT * 
         FROM historico_verificacao_email 
         WHERE nm_email = '" + email + @"' 
         ORDER BY dt_verificacao_email DESC, hr_verificacao_email DESC";
     com = new SqlCommand(sql, conexao);
     SqlDataReader dr = com.ExecuteReader();
     if (dr.HasRows)
     {
         while (dr.Read())
         {
             string[] dados_historico = new string[6];
             dados_historico[0] = dr["nm_email"].ToString();
             dados_historico[1] = dr["dt_verificacao_email"].ToString();
             dados_historico[1] = dados_historico[1].Substring(0, 10);
             dados_historico[2] = dr["hr_verificacao_email"].ToString();
             dados_historico[3] = dr["ds_tipo_verificacao"].ToString();
             sql = 
                 @"SELECT COUNT(e.cd_historico_verificacao_email) QT 
                 FROM emails_lidos e 
                 WHERE e.cd_historico_verificacao_email = 
                     '" + dr["cd_historico_verificacao_email"].ToString() + "'";
             tipo_sql = "seleção";
             conecta();
             com2 = new SqlCommand(sql, conexao);
             SqlDataReader dr3 = com2.ExecuteReader();
             while (dr3.Read())
             {
                 //quantidade de emails lidos naquela verificação
                 dados_historico[4] = dr3["QT"].ToString(); 
             }
             dr3.Close();
             conexao.Close();
             //login
             dados_historico[5] = dr["cd_login_usuario"].ToString();
             historicos.Add(dados_historico);
         }
         dr.Close();
     }
     else
     { 
         dr.Close();
     }
     conexao.Close();
     return historicos;
 }

我创建了两个单独的命令来纠正这个问题，但它仍然继续："已经有一个打开的DataReader与此命令关联，必须首先关闭"。

附加信息：相同的代码在另一个应用程序中工作。

Answer 1

只需在连接字符串中添加以下内容：

MultipleActiveResultSets=True;

Answer 2

1. 最佳解决方案可能是尝试将您的解决方案转换为不需要一次打开两个阅读器的形式。理想情况下，它可以是单个查询。我现在没有时间这么做

2. 如果您的问题非常特殊，以至于您确实需要同时打开更多的读卡器，并且您的要求不允许SQL Server 2005数据库后端的版本，那么神奇的词是MARS（多个活动结果集）。http://msdn.microsoft.com/en-us/library/ms345109%28v=SQL.90%29.aspx.Bob Vale的链接主题的解决方案展示了如何启用它：在连接字符串中指定MultipleActiveResultSets=true。我只是说这是一种有趣的可能性，但你应该改变你的解决方案。

   • 为了避免上述SQL注入的可能性，请将参数设置为SQLCommand本身，而不是将它们嵌入到查询字符串中。查询字符串应该只包含对传递到SqlCommand中的参数的引用

Answer 3

当您在同一连接上使用two different commands时，可能会遇到这样的问题，尤其是在loop中调用第二个命令。这就是为从第一个命令返回的每个记录调用第二个命令。如果第一个命令返回了大约10000条记录，则更可能出现此问题。

我过去常常把它作为一个命令来避免这种情况。。第一个命令返回所有需要的数据，并将其加载到DataTable中。

注意：MARS可能是一个解决方案，但它可能有风险，很多人不喜欢它

参考

1. 什么是"；当前命令出现严重错误。如果有结果，则应丢弃"SQL Azure错误意味着什么
2. Linq To Sql和MARS问题-当前命令出现严重错误。如果有结果，则应丢弃
3. DataTable上的复杂GROUP BY

Answer 4

我建议为第二个命令创建一个额外的连接，可以解决这个问题。尝试将两个查询组合在一个查询中。为计数创建子查询。

while (dr3.Read())
{
    dados_historico[4] = dr3["QT"].ToString(); //quantidade de emails lidos naquela verificação
}

为什么要一次又一次地覆盖相同的值？

if (dr3.Read())
{
    dados_historico[4] = dr3["QT"].ToString(); //quantidade de emails lidos naquela verificação
}

就足够了。

Answer 5

我敢打赌问题显示在这行中

SqlDataReader dr3 = com2.ExecuteReader();

我建议您执行第一个读取器，执行dr.Close();和迭代historicos，并使用另一个循环执行com2.ExecuteReader()。

public List<string[]> get_dados_historico_verificacao_email_WEB(string email)
    {
        List<string[]> historicos = new List<string[]>();
        conecta();
        sql = "SELECT * FROM historico_verificacao_email WHERE nm_email = '" + email + "' ORDER BY  dt_verificacao_email DESC, hr_verificacao_email DESC"; 
        com = new SqlCommand(sql, conexao);
        SqlDataReader dr = com.ExecuteReader();
        if (dr.HasRows)
        {
            while (dr.Read())
            {
                string[] dados_historico = new string[6];
                dados_historico[0] = dr["nm_email"].ToString();
                dados_historico[1] = dr["dt_verificacao_email"].ToString();
                dados_historico[1] = dados_historico[1].Substring(0, 10);
                //System.Windows.Forms.MessageBox.Show(dados_historico[1]);
                dados_historico[2] = dr["hr_verificacao_email"].ToString();
                dados_historico[3] = dr["ds_tipo_verificacao"].ToString();
                dados_historico[5] = dr["cd_login_usuario"].ToString();
                historicos.Add(dados_historico);
            }
            dr.Close();
            sql = "SELECT COUNT(e.cd_historico_verificacao_email) QT FROM emails_lidos e WHERE e.cd_historico_verificacao_email = '" + dr["cd_historico_verificacao_email"].ToString() + "'";
            tipo_sql = "seleção";
            com2 = new SqlCommand(sql, conexao);
            for(int i = 0 ; i < historicos.Count() ; i++)
            {
                SqlDataReader dr3 = com2.ExecuteReader();
                while (dr3.Read())
                {
                    historicos[i][4] = dr3["QT"].ToString(); //quantidade de emails lidos naquela verificação
                }
                dr3.Close();
            }
        }
        return historicos;

Answer 6

将MultipleActiveResultSets=true添加到连接字符串的提供者部分。参见以下示例：

<add name="DbContext" connectionString="Data Source=(LocalDb)v11.0;Initial Catalog=dbName;Persist Security Info=True;User ID=userName;Password=password;MultipleActiveResultSets=True" providerName="System.Data.SqlClient" />

Answer 7

尝试组合查询，它的运行速度将比每行执行一个额外的查询快得多。我不喜欢你使用的字符串[]，我会创建一个类来保存信息。

    public List<string[]> get_dados_historico_verificacao_email_WEB(string email)
    {
        List<string[]> historicos = new List<string[]>();
        using (SqlConnection conexao = new SqlConnection("ConnectionString"))
        {
            string sql =
                @"SELECT    *, 
                            (   SELECT      COUNT(e.cd_historico_verificacao_email) 
                                FROM        emails_lidos e 
                                WHERE       e.cd_historico_verificacao_email = a.nm_email ) QT
                  FROM      historico_verificacao_email a
                  WHERE     nm_email = @email
                  ORDER BY  dt_verificacao_email DESC, 
                            hr_verificacao_email DESC";
            using (SqlCommand com = new SqlCommand(sql, conexao))
            {
                com.Parameters.Add("email", SqlDbType.VarChar).Value = email;
                SqlDataReader dr = com.ExecuteReader();
                while (dr.Read())
                {
                    string[] dados_historico = new string[6];
                    dados_historico[0] = dr["nm_email"].ToString();
                    dados_historico[1] = dr["dt_verificacao_email"].ToString();
                    dados_historico[1] = dados_historico[1].Substring(0, 10);
                    //System.Windows.Forms.MessageBox.Show(dados_historico[1]);
                    dados_historico[2] = dr["hr_verificacao_email"].ToString();
                    dados_historico[3] = dr["ds_tipo_verificacao"].ToString();
                    dados_historico[4] = dr["QT"].ToString();
                    dados_historico[5] = dr["cd_login_usuario"].ToString();
                    historicos.Add(dados_historico);
                }
            }
        }
        return historicos;
    }

未经测试，但maybee给出了一些想法。