Why always close Database connection?

Question

If connecting to a database consumes a lot of resources, why should a database connection always be closed in your application if you have to open it again? Can I just make this connection available globally throughout my application so that other classes and methods can reuse it?

For example (in pseudo code):

public class PopulateGridViews()
{
    public SqlConnection conn = new SqlConnection(@"Database:DATABASE");
    conn.Open();

    void PopulateGrid1()
    {
        SqlCommand cmd = new SqlCommand("SELECT * FROM TABLE1");
        cmd.Connection = conn;
        cmd.ExecuteNonQuery();
        cmd.Dispose();
        // Populate Grid1
    }

    void PopulateGrid2()
    {
        SqlCommand cmd = new SqlCommand("SELECT * FROM TABLE2");
        cmd.Connection = conn;
        cmd.ExecuteNonQuery();
        cmd.Dispose();
        // Populate Grid2
    }
}

Answer 1

You should not leave connections open.

You should:

1. Open connections as late as possible
2. Close connections as soon as possible

The connection itself is returned to the connection pool. Connections are a limited and relatively expensive resource. Any new connection you establish that has exactly the same connection string will be able to reuse the connection from the pool.

We strongly recommend that you always close the connection when you are finished using it so that the connection will be returned to the pool. You can do this using either the Close or Dispose methods of the Connection object, or by opening all connections inside a using statement in C#, or a Using statement in Visual Basic. Connections that are not explicitly closed might not be added or returned to the pool. For more information, see using Statement (C# Reference) or How to: Dispose of a System Resource for Visual Basic. Ref.

You should appropriately wrap anything that implements IDisposable in a using statement block:

 using (SqlConnection connection = new SqlConnection(connectionString))
 {
     connection.Open();

     ...

     command.ExecuteNonQuery();
 }

Answer 2

Because (some) databases also hold open a connection until told by the calling app to close it. If you get hundreds of calls to a database then it's sitting there with 100 open connections tying up resources. It's not uncommon to have thousands or hundreds of thousands of calls to a database in a busy app, and sooner or later the DB performance will kill the app's performance.

It's really just common sense. If you have a valid reason to keep it open, then do so. If not, close it as soon as you're done with it. But it's better to be in the good habit of closing the connections so that you don't just leave them open when you don't intend to. It's a good habit like wearingyour seat belt or closing the refrigerator door when you're not getting food out.

This article states it well (even if it is a bit outdated):

http://www.bewebmaster.com/84.php

A common problem among hosting companies is that ASP websites do not close the database connections after they are opened. This is a basic step that you should consider to be part of mandatory code. If you do not close your database connections, many problems can occur like web pages hanging, slow page loads, and more.

Think of it as going through a door to your house. Maybe the door will shut by itself, but maybe it won't. If it doesn't shut, who knows what will happen. If you live in the country, a bear could walk in. If you live in the city, a mugger could walk in. Alright, well maybe leaving a database connection open won't lead to anything that bad, but it will lead to a lot of unnecessary headaches for both you and your hosting company.

Answer 3

It is clear if you donot close the connection than it would consume your resource continuously and which would have overall impact on your apllication and it may also not be added or returned to the pool.