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

标签: asp.net  mvc  任务调度  管理  class

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 [email protected]
                        ";
            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 [email protected]
                        ";
            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 [email protected]
                        ";
            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 [email protected]";

            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 [email protected] 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)
            };
        }
    }
}
版权声明:本文为WuLex原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/WuLex/article/details/74066825