"There is already an open DataReader associated with this Command which must be closed first" explained

"There is already an open DataReader associated with this Command which must be closed first" explained

  • Comments 12

Short one.

 

This sometimes catches people out.

When reading a SqlDataReader and then after the read is done, there is an attempt to use the connection that was used for the reading.

The exception thrown is (in this case it is thrown when trying to update a table using the same connection that was used for the reader):

 

System.InvalidOperationException: There is already an open DataReader associated with this Command which must be closed first.

   at System.Data.SqlClient.SqlInternalConnectionTds.ValidateConnectionForExecute(SqlCommand command)

   at System.Data.SqlClient.SqlCommand.ValidateCommand(String method, Boolean async)

   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)

   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()

 

Let’s do this with an example. First create a table and insert some rows:

 

create table SqlDataReaderTable (cid int identity, ctext nvarchar(20))

insert into SqlDataReaderTable values ('Row One')

insert into SqlDataReaderTable values ('Row Two')

--drop table SqlDataReaderTable

 

Then create a new C# console application:

 

            string cs = @"Data Source=<your server>;Integrated Security=SSPI;Initial Catalog=<your database>";

            string sql = "SELECT cid, ctext FROM SqlDataReaderTable";

            try

            {

                using (SqlConnection con = new SqlConnection(cs))

                {

                    con.Open();

                    SqlCommand cmd = new SqlCommand(sql, con);

                    SqlDataReader rdr = cmd.ExecuteReader();

                    while (rdr.Read())

                    {

                        Console.WriteLine("cid: {0}, ctext: {1}", rdr[0].ToString(), rdr[1].ToString());

                    }

                    // Now, trying to use the connection again will throw exception.

                    string update = "UPDATE SqlDataReaderTable SET ctext = 'New Value' WHERE cid = 1";

                    cmd.CommandText = update;

                    cmd.ExecuteNonQuery();

                    con.Close();

                }

            }

            catch (Exception ex)

            {

                Console.WriteLine(ex);

            }

        }

 

And run it, this will give the exception above.

 

So, what is happening? Most of the ADO.Net classes and design are disconnected. For example; setup your dataset, fill it and then close the connection. Now you can manipulate the data

and do whatever you like, then when you are done you simply commit the changes to the database, this will once again connect and commit and then disconnect.

 

However, the SqlDataReader (and the other DataReaders) is not disconnected. From:

".NET Framework Developer's Guide DataReaders (ADO.NET)"

http://msdn.microsoft.com/en-us/library/ms254509.aspx

 

 “You can use the ADO.NET DataReader to retrieve a read-only, forward-only stream of data from a database.

 Results are returned as the query executes, and are stored in the network buffer on the client until you request them using the Read method of the DataReader

 

This means that as long as the reader is open it has an associated and opened connection. It also means that as long as the reader is open then the connection is dedicated to the

command that is used for the reader. Simple as that. So all you have to do in order to avoid this exception is to remember to close the reader when done.

 

Either do it explicitly:

 

                    SqlCommand cmd = new SqlCommand(sql, con);

                    SqlDataReader rdr = cmd.ExecuteReader();

                    while (rdr.Read())

                    {

                        Console.WriteLine("cid: {0}, ctext: {1}", rdr[0].ToString(), rdr[1].ToString());

                    }

                    // Explicitly close the DataReader.

                    rdr.Close();

 

Or execute the whole thing in a Using statement (my recommendation):

 

                    SqlCommand cmd = new SqlCommand(sql, con);

                    using (SqlDataReader rdr = cmd.ExecuteReader())

                    {

                        while (rdr.Read())

                        {

                            Console.WriteLine("cid: {0}, ctext: {1}", rdr[0].ToString(), rdr[1].ToString());

                        }

                    }

 

Since the DataReader is a lightweight object that provides read-only, forward-only data, it is very fast (faster than for example DataAdapter.Fill()).

However, since it is read-only no updates can be done with a DataReader and since it is forward-only, a record that has been read can’t be returned to.

And as discussed above, it requires the exclusive use of an active connection as long as it is open.

 

".NET Framework Class LibrarySqlDataReader Class"

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader.aspx

".NET Framework Developer's GuideADO.NET

http://msdn.microsoft.com/en-us/library/e80y5yhx.aspx

Leave a Comment
  • Please add 1 and 6 and type the answer here:
  • Post
  • thanks Michael for this article.. :)

  • it was awesome explanation that I confronted with this problem a lot

    now i have to check that it occurs again or not. anyway, thank u so much

  • Thanks for the article.  The message is very misleading - it should say "There is already an open DataReader associated with this CONNECTION which must be closed first" instead of "There is already an open DataReader associated with this COMMAND which must be closed first"

  • @Virgel :  You're so right.  I created a new command with the existing connection and got this error.

  • thanks a lott it saved my life.. nice blog.... tahnks..

  • The two solutions still down account for a dependency on the DataReader, such as whether or not it returned rows. I for example need to execute one of two sql statements depending on whether or not my initial SqlDataReader returned, or did not return, rows. So I had to get arouund it this way, psuedo...

    using(Your connection)

    {

     SqlCommand sc = new SqlCommand("Select Statement");

     SqlDataReader dr = sqlCmd.ExecuteReader();

     if(dr.HasRows)

     {

       if(logic to determine if record should exist and if not....)

       {

         //This is Where my "Already open data connection" is solved

         dr.Close();      

         sqlCmd.CommandText = " Delete Record ";

         //Original location of "already open data reader" exception

         sqlCmd.ExecuteReader(CommandBehavior.CloseConnection)

       }

       else

       {

          ....

       }

     }

    }

    and like the author srecommends, one take comfort in knowing everything was cleaned up by the using{} stmt.

  • What Virgel said.  I'm trying to loop through a DataReader and use returned values in a totally different set of Command/DataReader objects (but the same Connection), and getting this error.  The error message indicates that it's a problem with the Command, but the reality is that the entire Connection is blocked by the open DataReader.  If the error message said so, I could have nailed down the issue a LOT faster.

  • Just a note regarding this error when using Linq.  I have had situations in which the DataContext using Linqpad (It happens with EntityFramework as well) still has a reader open because of a preceeding linq query.  When trying to use the data context later in code, this error will occur.  Robert Greiner has a nice post regarding this issue. (c.f. robertgreiner.com/.../entity-framework-open-datareader-associated-with-command)The solution is to force the linq query to execute and release the reader through use of one of the query operations like .ToList().

  • @dbleonard: Thanks a lot! I had the same problem with(\over) LinqPad :)

  • amazing explanation with example. thanks author

  • Interesting. Since the string "DataReader" appears nowhere in my code, I guess it must be some side-effect of the way I am trying to use LINQ somewhere, as some of the later comments here suggest. Hmm.

  • I need to iterate threw the reader and do an update for each entry. So, I can't close the reader before the update or can I.

    I see this below, will this do it?

    dr.Close();      

    sqlCmd.CommandText = " Delete Record ";

    //Original location of "already open data reader" exception

    sqlCmd.ExecuteReader(CommandBehavior.CloseConnection)

Page 1 of 1 (12 items)