MVC-利用Quartz实现定时调度

一.创建一个Windows服务（.NET Framework）类型的项目（如果没有服务，Visual Studio 2017 需要安装 .NET Framework 4.6 ）

二.在管理NuGet程序包中添加引用Quartz包

三  App.config操作

1).在App.config中添加代码

<appSettings>
    <add key="LogPath" value="D:\\log.txt"></add>        <!--定义日志操作存储地址-->
    <add key="TableName" value="Admini"></add>           <!--定义操作数据库表名-->
    <add key="BackUpPath" value="D:\\BackUp\\"></add>    <!--定义存储表数据地址-->
    <add key="connString" value="server=DESKTOP-GK2O68M\SQLEXPRESS;database=MiShop;uid=sa;pwd=sa"/>     <!--定义数据库连接字符串-->
  </appSettings>

2).将以下代码添加到App.config中（这个是必要的）

<quartz>
    <add key="quartz.scheduler.instanceName" value="ExampleDefaultQuartzScheduler"/>
    <add key="quartz.threadPool.type" value="Quartz.Simpl.SimpleThreadPool, Quartz"/>
    <add key="quartz.threadPool.threadCount" value="10"/>
    <add key="quartz.threadPool.threadPriority" value="2"/>
    <add key="quartz.jobStore.misfireThreshold" value="60000"/>
    <add key="quartz.jobStore.type" value="Quartz.Simpl.RAMJobStore, Quartz"/>
  </quartz>

3).在App.config中添加统计任务处理

  <Crons>
    <!--统计任务处理-->
    <add key="LogJob" value="0/5 * * * * ?"></add>
    <add key="BackupJob" value="0/5 * * * * ?"></add>
    <add key="QQJob" value="0/5 * * * * ?"></add>
  </Crons>

