IDbConnection factory using Dapper

Question

IDbConnection factory using Dapper

asp.net c# 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?

Nils Anders

View more on Stack Overflow

Tim Schmelter · Accepted Answer · 5/23/2017 12:25:40 PM

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

Question

Fastest Dapper Plus Extensions

Accepted Answer

Performance

Expression Evaluator