做项目的时候一般是一个链接一个库，但是有时候碰上分库的需求场景，比如saas模式，a租户数据存在a数据库，b租户数据存在b数据库。那怎么来实现数据源的动态、灵活的切换呢？下面我们就来看看怎么实现。
1、 web.config配置数据库连接串
<add name="Bqool_ServiceEntities" connectionString="metadata=res://*/BqoolService.csdl|res://*/BqoolService.ssdl|res://*/BqoolService.msl;provider=System.Data.SqlClient;provider connection string="data source={0};initial catalog={1};persist security info=True;user id=XXXXXX;password=XXXXX;MultipleActiveResultSets=True;App=EntityFramework"" providerName="System.Data.EntityClient" />
2、注入ConnectionService
//依客戶切換連線 RegisterScope(builder.RegisterType<ConnectionSchDbService>().As<IConnectionService>().WithParameter("sourceConnectionString", conn.Bqool_Service).Keyed<IConnectionService>(ConnectionType.Bqool_Service));3、 获取动态生成的数据库链接
public class ConnectionSchDbService : IConnectionService { private readonly string _sourceConnectionString; private readonly IAccountServiceRelService _accountServiceRel; private readonly ILoginStatusServices _loginStatusService; private bool? _isConnectUI; private readonly Logger _logger; public ConnectionSchDbService( string sourceConnectionString, IAccountServiceRelService accountServiceRel, ILoginStatusServices loginStatusService) { _sourceConnectionString = sourceConnectionString; _accountServiceRel = accountServiceRel; _loginStatusService = loginStatusService; _logger = LogManager.GetLogger(GetType().FullName); } public string GetDbConnectionString(string account = null) { var isConnectUI = false; if (_isConnectUI == true) { isConnectUI = true; } return SetConnection(account, isConnectUI); } public string GetServerName(string account = null) { var accountServiceRel = GetAccountServiceRelHistory(account); if (accountServiceRel != null) { return accountServiceRel.Server_Name; } return null; } public string GetServiceName(string account = null) { var accountServiceRel = GetAccountServiceRelHistory(account); if (accountServiceRel != null) { return accountServiceRel.Schedule_Name; } return null; } public void SetConnectionUI(bool isConnectUI) { _isConnectUI = isConnectUI; } private string SetConnection(string account, bool isConnectUI = false) { var environmentMode = CommonSetting.GetEnvironmentMode(); var returnConnection = string.Empty; //Default Setting (Account Service Rel 還沒建立的預設值) switch (environmentMode) { case EnvironmentMode.Dev: //開發 returnConnection = string.Format(_sourceConnectionString, "192.168.xxxx.xxxx", "Servicexxxx"); //開發環境使用 service1 ~ service3 xxxxx break; xxxxxx default: break; } //連線到 Account_Service_Rel_History var accountServiceRel = GetAccountServiceRelHistory(account); if (accountServiceRel != null) { returnConnection = string.Format(_sourceConnectionString, accountServiceRel.Server_IP, accountServiceRel.Schedule_Name); } if (!System.Environment.UserInteractive && System.Web.HttpContext.Current == null) //Windows Service { _logger.Debug($"account:{account ?? _loginStatusService.GetAccount()}, isConnectUI:{isConnectUI}, conn:{StringTools.ReplaceRegex(returnConnection, @"user.*", "")}"); } return returnConnection; }
public interface IConnectionService { /// <summary> /// 取得資料庫連線 /// </summary> /// <param name="account">不填使用登入的 Account</param> /// <returns></returns> string GetDbConnectionString(string account = null);
4 、定义访问的上下文DbContext
public class DbContextFactory : IDbContextFactory { private readonly IConnectionService _connectionService; private readonly ILoginStatusServices _loginStatusServices; private readonly string _user; public DbContextFactory(IConnectionService connectionService, ILoginStatusServices loginStatusService) { _connectionService = connectionService; _loginStatusServices = loginStatusService; _user = loginStatusService.GetUserEmail(); } private string _defaultConnectionString; private DbContext _dbContext; private SqlConnection _sqlConnection; private string _account; private string _serverName; private string _serviceName; public DbContext GetDbContext(string account = null) { var isResetConnection = IsResetConnection(account); if (_dbContext != null && isResetConnection) { DisposeDbContext(); } if (_dbContext == null || isResetConnection) { if (string.IsNullOrEmpty(_defaultConnectionString) || isResetConnection) { _defaultConnectionString = _connectionService.GetDbConnectionString(account); } Type t = typeof(DbContext); _dbContext = (DbContext)Activator.CreateInstance(t, _defaultConnectionString); } return _dbContext; }5 、根据得到的上下文链接SqlConnection ，访问数据库
public class GenericDapperRep : IGenericDapperRep { private IDbContextFactory _factory; protected string _serverName { get; set; } protected string _serviceName { get; set; } protected string _user { get; set; } protected SqlConnection _conn { get { return _factory.GetSqlConnection(); } } public GenericDapperRep(IDbContextFactory factory) { _factory = factory; //factory.SetConnectionUI(false); SetDbContextFactory(factory); } //public GenericDapperRep(IDbContextFactory factory, bool isConnectUI) //{ // _factory = factory; // factory.SetConnectionUI(isConnectUI); // SetDbContextFactory(factory); //} private void SetDbContextFactory(IDbContextFactory factory) { if (factory == null) { throw new ArgumentNullException("factory"); } //_conn = factory.GetSqlConnection(); _serverName = factory.GetServerName(); _serviceName = factory.GetServiceName(); _user = factory.GetUser(); } protected T DbQueryWrapper<T>(Func<T> doWork) { return _factory.DbQueryWrapper(doWork); } protected void Create(string sql, object data) { SetCreate(data); _conn.Execute(sql, data); }1、动态生成访问链接 2、定义访问的上下文DbContext
3、连上数据库操作
