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
– Cheesebaron, Commented Dec 25, 2012 at 22:46
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
– Tim Schmelter, Commented Dec 25, 2012 at 22:59
@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, Commented Dec 25, 2012 at 23:04

Community · Accepted Answer · 2017-05-23 12:25:40Z

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.

Collectives™ on Stack Overflow

IDbConnection factory using Dapper

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related