The error "There is already an open DataReader associated with this Command, which must be closed first" when using 2 separate commands

Question

I have this deprecated 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; } 

I created two separation commands to fix the problem, but it still continues: "There is already an open DataReader associated with this Command, which should be closed first."

Additional information: the same code works in another application.

Answer 1

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

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

Why redefine the same value over and over?

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

It would be enough.

Answer 2

Just add the following to the connection string:

 MultipleActiveResultSets=True; 

Answer 3

• The best solution may be to try to convert your solution into a form in which you do not need to open two readers at once. Ideally, this could be a single request. I do not have time for this.

• If your problem is so special that you really need to open more readers at the same time, and your requirements allow no earlier than the backend of the SQL Server 2005 database, then the magic word MARS (several active result sets) . http://msdn.microsoft.com/en-us/library/ms345109%28v=SQL.90%29.aspx . The solution for the associated Bob Vale section shows how to enable it: specify MultipleActiveResultSets=true in the connection string. I just say this as an interesting opportunity, but you should rather transform your decision.

  • to avoid the mentioned possibility of SQL injection, set the parameters to SQLCommand itself, and do not embed them in the query string. The query string should contain only references to the parameters that you pass to SqlCommand.

Answer 4

You can get this kind of problem when you are in two different commands on the same connection - especially when calling the second command in loop . This calls the second command for each record returned from the first command. If there are 10,000 entries returned by the first command, this problem will be more likely.

I used to avoid such a scenario by making it as one team. The first command returns all the necessary data and loads it into a DataTable.

Note: MARS may be a solution - but it can be risky, and many people do not like it.

Link

• What is "A severe error occurred in the current team. Results, if any, should be discarded." Azure SQL Error Means?
• Problems with Linq-To-Sql and MARS. A serious error has occurred in the current command. Results, if any, should be discarded.
• Integrated GROUP BY by DataTable

Answer 5

I bet the problem is shown on this line

 SqlDataReader dr3 = com2.ExecuteReader(); 

I suggest you execute the first reader and execute dr.Close(); and iterating historicos , with a different loop by executing 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

Try to combine the request, it will work much faster than completing an additional request in a line. Ik do not like the string [] that you use, I would create a class to store information.

  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; } 

Unconfirmed, but maybee gives some idea.

Answer 7

Add MultipleActiveResultSets=true to the provider part of your connection string. See the example below:

 <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" />