How can I use Dapper and manage the database connections?

.net c# dapper

Question

ZZZ_tmp
1
72
4/3/2013 6:46:58 PM

Popular Answer

v2.0.3 for Microsoft.AspNetCore.All and v1.50.2 for Dapper

Although I am unsure whether I am following best practices appropriately, I am handling multiple connection strings in this manner.

If you just have one connection string, it's simple.

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);
        }

        // ......
    }
}

issues if you use several connection strings

Since Dapper utilizes IDbConnection , you must consider how to distinguish between various database connections.

I made many interfaces that were "inherited" fromIDbConnection in accordance with various database connections, and injectSqlConnection use various database connection stringsStartup .

That fell short because ofSqlConnection receives fromDbConnection , andDbConnection enhances not onlyIDbConnection yet alsoComponent class. Consequently, your unique interfaces won't be able to use merely theSqlConnection implenentation.

I also attempted to develop my own.DbConnection a class that accepts various connection strings. That is very difficult since you have to employ every technique fromDbConnection class. Your assistance fromSqlConnection .

What I ultimately do

  1. During Startup I put the values of each connection string into a dictionary. Also, I produced anenum to prevent using magic strings in the names of any database connections.
  2. I entered Singleton into the dictionary.
  3. Rather than infusingIDbConnection , I made thisIDbConnectionFactory and injected that for all repositories as Transient. Currently, each repository takesIDbConnectionFactory in place ofIDbConnection .
  4. When should I choose the best connection? in each repository's constructor! I made repository basic classes and had the repositories inherit from them to organize things. In the base classes, the proper connection string may be chosen.

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);
    }
}

My own factory implementation, DapperDbConenctionFactory

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 you may create a separate repository base class for additional repositories that must communicate with the other connections.

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);
        }

        // ......
    }
}

I hope these are helpful.

34
11/21/2017 1:41:33 AM


Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow