using the same instance of SQLCommand more than one time in the same code for more than one query?

Question

I have question about using why i can not use the same instance of SQLCommand more than one time in the same code?

I tried the code down here and it runs good for the gridview but when i changed the query by using cmd.CommandText() method it keeps saying:

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

This is the code:

string cs = ConfigurationManager.ConnectionStrings["MyDB"].ConnectionString;
SqlConnection con = new SqlConnection(cs);

try
{
    SqlCommand cmd = new SqlCommand();
    cmd.Connection = con;
    con.Open();
    cmd.CommandText = "Select top 10 FirstName, LastName, Address, City, State from Customers";

    GridView1.DataSource = cmd.ExecuteReader(); 
    GridView1.DataBind();


    cmd.CommandText = "SELECT TOP 10 COUNT(CreditLimit) FROM Customers";
    int total = (int)cmd.ExecuteScalar();
    TotalCreditLble.Text = "The total Credit :" + total.ToString();

}
catch(Exception exp)
{
    Response.Write(exp.Message);
}
finally
{
    con.Close();
}

score 5 · Answer 1 · answered Nov 14 '13 at 18:32

The problem is that you are using the SqlCommand object to generate a DataReader via the command.ExecuteReader() command. While that is open, you can't re-use the command.

This should work:

using (var reader = cmd.ExecuteReader())
{
    GridView1.DataSource = reader;
    GridView1.DataBind();
}
//now the DataReader is closed/disposed and can re-use command
cmd.CommandText = "SELECT TOP 10 COUNT(CreditLimit) FROM Customers";
int total = (int)cmd.ExecuteScalar();
TotalCreditLble.Text = "The total Credit :" + total.ToString();

Mike Perrenoud · Answer 2 · 2013-11-14T18:38:51.540

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

This is the very reason you don't share a command. Somewhere in your code you did this:

cmd.ExecuteReader();

but you didn't leverage the using statement around the command because you wanted to share it. You can't do that. See, ExecuteReader leaves a connection to the server open while you read one row at a time; however that command is locked now because it's stateful at this point. The proper approach, always, is this:

using (SqlConnection c = new SqlConnection(cString))
{
    using (SqlCommand cmd = new SqlCommand(sql, c))
    {
        // inside of here you can use ExecuteReader
        using (SqlDataReader rdr = cmd.ExecuteReader())
        {
            // use the reader
        }
    }
}

These are unmanaged resources and need to be handled with care. That's why wrapping them with the using is imperative.

Do not share these objects. Build them, open them, use them, and dispose them.

By leveraging the using you will never have to worry about getting these objects closed and disposed.

Your code, written a little differently:

var cs = ConfigurationManager.ConnectionStrings["MyDB"].ConnectionString;
var gridSql = "Select top 10 FirstName, LastName, Address, City, State from Customers";
var cntSql = "SELECT TOP 10 COUNT(CreditLimit) FROM Customers";

using (SqlConnection con = new SqlConnection(cs))
{
    con.Open();

    try
    {
        using (SqlCommand cmd = new SqlCommand(gridSql, con))
        {
            GridView1.DataSource = cmd.ExecuteReader(); 
            GridView1.DataBind();
        }

        using (SqlCommand cmd = new SqlCommand(cntSql, con))
        {
            int total = (int)cmd.ExecuteScalar();
            TotalCreditLble.Text = "The total Credit :" + total.ToString();
        }
    }
    catch(Exception exp)
    {
        Response.Write(exp.Message);
    }
}

I agree with your sentiment about not re-using commands/connections as it can only get you in trouble. — Sven Grosen, Nov 14 '13 at 18:34

score 0 · Answer 3 · answered Nov 14 '13 at 20:02

Thank u quys but for the guys who where talking about using block ! why this code work fine which i seen it on example on a video ! It's the same thing using the same instance of SqlCommand and passing diffrent queries by using the method CommanText with the same instance of SqlCommand and it's execute just fine , this is the code :

using (SqlConnection con = new SqlConnection(cs))
{
   SqlCommand cmd = new SqlCommand();
   cmd.Connection = con;
   con.Open();

   cmd.CommandText = "Delete from tbleProduct where ProductID= 4";
   int TotalRowsAffected = cmd.ExecuteNonQuery();
   Response.Write("Total rows affected :" + TotalRowsAffected );

   cmd.CommandText = "Insert into tbleProduct values (4, 'Calculator', 100, 230)";
   TotalRowsAffected = cmd.ExecuteNonQuery();
   Response.Write("Total rows affected :" + TotalRowsAffected );


   cmd.CommandText = "ypdate tbleProduct set QtyAvailbe = 234 where ProductID = 2";
   TotalRowsAffected = cmd.ExecuteNonQuery();
   Response.Write("Total rows affected :" + TotalRowsAffected );

 }

using the same instance of SQLCommand more than one time in the same code for more than one query?

3 Answers3