Making sure a connection is closed

Question

I wanted to ask what is the common way of using database connections and closing them.

This is my program, but I see in an exeption, the connection.Close() will not execute.

Should i use a try-catch for the whole block? because for some reason i see most people doesnt.

using (SqlConnection connection = new SqlConnection(ConnectionString))
        {
            using (SqlCommand command = new SqlCommand())
            {
                command.CommandText = "procedure";

                command.Connection = connection;

                command.CommandType = CommandType.StoredProcedure;

                tmpParameter = DBUtils.createInSQLParam("@ImageID", SqlDbType.SmallInt, htmlImageId);
                command.Parameters.Add(tmpParameter);

                command.Connection.Open();

                using (SqlDataReader reader = command.ExecuteReader())
                {
                    htmlImageDetails = GetHtmlImageDetailsFromReader(reader, true, out newId);

                    reader.Close();
                }

                connection.Close();

                return htmlImageDetails;
            }
        }

This seems to answer your question - http://stackoverflow.com/a/4717802/978528. — nickfinity, Dec 10 '13 at 14:35
The close is not needed when using "using" the connections/readers are automatically closed. .net uses connection pooling by default so a connection is not closed if you dispose it it is put in the pool for reuse (this improves the performance in most cases). — Peter, Dec 10 '13 at 14:36
Within the using block, the object is read-only and cannot be modified or reassigned. So, maybe when you close() that is considered as modification of the object because close() is implicit after the closing } of the using block thus the error. — Edper, Dec 10 '13 at 14:42
Just to clarify what @Petoj said the connection is not closed when you call `Close()` either, it just returns the connection to the pool till it times out and then closes for real. (unless you told it to not use connection pooling in the connection string when you created the connection) — Scott Chamberlain, Dec 10 '13 at 15:10

score 7 · Accepted Answer · answered Dec 10 '13 at 14:34

7

You don't have to do it explicitly, because your SqlConnection instance will always be disposed (and then, connection closed) thanks to the using syntactic sugar.

answered Dec 10 '13 at 14:34

ken2k

48,145
10
116
176

Habib · Answer 2 · 2013-12-10T14:58:14.067

Should i use a try-catch for the whole block? because for some reason i see most people doesnt.

No. Since you are using using statement, that translates into try-finally block, so even if an exception occurs it will ensure the disposal of object.

using Statement (C# Reference)

The using statement ensures that Dispose is called even if an exception occurs while you are calling methods on the object. You can achieve the same result by putting the object inside a try block and then calling Dispose in a finally block; in fact, this is how the using statement is translated by the compiler.

SqlConnection.Dispose ensures that the connection is closed.

To ensure that connections are always closed, open the connection inside of a using block, ....... Doing so ensures that the connection is automatically closed when the code exits the block.

score 2 · Answer 3 · answered Dec 10 '13 at 14:36

You are opening the connection with a using block, and this means that the compiler will make sure that Dispose() gets called on the connection, which will call Close(). So not to worry, you don't need to Close() the connection yourself, even in case of an Exception.

Making sure a connection is closed

3 Answers3