Is my SqlCommand variable overworked? Did it get tired?

Question

In my testing project, I have a static class called FixtureSetup which I use to setup my integration testing data for validation.

I use the same SqlCommand and SqlParameter variable (not the object itself) within that class, repeatedly, using the same variable references over and over, assigning new SqlCommand and SqlParameter objects each time. My connection itself is created once and passed into the methods performing the setup, so each setup uses it's own distinct connection reference, and while the same conn is used multiple times, it's always in a linear sequence.

In one such method, I ran into a very odd situation, where my SqlCommand variable simply appears to have gotten tired.

        cmd = new SqlCommand("INSERT INTO Subscription (User_ID, Name, Active) VALUES (@User_ID, @Name, @Active)", conn);
        parameter = new SqlParameter("@User_ID", TestUserID); cmd.Parameters.Add(parameter);
        parameter = new SqlParameter("@Name", "TestSubscription"); cmd.Parameters.Add(parameter);
        parameter = new SqlParameter("@Active", true); cmd.Parameters.Add(parameter);
        cmd.ExecuteNonQuery();

        cmd = new SqlCommand("SELECT Subscription_ID FROM [Subscription] WHERE Name = 'TestSubscription'", conn);
        parameter = new SqlParameter("@User_ID", TestUserID);
        cmd.Parameters.Add(parameter);
        using (dr = cmd.ExecuteReader())
        {
            while (dr.Read())
            {
                TestSubscriptionID = dr.GetInt32(dr.GetOrdinal("Subscription_ID"));
            }
        }

        cmd = new SqlCommand("INSERT INTO SubscriptionCompany (Subscription_ID, Company_ID) VALUES (@Subscription_ID, @Company_ID)", conn);
        parameter = new SqlParameter("@Subscription_ID", TestSubscriptionID); cmd.Parameters.Add(parameter);
        parameter = new SqlParameter("@Company_ID", KnownCompanyId); cmd.Parameters.Add(parameter);
        cmd.ExecuteNonQuery();

In the above, at the last line shown, doing the same thing I've done quite literally in dozens of other places (insert data, read the ID column and capture it), I get the following:

SetUp : System.InvalidOperationException : ExecuteNonQuery requires an open and available Connection. The connection's current state is closed. at System.Data.SqlClient.SqlConnection.GetOpenConnection(String method)

BUT - replace cmd with new variable myCmd, and everything works swimmingly!

        SqlCommand myCmd;
        myCmd = new SqlCommand("INSERT INTO Subscription (User_ID, Name, Active) VALUES (@User_ID, @Name, @Active)", conn);
        parameter = new SqlParameter("@User_ID", TestUserID); myCmd.Parameters.Add(parameter);
        parameter = new SqlParameter("@Name", "TestSubscription"); myCmd.Parameters.Add(parameter);
        parameter = new SqlParameter("@Active", true); myCmd.Parameters.Add(parameter);
        myCmd.ExecuteNonQuery();

        myCmd = new SqlCommand("SELECT Subscription_ID FROM [Subscription] WHERE Name = 'TestSubscription'", conn);
        parameter = new SqlParameter("@User_ID", TestUserID);
        myCmd.Parameters.Add(parameter);
        using (dr = myCmd.ExecuteReader())
        {
            while (dr.Read())
            {
                TestSubscriptionID = dr.GetInt32(dr.GetOrdinal("Subscription_ID"));
            }
        }

        myCmd = new SqlCommand("INSERT INTO SubscriptionCompany (Subscription_ID, Company_ID) VALUES (@Subscription_ID, @Company_ID)", conn);
        parameter = new SqlParameter("@Subscription_ID", TestSubscriptionID); myCmd.Parameters.Add(parameter);
        parameter = new SqlParameter("@Company_ID", KnownCompanyId); myCmd.Parameters.Add(parameter);
        myCmd.ExecuteNonQuery();

What the heck is going on here? Did my command var just get tired???

What clued me to the "fix" was I noticed in my tracing that in my "read the id" block, my cmd.Parameters block had only ONE parameter in it, the 2nd one added, and when I forced the first cmd.Parameters.Add line to execute again, the number of parameters in the list dropped to 0. That's what prompted me to try a method level SqlCommand...cause I had the crazy idea that my cmd was tired... Imagine my shock when I apparently turned out to be right!

Edit: I'm not recycling any objects here - just the variable reference itself (static SqlCommand at the class level). My apologies for the earlier confusion in my wording of the question.

Answer 1

use one command per query and call dispose (or better yet, wrap in a using statement). you don't want to be "reusing" ado.net components.

Answer 2

Big Edit:

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

You must explicitly call the Close method when you are through using the SqlDataReader to use the associated SqlConnection for any other purpose.

The Close method fills in the values for output parameters, return values and RecordsAffected, increasing the time that it takes to close a SqlDataReader that was used to process a large or complex query. When the return values and the number of records affected by a query are not significant, the time that it takes to close the SqlDataReader can be reduced by calling the Cancel method of the associated SqlCommand object before calling the Close method.

so try:

using (dr = cmd.ExecuteReader())
{
    while (dr.Read())
    {
        TestSubscriptionID = dr.GetInt32(dr.GetOrdinal("Subscription_ID"));
    }
    dr.Close();
}

Answer 3

Check that you haven't set the DataReader to CommandBehavior.CloseConnection since you mentioned that you're re-using the connection for your test initialization.

Also, the DataReader does take resources, so utilize Dispose

Answer 4

Do you really need to do make a new connection object after each try?

myCmd = new SqlCommand(...)
//your code
myCmd = new SqlCommand(...)
//etc

You can just say:

myCmd.CommandText = "INSERT INTO SubscriptionCompany (Subscription_ID, Company_ID) VALUES (@Subscription_ID, @Company_ID)";

so that you may re-use your command object. Additionally you can reset your parameters as well after each call. Just call myCmd.Parameters.Clear().

Also, make sure you wrap your SqlCommand in a using statement so they will be properly cleaned up.

using (SqlCommand cmd = new SqlCommand())
{
    cmd.CommandText = "some proc"
    cmd.Connection = conn;
    //set params, get data, etc
    cmd.CommandText = "another proc"
    cmd.Parameters.Clear();
    //set params, get date, etc.
}