dotnet core dapper及EFCore操作多个数据、多Connecting链接

首先要引用以下包

新建DatabaseType枚举

    public enum DatabaseType
    {
        SQLServer,
        Oracle,
        PostgreSQL
    }

EF Core

1、新建IDbContextFactory接口

public interface IDbContextFactory
{
    public EFCoreContext CreateContext(DatabaseType database,string conn);
}

2、新建DbContextFactory工厂类

public class DbContextFactory : IDbContextFactory
{
    private IConfiguration _Configuration = null;
    public DbContextFactory(IConfiguration configuration)
    {
        _Configuration = configuration;
        //ConnectionList = configuration.GetSection("SQLServerConnectingList");
    }

    public EFCoreContext CreateContext(DatabaseType database, string conn)
    {
        IConfiguration ConnectionList = null;
        switch (database)
        {
            case DatabaseType.Oracle:
                ConnectionList = _Configuration.GetSection("OracleConnectingList");
                break;
            case DatabaseType.SQLServer:
                ConnectionList = _Configuration.GetSection("SQLServerConnectingList");
                break;
            case DatabaseType.PostgreSQL:
                ConnectionList = _Configuration.GetSection("PostgreSQLConnectingList");
                break;
            default:
                throw new Exception("数据库不存在！");
        }
        return new EFCoreContext(database, ConnectionList[conn]);
    }
}

3、新建EFCoreContext

public class EFCoreContext : DbContext
{
    private string _conn = "";
    private DatabaseType _database;
    private readonly int RetryNumber = 5;// 默认为5

    public EFCoreContext(DatabaseType database, string conn)
    {
        _conn = conn ?? throw new Exception("数据库连接不能为空！");
        _database = database;
        int timeout = !string.IsNullOrWhiteSpace(GetAppsettings.GetSection("DB:CommandTimeout")) ? Convert.ToInt32(GetAppsettings.GetSection("DB:CommandTimeout")) : 30;
        this.Database.SetCommandTimeout(timeout);
    }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        switch(_database)
        {
            case DatabaseType.Oracle:
                optionsBuilder.UseOracle(_conn);
                break;
            case DatabaseType.SQLServer:
                optionsBuilder.UseSqlServer(_conn, option=>option.EnableRetryOnFailure(RetryNumber));
                break;
            case DatabaseType.PostgreSQL:
                optionsBuilder.UseNpgsql(_conn, option => option.EnableRetryOnFailure(RetryNumber));
                break;
            default:
                throw new Exception("数据库不存在！");
        }
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        ......
    }

    ......
}

4、在Startup注入服务

services.AddTransient<IDbContextFactory, DbContextFactory>();

5、使用

public class LoginService : ILoginService
{
    private readonly IConfiguration _Configuration;
    private readonly IDbContextFactory _DbContextFactory;

    public LoginService(IConfiguration Configuration, IDbContextFactory DbContextFactory)
    {
        _Configuration = Configuration;
        _DbContextFactory = DbContextFactory;
    }
    public async Task<TokenInfo> Login(LoginRequest dto)
    {
        using (EFCoreContext ef = _DbContextFactory.CreateContext(DatabaseType.SQLServer, db))
        {
            
            ......
        }
    }
}

Dapper

1、新建IDapperFactory接口

public interface IDapperFactory
{
    DapperHelper CreateConnection(DatabaseType databaseType,string conn);
}

2、新建DapperFactory工厂类

    public class DapperFactory : IDapperFactory
    {
        private readonly IConfiguration _Configuration;

        public DapperFactory(IConfiguration Configuration)
        {
            _Configuration = Configuration;
        }
        public DapperHelper CreateConnection(DatabaseType databaseType, string conn)
        {
            IConfiguration ConnectionList = null;

            switch(databaseType)
            {
                case DatabaseType.Oracle:
                    ConnectionList = _Configuration.GetSection("OracleConnectingList");
                    break;
                case DatabaseType.SQLServer:
                    ConnectionList = _Configuration.GetSection("SQLServerConnectingList");
                    break;
                case DatabaseType.PostgreSQL:
                    ConnectionList = _Configuration.GetSection("PostgreSQLConnectingList");
                    break;
                default:
                    throw new Exception("数据库不存在！");
            }
            return new DapperHelper(databaseType, ConnectionList[conn]);
        }
    }

3、新建DapperHelper

    public class DapperHelper
    {
        private int CommandTimeout = !string.IsNullOrWhiteSpace(GetAppsettings.GetSection("DB:CommandTimeout"))? Convert.ToInt32(GetAppsettings.GetSection("DB:CommandTimeout")):30;
        private string _conn = "";
        private DatabaseType _database;
        public DapperHelper(DatabaseType database, string conn)
        {
            _conn = conn ?? throw new Exception("数据库连接不能为空！");
            _database = database;
        }
        private IDbConnection OpenConnection()
        {
            IDbConnection DbConnection = null;
            switch (_database)
            {
                case DatabaseType.Oracle:
                    DbConnection = new OracleConnection(_conn);
                    break;
                case DatabaseType.SQLServer:
                    DbConnection = new SqlConnection(_conn);
                    break;
                case DatabaseType.PostgreSQL:
                    DbConnection = new NpgsqlConnection(_conn);
                    break;
                default:
                    throw new Exception("数据库不存在！");
            }
            if (DbConnection.State == ConnectionState.Closed)
            {
                DbConnection.Open();
            }
            return DbConnection;
        }

        public IList<T> Query<T>(string sql, object condition = null)
        {
            using (IDbConnection conn = OpenConnection())
            {
                var res = conn.Query<T>(sql, condition, commandTimeout: CommandTimeout).ToList();
                conn.Close();
                return res;
            }
        }
        
      
       ..........      

    } 

4、在Startup注入服务

services.AddTransient<IDapperFactory, DapperFactory>();

5、使用

    public class RsService: IRsService
    {
        private readonly IDapperFactory _DapperFactory;

        public RsService(IDapperFactory DapperFactory)
        {
            _DapperFactory = DapperFactory;
        }
        public RsDetailList get()
        {
            DapperHelper dapper = _DapperFactory.CreateConnection(DatabaseType.SQLServer, DB);

            var Rs = dapper.Query<Detail>(@"SQL", new { });
            ........
            
        }
    }