Error There is already an open DataReader associated with this Command which must be closed first when using 2 distinct commands

Question

I have this legacy code     private void conecta             if  conexao State    ConnectionState Closed           conexao Open         public List lt string   gt  get dados historico verificacao email WEB string email          List lt string   gt  historicos   new List lt string   gt          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 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    An additional info  the same code is working in another app

User · Answer

Just add the following in your connection string   MultipleActiveResultSets True

User · Answer

You can get such a problem when you are two different commands on same connection - especially calling the second command in a loop  That is calling the second command for each record returned from the first command  If there are some 10 000 records returned by the first command  this issue will be more likely   I used to avoid such a scenario by making it as a single command   The first command returns all the required data and load it into a DataTable    Note  MARS may be a solution - but it can be risky and many people dislike it   Reference   What does  quot A severe error occurred on the current command  The results  if any  should be discarded  quot  SQL Azure error mean  Linq-To-Sql and MARS woes - A severe error occurred on the current command  The results  if any  should be discarded Complex GROUP BY on DataTable

User · Answer

Add MultipleActiveResultSets true to the provider part of your connection string  See the example below    lt 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    gt

User · Answer

The optimal solution could be to try to transform your solution into a form where you don t need to have two readers open at a time  Ideally it could be a single query  I don t have time to do that now  If your problem is so special that you really need to have more readers open simultaneously  and your requirements allow not older than SQL Server 2005 DB backend  then the magic word is MARS  Multiple Active Result Sets   http   msdn microsoft com en-us library ms345109 28v SQL 90 29 aspx  Bob Vale s linked topic s solution shows how to enable it  specify MultipleActiveResultSets true in your connection string  I just tell this as an interesting possibility  but you should rather transform your solution    in order to avoid the mentioned SQL injection possibility  set the parameters to the SQLCommand itself instead of embedding them into the query string  The query string should only contain the references to the parameters what you pass into the SqlCommand

User · Answer

I bet the problem is being shown in this line  SqlDataReader dr3   com2 ExecuteReader      I suggest that you execute the first reader and do a dr Close    and the iterate historicos  with another loop  performing the com2 ExecuteReader     public List lt string   gt  get dados historico verificacao email WEB string email                 List lt string   gt  historicos   new List lt string   gt             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  lt  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

User · Answer

Try to combine the query  it will run much faster than executing an additional query per row  Ik don t like the string   you re using  i would create a class for holding the information       public List lt string   gt  get dados historico verificacao email WEB string email                List lt string   gt  historicos   new List lt string   gt              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          Untested  but maybee gives some idea

User · Answer

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     Would be enough

[c#] Error "There is already an open DataReader associated with this Command which must be closed first" when using 2 distinct commands

Examples related to c#