Partager via

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

  • Article

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

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

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

".NET Framework Developer's GuideADO.NET

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

Comments

  • Anonymous
    July 09, 2012
    thanks Michael for this article.. :)

  • Anonymous
    October 09, 2012
    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

  • Anonymous
    November 02, 2012
    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"

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

  • Anonymous
    December 09, 2012
    thanks a lott it saved my life.. nice blog.... tahnks..

  • Anonymous
    January 25, 2013
    The comment has been removed

  • Anonymous
    July 08, 2013
    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.

  • Anonymous
    August 05, 2013
    The comment has been removed

  • Anonymous
    September 27, 2013
    @dbleonard: Thanks a lot! I had the same problem with(over) LinqPad :)

  • Anonymous
    October 30, 2013
    amazing explanation with example. thanks author

  • Anonymous
    November 05, 2013
    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.

  • Anonymous
    January 14, 2014
    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)

  • Anonymous
    March 03, 2015
    Please provide your code snippet with in the threading.Monitor class, like the below Object lockedObj = new Object(); Monitor.Enter(lockedObj); //My code snippet Monitor.Exit(lockedObj); This will definitely fix your problem.

  • Anonymous
    August 26, 2015
    FOR THIS ERROR:(There is already an open DataReader associated with this Command which must be closed first.)I think it may help u This can be easily solved by allowing MARS in your connection string. Add MultipleActiveResultSets=true to the provider part of your connection string (where Data Source, Initial Catalog, etc. are specified)

  • Anonymous
    February 16, 2016
    Best Solution :- There is only problem with your "CommandText" value. Let it be SP or normal Sql Query. Check-1: The parameter value which you are passing in your Sql Query is not changing and going same again and again in your ExecuteReader. Check-2: Sql Query string is wrongly formed. Check-3: Please create simplest code as follows. string ID = "C8CA7EE2"; string myQuery = "select * from ContactBase where contactid=" + "'" + ID + "'"; string connectionString = ConfigurationManager.ConnectionStrings["CRM_SQL_CONN_UAT"].ToString(); SqlConnection con = new SqlConnection(connectionString); con.Open(); SqlCommand cmd = new SqlCommand(myQuery, con); DataTable dt = new DataTable(); dt.Load(cmd.ExecuteReader()); con.Close();

  • Anonymous
    February 22, 2016
    I have used the entire SQLite things in Using block. Still i get the following exception System.InvalidOperationException: No connection associated with this command   at System.Data.SQLite.SQLiteCommand.InitializeForReader()   at System.Data.SQLite.SQLiteCommand.ExecuteReader(CommandBehavior behavior)   at System.Data.SQLite.SQLiteCommand.ExecuteReader() Please check the below code, how i have implemented the SQLite things. public SQLiteConnection GetLocalConnection() {    string dbPath = Path.Combine(IndexDirectoryPath, "Index.s3db");    string connectionString = "Data Source=" + dbPath + ";version=3";    SQLiteConnection connection = null;    if (File.Exists(dbPath))    {      try        {          connection = new SQLiteConnection(connectionString);          connection.Open();         }         catch (Exception)         {            throw;         }     }  return connection; } using (SQLiteConnection connection = GetLocalConnection()) {  using (SQLiteCommand command = new SQLiteCommand(connection))  {    command.CommandText = "SELECT * FROM Table1";    using (SQLiteDataReader reader = command.ExecuteReader())    {    }  } }