Does this code pool SQL connections?

Question

I'm using Essential.Logging's SQL logger. I looked at the source of the library here and saw (at the end)

Everytime you want to log to SQL:

using (var connection = DbProviderFactoryExtensions.CreateConnection(dbFactory, connectionSettings.ConnectionString))
{
    using (var command = DbProviderFactoryExtensions.CreateCommand(dbFactory, CommandText, connection))
    {
        command.Parameters.Add(DbProviderFactoryExtensions.CreateParameter(dbFactory, "@ApplicationName", ApplicationName != null ? (object)ApplicationName : DBNull.Value));
        // ..snip..
        command.Parameters.Add(DbProviderFactoryExtensions.CreateParameter(dbFactory, "@Data", dataString != null ? (object)dataString : DBNull.Value));

        connection.Open(); // <====
        command.ExecuteNonQuery();
    }
}

The helper function right on the top of the using statement is

public static DbConnection CreateConnection(DbProviderFactory dbFactory, string connectionString)
{
    if (dbFactory == null) throw new ArgumentNullException("dbFactory");

    var connection = dbFactory.CreateConnection();
    connection.ConnectionString = connectionString;
    return connection;
}

Question:

Is it opening (and closing) the connection to the SQL server everytime it does that? I assume it would be more efficient to pool the connection to the SQL server, right?
If I were to replace that with Entity Framework 5.0 (with a using (var db = new myDbContext()) { } ) would that result in connection pooling?

score 3 · Accepted Answer · answered Dec 28 '12 at 05:10

Is it opening (and closing) the connection to the SQL server everytime it does that? I assume it would be more efficient to pool the connection to the SQL server, right?

Yes. And no. It is calling Close() by way of Dispose(), but it's actually pooling the connections for you in the background, assuming you have not set any properties in the connection string that disallow pooling. You don't do anything for this; just open as late as possible, and close as soon as possible, and let ADO.NET do its thing.

If I were to replace that with Entity Framework 5.0 (with a using (var db = new myDbContext()) { } ) would that result in connection pooling?

The DbContext also uses pooling, because it uses the regular ADO.NET behind the scenes. The code it generates generally follows the advice I gave above: Opens as late as possible, and closes as early as possible - letting ADO.NET handle pooling.

score 2 · Answer 2 · edited May 23 '17 at 10:24

As a supportive information to Andrew's comment:

Unless you disable connection pooling (by adding Pooling=False into your connection string), ADO.NET pools your connections. It separates connection pools according to the connection strings. If there exists a pool for the given connection string, re-uses it, otherwise creates a new connection.

Default max connection pool size is 100. But you can change pooling behavior by setting related attributes of connection string - Min Pool Size, Max Pool Size, etc.

See related msdn article for how the connection pooling works and here for usage details.

Does this code pool SQL connections?

2 Answers2