SqlConnection and SqlCommand with using statement

Question

I have a connection to a database set up like this to call a stored procedure. I am just wondering if this is the best way to do this. I have two using statements one for the sqlConnection and one for the sqlCommand (which I am not really sure if its needed)

using (SqlConnection con1 = new SqlConnection(conString1))
{
    using (SqlCommand cmd1 = new SqlCommand())
    {
        cmd1.Connection = con1;

        cmd1.CommandType = CommandType.StoredProcedure;
        cmd1.CommandText = "updateVendorEstNo";
        cmd1.Parameters.AddWithValue("@plantNameNew", vPlantName.Value.ToString().Trim());

        var result = cmd1.Parameters.Add("@result", SqlDbType.Int);
        result.Direction = ParameterDirection.Output;
        var resultDesc = cmd1.Parameters.Add("@resultDesc", SqlDbType.VarChar, 100);
        resultDesc.Direction = ParameterDirection.Output;

        con1.Open(); // open connection
        cmd1.ExecuteNonQuery();

        res = result.Value.ToString().Trim();
        resDesc = resultDesc.Value.ToString().Trim();
    }                
}

My biggest question is when I am doing :

using (SqlCommand cmd1 = new SqlCommand())

Is it fine the way it is done right now.. or should it be more like,

using (SqlCommand cmd1 = new SqlCommand("updateVendorEstNo",con1))

Either way will work - which you'd know if you tested it. Just pick the one you like best. And yes, you should almost always wrap objects that implement IDisposable in a using statement. — mason, Aug 01 '17 at 15:26
There shouldn't be any impact on the `using` statement. It will continue to work in both cases. The only difference is the constructor calls, There shouldn't be any impact. — Habib, Aug 01 '17 at 15:26
I did test it. both ways worked.. but i wasn't if one was better performance wise . — psj01, Aug 01 '17 at 15:30
The only real difference is readability. The `SqlCommand` is contained in the `using` block either way. If you were running multiple `SqlCommand`s, I would leave the command text out of the `using` block statement for clarity, but if only one command is being run, I tend to include it in the `using` line. I think it's largely a question of personal preference. — Alex, Aug 01 '17 at 15:30
i wasn't asking the question for style tips.. i just wasn't sure if there is any advantage using one vs. the other.. sorry if the question wasn't clear enough... — psj01, Aug 01 '17 at 15:31
It means run some tests to determine which is faster. There are various libraries that can make this easier and handle some of the nuances, such as [BenchmarkDotNet](https://github.com/dotnet/BenchmarkDotNet). But, if you tested both ways and found out they worked, and you're not noticing any overt performance problems, then there's no need to worry about it. Worrying about performance before you have a problem is called pre-optimizing. And pre-optimizing is bad. Why waste time optimizing this code if it's not an issue? Surely other issues would be a better use of your time. — mason, Aug 01 '17 at 15:34
@mason sorry I am still kind of new at this. Learning as I go. Thanks for the tips. — psj01, Aug 01 '17 at 15:37

score 3 · Accepted Answer · edited Aug 02 '17 at 17:03

3

I think that the way you have it is fine, because the using statement will ensure that the object is disposed of either way.

edited Aug 02 '17 at 17:03

Cody Gray - on strike

239,200
50
490
574

answered Aug 01 '17 at 15:26

Scath

3,777
10
29
40

SqlConnection and SqlCommand with using statement

1 Answers1