ASP.NET MVC 5 实现基于Quartz.net 的任务调度管理平台(二)

来源：互联网发布：邑通软件科技有限公司编辑：程序博客网时间：2024/05/22 05:07

DAL层类图:

这里写图片描述

TaskDAL.cs

using System;using System.Collections.Generic;using System.Data;using System.Linq;using System.Text;using System.Web;using TaskManager.Models;using TaskManager.Common;using TaskManager.DBUtility;namespace TaskManager.DAL{    public class TaskDAL    {        /// <summary>        /// 获取任务列表        /// </summary>        /// <param name="pageIndex"></param>        /// <param name="pageSize"></param>        /// <returns></returns>        public PageOf<TaskModel> GetTaskList(int pageIndex, int pageSize)        {            var QUERY_SQL = @"( SELECT TaskID,TaskName,TaskParam,CronExpressionString,AssemblyName,ClassName,Status,IsDelete,CreatedTime,ModifyTime,RecentRunTime,NextFireTime,CronRemark,Remark                                FROM p_Task(nolock)                                WHERE IsDelete=0 ";            QUERY_SQL += ") pp ";            string SQL = string.Format(@" select * from (select ROW_NUMBER() OVER(order by pp.ModifyTime desc) AS RowNum,* from {0}                                        ) as A where A.RowNum BETWEEN (@PageIndex-1)* @PageSize+1 AND @PageIndex*@PageSize ORDER BY RowNum;",                                  QUERY_SQL);            SQL += string.Format(@" SELECT COUNT(1) FROM {0};", QUERY_SQL);            object param = new { pageIndex = pageIndex, pageSize = pageSize };            DataSet ds = SQLHelper.FillDataSet(SQL, param);            return new PageOf<TaskModel>()            {                PageIndex = pageIndex,                PageSize = pageSize,                Total = Convert.ToInt32(ds.Tables[1].Rows[0][0]),                Items = DataMapHelper.DataSetToList<TaskModel>(ds)            };        }        /// <summary>        /// 读取数据库中全部的任务        /// </summary>        /// <returns></returns>        public List<TaskModel> GetAllTaskList()        {            var sql = @"SELECT TaskID,TaskName,TaskParam,CronExpressionString,AssemblyName,ClassName,Status,IsDelete,CreatedTime,ModifyTime,RecentRunTime,NextFireTime,CronRemark,Remark                        FROM p_Task(nolock)                        WHERE IsDelete=0 and Status =1";            var result = SQLHelper.ToList<TaskModel>(sql);            return result;        }        /// <summary>        /// 删除任务        /// </summary>        /// <param name="taskId"></param>        /// <returns></returns>        public bool UpdateTaskStatus(string taskId, int status)        {            var sql = @" UPDATE p_Task                           SET Status = @Status                          WHERE TaskID=@TaskID                        ";            object param = new { TaskID = taskId, Status = status };            return SQLHelper.ExecuteNonQuery(sql, param) > 0;        }        /// <summary>        /// 修改任务的下次启动时间        /// </summary>        /// <param name="taskId"></param>        /// <param name="nextFireTime"></param>        /// <returns></returns>        public bool UpdateNextFireTime(string taskId, DateTime nextFireTime)        {            var sql = @" UPDATE p_Task                           SET NextFireTime = @NextFireTime                                ,ModifyTime = GETDATE()                         WHERE TaskID=@TaskID                        ";            object param = new { TaskID = taskId, NextFireTime = nextFireTime };            return SQLHelper.ExecuteNonQuery(sql, param) > 0;        }        /// <summary>        /// 根据任务Id 修改 上次运行时间        /// </summary>        /// <param name="taskId"></param>        /// <param name="recentRunTime"></param>        /// <returns></returns>        public bool UpdateRecentRunTime(string taskId, DateTime recentRunTime)        {            var sql = @" UPDATE p_Task                           SET RecentRunTime = @RecentRunTime                                ,ModifyTime = GETDATE()                         WHERE TaskID=@TaskID                        ";            object param = new { TaskID = taskId, RecentRunTime = recentRunTime };            return SQLHelper.ExecuteNonQuery(sql, param) > 0;        }        /// <summary>        /// 根据任务Id 获取任务        /// </summary>        /// <param name="taskId"></param>        /// <returns></returns>        public TaskModel GetTaskById(string taskId)        {            var sql = @"SELECT TaskID,TaskName,TaskParam,CronExpressionString,AssemblyName,ClassName,Status,IsDelete,CreatedTime,ModifyTime,RecentRunTime,NextFireTime,CronRemark,Remark                        FROM p_Task(nolock)                        WHERE TaskID=@TaskID";            object param = new { TaskID = taskId };            var result = SQLHelper.Single<TaskModel>(sql, param);            return result;        }        /// <summary>        /// 添加任务        /// </summary>        /// <param name="task"></param>        /// <returns></returns>        public bool Add(TaskModel task)        {            var sql = @" INSERT INTO p_Task                               (TaskID,TaskName,TaskParam,CronExpressionString,AssemblyName,ClassName,Status,IsDelete,CreatedTime,ModifyTime,CronRemark,Remark)                         VALUES                               (@TaskID ,@TaskName,@TaskParam,@CronExpressionString,@AssemblyName,@ClassName,@Status,0,getdate(),getdate(),@CronRemark,@Remark)";            object param = new            {                TaskID = task.TaskID,                TaskName = task.TaskName,                TaskParam = task.TaskParam,                CronExpressionString = task.CronExpressionString,                AssemblyName = task.AssemblyName,                ClassName = task.ClassName,                Status = task.Status,                CronRemark = task.CronRemark,                Remark = task.Remark            };            return SQLHelper.ExecuteNonQuery(sql, param) > 0;        }        /// <summary>        /// 修改任务        /// </summary>        /// <param name="task"></param>        /// <returns></returns>        public bool Edit(TaskModel task)        {            var sql = @" UPDATE p_Task                           SET TaskName = @TaskName,TaskParam = @TaskParam,CronExpressionString = @CronExpressionString,AssemblyName = @AssemblyName,ClassName = @ClassName,                               Status = @Status,IsDelete = 0,ModifyTime =getdate() ,CronRemark = @CronRemark,Remark = @Remark                         WHERE TaskID = @TaskID";            object param = new            {                TaskID = task.TaskID,                TaskName = task.TaskName,                TaskParam = task.TaskParam,                CronExpressionString = task.CronExpressionString,                AssemblyName = task.AssemblyName,                ClassName = task.ClassName,                Status = task.Status,                CronRemark = task.CronRemark,                Remark = task.Remark            };            return SQLHelper.ExecuteNonQuery(sql, param) > 0;        }    }}

UserDAL.cs

using System;using System.Collections.Generic;using System.Data;using System.Linq;using System.Text;using TaskManager.Models;using TaskManager.Common;using TaskManager.DBUtility;namespace TaskManager.DAL{    public class UserDAL    {        /// <summary>        /// 获取用户列表        /// </summary>        /// <param name="pageNo"></param>        /// <param name="pageSize"></param>        /// <returns></returns>        public PageOf<UserModel> GetUserList(int pageNo, int pageSize)        {            var QUERY_SQL = @"( select UserId,UserName,PassWord,TrueName,UserEmail,PhoneNum,IsAdmin,Status,CreateTime,LastLoginTime                                from  p_User";            QUERY_SQL += ") pp ";            string SQL = string.Format(@" select * from (select ROW_NUMBER() OVER(order by pp.CreateTime desc) AS RowNum,* from {0}                                        ) as A where A.RowNum BETWEEN (@PageIndex-1)* @PageSize+1 AND @PageIndex*@PageSize ORDER BY RowNum;",                                  QUERY_SQL);            SQL += string.Format(@" SELECT COUNT(1) FROM {0};", QUERY_SQL);            object param = new { pageIndex = pageNo, pageSize = pageSize };            DataSet ds = SQLHelper.FillDataSet(SQL, param);            return new PageOf<UserModel>()            {                PageIndex = pageNo,                PageSize = pageSize,                Total = Convert.ToInt32(ds.Tables[1].Rows[0][0]),                Items = DataMapHelper.DataSetToList<UserModel>(ds)            };        }        /// <summary>        /// 根据用户名和密码获取管理员用户信息        /// </summary>        /// <param name="userName"></param>        /// <param name="pwd"></param>        /// <returns></returns>        public UserModel GetUserModel(string userName, string pwd)        {            var sql = @"  select UserId,UserName,PassWord,TrueName,UserEmail,PhoneNum,IsAdmin,Status,CreateTime,LastLoginTime                          from  p_User                          where UserName=@UserName and PassWord = @PassWord";            object param = new { UserName = userName, PassWord = pwd };            return SQLHelper.Single<UserModel>(sql, param);        }    }}

LogDAL.cs

using System;using System.Collections.Generic;using System.Data;using System.Linq;using System.Text;using TaskManager.Models;using TaskManager.Common;using TaskManager.DBUtility;namespace TaskManager.DAL{    public class LogDAL    {        /// <summary>        /// 记录运行日志        /// </summary>        /// <param name="taskName"></param>        /// <param name="taskId"></param>        /// <param name="result"></param>        public void WriteRunInfo(string remark, string taskId, string result)        {            var sql = @"INSERT INTO p_RunningLog                            (TaskID                            ,Remark                            ,Description                            ,CreateTime)                        VALUES                            (@TaskID                            ,@Remark                            ,@Description                            ,GETDATE())";            object param = new { TaskID = taskId, Remark = remark, Description = result };            SQLHelper.ExecuteNonQuery(sql, param);        }        /// <summary>        /// 记录错误日志        /// </summary>        /// <param name="sLevel"></param>        /// <param name="sMessage"></param>        /// <param name="sException"></param>        /// <param name="sName"></param>        public void WriteErrorInfo(string sLevel, string sMessage, string sException, string sName)        {            var sql = @"INSERT INTO p_ErrorLog                               (dtDate                               ,sLevel                               ,sLogger                               ,sMessage                               ,sException                               ,sName)                         VALUES                               (GETDATE()                               ,@sLevel                               ,@sLogger                               ,@sMessage                               ,@sException                               ,@sName)";            object param = new { sLevel = sLevel, sLogger = "system", sMessage = sMessage, sException = sException, sName = sName };            SQLHelper.ExecuteNonQuery(sql, param);        }        /// <summary>        /// 读取错误日志列表        /// </summary>        /// <param name="pageNo"></param>        /// <param name="pageSize"></param>        /// <returns></returns>        public PageOf<ErrorLogModel> GetErrorLogList(int pageNo, int pageSize)        {            var QUERY_SQL = @"(  select nId,dtDate,sThread,sLevel,sLogger,sMessage,sException,sName                                 from p_ErrorLog                                  where DateDiff(dd,dtDate,getdate())<=30";            QUERY_SQL += ") pp ";            string SQL = string.Format(@" select * from (select ROW_NUMBER() OVER(order by pp.dtDate desc) AS RowNum,* from {0}                                        ) as A where A.RowNum BETWEEN (@PageIndex-1)* @PageSize+1 AND @PageIndex*@PageSize ORDER BY RowNum;",                                  QUERY_SQL);            SQL += string.Format(@" SELECT COUNT(1) FROM {0};", QUERY_SQL);            object param = new { pageIndex = pageNo, pageSize = pageSize };            DataSet ds = SQLHelper.FillDataSet(SQL, param);            return new PageOf<ErrorLogModel>()            {                PageIndex = pageNo,                PageSize = pageSize,                Total = Convert.ToInt32(ds.Tables[1].Rows[0][0]),                Items = DataMapHelper.DataSetToList<ErrorLogModel>(ds)            };        }        /// <summary>        /// 读取运行日志列表        /// </summary>        /// <param name="pageNo"></param>        /// <param name="pageSize"></param>        /// <returns></returns>        public PageOf<RunLogModel> GetRunLogList(int pageNo, int pageSize)        {            var QUERY_SQL = @"( select r.Id,r.Remark,r.Description,r.CreateTime,t.TaskName,t.ClassName                                 from p_RunningLog(nolock) r inner join p_task(nolock) t on r.TaskID = t.TaskID                                 where DateDiff(dd,r.CreateTime,getdate())<=30";            QUERY_SQL += ") pp ";            string SQL = string.Format(@" select * from (select ROW_NUMBER() OVER(order by pp.CreateTime desc) AS RowNum,* from {0}                                        ) as A where A.RowNum BETWEEN (@PageIndex-1)* @PageSize+1 AND @PageIndex*@PageSize ORDER BY RowNum;",                                  QUERY_SQL);            SQL += string.Format(@" SELECT COUNT(1) FROM {0};", QUERY_SQL);            object param = new { pageIndex = pageNo, pageSize = pageSize };            DataSet ds = SQLHelper.FillDataSet(SQL, param);            return new PageOf<RunLogModel>()            {                PageIndex = pageNo,                PageSize = pageSize,                Total = Convert.ToInt32(ds.Tables[1].Rows[0][0]),                Items = DataMapHelper.DataSetToList<RunLogModel>(ds)            };        }    }}

阅读全文

0 0