Quartz.Net框架实现表监听和作业调度
最近在项目中需要用到Quartz框架,该框架有java版本也有.Net版本的,后者的版本叫Quartz.Net,和java版本的用法几乎是一样的,只是底层代码的实现采用C#。
首先说下具体需求吧:当数据库某些指定表发生数据变化(增删改),则触发一个作业,该作业又根据选定的定时信息进行调度 比如:从某刻时候开始 每隔一秒钟执行一次作业,直到某个指定的时间则停止。
特别需要注意的是:在这个框架里面有两种定时的配置
1.简单表达式配置。
2.corn表达式配置。(也就类似于我们的js正则表达式)
其中简单表达式中,采用的是链式操作例如:
ITrigger trigger1 = TriggerBuilder.Create().WithIdentity("mytrigger" + a.ToString(), "group1").StartAt(job.Nowtask).EndAt(job.End).WithSimpleSchedule(x => x.WithIntervalInMinutes(value).RepeatForever()).Build();
这里表示的是从什么时候开始使用 StartAt() ,从什么时候结束使用EndAt(),参数都是DateTime类型哦,每隔几秒执行一次WithSimpleSchedule(x=>x.WithIntervalInMinutes().RepeatForever())
其中 WithIntervalInMinutes() 输入参数为int型 。
要注意的是如果使用corn表达式 StartAt() 就无效,程序只会根据时间表达式的具体情况执行,笔者不推荐这种使用方式,在某些特定场合比如 每个月的第一个星期三开始执行一次任务,则使用这种方式较好。
以下附上源码:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Common;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;
using Quartz;
using Quartz.Impl;
using System.Threading.Tasks;
using System.Xml;
using System.IO;
using System.Threading;
using AutoPhoto.Model;
namespace AutoPhoto.DAL
{
class MyProject
{
//企业主键
private static readonly String XF_MALLID = ConfigurationManager.AppSettings["xf_mall"].ToString();
//调度器
private static IScheduler scheduler = null;
//初始化待执行的任务信息集合(每执行一条任务后 直接新增下一时刻的定时任务,不新增、编辑此集合)
private static List<Timejob> jobs;
//定时配置表 表监听 没有企业编号
public static String Sql_Listeners_2 = "SELECT ZK_TimeId ,ZK_TimeType ,ZK_TimeValue ,ZK_StartTime FROM dbo.ZK_TIME";
//设备控制条件表监听
private static String Sql_Listeners_1 = String.Format(@"SELECT ZK_ConditionID ,ZK_ControlPLCID ,ZK_CollectPLCID ,ZK_ControlParamID ,ZK_CollectParamID, ZK_CommandColocationID,ZK_IsUsing,ZK_ControlType,ZK_TimeId,ZK_StartTime,
ZK_EndTime
FROM [dbo].[ZK_CONTROLPARAM_CONDITION]
WHERE XF_MALLID = '{0}' AND ZK_IsUsing='1' and ZK_ControlType='1' and ZK_TimeId IS NOT NULL AND ZK_TimeId != ''", XF_MALLID);
//开启数据库监听
public void StartSqlListeners()
{
try
{
using (DataAccess ds = new DataAccess())
{
ds.StartListenPLcManagement(dependency_OnChange_1, Sql_Listeners_1);
ds.StartListenPLcManagement(dependency_OnChange_2, Sql_Listeners_2);
}
}
catch (Exception ex)
{
Logger.LogTxt("******************SQLServer监听启动******************");
Logger.LogTxt(" 异常原因:" + ex.Message);
}
}
//摄像头-批次表的触发事件
private void dependency_OnChange_1(object sender, SqlNotificationEventArgs e)
{
try
{
Init();
using (DataAccess ds = new DataAccess())
{
ds.StartListenPLcManagement(dependency_OnChange_1, Sql_Listeners_1);
}
}
catch (Exception ex)
{
Logger.LogTxt("******************SQLServer监听改变******************");
Logger.LogTxt(" 异常原因:" + ex.Message);
}
}
//定时配置表的触发事件
private void dependency_OnChange_2(object sender, SqlNotificationEventArgs e)
{
try
{
Init();
using (DataAccess ds = new DataAccess())
{
ds.StartListenPLcManagement(dependency_OnChange_2, Sql_Listeners_2);
}
}
catch (Exception ex)
{
Logger.LogTxt("******************SQLServer监听改变******************");
Logger.LogTxt(" 异常原因:" + ex.Message);
}
}
public void Init()
{
jobs = new List<Timejob>();
DataTable dt_timing = new DataTable();
// DataTable dt_timings = new DataTable();
String sqltiming = String.Format(@"
select ZK_ConditionID ,T0.ZK_CommandColocationID,T1.ZK_TimeId,T1.ZK_TimeType,T1.ZK_TimeValue,T1.ZK_StartTime as sfm , T0.ZK_StartTime as nyr , T0.ZK_EndTime
from dbo.ZK_CONTROLPARAM_CONDITION T0 ,dbo.ZK_TIME T1
where T1.ZK_TimeId = T0.ZK_TimeId and T0.ZK_IsUsing='1' and T0.ZK_ControlType='1' and T0.XF_MALLID='{0}' and (T0.ZK_EndTime IS NULL OR T0.ZK_EndTime >'{1}') ", XF_MALLID, DateTime.Now);
try
{
using (DataAccess ds=new DataAccess())
{
dt_timing = ds.GetTable(sqltiming);
}
}
catch (Exception)
{
throw;
}
if (dt_timing !=null)
{
for (int i = 0; i < dt_timing.Rows.Count; i++)
{
Timejob job = new Timejob();
//先获取开始时间年月日
String nyr = (Convert.ToDateTime(dt_timing.Rows[i]["nyr"].ToString())).ToString("d");
//获取 开始时间时分秒
String sfm = (Convert.ToDateTime(dt_timing.Rows[i]["sfm"].ToString())).ToLongTimeString();
//生成具体第一次开始时间
DateTime time = Convert.ToDateTime(nyr + " " + sfm); //分开提取时分秒
//当前时间
DateTime nowtime = DateTime.Now;
//如果开始时间在现在时间之前,那么按照选定的间隔类型累加到大于等于现在的时间 经过这个循环可以得到真正的开始时间
while (time < nowtime)
{
if (dt_timing.Rows[i]["ZK_TimeType"].ToString() == "1") //间隔为天
{
time = nowtime.AddDays(Int32.Parse(dt_timing.Rows[i]["ZK_TimeValue"].ToString()));
}
else if (dt_timing.Rows[i]["ZK_TimeType"].ToString() == "0") //间隔为时
{
time = nowtime.AddHours(Int32.Parse(dt_timing.Rows[i]["ZK_TimeValue"].ToString()));
}
else if (dt_timing.Rows[i]["ZK_TimeType"].ToString() == "2") //间隔为分
{
time = nowtime.AddMinutes(Int32.Parse(dt_timing.Rows[i]["ZK_TimeValue"].ToString()));
}
else if (dt_timing.Rows[i]["ZK_TimeType"].ToString() == "3") //间隔为秒
{
time = nowtime.AddSeconds(Int32.Parse(dt_timing.Rows[i]["ZK_TimeValue"].ToString()));
}
}
//给任务对象赋值
job.Nowtask = time;
job.job_xf = XF_MALLID;
job.job_ID = Helper.BuildCode();
job.job_ZK_ConditionID = dt_timing.Rows[i]["ZK_ConditionID"].ToString();
job.job_ZK_CommandColocationID = dt_timing.Rows[i]["ZK_CommandColocationID"].ToString();
job.job_ZK_TimeId = dt_timing.Rows[i]["ZK_TimeId"].ToString();
job.job_ZK_TimeType = dt_timing.Rows[i]["ZK_TimeType"].ToString();
job.job_ZK_TimeValue = dt_timing.Rows[i]["ZK_TimeValue"].ToString();
//job.End = Convert.ToDateTime(dt_timing.Rows[i]["ZK_EndTime"].ToString());
//没有结束时间就为空 有的话就赋值 下面End为可空类型
if (!String.IsNullOrEmpty(dt_timing.Rows[i]["ZK_EndTime"].ToString()))
job.End = Convert.ToDateTime(dt_timing.Rows[i]["ZK_EndTime"].ToString());
//加入到任务集合
jobs.Add(job);
}
//以下为打印即将要执行任务的信息给用户看 也便于测试
int count = 0;
foreach (Timejob i in jobs)
{
count += 1;
Console.WriteLine("*****************************将要执行的任务*****************************");
Console.WriteLine(" 任务编号:" + i.job_ID);
Console.WriteLine(" 开始时间:" + i.Nowtask);
Console.WriteLine(" 间隔类型:" + (i.job_ZK_TimeType == "1" ? "天" : (i.job_ZK_TimeType == "0" ? "时" : (i.job_ZK_TimeType == "2" ? "分" : "秒"))));
Console.WriteLine(" 间隔值:" + i.job_ZK_TimeValue);
Console.WriteLine();
}
if (count == 0)
Console.WriteLine("暂时没有任务");
SetQuartz(jobs);
}
else
{
Console.WriteLine("暂时没有任务");
}
}
//设置定时任务
public void SetQuartz(List<Timejob> jobs)
{
scheduler = StdSchedulerFactory.GetDefaultScheduler();
//每次触发一次表监听要经过这里清楚一次
scheduler.Clear();
scheduler.Start();
if (jobs.Count > 0)
{
int a = 0;
foreach (Timejob job in jobs)
{
//所有的作业和触发器都放在一个组里面
IJobDetail job1 = JobBuilder.Create<Myjob>().WithIdentity("myjob" + a.ToString(), "group1").Build();
//传给执行的任务的数据库搬迁函数作为输入参数
job1.JobDataMap.Put("job", Helper.ObjToJson(job));
int value = Convert.ToInt32(job.job_ZK_TimeValue); //间隔值
if (job.job_ZK_TimeType.ToString() == "1") //天
{
if (job.End.HasValue) //有结束时间
{
ITrigger trigger1 = TriggerBuilder.Create().WithIdentity("mytrigger" + a.ToString(), "group1").StartAt(job.Nowtask).EndAt(job.End).WithSimpleSchedule(x => x.WithIntervalInHours(value * 24).RepeatForever()).Build(); //小时*24
scheduler.ScheduleJob(job1, trigger1);
}
else //没有结束时间
{
ITrigger trigger1 = TriggerBuilder.Create().WithIdentity("mytrigger" + a.ToString(), "group1").StartAt(job.Nowtask).WithSimpleSchedule(x => x.WithIntervalInHours(value * 24).RepeatForever()).Build(); //小时*24
scheduler.ScheduleJob(job1, trigger1);
}
}
else if (job.job_ZK_TimeType.ToString() == "0") //时
{
if (job.End.HasValue) //有结束时间
{
ITrigger trigger1 = TriggerBuilder.Create().WithIdentity("mytrigger" + a.ToString(), "group1").StartAt(job.Nowtask).EndAt(job.End).WithSimpleSchedule(x => x.WithIntervalInHours(value).RepeatForever()).Build();
scheduler.ScheduleJob(job1, trigger1);
}
else //没有结束时间
{
ITrigger trigger1 = TriggerBuilder.Create().WithIdentity("mytrigger" + a.ToString(), "group1").StartAt(job.Nowtask).WithSimpleSchedule(x => x.WithIntervalInHours(value).RepeatForever()).Build();
scheduler.ScheduleJob(job1, trigger1);
}
}
else if (job.job_ZK_TimeType.ToString() == "2") //分
{
if (job.End.HasValue) //有结束时间
{
ITrigger trigger1 = TriggerBuilder.Create().WithIdentity("mytrigger" + a.ToString(), "group1").StartAt(job.Nowtask).EndAt(job.End).WithSimpleSchedule(x => x.WithIntervalInMinutes(value).RepeatForever()).Build();
scheduler.ScheduleJob(job1, trigger1);
}
else //没有结束时间
{
ITrigger trigger1 = TriggerBuilder.Create().WithIdentity("mytrigger" + a.ToString(), "group1").StartAt(job.Nowtask).WithSimpleSchedule(x => x.WithIntervalInMinutes(value).RepeatForever()).Build();
scheduler.ScheduleJob(job1, trigger1);
}
}
else if (job.job_ZK_TimeType.ToString() == "3") //秒
{
if (job.End.HasValue) //有结束时间
{
ITrigger trigger1 = TriggerBuilder.Create().WithIdentity("mytrigger" + a.ToString(), "group1").StartAt(job.Nowtask).EndAt(job.End).WithSimpleSchedule(x => x.WithIntervalInSeconds(value).RepeatForever()).Build();
scheduler.ScheduleJob(job1, trigger1);
}
else //没有结束时间
{
ITrigger trigger1 = TriggerBuilder.Create().WithIdentity("mytrigger" + a.ToString(), "group1").StartAt(job.Nowtask).WithSimpleSchedule(x => x.WithIntervalInSeconds(value).RepeatForever()).Build();
scheduler.ScheduleJob(job1, trigger1);
}
}
a = a + 1;
}
}
}
}
//具体业务代码
[DisallowConcurrentExecution]
class Myjob : IJob
{
public void Execute(IJobExecutionContext context)
{
MyProject myproject = new MyProject(); //创建对象才能引用另外一个类的东西
Console.WriteLine("定时器在执行");
JobDataMap data = context.JobDetail.JobDataMap;
Timejob job = Helper.JsonToObj<Timejob>(data.GetString("job")); //拿到传过来的对象
//把命令插入到待发送命令表里面去
// ds.RunQuery(sqlString); insert语句可以直接使用
//先把所有信息先查出来放在一个对象里面
String myselectsql = String.Format(@"select t0.XF_MALLID, t1.ZK_CommandDictionaryID, t1.ZK_CommandName,t1.ZK_CommandRemark,t1.ZK_SlaveUnitAdd,t1.ZK_FunCode,t1.ZK_BaseAddress,t1.ZK_RegisterCount,t1.ZK_CRCCode,t1.ZK_TcpCommand,t1.ZK_CommandType,
t2.ZK_ParamID,t2.ZK_ParamName,t3.ZK_PLCID,t3.ZK_GatewayID,t3.ZK_TableSuffix,t4.ZK_ConfigID,t4.ZK_SheetName
from ZK_COMMAND_COLOCATION t0 ,ZK_COMMAND_DICTIONARY t1,ZK_PARAM_CONFIG t2,ZK_PLCDEVICES_MANAGEMENT t3,ZK_PLCDEVICES_CONFIG t4
where t0.ZK_CommandColocationID='{0}' and t0.ZK_CommandDictionaryID=t1.ZK_CommandDictionaryID and t0.ZK_ParamID=t2.ZK_ParamID and t0.ZK_PLCID=t3.ZK_PLCID and t2.ZK_ConfigID=t4.ZK_ConfigID
and t0.EB_ISDELETE='0' and t1.EB_ISDELETE='0' and t2.EB_ISDELETE='0' and t3.EB_ISDELETE='0' and t4.EB_ISDELETE='0' " ,job.job_ZK_CommandColocationID.ToString() );
DataTable myselect = new DataTable();
try
{
using (DataAccess ds = new DataAccess())
{
myselect = ds.GetTable(myselectsql);
}
}
catch (Exception)
{
throw;
}
ZK_COMMAND_REALTIME comm = new ZK_COMMAND_REALTIME();
//给对象赋值 主键不用赋
comm.ZK_PLCID = myselect.Rows[0]["ZK_PLCID"].ToString();
comm.ZK_GatewayID = myselect.Rows[0]["ZK_GatewayID"].ToString();
comm.ZK_CommandSource = myselect.Rows[0]["ZK_CommandName"].ToString();
comm.ZK_CommandName = myselect.Rows[0]["ZK_CommandDictionaryID"].ToString();
comm.ZK_CommandRemark = myselect.Rows[0]["ZK_CommandRemark"].ToString();
comm.ZK_SlaveUnitAdd = myselect.Rows[0]["ZK_SlaveUnitAdd"].ToString();
comm.ZK_FunCode = myselect.Rows[0]["ZK_FunCode"].ToString();
comm.ZK_BaseAddress = myselect.Rows[0]["ZK_BaseAddress"].ToString();
comm.ZK_RegisterCount = myselect.Rows[0]["ZK_RegisterCount"].ToString();
comm.ZK_CRCCode = myselect.Rows[0]["ZK_CRCCode"].ToString();
comm.ZK_CommandType = myselect.Rows[0]["ZK_CommandType"].ToString();
comm.ZK_SourceType = "3";
comm.ZK_ConfigID = myselect.Rows[0]["ZK_ConfigID"].ToString();
comm.ZK_SheetName = myselect.Rows[0]["ZK_SheetName"].ToString();
comm.ZK_ParamID = myselect.Rows[0]["ZK_ParamID"].ToString();
comm.ZK_ParamName = myselect.Rows[0]["ZK_ParamName"].ToString();
comm.ZK_TableSuffix = myselect.Rows[0]["ZK_TableSuffix"].ToString();
comm.ZK_IsSend = "";
comm.ZK_FailNum = 0;
comm.ZK_Send_DateTime = null;
comm.ZK_ReceiveCommand ="";
comm.ZK_ReceiveRemark ="";
comm.ZK_Receive_DateTime =null;
comm.EB_CREATE_DATETIME = DateTime.Now;
comm.XF_MALLID = myselect.Rows[0]["XF_MALLID"].ToString();
comm.EB_CREATEBY = job.job_xf;
comm.EB_LASTMODIFYBY = job.job_xf;
comm.EB_LASTMODIFY_DATETIME = DateTime.Now;
List<ZK_COMMAND_REALTIME> List = new List<ZK_COMMAND_REALTIME>();
List.Add(comm);
//写sql语句插入
//;string insertsql = "";
//插入
bool flag= Tools.GeneralBLL<ZK_COMMAND_REALTIME>(List, OperationEnum.Create, null, true);
if (flag)
{
Console.WriteLine("控制表主键为:"+ job.job_ZK_ConditionID.ToString()+"插入成功");
}
//if (job.End.HasValue)
// job.End = job.End.Value.ToLocalTime();
//Console.WriteLine("******************************GO*******************************");
//Console.WriteLine(" 批次标题:" + job.ZK_BatchTitle);
//Console.WriteLine(" 环节标题:" + job.ZK_IDTitle);
//Console.WriteLine(" 类型:" + (job.ZK_Type == "0" ? "天" : (job.ZK_Type == "1" ? "时" : "分")));
//Console.WriteLine(" 值:" + job.ZK_Value);
//Console.WriteLine(" 上次时间:" + job.LastPhoto);
//Console.WriteLine(" 当前时间:" + DateTime.Now);
//Console.WriteLine();
}
}
class Timejob
{
public string job_ID { get; set; }
public string job_ZK_ConditionID { get; set; } //设备控制条件表主键
public string job_ZK_CommandColocationID { get; set; } //命令配置表主键
public string job_xf { get; set; }
public string job_ZK_TimeId { get; set; } //定时主键
public string job_ZK_TimeType { get; set; }
public string job_ZK_TimeValue { get; set; }
public DateTime Nowtask { get; set; }
public DateTime? Nexttask { get; set; }
public DateTime? End { get; set; }
}
}
//作者:CJM 2019-11 广州