How do I handle Database Connections with Dapper in .NET?
Update: clarification from MarredCheese's comment:
"No need to use a using statement. Dapper will automatically open, close, and dispose of the connection for you." That's not correct. Dapper will automatically open closed connections, and it will automatically close connections that it auto-opened, but it will not automatically dispose of connections. Marc Gravell and Eric Lippert both advocate using using with Dapper here.
Microsoft.AspNetCore.All: v2.0.3 | Dapper: v1.50.2
I am not sure if I am using the best practices correctly or not, but I am doing it this way, in order to handle multiple connection strings.
It's easy if you have only 1 connection string
Startup.cs
using System.Data;
using System.Data.SqlClient;
namespace DL.SO.Project.Web.UI
{
public class Startup
{
public IConfiguration Configuration { get; private set; }
// ......
public void ConfigureServices(IServiceCollection services)
{
// Read the connection string from appsettings.
string dbConnectionString = this.Configuration.GetConnectionString("dbConnection1");
// Inject IDbConnection, with implementation from SqlConnection class.
services.AddTransient<IDbConnection>((sp) => new SqlConnection(dbConnectionString));
// Register your regular repositories
services.AddScoped<IDiameterRepository, DiameterRepository>();
// ......
}
}
}
DiameterRepository.cs
using Dapper;
using System.Data;
namespace DL.SO.Project.Persistence.Dapper.Repositories
{
public class DiameterRepository : IDiameterRepository
{
private readonly IDbConnection _dbConnection;
public DiameterRepository(IDbConnection dbConnection)
{
_dbConnection = dbConnection;
}
public IEnumerable<Diameter> GetAll()
{
const string sql = @"SELECT * FROM TABLE";
// No need to use using statement. Dapper will automatically
// open, close and dispose the connection for you.
return _dbConnection.Query<Diameter>(sql);
}
// ......
}
}
Problems if you have more than 1 connection string
Since Dapper utilizes IDbConnection, you need to think of a way to differentiate different database connections.
I tried to create multiple interfaces, 'inherited' from IDbConnection, corresponding to different database connections, and inject SqlConnection with different database connection strings on Startup.
That failed because SqlConnection inherits from DbConnection, and DbConnection inplements not only IDbConnection but also Component class. So your custom interfaces won't be able to use just the SqlConnection implenentation.
I also tried to create my own DbConnection class that takes different connection string. That's too complicated because you have to implement all the methods from DbConnection class. You lost the help from SqlConnection.
What I end up doing
- During
Startup, I loaded all connection string values into a dictionary. I also created anenumfor all the database connection names to avoid magic strings. - I injected the dictionary as Singleton.
- Instead of injecting
IDbConnection, I createdIDbConnectionFactoryand injected that as Transient for all repositories. Now all repositories takeIDbConnectionFactoryinstead ofIDbConnection. - When to pick the right connection? In the constructor of all repositories! To make things clean, I created repository base classes and have the repositories inherit from the base classes. The right connection string selection can happen in the base classes.
DatabaseConnectionName.cs
namespace DL.SO.Project.Domain.Repositories
{
public enum DatabaseConnectionName
{
Connection1,
Connection2
}
}
IDbConnectionFactory.cs
using System.Data;
namespace DL.SO.Project.Domain.Repositories
{
public interface IDbConnectionFactory
{
IDbConnection CreateDbConnection(DatabaseConnectionName connectionName);
}
}
DapperDbConenctionFactory - my own factory implementation
namespace DL.SO.Project.Persistence.Dapper
{
public class DapperDbConnectionFactory : IDbConnectionFactory
{
private readonly IDictionary<DatabaseConnectionName, string> _connectionDict;
public DapperDbConnectionFactory(IDictionary<DatabaseConnectionName, string> connectionDict)
{
_connectionDict = connectionDict;
}
public IDbConnection CreateDbConnection(DatabaseConnectionName connectionName)
{
string connectionString = null;
if (_connectDict.TryGetValue(connectionName, out connectionString))
{
return new SqlConnection(connectionString);
}
throw new ArgumentNullException();
}
}
}
Startup.cs
namespace DL.SO.Project.Web.UI
{
public class Startup
{
// ......
public void ConfigureServices(IServiceCollection services)
{
var connectionDict = new Dictionary<DatabaseConnectionName, string>
{
{ DatabaseConnectionName.Connection1, this.Configuration.GetConnectionString("dbConnection1") },
{ DatabaseConnectionName.Connection2, this.Configuration.GetConnectionString("dbConnection2") }
};
// Inject this dict
services.AddSingleton<IDictionary<DatabaseConnectionName, string>>(connectionDict);
// Inject the factory
services.AddTransient<IDbConnectionFactory, DapperDbConnectionFactory>();
// Register your regular repositories
services.AddScoped<IDiameterRepository, DiameterRepository>();
// ......
}
}
}
DiameterRepository.cs
using Dapper;
using System.Data;
namespace DL.SO.Project.Persistence.Dapper.Repositories
{
// Move the responsibility of picking the right connection string
// into an abstract base class so that I don't have to duplicate
// the right connection selection code in each repository.
public class DiameterRepository : DbConnection1RepositoryBase, IDiameterRepository
{
public DiameterRepository(IDbConnectionFactory dbConnectionFactory)
: base(dbConnectionFactory) { }
public IEnumerable<Diameter> GetAll()
{
const string sql = @"SELECT * FROM TABLE";
// No need to use using statement. Dapper will automatically
// open, close and dispose the connection for you.
return base.DbConnection.Query<Diameter>(sql);
}
// ......
}
}
DbConnection1RepositoryBase.cs
using System.Data;
using DL.SO.Project.Domain.Repositories;
namespace DL.SO.Project.Persistence.Dapper
{
public abstract class DbConnection1RepositoryBase
{
public IDbConnection DbConnection { get; private set; }
public DbConnection1RepositoryBase(IDbConnectionFactory dbConnectionFactory)
{
// Now it's the time to pick the right connection string!
// Enum is used. No magic string!
this.DbConnection = dbConnectionFactory.CreateDbConnection(DatabaseConnectionName.Connection1);
}
}
}
Then for other repositories that need to talk to the other connections, you can create a different repository base class for them.
using System.Data;
using DL.SO.Project.Domain.Repositories;
namespace DL.SO.Project.Persistence.Dapper
{
public abstract class DbConnection2RepositoryBase
{
public IDbConnection DbConnection { get; private set; }
public DbConnection2RepositoryBase(IDbConnectionFactory dbConnectionFactory)
{
this.DbConnection = dbConnectionFactory.CreateDbConnection(DatabaseConnectionName.Connection2);
}
}
}
using Dapper;
using System.Data;
namespace DL.SO.Project.Persistence.Dapper.Repositories
{
public class ParameterRepository : DbConnection2RepositoryBase, IParameterRepository
{
public ParameterRepository (IDbConnectionFactory dbConnectionFactory)
: base(dbConnectionFactory) { }
public IEnumerable<Parameter> GetAll()
{
const string sql = @"SELECT * FROM TABLE";
return base.DbConnection.Query<Parameter>(sql);
}
// ......
}
}
Hope all these help.
It was asked about 4 years ago... but anyway, maybe the answer will be useful to someone here:
I do it like this in all the projects. First, I create a base class which contains a few helper methods like this:
public class BaseRepository
{
protected T QueryFirstOrDefault<T>(string sql, object parameters = null)
{
using (var connection = CreateConnection())
{
return connection.QueryFirstOrDefault<T>(sql, parameters);
}
}
protected List<T> Query<T>(string sql, object parameters = null)
{
using (var connection = CreateConnection())
{
return connection.Query<T>(sql, parameters).ToList();
}
}
protected int Execute(string sql, object parameters = null)
{
using (var connection = CreateConnection())
{
return connection.Execute(sql, parameters);
}
}
// Other Helpers...
private IDbConnection CreateConnection()
{
var connection = new SqlConnection(...);
// Properly initialize your connection here.
return connection;
}
}
And having such a base class I can easily create real repositories without any boilerplate code:
public class AccountsRepository : BaseRepository
{
public Account GetById(int id)
{
return QueryFirstOrDefault<Account>("SELECT * FROM Accounts WHERE Id = @Id", new { id });
}
public List<Account> GetAll()
{
return Query<Account>("SELECT * FROM Accounts ORDER BY Name");
}
// Other methods...
}
So all the code related to Dapper, SqlConnection-s and other database access stuff is located in one place (BaseRepository). All real repositories are clean and simple 1-line methods.
I hope it will help someone.
I created extension methods with a property that retrieves the connection string from configuration. This lets the callers not have to know anything about the connection, whether it's open or closed, etc. This method does limit you a bit since you're hiding some of the Dapper functionality, but in our fairly simple app it's worked fine for us, and if we needed more functionality from Dapper we could always add a new extension method that exposes it.
internal static string ConnectionString = new Configuration().ConnectionString;
internal static IEnumerable<T> Query<T>(string sql, object param = null)
{
using (SqlConnection conn = new SqlConnection(ConnectionString))
{
conn.Open();
return conn.Query<T>(sql, param);
}
}
internal static int Execute(string sql, object param = null)
{
using (SqlConnection conn = new SqlConnection(ConnectionString))
{
conn.Open();
return conn.Execute(sql, param);
}
}
I do it like this:
internal class Repository : IRepository {
private readonly Func<IDbConnection> _connectionFactory;
public Repository(Func<IDbConnection> connectionFactory)
{
_connectionFactory = connectionFactory;
}
public IWidget Get(string key) {
using(var conn = _connectionFactory())
{
return conn.Query<Widget>(
"select * from widgets with(nolock) where widgetkey=@WidgetKey", new { WidgetKey=key });
}
}
}
Then, wherever I wire-up my dependencies (ex: Global.asax.cs or Startup.cs), I do something like:
var connectionFactory = new Func<IDbConnection>(() => {
var conn = new SqlConnection(
ConfigurationManager.ConnectionStrings["connectionString-name"];
conn.Open();
return conn;
});