四.新添加一个cs类型项目添加一个文件夹（Common）

    1）文件夹（Common）中添加 JobScheduleManager.cs（调度中心的帮助类）

    public  class JobScheduleManager
    {
        /// <summary>
        /// 调度中心
        /// </summary>
        public static void AddSchedule<T>(ref IScheduler sched, string croname,string trigroup,string trigGroupName) where T : IJob
        {
            var jobKey = new JobKey(croname + "key", trigroup);
            //创建调度服务
            var jobDetail = JobBuilder.Create<T>().WithIdentity(jobKey).Build();
            //设置调度时间间隔
            var jobTrig = new CronTriggerImpl(croname + "Trig", trigGroupName,ConfigurationHelper.GetSectionValue("Crons", croname));
            sched.ScheduleJob(jobDetail, jobTrig);
        }
    }

 

    2）文件夹（Common）中添加 BackupJobSchedule.cs（表生成文本类）

 public class BackupJobSchedule
    {
        /// <summary>
        /// 根据DataTable,生成建表语句
        /// </summary>
        /// <param name="table"></param>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public string GetCreateTableSql(DataTable table, string tableName)
        {
            StringBuilder stringBuilder = new StringBuilder();
            #region 创建表之前的开关
            stringBuilder.AppendLine("SET ANSI_NULLS ON");
            stringBuilder.AppendLine("GO");
            stringBuilder.AppendLine("SET QUOTED_IDENTIFIER ON");
            stringBuilder.AppendLine("GO");
            stringBuilder.AppendLine("SET ANSI_PADDING ON");
            stringBuilder.AppendLine("GO");
            #endregion

            stringBuilder.AppendLine($"CREATE TABLE [dbo].[{tableName}](");
            //遍历列，获取字段属性
            foreach (DataColumn col in table.Columns)
            {
                //数据类型
                var datatype = GetSqlDbType(col.DataType.Name);
                //是否自增
                var isautoIn = col.AutoIncrement ? $"IDENTITY({col.AutoIncrementSeed},{col.AutoIncrementStep})" : "";
                //是否为空
                var isnull = col.AllowDBNull ? "NULL" : "NOT NULL";
                //拼接
                var colStr = $" [{col.ColumnName}] {datatype} {isautoIn} {isnull}, ";
                stringBuilder.AppendLine(colStr);
            }

            #region 设置主键
            stringBuilder.AppendLine("PRIMARY KEY CLUSTERED");
            stringBuilder.AppendLine("( ");
            stringBuilder.AppendLine($"[{table.PrimaryKey[0].ColumnName}] ASC ");
            stringBuilder.AppendLine(")WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ");
            stringBuilder.AppendLine(") ON [PRIMARY] ");
            stringBuilder.AppendLine("GO");
            stringBuilder.AppendLine("SET ANSI_PADDING OFF");
            stringBuilder.AppendLine("GO ");
            stringBuilder.AppendLine($"SET IDENTITY_INSERT [dbo].[{tableName}] ON  ");
            #endregion
            return stringBuilder.ToString();
        }

        /// <summary>
        /// 根据C#数据类型转换sql数据类型
        /// </summary>
        /// <param name="sqlTypeString"></param>
        /// <returns></returns>
        public static string GetSqlDbType(string sqlTypeString)
        {
            string sqlDbType = string.Empty;

            switch (sqlTypeString)
            {
                case "Int32":
                    sqlDbType = "[int]";
                    break;
                case "Int64":
                    sqlDbType = "[bigint]";
                    break;
                case "Int16":
                    sqlDbType = "[smallint]";
                    break;
                case "String":
                    sqlDbType = "[varchar] (200)";
                    break;
                case "DateTime":
                    sqlDbType = "[datetime]";
                    break;
                case "Boolean":
                    sqlDbType = "bit";
                    break;

            }
            return sqlDbType;
        }

        /// <summary>
        /// 根据DataTable,生成insert into语句
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public string GetInsertTableSql(DataTable table, string tableName)
        {
            StringBuilder stringBuilder = new StringBuilder();
            //字符串

            string column = string.Empty;
            foreach (DataColumn item in table.Columns)
            {
                column += $"[{item.ColumnName}], ";
            }
            column = column.Substring(0, column.Length - 2) + ") VALUES (";
            for (int i = 0; i < table.Rows.Count; i++)
            {
                //拼接表头
                stringBuilder.Append($"INSERT [dbo].[{tableName}] (");
                //拼接列名
                stringBuilder.Append(column);
                string values = string.Empty;
                for (int j = 0; j < table.Columns.Count; j++)
                {
                    var datatype = table.Columns[j].DataType.Name;
                    //string 和 datetime 加单引号
                    if (datatype == "String" || datatype == "DateTime")
                        values += "'" + table.Rows[i][j].ToString() + "',";
                    //其他不加单引号
                    else
                        values += "" + table.Rows[i][j].ToString() + ",";
                }
                //每次循环一条记录换一行
                stringBuilder.AppendLine(values.Remove(values.Length - 1) + ")\n");
            }
            stringBuilder.AppendLine($"SET IDENTITY_INSERT [dbo].[{tableName}] off  ");
            return stringBuilder.ToString();
        }
    }

  3）文件夹（Common）中添加 mailSchedule.cs（QQ邮箱发送）

 public class mailSchedule
    {
        ///<summary>
        /// 发送邮件方法
        ///</summary>
        ///<param name="mailTo">收件人邮箱</param>
        ///<param name="mailSubject">邮件标题</param>
        ///<param name="mailContent">邮件内容</param>
        public static void SendEmail(string mailTo, string mailSubject, string mailContent)
        {
            // 设置发送方的邮件信息,例如使用网易的smtp
            string smtpServer = "smtp.qq.com"; //SMTP服务器
            string mailFrom = "3214727901@qq.com"; //登陆用户名
            string userPassword = "ckodytotnjsndcfd";//授权密码

            // 邮件服务设置
            SmtpClient smtpClient = new SmtpClient();
            smtpClient.DeliveryMethod = SmtpDeliveryMethod.Network;//指定电子邮件发送方式
            smtpClient.Host = smtpServer; //指定SMTP服务器
            smtpClient.Credentials = new System.Net.NetworkCredential(mailFrom, userPassword);//用户名和密码

            // 发送邮件设置       
            MailMessage mailMessage = new MailMessage(mailFrom, mailTo); // 发送人和收件人
            mailMessage.Subject = mailSubject;//主题
            mailMessage.Body = mailContent;//内容
            mailMessage.BodyEncoding = Encoding.UTF8;//正文编码
            mailMessage.IsBodyHtml = true;//设置为HTML格式
            mailMessage.Priority = MailPriority.Low;//优先级

            try
            {
                smtpClient.Send(mailMessage); // 发送邮件
            }
            catch (SmtpException ex)
            {

            }
        }
    }

