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.