Is my SqlCommand variable overloaded? Is he tired?

In my testing project, I have a static class called FixtureSetup that I use to set up my integration testing data for validation.

I am using the same SqlCommand and SqlParameter variable (not the object itself) inside this 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 to the methods doing the setup, so each setup uses its own separate connection reference, and although the same pin is used multiple times, it is always in a linear sequence.

In one of these methods, I ran into a very strange situation where my SqlCommand variable just got 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 example above, the last line shows that I have done the same thing that I have done literally in dozens of other places (insert data, read identity columns and grab them), I get this:

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

BUT - replace cmd with the new variable myCmd and everything works smoothly!

        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 hell is going on here? Is my var command just tired ???

What prompted me to "fix" I noticed in my trace that in my "read id" block my cmd.Parameters block had only one parameter, the second was added and when I forced cmd.Parameters. parameters in the list dropped to 0. This prompted me to try the SqlCommand method level ... because I had a crazy idea that my cmd was tired ... Imagine mine when I was apparently right!

Edit: I am not recycling any objects here - just a variable reference (static SqlCommand at the class level). We apologize for the early confusion in my wording of the question.

+3


source to share


4 answers


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 use the SqlDataReader to use the associated SqlConnection for any other Target.

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



try:

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

      

+2


source


use one command per request and remove the call (or better yet, wrap in a using statement). you don't want to "reuse" ado.net components.



+3


source


Make sure you don't set DataReader

in CommandBehavior.CloseConnection

, as you mentioned that you are reusing the connection for your test initialization.

It also DataReader

uses resources, so use the utility

+2


source


Do you really need to make a new connection object after every 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 you can reuse your command object. Also, after each call, you can reset your parameters. Just call myCmd.Parameters.Clear()

.

Also, make sure you wrap SqlCommand

in the instructions using

so they are properly cleaned.

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.
}

      

0


source







All Articles