C# – ITJOY.NET

工作中有很多定期执行、定时同步的需求，最适合的方案是SqlServer+Topshelf+Quartznet ，用SqlServer做任务池，用Quartznet做调度计划，用Topshelf将项目部署为系统服务后台运行。

但是整个项目比较庞大，一时半会做不出来。结合手工已经在用的组件，设计了WebApi+SQLServer 的方案。在WebApi里写工作任务，利用SQLServer的维护计划做调度，需要有安装IIS和SQL数据库。

1.首先是写一个可以http调用的api程序。以下程序调用路径是 /api.aspx?act=GO

当然也可以写一个规范的MVC api程序。

using System;
using System.Reflection;
using System.Web.UI;
using Newtonsoft.Json;

namespace MIS.范例
{

    public partial class api : Page
    {
        private string errcode;
        private string errmsg;
        private string info;
        protected void Page_Load(object sender, EventArgs e)
        {
            this.errcode = "0";
            this.errmsg = "";
            this.info = "";

            string act = Request.QueryString["act"];
            if (!string.IsNullOrEmpty(act))
            {
                try
                {
                    //执行act动作
                    var result = GetType().GetMethod(act, BindingFlags.Instance | BindingFlags.Public).Invoke(this, null) as string;
                    if (!string.IsNullOrEmpty(result))
                    {
                        Context.Response.ContentType = "text/plain";
                        Response.Write(result);
                    }
                }
                catch (Exception ex)
                {
                    //错误
                    Context.Response.ContentType = "text/plain";
                    Response.Write(JsonConvert.SerializeObject(new { this.errcode, errmsg = ex.ToString(), this.info }));
                }
                Response.End();
            }
        }
        //任务 /act=GO
        public string GO()
        {
            //执行动作
            //返回JSON结果
            return JsonConvert.SerializeObject(new { this.errcode, this.errmsg, this.info });
        }
    }
}

using System;

using System.Reflection;

using System.Web.UI;

using Newtonsoft.Json;

namespace MIS.范例

{

public partial class api : Page

{

private string errcode;

private string errmsg;

private string info;

protected void Page_Load(object sender, EventArgs e)

{

this.errcode = "0";

this.errmsg = "";

this.info = "";

string act = Request.QueryString["act"];

if (!string.IsNullOrEmpty(act))

{

try

{

//执行act动作

var result = GetType().GetMethod(act, BindingFlags.Instance | BindingFlags.Public).Invoke(this, null) as string;

if (!string.IsNullOrEmpty(result))

{

Context.Response.ContentType = "text/plain";

Response.Write(result);

}

catch (Exception ex)

{

//错误

Context.Response.ContentType = "text/plain";

Response.Write(JsonConvert.SerializeObject(new { this.errcode, errmsg = ex.ToString(), this.info }));

}

Response.End();

}

//任务 /act=GO

public string GO()

{

//执行动作

//返回JSON结果

return JsonConvert.SerializeObject(new { this.errcode, this.errmsg, this.info });

}

2. 在SQLServer维护计划里添加T-SQL语句任务，并设置按周期执行。

use DATABASE
declare @url as Varchar(200) ,@PostData Varchar(10),@ResponseText as Varchar(8000)
declare @errcode nvarchar(20),@errmsg nvarchar(2000)
set @url='http://10.0.0.1:8080/api?act=GO'

exec P_Url_SendRequest @url ,@PostData,@ResponseText OUTPUT
select @errcode=StringValue from parsejson(@ResponseText) where NAME='errcode'
select @errmsg='考勤同步失败,'+StringValue from parsejson(@ResponseText) where NAME='errmsg'	--errcode  errmsg  info

if @errcode<>'0'
begin
	exec  P_Send_TextMsg '企业微信消息','wxuser1,wxuser2',@errmsg
end

use DATABASE

declare @url as Varchar(200) ,@PostData Varchar(10),@ResponseText as Varchar(8000)

declare @errcode nvarchar(20),@errmsg nvarchar(2000)

set @url='http://10.0.0.1:8080/api?act=GO'

exec P_Url_SendRequest @url ,@PostData,@ResponseText OUTPUT

select @errcode=StringValue from parsejson(@ResponseText) where NAME='errcode'

select @errmsg='考勤同步失败,'+StringValue from parsejson(@ResponseText) where NAME='errmsg' --errcode errmsg info

if @errcode<>'0'

begin

exec P_Send_TextMsg '企业微信消息','wxuser1,wxuser2',@errmsg

end

3. 以上SQL里用到的函数组件

P_Url_SendRequest 连接URL，支持POST\GET 支持返回值

MSSQL访问WebServer接口代码 – P_Url_SendRequest

parsejson JSON解析函数

MSSQL JSON函数-parseJSON

P_Send_TextMsg 企业微信消息推送执行结果

MSSQL调用企业微信发送消息接口封装-P_Send_TextMsg

两个值求最大最小：

int a=12;
int b=13;
int max=Math.Max(a,b);//Math.Max()不能比较datetime
int min=a<b?a:b;

int a=12;

int b=13;

int max=Math.Max(a,b);//Math.Max()不能比较datetime

int min=a<b?a:b;

三个值求最大最小：

int a=12;
int b=13;
int c=14; 
int max=a>b?(a>c?a:c):(b>c?b:c); 
int min=a<b?(a<c?a:c):(b<c?b:c);

int a=12;

int b=13;

int c=14;

int max=a>b?(a>c?a:c):(b>c?b:c);

int min=a<b?(a<c?a:c):(b<c?b:c);

多个值求最大最小：

int a = 12;
int b = 13;
int c = 14;
int d = 15; 
int max = new List<int> { a, b, c, d }.Max(); 
int min = new List<int> { a, b, c, d }.Min();

int a = 12;

int b = 13;

int c = 14;

int d = 15;

int max = new List<int> { a, b, c, d }.Max();

int min = new List<int> { a, b, c, d }.Min();

分类：C#

C#连接Oracle 环境配置–走过的坑

WebApi+SQLServer定时任务方案

生成金蝶用户密码加密字串

C# 如何优雅的求最大值最小值

在PropertyGrid中设置时间填写功能