C# Closing Database Connections

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.

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.)