1 实现
CustomDbContext扩展了DbContext，其构造函数带有形式参nameOrConnectionString，可以在使用CustomDbContext时指定数据库连接字符串。
DbContextFactory包含两个属性MasterDbContext和SlaveDbContext，MasterDbContext为主库上下文，SlaveDbContext为从库上下文。DbContextFactory还包含了一个方法：UpdateSlaves用于实现对SlaveDbContext的更新，因为SlaveDbContext是从多个配置的从库随机取出一个，因此定时检测不可用从库，将其从从库集合中剔除。
JobScheduler为定时任务规划器，使用Quartz实现。quartz.config和quartz_jobs.xml为定时任务配置文件。
为了使定时任务工作，在WebApiApplication类的Application_Start()函数应添加：
JobScheduler jobScheduler = new JobScheduler(); jobScheduler.log4netPath = AppSettings.Log4netPathForWeb; jobScheduler.OnStart();
关键代码
/// <summary> /// 自定义上下文 /// </summary> [DbConfigurationType(typeof(MySqlEFConfiguration))] public class CustomDbContext:DbContext { public CustomDbContext(string nameOrConnectionString) : base(nameOrConnectionString) { Database.SetInitializer<CustomDbContext>(null); } ...... public DbSet<Collection> Collections { get; set; } public DbSet<CollectionUser> CollectionUsers { get; set; } ...... protected override void OnModelCreating(DbModelBuilder modelBuilder) { base.OnModelCreating(modelBuilder); EntityConfiguration.Set(modelBuilder); } } public class EntityConfiguration { public static void Set(DbModelBuilder modelBuilder) { modelBuilder.Entity<Collection>().Property(c => c.FileName) .IsUnicode(false) .IsRequired() .HasMaxLength(50); modelBuilder.Entity<Collection>().Property(c => c.TableName) .IsUnicode(false) .IsRequired() .HasMaxLength(50); modelBuilder.Entity<Collection>().Property(c => c.Title) .IsUnicode(false) .IsRequired() .HasMaxLength(200); modelBuilder.Entity<Collection>().Property(c => c.Author) .IsUnicode(false) .IsOptional() .HasMaxLength(50); modelBuilder.Entity<Collection>().Property(c => c.PublicationName) .IsUnicode(false) .IsOptional() .HasMaxLength(200); modelBuilder.Entity<Collection>().Property(c => c.DiscNo) .IsUnicode(false) .IsOptional() .HasMaxLength(50); modelBuilder.Entity<Collection>().Property(c => c.ResourceType) .IsUnicode(false) .IsOptional() .HasMaxLength(50); modelBuilder.Entity<Collection>().Property(c => c.PublisherUnit) .IsUnicode(false) .IsOptional() .HasMaxLength(200); modelBuilder.Entity<Collection>().Property(c => c.Year) .IsUnicode(false) .IsOptional() .HasMaxLength(10); modelBuilder.Entity<Collection>().Property(c => c.Period) .IsUnicode(false) .IsOptional() .HasMaxLength(10); modelBuilder.Entity<Collection>().Property(c => c.PublicationDate) .IsOptional(); modelBuilder.Entity<Collection>().Property(c => c.Downloads) .IsOptional(); modelBuilder.Entity<Collection>().Property(c => c.CitationNumber) .IsOptional(); } } /// <summary> /// db上下文工厂 /// </summary> public class DbContextFactory { private static List<string> allSlaves = GetAllSlaves(); private DbContextFactory() { } /// <summary> /// 主 /// </summary> public static CustomDbContext MasterDbContext { get { return new CustomDbContext("name=Master"); } } /// <summary> /// 从 /// </summary> public static CustomDbContext SlaveDbContext { get { Random rm = new Random(); if (allSlaves.Count > 0) { int i = rm.Next(allSlaves.Count); string name = string.Format("name={0}", allSlaves.ElementAt(i)); return new CustomDbContext(name); } else { return MasterDbContext; } } } /// <summary> /// 获得所有可用连接 /// </summary> /// <returns></returns> private static List<string> GetAllSlaves() { List<string> connNames = new List<string>(); var conns = ConfigurationManager.ConnectionStrings; if (conns == null) { throw new Exception("ConfigurationManager.ConnectionStrings 是空值，请检查Web.config"); } var masterConn = conns["Master"]; if (masterConn == null) { throw new Exception("名称为Master的连接配置不存在，请检查Web.config"); } //conn中必然包含master，还有一个默认的LocalSqlServer int connCount = conns.Count - 1; if (connCount == 0) { throw new Exception("连接配置中只包含Master，不包含任何Slave，请检查Web.config，并配置Slave"); } for (int i = 0; i < connCount; i++) { string connName = string.Format("Slave{0}", i); var conn = ConfigurationManager.ConnectionStrings[connName]; if (conn == null) { string msg = string.Format("{0}不存在，请检查配置Web.config", connName); throw new Exception(msg); } //检测是否可连接 bool canConn = CanConnect(connName); if (canConn) { connNames.Add(connName); } } return connNames; } public static void UpdateSlaves() { allSlaves = GetAllSlaves(); if (allSlaves.Count == 0) { allSlaves.Add("Master"); } } private static bool CanConnect(string connName) { bool ret = false; DbConnection dbConnection = null; try { string connStr = ConfigurationManager.ConnectionStrings[connName].ToString(); MySqlConnectionFactory factory = new MySqlConnectionFactory(); dbConnection = factory.CreateConnection(connStr); dbConnection.Open();//打不开会抛异常 ret = true; } catch (Exception ex) { } finally { if (dbConnection != null && dbConnection.State == System.Data.ConnectionState.Open) dbConnection.Close(); } return ret; } } public class JobScheduler { /// <summary> /// log4net配置文件位置 /// </summary> public string log4netPath { get; set; } private IScheduler scheduler; public JobScheduler() { } public void OnStart() { //构造函数自动加载Quartz.config,并通过quartz.plugin.xml.fileNames加载~/quartz_jobs.xml try { if (string.IsNullOrWhiteSpace(log4netPath)) { log4netPath = AppSettings.Log4netPathForApp; } //加载日志 LogConfigLoading.Load(log4netPath); ISchedulerFactory sf = new StdSchedulerFactory(); scheduler = sf.GetScheduler(); scheduler.Start(); } catch (Exception ex) { LogHelper.LogError(ex, "JobScheduler"); } } public void OnStop() { if (scheduler != null && scheduler.IsStarted) { scheduler.Shutdown(false); } } public void OnPause() { if (scheduler != null && scheduler.IsStarted) { scheduler.PauseAll(); } } public void OnContinue() { if (scheduler != null) { bool isJobGroupPaused = false; var groups = scheduler.GetJobGroupNames(); foreach (var group in groups) { isJobGroupPaused = scheduler.IsJobGroupPaused(group); if (!isJobGroupPaused) { break; } } if (isJobGroupPaused) { scheduler.ResumeAll(); } } } } public class DbMonitorJob : IJob { public void Execute(IJobExecutionContext context) { DbContextFactory.UpdateSlaves(); } }
定时器配置文件quartz_jobs.xml
<?xml version="1.0" encoding="UTF-8"?> <!-- This file contains job definitions in schema version 2.0 format --> <job-scheduling-data xmlns= "http://quartznet.sourceforge.net/JobSchedulingData" xmlns:xsi ="http://www.w3.org/2001/XMLSchema-instance " version ="2.0 "> <processing-directives> <overwrite-existing-data>true</overwrite-existing-data> </processing-directives> <schedule> <job> <name>DbMonitorJob</name> <group>DbMonitorJobGroup</group> <description>更新可用从库</description> <job-type>HY_WebApi.TaskScheduler.Jobs.DbMonitorJob, HY_WebApi.TaskScheduler</job-type> <durable>true</durable> <recover>false</recover> </job> <trigger> <simple> <name>DbMonitorJobTrigger</name> <group>DbMonitorJobTriggerGroup</group> <description>更新可用从库</description> <job-name>DbMonitorJob</job-name> <job-group>DbMonitorJobGroup</job-group> <misfire-instruction>SmartPolicy</misfire-instruction> <repeat-count>-1</repeat-count> <repeat-interval>10000</repeat-interval> </simple> </trigger> </schedule> </job-scheduling-data>
定时任务配置文件quartz.config
# You can configure your scheduler in either <quartz> configuration section # or in quartz properties file # Configuration section has precedence quartz.scheduler.instanceName = ServerScheduler # configure thread pool info quartz.threadPool.type = Quartz.Simpl.SimpleThreadPool, Quartz quartz.threadPool.threadCount = 1 quartz.threadPool.threadPriority = Normal # job initialization plugin handles our xml reading, without it defaults are used quartz.plugin.xml.type = Quartz.Plugin.Xml.XMLSchedulingDataProcessorPlugin, Quartz quartz.plugin.xml.fileNames = ~quartz_jobs.xml # export this server to remoting context quartz.scheduler.exporter.type = Quartz.Simpl.RemotingSchedulerExporter, Quartz quartz.scheduler.exporter.port = 555 quartz.scheduler.exporter.bindName = QuartzScheduler quartz.scheduler.exporter.channelType = tcp quartz.scheduler.exporter.channelName = httpQuartz
web项目配置文件Web.config
<configuration> <connectionStrings> <clear/><!--清除默认的连接字符串，务必加上！！！--> <add name="Master" connectionString="Database=hy_webapi_n;Data Source=192.168.107.65;User Id=root;Password=cnki2016;CharSet=utf8;port=3306" providerName="MySql.Data.MySqlClient" /> <add name="Slave0" connectionString="Database=hy_webapi_n;Data Source=192.168.107.62;User Id=root;Password=cnki2016;CharSet=utf8;port=3306" providerName="MySql.Data.MySqlClient" /> <add name="Slave1" connectionString="Database=hy_webapi_n;Data Source=192.168.107.63;User Id=root;Password=cnki2016;CharSet=utf8;port=3306" providerName="MySql.Data.MySqlClient" /> </connectionStrings> ...... </configuration>
加载定时器
public class WebApiApplication : System.Web.HttpApplication { protected void Application_Start() { GlobalConfiguration.Configure(WebApiConfig.Register); JobScheduler jobScheduler = new JobScheduler(); jobScheduler.log4netPath = AppSettings.Log4netPathForWeb; jobScheduler.OnStart(); } }
2 代码分析
最核心的部分是DbContextFactory。下面详细分析其设计与实现。
获得web.config配置文件中的连接名称
使用静态私钥变量allSlaves来表示从库集合，这样做的好处是：静态私有变量只在使用前初始化一次，当第一次被allSlaves使用时初始化一次，即调用GetAllSlaves()方法获得所有可用的从库。当第二次使用allSlaves时，即当SlaveDbContext属性第二次被调用时，不在计算allSlaves。大部分时间都花费在测试数据库是否可用，因此不在重复计算allSlaves节省了时间。直接的效果就是由于检测数据库是否可用的影响可以忽略不计。
不可使用单例模式
由于检测数据库是否可用相对耗费时间的比例较大，于是想到通过单例模式来实现DbContextFactory，这样会导致系统报错：The operation cannot be completed because the DbContext has been disposed.其原因就在于使用DbContext时，慎重使用单例模式，全局的DbContext会引起第二次调用出错，即第一次调用后DbContext资源即被释放。
类似于单例模式的实现，即全局的DbContext，也是不可取的。
基于上述考虑设计实现SlaveDbContext，在每次被调用时，都会返回一个新的实例。
多从库随机选择
当配置了多个从库时，应随机从从库集合中选择一个。于是使用伪随机数生成器Random。
所有从库不可用时切换到主库
当所有从库都不可用时，SlaveDbContext值为MasterDbContext。这里还应该增加一个额外的监测服务，当有从库不可用时自动报警，供系统维护人员查看。
注意先写后读的操作
对于这种操作，若主从同步延迟稍大，那么会造成操作失败，解决的办法是：只操作主库。保守的做法就是只操作主库，一般主从分部在内网的两台机器上，网络通信延迟一旦较大时，就会造成数据无法同步的假象。
-----------------------------------------------------------------------------------------
转载与引用请注明出处。
时间仓促，水平有限，如有不当之处，欢迎指正。