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();
 }