IDbConnection factory using Dapper

Question

I using dapper in my ASP.NET WebForms solution.

All my classes which handles data retrieves an open connection from this base class

public abstract class SalesDb : IDisposable
{
    protected static IDbConnection OpenConnection()
    {
        IDbConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["SalesDb"].ConnectionString);
        connection.Open();
        return connection;
    }

    public void Dispose()
    {
        OpenConnection().Dispose();
    }
}

Example of a service class that uses this base class

public class LeadService : SalesDb
{
    public IEnumerable<LeadDto> Select()
    {
        using (IDbConnection connection = OpenConnection())
        {
            return connection.Query<LeadDto>("Lead_Select",
                null, null, true, null, CommandType.StoredProcedure);
        }
    }
}

Is there any disadvantage that OpenConnection() method in the base class is static and return a new instance for every call?

If you are going to do a lot of calls to the database you will have this overhead of creating and destroying connections to the database all the time. — Cheesebaron
You are creating a new connection in dispose? That won't work! Use a simple loop with more than 100 iterations and you'll probably see some kind of exceptions(too many open connections etc.). In any case i expect serious performance problems since the pool needs to create new physical connections consecutively. — Tim Schmelter
@anpe: Yes, you're using the default SQL Server Connection Pooling. I would not reinvent the connection-pool. My advice is: don't use that class, all the more in asp.net. — Tim Schmelter

Tim Schmelter Tim Schmelter · Accepted Answer · 2012-12-25T23:20:29

You are creating a new connection in dispose? That won't work! Use a simple loop with more than 100 iterations( max pool size default is 100 ) and you'll probably see some kind of exceptions(too many open connections, ExecuteReader requires an open and available Connection etc.).

In any case i expect serious performance problems since the pool needs to create new physical connections consecutively.

I would not reinvent the Connection-Pool. My advice is: don't use that class, all the more in asp.net.

Don't poach on the Connection-Pool's territory ;-)

But note that both of your questions can be answered with No. The static method is not a problem(unlike a static connection) and also not that it always returns a new connection(since the pool would return connections that are currently not in use). But the main problem of your class is the dispose which creates connections and opens them.

IDbConnection factory using Dapper

1 Answers