浙江省网站备案注销申请表,网页设置背景颜色,wordpress待发布,用树莓派做网站文章转载授权级别#xff1a;B无论是 Quartz.net 还是 MySql.Data 都是我们比较熟悉的库了#xff0c;Quartz.net 如果配置为使用 MySql 数据库做持久化时#xff0c;默认是硬编码了使用 MySql.Data 来操作 MySql 数据库的。下面是我的一些个人诉求和实践#xff0c;和大家… 文章转载授权级别B无论是 Quartz.net 还是 MySql.Data 都是我们比较熟悉的库了Quartz.net 如果配置为使用 MySql 数据库做持久化时默认是硬编码了使用 MySql.Data 来操作 MySql 数据库的。下面是我的一些个人诉求和实践和大家共同探讨一下。0x01 为什么要替换 MySql.DataMySql.Data 虽然是官方驱动但实际上实现质量并不是很高。我和一些朋友都经历过在使用它做长时间的连接查询时异常崩溃在 老农 菊巨 的推荐以后我一直使用 MySqlConnector 这个 MySql 驱动。因为我的项目都是使用了 MySqlConnector 这个驱动此驱动为了兼容官方的写法类名和命名空间基本保持一致这就导致如果你同时引用这两个库后再想创建 MySqlConnection 对象时编译器无法识别到底是哪一个程序集的类型。因此如果你项目中已经使用了 MySqlConnector那么最好还是替换掉 Quarzt.net 默认使用的驱动。0x02 测试 Quartz.net 使用 MySql.Data在本地 MySql 数据库中创建测试数据库 quartz, 并通过执行脚本 https://raw.githubusercontent.com/dotnetcore/DotnetSpider/master/src/DotnetSpider.Portal/DDL/MySql.sql 来创建所需要的表首先创建一个空的 Console 项目并添加 Quartz 这个包修改 Program.cs 代码如下class Program
{ public class HelloJob : IJob { public Task Execute(IJobExecutionContext context) { Console.WriteLine(${DateTime.Now:yyyy-MM-dd HH:mm:ss} Greetings from HelloJob!); return Task.CompletedTask; } } static async Task Main(string[] args) { IScheduler scheduler null; try { var properties new NameValueCollection { {quartz.jobStore.type, Quartz.Impl.AdoJobStore.JobStoreTX, Quartz}, {quartz.jobStore.dataSource, myDs}, {quartz.serializer.type, binary}, {quartz.dataSource.myDs.provider, MySql}, { quartz.dataSource.myDs.connectionString, Databasequartz;Data Sourcelocalhost;password1qazZAQ!;User IDroot;Port3306; } }; scheduler await new StdSchedulerFactory(properties).GetScheduler(); await scheduler.Start(); if (!await scheduler.CheckExists(new JobKey(job1, group1))) { var trigger TriggerBuilder.Create().WithCronSchedule(*/5 * * * * ?).WithIdentity(trigger1) .Build(); var job JobBuilder.CreateHelloJob().WithIdentity(job1, group1) .RequestRecovery(true).Build(); await scheduler.ScheduleJob(job, trigger); } Console.Read(); } finally { if (scheduler ! null) { await scheduler.Shutdown(); } } } }直接运行可以发现程序异常退出了异常信息如下Unhandled Exception: Quartz.SchedulerException: Could not Initialize DataSource: myDs --- System.ArgumentException: Error while reading metadata information for provider MySql
Parameter name: providerName --- Quartz.SchedulerConfigException: Could not parse property connectionType into correct data type: Could not load file or assembly MySql.Data, Cultureneutral, PublicKeyTokennull. The system cannot find the file specified.--- System.IO.FileNotFoundException: Could not load file or assembly MySql.Data, Cultureneutral, PublicKeyTokennull. The system cannot find the file specified.
可以得到明确的错误提示未能找到 MySql.Data 程序集引用 MySql.Data 包并重新运行程序可以发现程序正常运行了结果如下2019-09-26 22:20:46 Greetings from HelloJob!
2019-09-26 22:20:50 Greetings from HelloJob!
2019-09-26 22:20:55 Greetings from HelloJob!0x03 测试添加 MySqlconnector在上一小节中我们成功的使用 MySql 数据库做为 Quartz 的持久化。在上面代码的基础之上我们直接引用 MysqlConnector 包看一下会有什么影响。运行后发现程序运行正常那是不是意味着 MySql.Data 和 MysqlConnector 可以和平共处呢如果我们想自己使用 MySqlConnection 时会发生什么呢编译器报了如下错误Program.cs(23, 28): [CS0433] The type MySqlConnection exists in both MySql.Data, Version8.0.17.0, Cultureneutral, PublicKeyTokenc5687fc88969c44d and MySqlConnector, Version0.59.0.0, Cultureneutral, PublicKeyTokend33d3e53aa5f8c92
这个结果很容易想到因为两个程序集里有相同的命名空间相同的类型所以无法判断到底应该使用哪个类型。那为什么并不影响 Quartz.net 的正常运行呢答案也是比较容易想到的它用了反射指定了程序集和类型。我们测试如下代码var ass AppDomain.CurrentDomain.Load(MySqlConnector);
var type ass.GetType(MySql.Data.MySqlClient.MySqlConnection);var conn (IDbConnection) Activator.CreateInstance( type, Databasedotnetspider;Data Sourcemy.com;password1qazZAQ!;User IDroot;Port3306;);
有经验的同学肯定知道以上代码是能够正常运行的。0x04 翻看 quartz.net 的源码并尝试替换在查看了 Quartz.net 的源码之后发现会发现针对 MySql 的配置有如下# MySQL
quartz.dbprovider.MySql.productNameMySQL, MySQL provider
quartz.dbprovider.MySql.assemblyNameMySql.Data
quartz.dbprovider.MySql.connectionTypeMySql.Data.MySqlClient.MySqlConnection, MySql.Data
quartz.dbprovider.MySql.commandTypeMySql.Data.MySqlClient.MySqlCommand, MySql.Data
quartz.dbprovider.MySql.parameterTypeMySql.Data.MySqlClient.MySqlParameter, MySql.Data
quartz.dbprovider.MySql.parameterDbTypeMySql.Data.MySqlClient.MySqlDbType, MySql.Data
quartz.dbprovider.MySql.parameterDbTypePropertyNameMySqlDbType
quartz.dbprovider.MySql.parameterNamePrefix?
quartz.dbprovider.MySql.exceptionTypeMySql.Data.MySqlClient.MySqlException, MySql.Data
quartz.dbprovider.MySql.useParameterNamePrefixInParameterCollectiontrue
quartz.dbprovider.MySql.bindByNametrue
quartz.dbprovider.MySql.dbBinaryTypeNameBlob
果然这里 hardcode 的程序为 MySql.Data那么我们是否可以通过修改配置文件来替换数据库驱动呢在第一章节的测试代码中我们添加配置后的代码如下var properties new NameValueCollection
{{quartz.dbprovider.MySql.assemblyName, MySqlConnector},{quartz.jobStore.type, Quartz.Impl.AdoJobStore.JobStoreTX, Quartz},{quartz.jobStore.dataSource, myDs},{quartz.serializer.type, binary},{quartz.dataSource.myDs.provider, MySql},{quartz.dataSource.myDs.connectionString,Databasequartz;Data Sourcelocalhost;password1qazZAQ!;User IDroot;Port3306;}
};
删除干扰因素MySql.Data 包后运行程序发现程序异常如下Unhandled Exception: Quartz.SchedulerException: Could not Initialize DataSource: myDs --- System.ArgumentException: Error while reading metadata information for provider MySql
Parameter name: providerName --- Quartz.SchedulerConfigException: Could not parse property connectionType into correct data type: Could not load file or assembly MySql.Data, Cultureneutral, PublicKeyTokennull. The system cannot find the file specified.--- System.IO.FileNotFoundException: Could not load file or assembly MySql.Data, Cultureneutral, PublicKeyTokennull. The system cannot find the file specified.at System.RuntimeTypeHandle.GetTypeByName(String name, Boolean throwOnError, Boolean ignoreCase, Boolean reflectionOnly, StackCrawlMarkHandle stackMark, IntPtr pPrivHostBinder, Boolean loadTypeFromPartialName, ObjectHandleOnStack type, ObjectHandleOnStack keepalive)at System.RuntimeTypeHandle.GetTypeByName(String name, Boolean throwOnError, Boolean ignoreCase, Boolean reflectionOnly, StackCrawlMark stackMark, IntPtr pPrivHostBinder, Boolean loadTypeFromPartialName)at System.RuntimeTypeHandle.GetTypeByName(String name, Boolean throwOnError, Boolean ignoreCase, Boolean reflectionOnly, StackCrawlMark stackMark, Boolean loadTypeFromPartialName)at System.RuntimeType.GetType(String typeName, Boolean throwOnError, Boolean ignoreCase, Boolean reflectionOnly, StackCrawlMark stackMark)at System.Type.GetType(String typeName, Boolean throwOnError)at Quartz.Util.ObjectUtils.ConvertValueIfNecessary(Type requiredType, Object newValue) in C:\projects\quartznet\src\Quartz\Util\ObjectUtils.cs:line 69at Quartz.Util.ObjectUtils.SetPropertyValue(Object target, String propertyName, Object value) in C:\projects\quartznet\src\Quartz\Util\ObjectUtils.cs:line 200at Quartz.Util.ObjectUtils.SetObjectProperties(Object obj, NameValueCollection props) in C:\projects\quartznet\src\Quartz\Util\ObjectUtils.cs:line 151--- End of inner exception stack trace ---at Quartz.Util.ObjectUtils.SetObjectProperties(Object obj, NameValueCollection props) in C:\projects\quartznet\src\Quartz\Util\ObjectUtils.cs:line 154at Quartz.Impl.AdoJobStore.Common.EmbeddedAssemblyResourceDbMetadataFactory.GetDbMetadata(String providerName) in C:\projects\quartznet\src\Quartz\Impl\AdoJobStore\Common\EmbeddedAssemblyResourceDbMetadataFactory.cs:line 64--- End of inner exception stack trace ---at Quartz.Impl.AdoJobStore.Common.EmbeddedAssemblyResourceDbMetadataFactory.GetDbMetadata(String providerName) in C:\projects\quartznet\src\Quartz\Impl\AdoJobStore\Common\EmbeddedAssemblyResourceDbMetadataFactory.cs:line 70at Quartz.Impl.AdoJobStore.Common.DbProvider.GetDbMetadata(String providerName) in C:\projects\quartznet\src\Quartz\Impl\AdoJobStore\Common\DbProvider.cs:line 112at Quartz.Impl.AdoJobStore.Common.DbProvider..ctor(String dbProviderName, String connectionString) in C:\projects\quartznet\src\Quartz\Impl\AdoJobStore\Common\DbProvider.cs:line 74at Quartz.Impl.StdSchedulerFactory.Instantiate() in C:\projects\quartznet\src\Quartz\Impl\StdSchedulerFactory.cs:line 607--- End of inner exception stack trace ---at Quartz.Impl.StdSchedulerFactory.Instantiate() in C:\projects\quartznet\src\Quartz\Impl\StdSchedulerFactory.cs:line 616at Quartz.Impl.StdSchedulerFactory.GetScheduler(CancellationToken cancellationToken) in C:\projects\quartznet\src\Quartz\Impl\StdSchedulerFactory.cs:line 1114at quartz.demo.Program.Main(String[] args) in /Users/lewis/Documents/codes/quartz.demo/quartz.demo/Program.cs:line 44at quartz.demo.Program.Main(String[] args) in /Users/lewis/Documents/codes/quartz.demo/quartz.demo/Program.cs:line 67at quartz.demo.Program.Main(String[] args)可以猜测这个配置项目并没有起作用。通过堆栈信息我们发现错误是在 DbProvider.cs:line 74以此为突破口逐行代码扫过去原来 DbProvider 静态加载了2个 DbMetadataFactory一个是通过配置文件、另一个是通过内嵌的资源文件而配置文件它却是通过 ConfigurationManager 这个类来获取的也就是说我们代码里的配置并没有真正的生效。于是我们可以在项目中添加 App.config 文件并修改内容为如下?xml version1.0 encodingutf-8 ?
configurationconfigSectionssection namequartz typeSystem.Configuration.NameValueSectionHandler, System.Configuration.ConfigurationManager, Version4.0.1.0, Cultureneutral, PublicKeyTokencc7b13ffcd2ddd51 //configSectionsquartzadd keyquartz.dbprovider.MySql.assemblyName valueMySqlConnector /add keyquartz.dbprovider.MySql.commandType valueMySql.Data.MySqlClient.MySqlCommand, MySqlConnector/add keyquartz.dbprovider.MySql.parameterType valueMySql.Data.MySqlClient.MySqlParameter, MySqlConnector/add keyquartz.dbprovider.MySql.parameterDbType valueMySql.Data.MySqlClient.MySqlDbType, MySqlConnector/add keyquartz.dbprovider.MySql.parameterDbTypePropertyName valueMySqlDbType/add keyquartz.dbprovider.MySql.connectionType valueMySql.Data.MySqlClient.MySqlConnection, MySqlConnector/add keyquartz.dbprovider.MySql.parameterNamePrefix value?/add keyquartz.dbprovider.MySql.exceptionType valueMySql.Data.MySqlClient.MySqlException, MySqlConnector//quartz
/configuration
重启程序可以发现正常运行了回过头来看代码我们能够发现驱动相关的配置是从内嵌配置和 App.config 中读取的而数据库实例的配置则可以通过参数传递到 Factory 中。那么是否可以完全通过配置文件来设置呢修改配置文件如下?xml version1.0 encodingutf-8 ?
configurationconfigSectionssection namequartz typeSystem.Configuration.NameValueSectionHandler, System.Configuration.ConfigurationManager, Version4.0.1.0, Cultureneutral, PublicKeyTokencc7b13ffcd2ddd51 //configSectionsquartzadd keyquartz.dbprovider.MySql.assemblyName valueMySqlConnector /add keyquartz.dbprovider.MySql.commandType valueMySql.Data.MySqlClient.MySqlCommand, MySqlConnector/add keyquartz.dbprovider.MySql.parameterType valueMySql.Data.MySqlClient.MySqlParameter, MySqlConnector/add keyquartz.dbprovider.MySql.parameterDbType valueMySql.Data.MySqlClient.MySqlDbType, MySqlConnector/add keyquartz.dbprovider.MySql.parameterDbTypePropertyName valueMySqlDbType/add keyquartz.dbprovider.MySql.connectionType valueMySql.Data.MySqlClient.MySqlConnection, MySqlConnector/add keyquartz.dbprovider.MySql.parameterNamePrefix value?/add keyquartz.dbprovider.MySql.exceptionType valueMySql.Data.MySqlClient.MySqlException, MySqlConnector/add keyquartz.jobStore.type valueQuartz.Impl.AdoJobStore.JobStoreTX, Quartz/add keyquartz.jobStore.dataSource valuemyDs/add keyquartz.serializer.type valuebinary/add keyquartz.dataSource.myDs.provider valueMySql/add keyquartz.dataSource.myDs.connectionString valueDatabasequartz;Data Sourcelocalhost;password1qazZAQ!;User IDroot;Port3306;//quartz
/configuration
同时代码中修改一行scheduler await new StdSchedulerFactory().GetScheduler();
重启程序后得到结论程序正常运行。0x05 初步小结 至此基本上已经清楚了因为我基本上已经不再使用 App.config当我尝试使用 quarzt.net 从网上找到的资料大多是配置 *.properties 文件于是为了找替代我通过构造传入了必要参数进 factory 来构造 scheduler。但这个参数迷惑了我以为所有的配置都会从此生效实际上是对数据库驱动相关的DbMetadata) 是只能通过配置文件来配置。0x06 其它方式因为现在我们基本上都是使用 appsettings.json 来做配置了 App.config 实在是使用得少可能有人就会对此有癖好不想添加一个新的配置文件来解决这个问题。那么接着往下分析代码我们会发现实际上作者提供了一个配置quartz.dataSource.myDs.connectionProvider.type
即我们是可以自己实际数据库相关的 Provider 的于是我们回到第一小节的 NameCollection 中修改为只有一个配置{ quartz.dataSource.myDs.connectionProvider.type, quartz.demo.MySqlDbProvider, quartz.demo}
同时添加如下 Provider 的实现public class MySqlDbProvider : IDbProvider
{public string Provider { get; set; }public MySqlDbProvider(){ConnectionString Databasequartz;Data Sourcelocalhost;password1qazZAQ!;User IDroot;Port3306;;var metadata new MySqlMetadata();Metadata metadata;}public void Initialize(){}public DbCommand CreateCommand(){return new MySqlCommand();}public DbConnection CreateConnection(){return new MySqlConnection(ConnectionString);}public void Shutdown(){}public string ConnectionString { get; set; }public DbMetadata Metadata { get; }
}public class MySqlMetadata : DbMetadata
{private readonly Enum _dbBinaryType;public MySqlMetadata(){var dbBinaryTypeName Blob;_dbBinaryType (Enum) Enum.Parse(Type.GetType(MySql.Data.MySqlClient.MySqlDbType, MySqlConnector),dbBinaryTypeName);DbBinaryTypeName dbBinaryTypeName;var parameterDbTypePropertyName MySqlDbType;ParameterDbTypePropertyName parameterDbTypePropertyName;ConnectionType Type.GetType(MySql.Data.MySqlClient.MySqlConnection, MySqlConnector);CommandType Type.GetType(MySql.Data.MySqlClient.MySqlCommand, MySqlConnector);ParameterDbType Type.GetType(MySql.Data.MySqlClient.MySqlDbType, MySqlConnector);ParameterType Type.GetType(MySql.Data.MySqlClient.MySqlParameter, MySqlConnector);ParameterDbTypeProperty ParameterType.GetProperty(MySqlDbType);if (ParameterDbTypeProperty null){throw new ArgumentException($Couldnt parse parameter db type for database type {ProductName});}ExceptionType Type.GetType(MySql.Data.MySqlClient.MySqlException, MySqlConnector);}public override string AssemblyName MySqlConnector;public override string ProductName MySQL, MySQL provider;public override string ParameterNamePrefix ?;public override bool BindByName true;public override Enum DbBinaryType _dbBinaryType;
}
直接运行验证是不是正常。以上 Provider 实现是 hardcode 各项值实际可以通过从 appsettings.json 中读取来设置。这样就达到统一使用一个驱动库不添加额外配置文件0x07 总结开源的好处就是我们可以直接查阅代码去学习并找出其中的关键点然后自己可以做各种各样的 workaround。.NET 在国内的情况是真的岌岌可危了希望 .neter 能够多花时间在开源生态的建设上少打嘴炮多研究代码多写代码。
