ASP.NET Tips #4 - Make the most of connection pooling by closing SQLConnection as soon as possible

Close or Dispose your SqlConnection as quickly as possible to take advantage of connection pooling in ADO.NET. Closed connections return to the connection pool, where they remain cached, so you won’t need to create a new connection. Take advantage of the using statement to restrict the scope of your SqlConnections for the short time you need them.

Sample C# code:

DataTable dt = new DataTable();
try
{
    using (SqlConnection connection = new SqlConnection(SqlConnectionString))
    {
        using (SqlCommand command = connection.CreateCommand())
        {
            connection.Open();

            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = queryString;

            SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
            dt.Load(reader);

            reader.Close();
        }
        connection.Close();
    }
}
catch (Exception ex)
{
    throw new Exception("SqlExecuteReader Error: " + ex.Message);
}