Can an Exception cause a Sqlconnection to close?

Question

I’m building the treatment of a connection and I came up with this question in mind, yet I couldn’t find a satisfactory answer. So I came here to ask the most experienced programmers.

Is there any possibility when a Exception a SqlConnection already open be closed because of this Exception?

Illustrative code:

SqlConnection con = new SqlConnection(connectionString);

        try
        {
            con.Open();

            //algum código aqui....
        }
        catch (Exception)
        {
            //possibilidade de fechamento da conexão? .....
            throw;
        }

Answer 1

Although the ideal is not to capture Exception by being too generic and throwing the exception again, is not the most appropriate thing to do, yes, it is possible to put the closure of the connection there. It’s just probably not what you want. You want the connection to always be closed, right? If all goes well or an exception is made, the connection must be closed at the end of the process. The language has a standard that ensures this much more simply and effectively:

using (var con = new SqlConnection(connectionString)) {
    con.Open();
    //algum código aqui....
}

Okay, you’re ensuring closure, you don’t need anything else. The using will produce a try-finally calling the dispose() which will be responsible for closing the connection. Ideally do not try to do in hand, this is the correct form and without risks.

The connection may hang if you do not do so. This applies to any external resource that is acquired. Every time you "open" something needs to ensure that it will be closed the lock is not automatic. This pattern helps achieve this goal in the right way.

• See more in another question on the subject.
• More details.
• An example in case that the using should be replaced by try-finally (see the alternative in the answer).

In C# 9 can be:

using var con = new SqlConnection(connectionString);
con.Open();
//algum código aqui....

I put in the Github for future reference.

Answer 2

You are creating the variable within the try so it is not visible outside of it, I will put as it would be to use the variable outside because this holds for any variable, not only for the case of connection:

SqlConnection con
 try
        {
            con = new SqlConnection(connectionString);

            con.Open();

            //algum código aqui....
        }
        catch (Exception)
        {
            con.Close()
            throw;
        }

There are other ways to use it, even better ways to treat it, the most appropriate would be something like this: (below code taken from that link)

using (SqlConnection con = new SqlConnection(connectionString))
    {
        //
        // Open the SqlConnection.
        //
        con.Open();
        //
        // The following code uses an SqlCommand based on the SqlConnection.
        //
        using (SqlCommand command = new SqlCommand("SELECT TOP 2 * FROM Dogs1", con))
        using (SqlDataReader reader = command.ExecuteReader())
        {
        while (reader.Read())
        {
            Console.WriteLine("{0} {1} {2}",
            reader.GetInt32(0), reader.GetString(1), reader.GetString(2));
        }
        }
    }