How to handle connections to use in Dapper?

dapper inversion-of-control structuremap

Question

I am trying to use Dapper on a service layer. How should it handle the IDbConnection?

I am using this in a MVC5 project with StructureMap for IoC.

Can I inject it in the server layer classes? Or maybe a DapperWrapper as follows:

public class DapperWrapper {

  private ConnectionString { get { return Settings.ConnectionString; } }

  public IEnumerable<T> Query<T>(String sql, dynamic parameters = null) {

    IEnumerable<T> result;

    using (IDbConnection connection = new SqlConnection(_connection)) {
      connection.Open();
      result = SqlMapper.Query<T>(connection, sql, parameters);        
    }

    return result;

  } // Query

  // OTHER DAPPER METHODS
 }
  1. I am afraid this approach opens and closes many connections. Is this a problem?

  2. Should I inject a connection in the DapperWrapper, and if so, How?

What is the best way to do this?

Expert Answer

  1. As long as connection pooling is enabled (for SQL server it is enabled by default) you aren't actually opening and closing connections - you are fetching from / releasing to the connection pool. It should take microseconds only.

  2. That is entirely a design decision. Injection works. On-demand construction works. In the case of an http request, you can also often make use of on-demand (deferred but cached) request-focused connection storage (just make sure you dispose it at the end of the request!).




Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why