C# Closing Database Connections

Question

I need a to get a bit of understanding in this, When you open a connection to a Database can you leave it open?

How does this connection close?

Is it good practise or bad practice?

Currently I have a request to a database that works no problem

oCON.Open();
oCMD.ExecuteNonQuery();
oCON.Close();

However Some of the examples that I have seen are something like this with no database close.

oCON.Open();
oCMD.ExecuteNonQuery();

How would this connection get closed?

Is this bad practice?

Answer 1

I was looking for a duplicate, as this seems to be a common question. The top answer I found is this one, however, I don't like the answer that was given.

You should always close your connection as soon as you're done with it. The database has a finite number of connections that it allows, and it also takes a lot of resources.

The "old school" way to ensure the close occurred was with a try/catch/finally block:

SqlConnection connection;
SqlCommand command;

try 
{
    // Properly fill in all constructor variables.
    connection = new SqlConnection();
    command = new SqlCommand();

    connection.Open();
    command.ExecuteNonQuery();

    // Parse the results
}
catch (Exception ex)
{
    // Do whatever you need with exception
}
finally
{
    if (connection != null)
    {
        connection.Dispose();
    }
    if (command != null)
    {
        command.Dispose();
    }
}

However, the using statement is the preferred way as it will automatically Dispose of the object.

try
{
    using (var connection = new SqlConnection())
    using (var command = new SqlCommand())
    {
        connection.Open();
        command.ExecuteNonQuery();

        // Do whatever else you need to.
    }
}
catch (Exception ex)
{
    // Handle any exception.
}

The using statement is special in that even if an exception gets thrown, it still disposes of the objects that get created before the execution of the code stops. It makes your code more concise and easier to read.

As mentioned by christophano in the comments, when your code gets compiled down to IL, it actually gets written as a try/finally block, replicating what is done in the above example.

Answer 2

You want your SqlConnection to be in a using block:

using(var connection = new SqlConnection(connectionString))
{
...
}

That ensures that the SqlConnectionwill be disposed, which also closes it.

From your perspective the connection is closed. Behind the scenes the connection may or may not actually be closed. It takes time and resources to establish a SQL connection, so behind the scenes those connections aren't immediately closed. They're kept open and idle for a while so that they can be reused. It's called connection pooling. So when you open a connection, you might not really be opening a new connection. You might be retrieving one from the connection pool. And when you close it, it doesn't immediately close, it goes back to the pool.

That's all handled behind the scenes and it doesn't change what we explicitly do with our connections. We always "close" them as quickly as possible, and then the .NET Framework determines when they actually get closed. (It's possible to have some control over that behavior but it's rarely necessary.)

Answer 3

Take a look at the Repository Pattern with Unit of Work. A connection context should be injected into the class which operates commands to the database.

A sql execution class - like a repository class represents - should not create a connection. It is not testable and hurts the paradigm of SRP. It should accept an IDbConnection object like in the constructor. The repository should not take care if behind the IDbConnection is an instance of SqlConnection, MysqlConnection or OracleConnection.

All of the ADO.NET connection objects are compatible to IDbConnection.