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

Question

I'm working on application which needs to connect to another database to get some data, and to do that, I decided to use SqlConnection, reader etc..

And I need to execute few queries, for example first I need to get CARD ID for some user, after that I need to get some data by that CARD ID..

Here is my code:

#region Connection to another Database

SqlConnection sqlConnection1 = new SqlConnection("Data Source=ComputerOne; Initial Catalog=TestDatabase;Integrated Security=False; User ID=test; Password=test123;");
SqlCommand cmd = new SqlCommand();
SqlDataReader reader;

cmd.CommandText = "Select * From Users Where CardID=" + "'" + user.CardID + "'";
cmd.CommandType = CommandType.Text;
cmd.Connection = sqlConnection1;

sqlConnection1.Open();

reader = cmd.ExecuteReader();

string cardID = "";
string quantity="";

while (reader.Read())
{
    cardID = reader["CardID"].ToString();
}
//HOW COULD I WRITE ANOTHER QUERY NOW, FOR EXAMPLE, OK I GOT CARDID NOW GIVE ME SOME OTHER THINGS FROM THAT DATABASE BY THAT cardID
//here I tried to change CommandText and to keep working with reader.. but its not working like this because its throwing me exception mention in question title.

cmd.CommandText = "Select T1.CardID, T2.Title, Sum(T1.Quantity) as Quantity From CardTransactions as T1 JOIN Adds as T2 ON T1.AddsID = T2.AddsID Where T1.CardID =" + cardID + "AND T1.Type = 1 Group By T1.CardID, T2.Title";

reader = cmd.ExecuteReader();

while (reader.Read())
{
    quantity = reader["Quantity"].ToString();
}

// Data is accessible through the DataReader object here.

sqlConnection1.Close();

#endregion

So guys how could I execute few queries statemens using this example.

Thanks a lot! Cheers

