C# – Does connection close when command is disposed and the connection is defined directly on the command

cusingusing-statement

I know that a lot of examples exist where a SqlConnection is defined and then a SqlCommand is defined, both in Using blocks:

using (var conn = new SqlConnection(connString)) {
      using (var cmd = new SqlCommand()) {
        cmd.Connection = conn;
        //open the connection
      }
}

My question: If I define the connection directly on the SqlCommand, does the connection close when the command is disposed?

using (var cmd = new SqlCommand()) {
      cmd.Connection = new SqlConnection(connString);
      //open the connection
}

Best Answer

No, SqlCommand never attempts to close/dispose of the connection.

Related Solutions

C# closing sqlconnection and sqldatareader or not

You have to close connection before return. The best way to do it is USING block, because SqlConnection implements IDisposable interface. In that case you don't have to keep in mind that you have to close connection even if exception was thrown.

See the example below:

using (var conn = new SqlConnection(strconString))
{
    string cmdstr = 
        "select status from racpw where vtgid = " + vtgid;
    using (var cmdselect = new SqlCommand(cmdstr, conn))
    {
        conn.Open();
        using(var dtr = cmdselect.ExecuteReader())
        {
            if (dtr.Read())
            {
                return;
            }
            else
            {
                ...
            }
        }
    }
}

C# – Why must we define both == and != in C#

I can't speak for the language designers, but from what I can reason on, it seems like it was intentional, proper design decision.

Looking at this basic F# code, you can compile this into a working library. This is legal code for F#, and only overloads the equality operator, not the inequality:

module Module1

type Foo() =
    let mutable myInternalValue = 0
    member this.Prop
        with get () = myInternalValue
        and set (value) = myInternalValue <- value

    static member op_Equality (left : Foo, right : Foo) = left.Prop = right.Prop
    //static member op_Inequality (left : Foo, right : Foo) = left.Prop <> right.Prop

This does exactly what it looks like. It creates an equality comparer on == only, and checks to see if the internal values of the class are equal.

While you can't create a class like this in C#, you can use one that was compiled for .NET. It's obvious it will use our overloaded operator for == So, what does the runtime use for !=?

The C# EMCA standard has a whole bunch of rules (section 14.9) explaining how to determine which operator to use when evaluating equality. To put it overly-simplified and thus not perfectly accurate, if the types that are being compared are of the same type and there is an overloaded equality operator present, it will use that overload and not the standard reference equality operator inherited from Object. It is no surprise, then, that if only one of the operators is present, it will use the default reference equality operator, that all objects have, there is not an overload for it.¹

Knowing that this is the case, the real question is: Why was this designed in this way and why doesn't the compiler figure it out on its own? A lot people are saying this wasn't a design decision, but I like to think it was thought out this way, especially regarding the fact all objects have a default equality operator.

So, why doesn't the compiler automagically create the != operator? I can't know for sure unless someone from Microsoft confirms this, but this is what I can determine from reasoning on the facts.

To prevent unexpected behavior

Perhaps I want to do a value comparison on == to test equality. However, when it came to != I didn't care at all if the values were equal unless the reference was equal, because for my program to consider them equal, I only care if the references match. After all, this is actually outlined as default behavior of the C# (if both operators were not overloaded, as would be in case of some .net libraries written in another language). If the compiler was adding in code automatically, I could no longer rely on the compiler to output code that should is compliant. The compiler should not write hidden code that changes the behavior of yours, especially when the code you've written is within standards of both C# and the CLI.

In terms of it forcing you to overload it, instead of going to the default behavior, I can only firmly say that it is in the standard (EMCA-334 17.9.2)². The standard does not specify why. I believe this is due to the fact that C# borrows much behavior from C++. See below for more on this.

When you override `!=` and `==`, you do not have to return bool.

This is another likely reason. In C#, this function:

public static int operator ==(MyClass a, MyClass b) { return 0; }

is as valid as this one:

public static bool operator ==(MyClass a, MyClass b) { return true; }

If you're returning something other than bool, the compiler cannot automatically infer an opposite type. Furthermore, in the case where your operator does return bool, it just doesn't make sense for them create generate code that would only exist in that one specific case or, as I said above, code that hides the default behavior of the CLR.

C# borrows much from C++³

When C# was introduced, there was an article in MSDN magazine that wrote, talking about C#:

Many developers wish there was a language that was easy to write, read, and maintain like Visual Basic, but that still provided the power and flexibility of C++.

Yes the design goal for C# was to give nearly the same amount of power as C++, sacrificing only a little for conveniences like rigid type-safety and garbage-collection. C# was strongly modeled after C++.

You may not be surprised to learn that in C++, the equality operators do not have to return bool, as shown in this example program

Now, C++ does not directly require you to overload the complementary operator. If your compiled the code in the example program, you will see it runs with no errors. However, if you tried adding the line:

cout << (a != b);

you will get

compiler error C2678 (MSVC) : binary '!=' : no operator found which takes a left-hand operand of type 'Test' (or there is no acceptable conversion)`.

So, while C++ itself doesn't require you to overload in pairs, it will not let you use an equality operator that you haven't overloaded on a custom class. It's valid in .NET, because all objects have a default one; C++ does not.

^{1. As a side note, the C# standard still requires you to overload the pair of operators if you want to overload either one. This is a part of the standard and not simply the compiler. However, the same rules regarding the determination of which operator to call apply when you're accessing a .net library written in another language that doesn't have the same requirements.}

^{2. EMCA-334 (pdf) (http://www.ecma-international.org/publications/files/ECMA-ST/Ecma-334.pdf)}

^{3. And Java, but that's really not the point here}