public virtual bool Add2(DbConn PubConn, tb_producter_model model)
{
return SqlHelper.Visit((ps) =>
{
List<ProcedureParameter> Par = new List<ProcedureParameter>()
{
//��������ʱid(�����������Ψһ,Guidתlong)
new ProcedureParameter("@tempid", model.tempid),
//����������
new ProcedureParameter("@productername", model.productername),
//ip��ַ
new ProcedureParameter("@ip", model.ip),
//����id
new ProcedureParameter("@mqpathid", model.mqpathid),
////�������������ʱ��
//new ProcedureParameter("@lastheartbeat", model.lastheartbeat),
////�����ߴ���ʱ��
//new ProcedureParameter("@createtime", model.createtime)
};
int rev = PubConn.ExecuteSql(@"insert into tb_producter(tempid,productername,ip,mqpathid,lastheartbeat,createtime)
values(@tempid,@productername,@ip,@mqpathid,getdate(),getdate())", Par);
return rev == 1;
});
}
public virtual bool Add(DbConn PubConn, tb_user_model model)
{
List<ProcedureParameter> Par = new List<ProcedureParameter>()
{
//员工工号
new ProcedureParameter("@userstaffno", model.userstaffno),
//
new ProcedureParameter("@username", model.username),
//员工角色，查看代码枚举：开发人员，管理员
new ProcedureParameter("@userrole", model.userrole),
//
new ProcedureParameter("@usercreatetime", model.usercreatetime),
//员工手机号码
new ProcedureParameter("@usertel", model.usertel),
//
new ProcedureParameter("@useremail", model.useremail),
//登录密码
new ProcedureParameter("@userpsw", model.userpsw)
};
int rev = PubConn.ExecuteSql(@"insert into tb_user(userstaffno,username,userrole,usercreatetime,usertel,useremail,userpsw)
values(@userstaffno,@username,@userrole,@usercreatetime,@usertel,@useremail,@userpsw)", Par);
return rev == 1;
}
public virtual bool Edit(DbConn PubConn, tb_user_model model)
{
List<ProcedureParameter> Par = new List<ProcedureParameter>()
{
//员工工号
new ProcedureParameter("@userstaffno", model.userstaffno),
//
new ProcedureParameter("@username", model.username),
//员工角色，查看代码枚举：开发人员，管理员
new ProcedureParameter("@userrole", model.userrole),
//
new ProcedureParameter("@usercreatetime", model.usercreatetime),
//员工手机号码
new ProcedureParameter("@usertel", model.usertel),
//
new ProcedureParameter("@useremail", model.useremail),
//登录密码
new ProcedureParameter("@userpsw", model.userpsw)
};
Par.Add(new ProcedureParameter("@id", model.id));
int rev = PubConn.ExecuteSql("update tb_user set userstaffno=@userstaffno,username=@username,userrole=@userrole,usercreatetime=@usercreatetime,usertel=@usertel,useremail=@useremail,userpsw=@userpsw where id=@id", Par);
return rev == 1;
}
public virtual bool Add2(DbConn PubConn, tb_consumer_model model)
{
return SqlHelper.Visit((ps) =>
{
List<ProcedureParameter> Par = new List<ProcedureParameter>()
{
//��������ʱid(�����������Ψһ,Guidתlong)
new ProcedureParameter("@tempid", model.tempid),
//������clinet��id
new ProcedureParameter("@consumerclientid", model.consumerclientid),
//֧�ֵķ���˳���(֧�ֶ��˳���)
new ProcedureParameter("@partitionindexs", model.partitionindexs),
//�ͻ�������
new ProcedureParameter("@clientname", model.clientname),
////�������ʱ��(�Ե�ǰ��ʱ��Ϊ)
//new ProcedureParameter("@lastheartbeat", model.lastheartbeat),
////��һ�θ���ʱ��(�Ե�ǰ��ʱ��Ϊ)
//new ProcedureParameter("@lastupdatetime", model.lastupdatetime),
////�ͻ��˴���ʱ��(�Ե�ǰ��ʱ��Ϊ)
//new ProcedureParameter("@createtime", model.createtime)
};
int rev = PubConn.ExecuteSql(@"insert into tb_consumer(tempid,consumerclientid,partitionindexs,clientname,lastheartbeat,lastupdatetime,createtime)
values(@tempid,@consumerclientid,@partitionindexs,@clientname,getdate(),getdate(),getdate())", Par);
return rev == 1;
});
}
/// <summary>
/// createtime expiresetime不用传。
/// </summary>
/// <param name="PubConn"></param>
/// <param name="model"></param>
/// <param name="tokentype"></param>
/// <returns></returns>
public virtual bool Add(DbConn PubConn, DbModels.tb_token model, Models.DbModels.TokenType tokentype)
{
DateTime nowtime = PubConn.GetServerDate();
model.createtime = nowtime;
model.expires = nowtime.AddMinutes(GetExpiresminutes(tokentype));
List<ProcedureParameter> Par = new List<ProcedureParameter>()
{
//
new ProcedureParameter("@token", model.token),
//
new ProcedureParameter("@userid", model.userid),
new ProcedureParameter("@id", model.id),
//
new ProcedureParameter("@username", model.username),
//
new ProcedureParameter("@appid", model.appid),
//
new ProcedureParameter("@createtime", model.createtime),
//
new ProcedureParameter("@expires", model.expires )
};
int rev = PubConn.ExecuteSql("insert into " + tokentype.ToString() + " (token,userid,id,username,appid,createtime,expires)" +
" values(@token,@userid,@id,@username,@appid,@createtime,@expires)", Par);
return rev == 1;
}
public int UpdateTaskSuccess(DbConn PubConn, int id)
{
return SqlHelper.Visit(ps =>
{
string cmd = "update tb_task set taskerrorcount=0,taskruncount=taskruncount+1 where id=@id";
ps.Add("id", id);
return PubConn.ExecuteSql(cmd, ps.ToParameters());
});
}
public void DeleteAll(DbConn PubConn)
{
SqlHelper.Visit((ps) =>
{
string Sql = "truncate table tb_error";
int rev = PubConn.ExecuteSql(Sql, ps.ToParameters());
return rev;
});
}
public bool ClientHeatbeat(DbConn PubConn, int mqpathid, long tempid)
{
return SqlHelper.Visit((ps) =>
{
ps.Add("@mqpathid", mqpathid); ps.Add("@tempid", tempid);
string Sql = "update tb_producter WITH (ROWLOCK) set lastheartbeat=getdate() where mqpathid=@mqpathid and tempid=@tempid";
int rev = PubConn.ExecuteSql(Sql, ps.ToParameters());
return true;
});
}
public bool DeleteOneNode(DbConn PubConn, int id)
{
return SqlHelper.Visit<bool>(ps =>
{
ps.Add("id", id);
string sql = "delete from tb_user where (select count(1) from tb_task where taskcreateuserid=@id)=0 and id=@id";
int i = PubConn.ExecuteSql(sql, ps.ToParameters());
return i > 0;
});
}
public bool DeleteNotOnLineByClientID(DbConn PubConn, int consumerclientid, int maxtimeoutsenconds)
{
return SqlHelper.Visit((ps) =>
{
ps.Add("@consumerclientid", consumerclientid); ps.Add("@maxtimeoutsenconds", maxtimeoutsenconds);
string Sql = "delete from tb_consumer where consumerclientid=@consumerclientid and DATEDIFF(S,lastheartbeat,getdate())>@maxtimeoutsenconds";
int rev = PubConn.ExecuteSql(Sql, ps.ToParameters());
return true;
});
}
public bool Update(DbConn PubConn, tb_category_model model)
{
List<ProcedureParameter> Par = new List<ProcedureParameter>()
{
new ProcedureParameter("@categoryname", model.categoryname),
new ProcedureParameter("@id", model.id),
};
int rev = PubConn.ExecuteSql(@"update tb_category set categoryname=@categoryname where id=@id", Par);
return rev == 1;
}
public int UpdateTaskError(DbConn PubConn, int id, DateTime time)
{
return SqlHelper.Visit(ps =>
{
string cmd = "update tb_task set taskerrorcount=taskerrorcount+1,tasklasterrortime=@tasklasterrortime where id=@id";
ps.Add("id", id);
ps.Add("tasklasterrortime", time);
return PubConn.ExecuteSql(cmd, ps.ToParameters());
});
}
public int UpdateLastStartTime(DbConn PubConn, int id, DateTime time)
{
return SqlHelper.Visit(ps =>
{
string cmd = "update tb_task set tasklaststarttime=@tasklaststarttime where id=@id";
ps.Add("id", id);
ps.Add("tasklaststarttime", time);
return PubConn.ExecuteSql(cmd, ps.ToParameters());
});
}
public virtual int SaveTempData(DbConn PubConn,int taskid,string json)
{
return SqlHelper.Visit(ps =>
{
string cmd = "update tb_tempdata set tempdatajson=@tempdatajson where taskid=@taskid";
ps.Add("taskid", taskid);
ps.Add("tempdatajson",json);
return PubConn.ExecuteSql(cmd, ps.ToParameters());
});
}
public virtual void Add2(DbConn PubConn, string client)
{
SqlHelper.Visit((ps) =>
{
ps.Add("@client", client);
PubConn.ExecuteSql(@"insert into tb_consumer_client(client,createtime)
values(@client,getdate())", ps.ToParameters());
return true;
});
}
public void UpdateCommandState(DbConn PubConn,int id,int commandstate)
{
SqlHelper.Visit(ps =>
{
ps.Add("@commandstate", commandstate);
ps.Add("@id", id);
StringBuilder stringSql = new StringBuilder();
stringSql.Append(@"update tb_command set commandstate=@commandstate where id=@id");
return PubConn.ExecuteSql(stringSql.ToString(),ps.ToParameters());
});
}
public int AddOrUpdate(DbConn PubConn, tb_node_model model)
{
return SqlHelper.Visit(ps =>
{
ps.Add("@nodename", model.nodename);
ps.Add("@nodecreatetime", model.nodecreatetime);
ps.Add("@nodeip", model.nodeip);
ps.Add("@nodelastupdatetime", model.nodelastupdatetime);
ps.Add("@ifcheckstate", model.ifcheckstate);
ps.Add("@id", model.id);
string updatecmd = "update tb_node set nodeip=@nodeip,nodelastupdatetime=@nodelastupdatetime where id=@id";
string insertcmd = @"insert into tb_node(nodename,nodecreatetime,nodeip,ifcheckstate)
values(@nodename,@nodecreatetime,@nodeip,@ifcheckstate)";
if (PubConn.ExecuteSql(updatecmd, ps.ToParameters()) <= 0)
{
PubConn.ExecuteSql(insertcmd, ps.ToParameters());
}
return 1;
});
}
public virtual bool Add(DbConn PubConn, string categoryname)
{
List<ProcedureParameter> Par = new List<ProcedureParameter>()
{
new ProcedureParameter("@categoryname", categoryname),
};
int rev = PubConn.ExecuteSql(@"insert into tb_category(categoryname,categorycreatetime)
values(@categoryname,getdate())", Par);
return rev == 1;
}
public int AddOrUpdate(DbConn PubConn, tb_performance_model model)
{
return SqlHelper.Visit(ps =>
{
ps.Add("@cpu", model.cpu);
ps.Add("@memory", model.memory);
ps.Add("@installdirsize", model.installdirsize);
ps.Add("@taskid", model.taskid);
ps.Add("@nodeid", model.nodeid);
ps.Add("@lastupdatetime", model.lastupdatetime);
ps.Add("@id", model.id);
string updatecmd = "update tb_performance set cpu=@cpu,memory=@memory,installdirsize=@installdirsize,nodeid=@nodeid,lastupdatetime=@lastupdatetime where taskid=@taskid";
string insertcmd = @"insert into tb_performance(cpu,memory,installdirsize,taskid,nodeid,lastupdatetime)
values(@cpu,@memory,@installdirsize,@taskid,@nodeid,@lastupdatetime)";
if (PubConn.ExecuteSql(updatecmd, ps.ToParameters()) <= 0)
{
PubConn.ExecuteSql(insertcmd, ps.ToParameters());
}
return 1;
});
}
public int Add2(DbConn PubConn, tb_log_model model)
{
return SqlHelper.Visit(ps =>
{
ps.Add("@msg", model.msg);
ps.Add("@logtype", model.logtype);
ps.Add("@logcreatetime", model.logcreatetime);
ps.Add("@taskid", model.taskid);
ps.Add("@nodeid", model.nodeid);
return PubConn.ExecuteSql(@"insert into tb_log(msg,logtype,logcreatetime,taskid,nodeid)
values(@msg,@logtype,@logcreatetime,@taskid,@nodeid)", ps.ToParameters());
});
}
public int UpdateByTaskID(DbConn PubConn, tb_tempdata_model model)
{
return SqlHelper.Visit<int>(ps =>
{
ps.Add("@taskid", model.taskid);
ps.Add("@tempdatajson", model.tempdatajson);
ps.Add("@tempdatalastupdatetime", model.tempdatalastupdatetime);
ps.Add("@id", model.id);
int rev = PubConn.ExecuteSql("update tb_tempdata set tempdatajson=@tempdatajson,tempdatalastupdatetime=@tempdatalastupdatetime where taskid=@taskid", ps.ToParameters());
return rev;
});
}
public virtual bool Add2(DbConn PubConn, string mqpath)
{
return SqlHelper.Visit((ps) =>
{
List<ProcedureParameter> Par = new List<ProcedureParameter>()
{
//mq路径
new ProcedureParameter("@mqpath", mqpath),
};
int rev = PubConn.ExecuteSql(@"insert into tb_mqpath(mqpath,lastupdatetime,createtime)
values(@mqpath,getdate(),getdate())", Par);
return rev == 1;
});
}
public virtual bool Add(DbConn PubConn, tb_config_model model)
{
List<ProcedureParameter> Par = new List<ProcedureParameter>()
{
new ProcedureParameter("@key", model.key),
//����Value
new ProcedureParameter("@value", model.value),
//���ñ�ע��Ϣ
new ProcedureParameter("@remark", model.remark)
};
int rev = PubConn.ExecuteSql(@"insert into tb_config([key],value,remark)
values(@key,@value,@remark)", Par);
return rev == 1;
}
public virtual bool Add(DbConn PubConn, tb_consumer_client_model model)
{
List<ProcedureParameter> Par = new List<ProcedureParameter>()
{
//�ͻ��ˣ�������client����ͬҵ��������ע�����һ�£�
new ProcedureParameter("@client", model.client),
//��ǰ�����ߴ���ʱ��(�Ե�ǰ��ʱ��Ϊ)
new ProcedureParameter("@createtime", model.createtime)
};
int rev = PubConn.ExecuteSql(@"insert into tb_consumer_client(client,createtime)
values(@client,@createtime)", Par);
return rev == 1;
}
public virtual bool Add(DbConn PubConn, tb_partition_model model)
{
List<ProcedureParameter> Par = new List<ProcedureParameter>()
{
//�Ƿ���ʹ��
new ProcedureParameter("@isused", model.isused),
//����ʱ��(�Ե�ǰ��ʱ��Ϊ)
new ProcedureParameter("@createtime", model.createtime)
};
int rev = PubConn.ExecuteSql(@"insert into tb_partition(isused,createtime)
values(@isused,@createtime)", Par);
return rev == 1;
}
public virtual bool Edit(DbConn PubConn, tb_config_model model)
{
List<ProcedureParameter> Par = new List<ProcedureParameter>()
{
//����Value
new ProcedureParameter("@value", model.value),
//���ñ�ע��Ϣ
new ProcedureParameter("@remark", model.remark)
};
Par.Add(new ProcedureParameter("@key", model.key));
int rev = PubConn.ExecuteSql("update tb_config set value=@value,remark=@remark where [key]=@key", Par);
return rev == 1;
}
public virtual bool Edit(DbConn PubConn, tb_consumer_client_model model)
{
List<ProcedureParameter> Par = new List<ProcedureParameter>()
{
//�ͻ��ˣ�������client����ͬҵ��������ע�����һ�£�
new ProcedureParameter("@client", model.client),
//��ǰ�����ߴ���ʱ��(�Ե�ǰ��ʱ��Ϊ)
new ProcedureParameter("@createtime", model.createtime)
};
Par.Add(new ProcedureParameter("@id", model.id));
int rev = PubConn.ExecuteSql("update tb_consumer_client set client=@client,createtime=@createtime where id=@id", Par);
return rev == 1;
}
public int Update(DbConn PubConn, tb_node_model model)
{
return SqlHelper.Visit(ps =>
{
ps.Add("@nodename", model.nodename);
ps.Add("@nodeip", model.nodeip);
ps.Add("@id", model.id);
ps.Add("@ifcheckstate", model.ifcheckstate);
string updatecmd = "update tb_node set nodeip=@nodeip,nodename=@nodename,ifcheckstate=@ifcheckstate where id=@id";
return PubConn.ExecuteSql(updatecmd, ps.ToParameters()) ;
});
}
public virtual tb_shuntruleconn_config_model Get(DbConn PubConn, int id)
{
List<ProcedureParameter> Par = new List<ProcedureParameter>();
Par.Add(new ProcedureParameter("@id", id));
StringBuilder stringSql = new StringBuilder();
stringSql.Append(@"select s.* from tb_distribution_rule_config s where s.id=@id");
int rev = PubConn.ExecuteSql(stringSql.ToString(), Par);
DataSet ds = new DataSet();
PubConn.SqlToDataSet(ds, stringSql.ToString(), Par);
if (ds != null && ds.Tables.Count > 0)
{
return CreateModel(ds.Tables[0].Rows[0]);
}
return null;
}
/// <summary>清除过期token</summary>
public static void DeleteExpiresToken()
{
try
{
string sql = @"delete from usertoken where expires<GETDATE()
delete from managetoken where expires<GETDATE()
delete from shoptoken where expires<GETDATE()";
using (XXF.Db.DbConn PubConn = XXF.Db.DbConfig.CreateConn())
{
PubConn.Open();//打开基本
PubConn.ExecuteSql(sql, null);
}
}
catch (Exception ex) { }
}
public virtual bool Edit(DbConn PubConn, tb_partition_model model)
{
List<ProcedureParameter> Par = new List<ProcedureParameter>()
{
//�Ƿ���ʹ��
new ProcedureParameter("@isused", model.isused),
//����ʱ��(�Ե�ǰ��ʱ��Ϊ)
new ProcedureParameter("@createtime", model.createtime)
};
Par.Add(new ProcedureParameter("@partitionid", model.partitionid));
int rev = PubConn.ExecuteSql("update tb_partition set isused=@isused,createtime=@createtime where partitionid=@partitionid", Par);
return rev == 1;
}
