Managing SQL Server Connections

Question

What is the the best practice for SQL connections?

Currently I am using the following:

using (SqlConnection sqlConn = new SqlConnection(CONNECTIONSTRING))
{
    sqlConn.Open();
    // DB CODE GOES HERE
}

I have read that this is a very effective way of doing SQL connections. By default the SQL pooling is active, so how I understand it is that when the using code ends the SqlConnection object is closed and disposed but the actual connection to the DB is put in the SQL connection pool. Am i wrong about this?

Answer 1

Your understanding of using is correct, and that method of usage is the recommended way of doing so. You can also call close in your code as well.

Answer 2

Also : Open late, close early.

Don't open the connection until there are no more steps left before calling the database. And close the connection as soon as you're done.

Answer 3

That's most of it. Some additional points to consider:

• Where do you get your connection string? You don't want that hard-coded all over the place and you may need to secure it.
• You often have other objects to create as well before your really use the connection (SqlCommand, SqlParameter, DataSet, SqlDataAdapter), and you want to wait as long as possible to open the connection. The full pattern needs to account for that.
• You want to make sure your database access is forced into it's own data layer class or assembly. So a common thing to do is express this as a private function call:

.

private static string connectionString = "load from encrypted config file";
private SqlConnection getConnection()
{
    return new SqlConnection(connectionString);
}

And then write your sample like this:

using (SqlConnection sqlConn = getConnection())
{
    // create command and add parameters

    // open the connection
    sqlConn.Open();

   // run the command
}

That sample can only exist in your data access class. An alternative is to mark it internal and spread the data layer over an entire assembly. The main thing is that a clean separation of your database code is strictly enforced.

A real implementation might look like this:

public IEnumerable<IDataRecord> GetSomeData(string filter)
{
    string sql = "SELECT * FROM [SomeTable] WHERE [SomeColumn] LIKE @Filter + '%'";

    using (SqlConnection cn = getConnection())
    using (SqlCommand cmd = new SqlCommand(sql, cn))
    {
        cmd.Parameters.Add("@Filter", SqlDbType.NVarChar, 255).Value = filter;
        cn.Open();

        using (IDataReader rdr = cmd.ExecuteReader())
        {
            while (rdr.Read())
            {
                yield return (IDataRecord)rdr;
            }
        }
    }
}

Notice that I was also able to "stack" the creation of the cn and cmd objects, and thus reduce nesting and only create one scope block.

Finally, a word of caution about using the yield return code in this specific sample. If you call the method and don't complete your DataBinding or other use right away it could hold the connection open for a long time. An example of this is using it to set a data source in the Load event of an ASP.NET page. Since the actual data binding event won't occur until later you could hold the connection open much longer than needed.

Answer 4

Microsoft's Patterns and Practices libraries are an excellent approach to handling database connectivity. The libraries encapsulate most of the mechanisms involved with opening a connection, which in turn will make your life easier.