[C#] What is the proper way to ensure a SQL connection is closed when an exception is thrown?


Answers

The .Net Framework mantains a connection pool for a reason. Trust it! :) You don't have to write so much code just to connect to the database and release the connection.

You can just use the 'using' statement and rest assured that 'IDBConnection.Release()' will close the connection for you.

Highly elaborate 'solutions' tend to result in buggy code. Simple is better.

Question

I use a pattern that looks something like this often. I'm wondering if this is alright or if there is a best practice that I am not applying here.

Specifically I'm wondering; in the case that an exception is thrown is the code that I have in the finally block enough to ensure that the connection is closed appropriately?

public class SomeDataClass : IDisposable
{
    private SqlConnection _conn;

    //constructors and methods

    private DoSomethingWithTheSqlConnection()
    {
        //some code excluded for brevity

        try
        {
            using (SqlCommand cmd = new SqlCommand(SqlQuery.CountSomething, _SqlConnection))
            {
                _SqlConnection.Open();
                countOfSomething = Convert.ToInt32(cmd.ExecuteScalar());
            }
        }
        finally
        {
            //is this the best way?
            if (_SqlConnection.State == ConnectionState.Closed)
                _SqlConnection.Close();
        }

        //some code excluded for brevity
    }

    public Dispose()
    {
        _conn.Dispose();
    }
}



Is using a singleton for the connection a good idea in ASP.NET website

Using a single connection is an extremely bad idea - if access to the connection is properly locked, it means that ASP.NET can only serve one user at a time, which will seriously limit your application's ability to grow.

If the connection is not properly locked, things can get really weird. For example, one thread might dispose the connection while another thread is trying to execute a command against it.

Instead of using a single connection, you should just create new connection objects when you need them, to take advantage of connection pooling.

Connection pooling is the default behavior for the SqlClient classes (and probably other data providers). When you use connection pooling, any time you 'create' a connection, the connection will actually be pulled from a pool of existing ones so that you don't incur the costs of building one from scratch each time. When you release it (close it or dispose of it) you return it to the connection pool, keeping your total count of connections relatively low.


Edit: You'll see the error you mention (The timeout period elapsed prior to obtaining a connection from the pool) if you're not closing (or disposing) your connections. Make sure you do that as soon as you're done using each connection.

There are several good questions that discuss this, which I suspect might be helpful!




I answered in detail a question about closing connections that you might find interesting here.


Microsoft has answered this question here:

Q. How long does my database connection remain open?

A. A connection typically remains open until you consume the query results. If you expect to take time to process all the results and are not opposed to caching the results, apply ToList<(Of <(TSource>)>) to the query. In common scenarios where each object is processed only one time, the streaming model is superior in both DataReader and LINQ to SQL.

The exact details of connection usage depend on the following:

Connection status if the DataContext is constructed with a connection object.

Connection string settings (for example, enabling Multiple Active Result Sets (MARS). For more information, see Multiple Active Result Sets (MARS).


More detail can be found here:

You can supply an existing ADO.NET connection when you create a LINQ to SQL DataContext. All operations against the DataContext (including queries) use this provided connection. If the connection is already open, LINQ to SQL leaves it as is when you are finished with it.




Does Dispose still get called when exception is thrown inside of a using statement?

Yes, using wraps your code in a try/finally block where the finally portion will call Dispose() if it exists. It won't, however, call Close() directly as it only checks for the IDisposable interface being implemented and hence the Dispose() method.

See also:




The reason why using a Connection to the database as a singleton is an horrific idea, is because every 2nd+ connection will then have to WAIT for the first connection to be released.

A singleton means that there's only one database connection object, to connect to the db. So if a second person wants to connect to it, they need to wait until they can access that object.

That's bad news.

Just keep creating new instances of the database connection object, when required. The trick here is to open the connection as late as possible and then close that connection as soon as possible.

The most expensive operation in a database connection object, is the actual connection. not the creation.