错误＆QUOT;已经有一个用此命令，必须先与QUOT关闭相关联的打开DataReader的;使用2个不同的命令时 [英] Error "There is already an open DataReader associated with this Command which must be closed first" when using 2 distinct commands

问题描述

我有这样的传统code：

I have this legacy code :

 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，必须先关闭

I have created two separates commands to correct the issue, but it still continues: "There is already an open DataReader associated with this Command which must be closed first".

另外一个信息：在同一code是另一个应用程序的工作。

An additional info: the same code is working in another app.

推荐答案

我建议创建了第二个命令一个额外的连接，将解决这个问题。尝试两种查询在一个查询相结合。创建计数的子查询。

I suggest creating an additional connection for the second command, would solve it. Try to combine both queries in one query. Create a subquery for the count.

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

为什么一次又一次地重写相同的值？

Why override the same value again and again?

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

就足够了。

这篇关于错误＆QUOT;已经有一个用此命令，必须先与QUOT关闭相关联的打开DataReader的;使用2个不同的命令时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！