Possible duplicate of [There is already an open DataReader associated with this Command which must be closed first](http://stackoverflow.com/questions/6062192/there-is-already-an-open-datareader-associated-with-this-command-which-must-be-c) — Ian R. O'Brien, Feb 24 '17 at 14:57
@IanR.O'Brien That one was about entity and this one is not. — Roxy'Pro, Feb 24 '17 at 15:22

NicoRiff · Accepted Answer · 2017-02-24T13:23:49.993

Your problem is that you are not disposing the objects you are using. For that purpose is better to always use using structure, since it will guarantee you that everithing is gonna be disposed. Try the code below:

SqlConnection sqlConnection1 = new SqlConnection("Data Source=ComputerOne; Initial Catalog=TestDatabase;Integrated Security=False; User ID=test; Password=test123;");
SqlCommand cmd = new SqlCommand();
SqlDataReader reader;
string cardID = "";
string quantity="";

using(sqlConnection1 = new SqlConnection("Data Source=ComputerOne; Initial Catalog=TestDatabase;Integrated Security=False; User ID=test; Password=test123;"))
{
    sqlConnection1.Open();

    using(cmd = new SqlCommand())
    {
        cmd.CommandText = "Select * From Users Where CardID=" + "'" + user.CardID + "'";
        cmd.CommandType = CommandType.Text;
        cmd.Connection = sqlConnection1;

        using(reader = cmd.ExecuteReader())
        {


            while (reader.Read())
            {
                cardID = reader["CardID"].ToString();
            }
        } //reader gets disposed right here
    } //cmd gets disposed right here

    using(cmd = new SqlCommand())
    {
        cmd.CommandText = "Select T1.CardID, T2.Title, Sum(T1.Quantity) as Quantity From CardTransactions as T1 JOIN Adds as T2 ON T1.AddsID = T2.AddsID Where T1.CardID =" + cardID + "AND T1.Type = 1 Group By T1.CardID, T2.Title";
        cmd.CommandType = CommandType.Text;
        cmd.Connection = sqlConnection1;

        using(reader = cmd.ExecuteReader())
        {
            while (reader.Read())
            {
                quantity = reader["Quantity"].ToString();
            }
        } //reader gets disposed right here
    } //cmd gets disposed right here
    sqlConnection1.Close();
} //sqlConnection1 gets disposed right here

can you explain me now little bit why did you put everything in using, I understand that you did for new SqlConnection to ensure one connection is opened at a time, but why for everything else? for readers also etc, couldn't I just finish with one reader, that reader.Close(); after that write new cmd.CommandText="Select * from Users"; reader=cmd.ExecuteReader(); and so on .. close reader - > write new cmd text -> add result to reader .. BUT THANKS FOR ANSWER ANYWAY! — Roxy'Pro, Feb 24 '17 at 13:17
@Roxy'Pro Basically, What was done is to use SqlConnection, SqlCommand and SqlDataReader inside a using statement. When that is used, the objects are disposed right after the block has finished. So you can reuse them later, I will edit the answer so you can see it better. Is like you call Dispose() everytime you use the objects (that would also solve this problem, but 'using' is the right way to go) — NicoRiff, Feb 24 '17 at 13:22
@NicoRiff One question is which objects in general I should put in using statements, I mean which kind of objects should be disposed after block is finished, like how could I know why something should be in using statement? — , Feb 27 '17 at 08:02
@NicoRiff One question is which objects in general I should put in using statements, I mean which kind of objects should be disposed after block is finished, like how could I know why something should be in using statement? — , Feb 27 '17 at 08:15

score 3 · Answer 2 · answered Feb 24 '17 at 12:52

3

The reader you opened is still active and open. And you can have just one active reader at a time. You should wrap all Sql... instances in a using to ensure they get closed properly.

using (SqlConnection connection = new SqlConnection(...))
{
    using (SqlDataReader reader = cmd.ExecuteReader())
    {
        // the code using reader
    }
}

answered Feb 24 '17 at 12:52

Patrick Hofman

153,850
22
249
325

so how could I execute 2 or more queries and read them with same reader.. ? – Roxy'Pro Feb 24 '17 at 12:54
When using the above code you can, but never 2 at the same time. You close the first and then continue reading the second. – Patrick Hofman Feb 24 '17 at 12:55

Ionut Ungureanu · Answer 3 · 2017-02-24T13:34:12.703

-1

well ... you receive the error because the used reader for the first call was not closed. You should always call the Close method when you have finished using the DataReader object insuring that the connection used by the reader is returned to the connection pool (the connection is in use exclusively by that DataReader). Partial code:

reader = cmd.ExecuteReader();
try
{
  while(myReader.Read()) 
  {
    while (reader.Read())
    {
        cardID = reader["CardID"].ToString();
    }
}
finally
{
  myReader.Close();
}
...
reader = cmd.ExecuteReader();
try
{
  while(myReader.Read()) 
  {
        reader = cmd.ExecuteReader();

        while (reader.Read())
        {
            quantity = reader["Quantity"].ToString();
        }
  }
}
finally
{
  myReader.Close();
  myConnection.Close();
}

Also... as a clean code rule, separate your calls in different methods (SOLID principles)

edited Feb 24 '17 at 13:34

answered Feb 24 '17 at 12:56

Ionut Ungureanu

1,020
1
13
16

1

No, advising that is dangerous. In case of an exception the data reader will remain open if that line doesn't get executed. – Patrick Hofman Feb 24 '17 at 12:57
4

"You should always call the Close method when you have finished using the DataReader object. If your Command contains output parameters or return values, they will not be available until the DataReader is closed. Note that while a DataReader is open, the Connection is in use exclusively by that DataReader. You cannot execute any commands for the Connection, including creating another DataReader, until the original DataReader is closed." (taken from https://msdn.microsoft.com/en-us/library/haa3afyz(v=vs.110).aspx ) – Ionut Ungureanu Feb 24 '17 at 13:00
Plus... in case of an exception, one could make good use of the try-catch mechanism – Ionut Ungureanu Feb 24 '17 at 13:01
Then explain that in your answer. – Patrick Hofman Feb 24 '17 at 13:02
@PatrickHofman your answer beter than.. but lonut right too. if the question was "how to garanti that reader closing..", you are ok.. hi correctly interpreted the error. – levent Feb 24 '17 at 13:03
1

I disagree, giving half an answer that is potentially dangerous isn't a solution. @levent – Patrick Hofman Feb 24 '17 at 13:14

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

3 Answers3

Related