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?
Accepted Answer
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.