五.在cs类型项目再添加一个文件夹（Job）

 1）文件夹（Job）中添加 LogJob.cs（保存日志数据）

  

public class LogJob : IJob
    {
        public async Task Execute(IJobExecutionContext context)
        {
            //获取配置文件中的日志路径
            string path = ConfigurationHelper.GetAppSettingValue("LogPath");
            //按要求写入文件
            File.AppendAllText(path,"520"+Environment.NewLine);
            await Task.Delay(1);
        }
    }

  2）文件夹（Job）中添加 BackupJob.cs（保存表生成后的数据）

 

public  class BackupJob:IJob
    {
        public async Task Execute(IJobExecutionContext context)
        {
            //获取配置文件中的日志路径
            string path = ConfigurationHelper.GetAppSettingValue("BackUpPath");
           
            //获取表的名
            string TableName= ConfigurationHelper.GetAppSettingValue("TableName");
            //根据DataTable,生成建表语句
            string sql = "select *from "+ TableName;
            DBHelper db = new DBHelper();
            BackupJobSchedule b = new BackupJobSchedule();
            DataSet dataSet = db.GetDateSet(sql, null);
            DataTable d = dataSet.Tables[0];
            string DataTableSql =b.GetCreateTableSql(d,TableName);
            string InsertSql = b.GetInsertTableSql(d, TableName);
           
            if (!Directory.Exists(path))
            {
                Directory.CreateDirectory(path);
            }
            string filePath = path + DateTime.Now.Ticks.ToString() + TableName + ".sql";
            //按要求写入文件
            File.AppendAllText(filePath, DataTableSql + Environment.NewLine+ InsertSql);
            await Task.Delay(1);
        }
    }

 3）文件夹（Job）中添加 QQJob.cs（保存表生成后的数据）

  

 public  class QQJob: IJob
    {
        public async Task Execute(IJobExecutionContext context)
        {
            mailSchedule.SendEmail("2495724625@qq.com", "11","22");
            await Task.Delay(1);
        }
    }

六.在开始创建的Windows服务（.NET Framework）中找到Service1.cs 右键—查看代码中编写OnStart方法

 protected async override void OnStart(string[] args)
        {
            //实例化调度程序
            ISchedulerFactory sf = new StdSchedulerFactory();
            IScheduler _sched = await sf.GetScheduler();
            //得到key的集合
            List<string> list = ConfigurationHelper.GetSectionList("Crons"); 
            if (list.Contains("LogJob"))
            {
                JobScheduleManager.AddSchedule<LogJob>(ref _sched, "LogJob", trigroup, trigGroupName);
            }
            if (list.Contains("BackupJob"))
            {
                JobScheduleManager.AddSchedule<BackupJob>(ref _sched, "BackupJob", trigroup, trigGroupName);
            }
            if (list.Contains("QQJob"))
            {
                JobScheduleManager.AddSchedule<QQJob>(ref _sched, "QQJob", trigroup, trigGroupName);
            }
            await _sched.Start();
        }