public List <Models.DbModels.tb_token> GetByPage(XXF.Db.DbConn PubConn, int pno, int pagesize, string keywords, DbModels.TokenType tokentype, out int totalcount) { List <DbModels.tb_token> tokenlist = new List <DbModels.tb_token>(); string basesql = "select {0} from " + tokentype.ToString() + " "; string whercon = ""; XXF.Db.SimpleProcedureParameter para = new SimpleProcedureParameter(); string querysql = string.Format(basesql, "ROW_NUMBER() over (order by createtime desc) as rownum, token,userid,id,username,appid,createtime,expires"); if (!string.IsNullOrEmpty(keywords)) { para.Add("@keywords", keywords); whercon = " where token=@keywords or userid like '%' + @keywords + '%' or username like '%' + @keywords + '%' or appid like '%' + @keywords + '%' "; } querysql = string.Concat("select A.* from (", querysql, whercon, ") A ", " where A.rownum between ", (pno - 1) * pagesize + 1, " and ", pagesize * pno); DataSet ds = new DataSet(); PubConn.SqlToDataSet(ds, querysql, para.ToParameters()); foreach (DataRow dr in ds.Tables[0].Rows) { tokenlist.Add(CreateModel(dr)); } totalcount = (int)PubConn.ExecuteScalar(string.Format(basesql, " count(*) ") + whercon, para.ToParameters()); return(tokenlist); }
public virtual List<tb_shuntruleconn_config_model> Get(DbConn PubConn,DateTime lastUpdateTime) { return XXF.ProjectTool.SqlHelper.Visit(ps => { List<ProcedureParameter> Par = new List<ProcedureParameter>(); StringBuilder stringSql = new StringBuilder(); if (lastUpdateTime == default(DateTime)) { stringSql.Append(@"select s.* from tb_distribution_rule_config s"); } else { stringSql.Append(@"select s.* from tb_distribution_rule_config s where s.f_last_update_time>=@lastTime"); ps.Add("lastTime", lastUpdateTime); } // int rev = PubConn.ExecuteSql(stringSql.ToString(), ps.ToParameters()); DataSet ds = new DataSet(); PubConn.SqlToDataSet(ds, stringSql.ToString(), ps.ToParameters()); var rs = new List<tb_shuntruleconn_config_model>(); if (ds != null && ds.Tables.Count > 0) { foreach (DataRow dr in ds.Tables[0].Rows) { var r = CreateModel(dr); rs.Add(r); } } return rs; }); }
public List <DbModels.certcenterlog> GetPage(XXF.Db.DbConn PubConn, int pno, int pagesize, string keywords, out int totalcount) { string basesql = "select {0} from certcenterlog "; string querysql = string.Format(basesql, " ROW_NUMBER() over (order by reqtime desc ) as rownum,id,url,reqdata,userid,username,reqtime,ip,opecontent"); XXF.Db.SimpleProcedureParameter para = new SimpleProcedureParameter(); string wherecon = ""; if (!string.IsNullOrEmpty(keywords)) { wherecon = " where userid like '%' +@keywords+'%' or username like '%' +@keywords+'%' or url like '%' +@keywords+'%' or ip like '%' +@keywords+'%' "; para.Add("@keywords", keywords); } DataSet ds = new DataSet(); querysql = string.Concat("select A.* from (", querysql, wherecon, ") A", " where A.rownum between ", (pno - 1) * pagesize + 1, " and ", pagesize * pno); PubConn.SqlToDataSet(ds, querysql, para.ToParameters()); List <DbModels.certcenterlog> listlog = new List <DbModels.certcenterlog>(); foreach (DataRow a in ds.Tables[0].Rows) { listlog.Add(CreateModel(a)); } totalcount = (int)PubConn.ExecuteScalar(string.Format(basesql, " count(*) ") + wherecon, para.ToParameters()); return(listlog); }
public tb_tempdata_model GetByTaskID(DbConn PubConn, int taskid) { return SqlHelper.Visit(ps => { ps.Add("taskid", taskid); string sql = "select * from tb_tempdata where taskid=@taskid"; DataSet ds = new DataSet(); PubConn.SqlToDataSet(ds, sql, ps.ToParameters()); tb_tempdata_model model = CreateModel(ds.Tables[0].Rows[0]); return model; }); }
public virtual tb_node_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_node s where s.id=@id"); DataSet ds = new DataSet(); PubConn.SqlToDataSet(ds, stringSql.ToString(), Par); if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0) { return CreateModel(ds.Tables[0].Rows[0]); } return null; }
public virtual string Get2(DbConn PubConn, string key) { List<ProcedureParameter> Par = new List<ProcedureParameter>(); Par.Add(new ProcedureParameter("@key", key)); StringBuilder stringSql = new StringBuilder(); stringSql.Append(@"select value from tb_config s WITH(NOLOCK) where s.[key]=@key"); DataSet ds = new DataSet(); PubConn.SqlToDataSet(ds, stringSql.ToString(), Par); if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0) { return Convert.ToString(ds.Tables[0].Rows[0][0]); } return null; }
public virtual tb_consumer_model GetByTempId(DbConn PubConn, long tempid) { List<ProcedureParameter> Par = new List<ProcedureParameter>(); Par.Add(new ProcedureParameter("@tempid", tempid)); StringBuilder stringSql = new StringBuilder(); stringSql.Append(@"select s.* from tb_consumer s WITH(NOLOCK) where s.tempid=@tempid"); DataSet ds = new DataSet(); PubConn.SqlToDataSet(ds, stringSql.ToString(), Par); if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0) { return CreateModel(ds.Tables[0].Rows[0]); } return null; }
public virtual tb_mqpath_model GetByPartitionID(DbConn PubConn, int partitionid) { List<ProcedureParameter> Par = new List<ProcedureParameter>(); Par.Add(new ProcedureParameter("@partitionid", partitionid)); StringBuilder stringSql = new StringBuilder(); stringSql.Append(@"select m.* from tb_mqpath_partition s,tb_mqpath m WITH(NOLOCK) where s.partitionid=@partitionid and s.mqpathid=m.id"); DataSet ds = new DataSet(); PubConn.SqlToDataSet(ds, stringSql.ToString(), Par); if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0) { return CreateModel(ds.Tables[0].Rows[0]); } return null; }
public List <DbModels.api> GetGradeApis(XXF.Db.DbConn PubConn, int apptype, int appgradeno) { string sql = "SELECT apiid,apptype,appgradeno,categoryid,apiname,apititle,area,controller,action,para,apidesc,freeze FROM api where apptype=" + apptype + " and appgradeno=" + appgradeno; DataSet ds = new DataSet(); PubConn.SqlToDataSet(ds, sql, null); List <DbModels.api> list = new List <DbModels.api>(); foreach (DataRow dr in ds.Tables[0].Rows) { list.Add(CreateModel(dr)); } return(list); }
public virtual List<tb_config_model> List(DbConn PubConn) { List<ProcedureParameter> Par = new List<ProcedureParameter>(); StringBuilder stringSql = new StringBuilder(); stringSql.Append(@"select s.* from tb_config s WITH(NOLOCK)"); DataSet ds = new DataSet(); PubConn.SqlToDataSet(ds, stringSql.ToString(), Par); List<tb_config_model> rs = new List<tb_config_model>(); if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0) { foreach (DataRow dr in ds.Tables[0].Rows) rs.Add(CreateModel(dr)); } return rs; }
public int GetMaxCommandID(DbConn PubConn) { return SqlHelper.Visit(ps => { StringBuilder stringSql = new StringBuilder(); stringSql.Append(@"select max(id) from tb_command s "); DataSet ds = new DataSet(); PubConn.SqlToDataSet(ds, stringSql.ToString(), ps.ToParameters()); if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0) { return Convert.ToInt32(ds.Tables[0].Rows[0][0]); } return 0; }); }
public virtual tb_consumer_client_model GetByClient(DbConn PubConn, string client) { return SqlHelper.Visit((ps) => { ps.Add("@client", client); StringBuilder stringSql = new StringBuilder(); stringSql.Append(@"select top 1 s.* from tb_consumer_client s WITH(NOLOCK) where s.client=@client"); DataSet ds = new DataSet(); PubConn.SqlToDataSet(ds, stringSql.ToString(), ps.ToParameters()); if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0) { return CreateModel(ds.Tables[0].Rows[0]); } return null; }); }
public virtual tb_mqpath_model Get(DbConn PubConn, string mqpath) { return SqlHelper.Visit((ps) => { List<ProcedureParameter> Par = new List<ProcedureParameter>(); Par.Add(new ProcedureParameter("@mqpath", mqpath)); StringBuilder stringSql = new StringBuilder(); stringSql.Append(@"select s.* from tb_mqpath s WITH(NOLOCK) where s.mqpath=@mqpath"); DataSet ds = new DataSet(); PubConn.SqlToDataSet(ds, stringSql.ToString(), Par); if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0) { return CreateModel(ds.Tables[0].Rows[0]); } return null; }); }
public virtual string GetTempData(DbConn PubConn, int taskid) { return SqlHelper.Visit(ps => { StringBuilder stringSql = new StringBuilder(); stringSql.Append(@"select s.* from tb_tempdata s where s.taskid=@taskid"); ps.Add("taskid", taskid); DataSet ds = new DataSet(); PubConn.SqlToDataSet(ds, stringSql.ToString(), ps.ToParameters()); if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0) { return Convert.ToString( ds.Tables[0].Rows[0]["tempdatajson"]); } return null; }); }
public CertCenter.Models.DbModels.tb_token GetToken(XXF.Db.DbConn PubConn, string userid, string appid, CertCenter.Models.DbModels.TokenType tokentype) { string sql = " select token,userid,id,username,appid,createtime,expires from " + tokentype.ToString() + " where userid=@userid and appid=@appid"; List <ProcedureParameter> Par = new List <ProcedureParameter>(); Par.Add(new ProcedureParameter("@userid", userid)); Par.Add(new ProcedureParameter("@appid", appid)); DataSet ds = new DataSet(); PubConn.SqlToDataSet(ds, sql, Par); if (ds.Tables[0].Rows.Count != 1) { return(null); } return(CreateModel(ds.Tables[0].Rows[0])); }
public virtual tb_version_model GetCurrentVersion(DbConn PubConn, int taskid,int version) { return SqlHelper.Visit(ps => { ps.Add("@taskid", taskid); ps.Add("@version", version); StringBuilder stringSql = new StringBuilder(); stringSql.Append(@"select s.* from tb_version s where s.taskid=@taskid and s.version=@version"); DataSet ds = new DataSet(); PubConn.SqlToDataSet(ds, stringSql.ToString(), ps.ToParameters()); if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count>0) { return CreateModel(ds.Tables[0].Rows[0]); } return null; }); }
public tb_user_model GetUserName(DbConn PubConn, string userstaffno) { return SqlHelper.Visit(ps => { ps.Add("userstaffno", userstaffno); string sql = "select id,username,userrole from tb_user where userstaffno=@userstaffno"; DataSet ds = new DataSet(); PubConn.SqlToDataSet(ds, sql, ps.ToParameters()); if (ds.Tables[0].Rows.Count > 0) { tb_user_model m = CreateModel(ds.Tables[0].Rows[0]); return m; } else return null; }); }
public List<tb_version_model> GetTaskVersion(DbConn PubConn, int taskid) { return SqlHelper.Visit(ps => { ps.Add("@taskid", taskid); string sql = "select version,zipfilename from tb_version where taskid=@taskid"; DataSet ds = new DataSet(); PubConn.SqlToDataSet(ds, sql, ps.ToParameters()); List<tb_version_model> model = new List<tb_version_model>(); foreach (DataRow dr in ds.Tables[0].Rows) { tb_version_model m = CreateModel(dr); model.Add(m); } return model; }); }
public virtual tb_datanode_model Get2(DbConn PubConn, int datanodepartition) { return SqlHelper.Visit((ps) => { List<ProcedureParameter> Par = new List<ProcedureParameter>(); Par.Add(new ProcedureParameter("@datanodepartition", datanodepartition)); StringBuilder stringSql = new StringBuilder(); stringSql.Append(@"select s.* from tb_datanode s WITH(NOLOCK) where s.datanodepartition=@datanodepartition"); DataSet ds = new DataSet(); PubConn.SqlToDataSet(ds, stringSql.ToString(), Par); if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0) { return CreateModel(ds.Tables[0].Rows[0]); } return null; }); }
public bool CheckMaxPartitionIndexOfMqPathIsRunning(DbConn PubConn, int mqpathid) { List<ProcedureParameter> Par = new List<ProcedureParameter>(); Par.Add(new ProcedureParameter("@mqpathid", mqpathid)); Par.Add(new ProcedureParameter("@state", (int)XXF.BaseService.MessageQuque.BusinessMQ.SystemRuntime.EnumMqPathPartitionState.Running)); StringBuilder stringSql = new StringBuilder(); stringSql.Append(@"select top 1 id from tb_mqpath_partition s WITH(NOLOCK) where s.mqpathid=@mqpathid and partitionindex = (select max(partitionindex) from tb_mqpath_partition s WITH(NOLOCK) where s.mqpathid=@mqpathid) and state=@state"); DataSet ds = new DataSet(); PubConn.SqlToDataSet(ds, stringSql.ToString(), Par); if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0) { if (ds.Tables[0].Rows[0][0] is DBNull) return false; return true; } return true; }
public static string GetConnStr(CertCenter.Models.DbModels.TokenType tokentype, string userid = "") { string constr = ""; switch (tokentype) { case CertCenter.Models.DbModels.TokenType.usertoken: constr = XXF.Db.DbConfig.GetConfig("dydDbConn"); break; case CertCenter.Models.DbModels.TokenType.shoptoken: using (XXF.Db.DbConn PubConn = XXF.Db.DbConfig.CreateConn(XXF.Db.DbType.SQLSERVER, XXF.Db.DbConfig.GetConfig("dydDbConn"))) { PubConn.Open(); string sql = "select f_shzh,f_shdqbm,f_shsj from tb_shop_area where f_shzh=@userid or f_shsj=@userid"; XXF.Db.SimpleProcedureParameter para = new SimpleProcedureParameter(); para.Add("@userid", userid); DataSet ds = new DataSet(); PubConn.SqlToDataSet(ds, sql, para.ToParameters()); if (ds.Tables[0].Rows.Count == 0) { return(""); } string dqbm = ds.Tables[0].Rows[0]["f_shdqbm"].ToString(); string t = XXF.Db.DbConfig.GetConfig("ShopAreaConnectString"); throw new Exception("no show"); // constr = XXF.Db.DbAreaRule.ShopAreaPartitionRule(t, Convert.ToInt32(dqbm)); } break; case CertCenter.Models.DbModels.TokenType.managetoken: constr = XXF.Db.DbConfig.GetConfig("CrmDbConn"); break; default: break; } if (constr == "") { throw new Exception("车有问题。"); } return(constr); }
public List<tb_performanceinfo_model> GetAllWithTask(DbConn PubConn, string nodeid, string taskid, string orderby, DateTime? lastupdatetime) { return SqlHelper.Visit(ps => { StringBuilder stringSql = new StringBuilder(); stringSql.Append(@"select p.*,n.nodename,t.taskname from tb_performance p,tb_node n,tb_task t where p.nodeid=n.id and p.taskid=t.id "); if (!string.IsNullOrEmpty(nodeid)) { stringSql.Append(@" and p.nodeid=@nodeid "); ps.Add("@nodeid",nodeid); } if (!string.IsNullOrEmpty(taskid)) { stringSql.Append(@" and taskid=@taskid "); ps.Add("@taskid", taskid); } if (lastupdatetime!=null) { stringSql.Append(@" and lastupdatetime>@lastupdatetime "); ps.Add("@lastupdatetime", lastupdatetime); } if (!string.IsNullOrEmpty(orderby)) { stringSql.Append(@" order by " + orderby); } DataSet ds = new DataSet(); PubConn.SqlToDataSet(ds, stringSql.ToString(), ps.ToParameters()); List<tb_performanceinfo_model> rs = new List<tb_performanceinfo_model>(); if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0) { foreach (DataRow dr in ds.Tables[0].Rows) { tb_performanceinfo_model m = new tb_performanceinfo_model(); m.model = CreateModel(dr); m.nodename = Convert.ToString(dr["nodename"]); m.taskname = Convert.ToString(dr["taskname"]); rs.Add(m); } } return rs; }); }
public List<tb_user_model> GetAllUsers(DbConn PubConn) { return SqlHelper.Visit(ps => { StringBuilder stringSql = new StringBuilder(); stringSql.Append(@"select * from tb_user s order by id desc"); DataSet ds = new DataSet(); PubConn.SqlToDataSet(ds, stringSql.ToString(), ps.ToParameters()); List<tb_user_model> rs = new List<tb_user_model>(); if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0) { foreach (DataRow dr in ds.Tables[0].Rows) { rs.Add(CreateModel(dr)); } } return rs; }); }
public virtual List<tb_datanode_model> List(DbConn PubConn, List<int> datanodepartitions) { return SqlHelper.Visit((ps) => { List<tb_datanode_model> rs = new List<tb_datanode_model>(); if (datanodepartitions.Count > 0) { List<ProcedureParameter> Par = new List<ProcedureParameter>(); StringBuilder stringSql = new StringBuilder(); stringSql.Append(string.Format(@"select s.* from tb_datanode s WITH(NOLOCK) where datanodepartition in ({0})", SqlHelper.CmdIn<int>(Par, datanodepartitions))); DataSet ds = new DataSet(); PubConn.SqlToDataSet(ds, stringSql.ToString(), Par); if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0) { foreach (DataRow dr in ds.Tables[0].Rows) rs.Add(CreateModel(dr)); } } return rs; }); }
public virtual DateTime? GetLastUpdateTimeOfMqPath(DbConn PubConn, string mqpath) { return SqlHelper.Visit<DateTime?>((ps) => { List<ProcedureParameter> Par = new List<ProcedureParameter>(); Par.Add(new ProcedureParameter("@mqpath", mqpath)); StringBuilder stringSql = new StringBuilder(); stringSql.Append(@"select lastupdatetime from tb_mqpath s WITH(NOLOCK) where s.mqpath=@mqpath"); DataSet ds = new DataSet(); PubConn.SqlToDataSet(ds, stringSql.ToString(), Par); if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0) { var o = ds.Tables[0].Rows[0]["lastupdatetime"]; if (Convert.IsDBNull(o)) return null; else return Convert.ToDateTime(o); } return null; }); }
public List<tb_command_model> GetNodeCommands(DbConn PubConn, int nodeid, int lastmaxid) { return SqlHelper.Visit(ps => { ps.Add("@nodeid", nodeid); ps.Add("@id", lastmaxid); StringBuilder stringSql = new StringBuilder(); stringSql.Append(@"select s.* from tb_command s where (s.nodeid=@nodeid or s.nodeid=0) and s.id>@id order by id asc"); DataSet ds = new DataSet(); PubConn.SqlToDataSet(ds, stringSql.ToString(), ps.ToParameters()); List<tb_command_model> rs = new List<tb_command_model>(); if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0) { foreach (DataRow dr in ds.Tables[0].Rows) { rs.Add(CreateModel(dr)); } } return rs; }); }
public List<tb_category_model> GetList(DbConn PubConn,string keyword) { return SqlHelper.Visit(ps => { string sql = "select id,categoryname,categorycreatetime from tb_category "; if (!string.IsNullOrWhiteSpace(keyword)) { ps.Add("keyword", keyword); sql += "where categoryname like '%'+@keyword+'%'"; } DataSet ds = new DataSet(); PubConn.SqlToDataSet(ds, sql, ps.ToParameters()); List<tb_category_model> Model = new List<tb_category_model>(); foreach (DataRow dr in ds.Tables[0].Rows) { tb_category_model m = CreateModel(dr); Model.Add(m); } return Model; }); }
public List<int> GetTaskIDsByState(DbConn PubConn, int taskstate,int nodeid) { return SqlHelper.Visit(ps => { ps.Add("@taskstate", taskstate); ps.Add("@nodeid", nodeid); StringBuilder stringSql = new StringBuilder(); stringSql.Append(@"select id from tb_task s where s.taskstate=@taskstate and s.nodeid=@nodeid"); DataSet ds = new DataSet(); PubConn.SqlToDataSet(ds, stringSql.ToString(), ps.ToParameters()); List<int> rs = new List<int>(); if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0) { foreach (DataRow dr in ds.Tables[0].Rows) { rs.Add(Convert.ToInt32(dr[0])); } } return rs; }); }
public List<tb_task_model> GetLongRunningTaskIDs(DbConn PubConn, int maxrunningtimeseconds) { return SqlHelper.Visit(ps => { ps.Add("@maxrunningtime", DateTime.Parse("1900-01-01").AddSeconds((int)maxrunningtimeseconds)); ps.Add("@taskstate", (int)EnumTaskState.Running); StringBuilder stringSql = new StringBuilder(); stringSql.Append(@"select * from tb_task s where (s.tasklastendtime-s.tasklaststarttime)>@maxrunningtime and taskstate=@taskstate"); DataSet ds = new DataSet(); PubConn.SqlToDataSet(ds, stringSql.ToString(), ps.ToParameters()); List<tb_task_model> rs = new List<tb_task_model>(); if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0) { foreach (DataRow dr in ds.Tables[0].Rows) { rs.Add(CreateModel(dr)); } } return rs; }); }
public List<tb_node_model> GetList(DbConn PubConn, string keyword, string cstime, string cetime, int pagesize, int pageindex, out int count) { int _count = 0; List<tb_node_model> Model = new List<tb_node_model>(); DataSet dsList = SqlHelper.Visit<DataSet>(ps => { string sqlwhere = ""; string sql = "select ROW_NUMBER() over(order by id desc) as rownum,id,nodename,nodecreatetime,nodeip,nodelastupdatetime,ifcheckstate from tb_node where 1=1 "; if (!string.IsNullOrWhiteSpace(keyword)) { ps.Add("keyword", keyword); sqlwhere = " and (nodename like '%'+@keyword+'%' or nodeip like '%'+@keyword+'%') "; } DateTime d = DateTime.Now; if (DateTime.TryParse(cstime, out d)) { ps.Add("CStime", Convert.ToDateTime(cstime)); sqlwhere += " and nodecreatetime>=@CStime"; } if (DateTime.TryParse(cetime, out d)) { ps.Add("CEtime", Convert.ToDateTime(cetime)); sqlwhere += " and nodecreatetime<=@CEtime"; } _count = Convert.ToInt32(PubConn.ExecuteScalar("select count(1) from tb_node where 1=1 " + sqlwhere, ps.ToParameters())); DataSet ds = new DataSet(); string sqlSel = "select * from (" + sql + sqlwhere + ") A where rownum between " + ((pageindex - 1) * pagesize + 1) + " and " + pagesize * pageindex; PubConn.SqlToDataSet(ds, sqlSel, ps.ToParameters()); return ds; }); foreach (DataRow dr in dsList.Tables[0].Rows) { tb_node_model n = CreateModel(dr); Model.Add(n); } count = _count; return Model; }
public List<tb_senderror_model> GetErrors(DbConn PubConn, int lastlogid) { return SqlHelper.Visit(ps => { ps.Add("@lastlogid", lastlogid); StringBuilder stringSql = new StringBuilder(); stringSql.Append(@"select top 100 s.*,t.taskcreateuserid,t.taskname from tb_error s,tb_task t where s.id>@lastlogid and s.taskid=t.id order by s.id desc"); DataSet ds = new DataSet(); PubConn.SqlToDataSet(ds, stringSql.ToString(), ps.ToParameters()); List<tb_senderror_model> rs = new List<tb_senderror_model>(); if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0) { foreach (DataRow dr in ds.Tables[0].Rows) { tb_senderror_model model = new tb_senderror_model(); model.error_model = CreateModel(dr); model.taskcreateuserid = Convert.ToInt32(dr["taskcreateuserid"]); model.taskname = Convert.ToString(dr["taskname"]); rs.Add(model); } } return rs; }); }
public Models.DbModels.api Get(DbConn PubConn, string apiname, int apptype) { XXF.Db.SimpleProcedureParameter para = new SimpleProcedureParameter(); para.Add("area", apiname ?? ""); para.Add("@apptype", apptype); StringBuilder stringSql = new StringBuilder(); stringSql.Append(@"select s.* from api s where s.appname=@appname and s.apptype=@apptype"); DataSet ds = new DataSet(); PubConn.SqlToDataSet(ds, stringSql.ToString(), para.ToParameters()); if (ds != null && ds.Tables.Count > 0) { return CreateModel(ds.Tables[0].Rows[0]); } return null; }
public List<tb_loginfo_model> GetList(DbConn PubConn, string keyword, int id, string cstime, string cetime, int logtype, int taskid, int nodeid, int pagesize, int pageindex, out int count) { List<tb_loginfo_model> model = new List<tb_loginfo_model>(); int _count = 0; DataSet dsList = SqlHelper.Visit<DataSet>(ps => { string sqlwhere = ""; string sql = "select ROW_NUMBER() over(order by E.id desc) as rownum,E.*,ISNULL(T.taskcreateuserid,0) taskcreateuserid, u.username as taskusername,T.taskname,n.nodename as tasknodename from tb_log E left join tb_task T on E.taskid=T.id left join tb_user u on t.taskcreateuserid=u.id left join tb_node n on n.id=E.nodeid where 1=1 "; if (!string.IsNullOrWhiteSpace(keyword)) { ps.Add("keyword", keyword); sqlwhere = " and msg like '%'+@keyword+'%' "; } if (taskid != -1) { ps.Add("taskid", taskid); sqlwhere = " and E.taskid=@taskid"; } if (nodeid != -1) { ps.Add("nodeid", nodeid); sqlwhere = " and E.nodeid=@nodeid"; } if (id != -1) { ps.Add("id", id); sqlwhere = " and E.id=@id"; } DateTime d = DateTime.Now; if (DateTime.TryParse(cstime, out d)) { ps.Add("CStime", Convert.ToDateTime(cstime)); sqlwhere += " and E.logcreatetime>=@CStime"; } if (DateTime.TryParse(cetime, out d)) { ps.Add("CEtime", Convert.ToDateTime(cetime)); sqlwhere += " and E.logcreatetime<=@CEtime"; } if (logtype != -1) { ps.Add("logtype", logtype); sqlwhere += " and E.logtype=@logtype"; } _count = Convert.ToInt32(PubConn.ExecuteScalar("select count(1) from tb_log E where 1=1 " + sqlwhere, ps.ToParameters())); DataSet ds = new DataSet(); string sqlSel = "select * from (" + sql + sqlwhere + ") A where rownum between " + ((pageindex - 1) * pagesize + 1) + " and " + pagesize * pageindex; PubConn.SqlToDataSet(ds, sqlSel, ps.ToParameters()); return ds; }); foreach (DataRow dr in dsList.Tables[0].Rows) { tb_loginfo_model m = new tb_loginfo_model(); m.log_model = CreateModel(dr); m.taskusername = Convert.ToString(dr["taskusername"]); m.taskname = Convert.ToString(dr["taskname"]); model.Add(m); } count = _count; return model; }
public List<tb_command_model_Ex> GetList(DbConn PubConn, int commandstate, int taskid, int nodeid, int pagesize, int pageindex, out int count) { int _count = 0; List<tb_command_model_Ex> Model = new List<tb_command_model_Ex>(); DataSet dsList = SqlHelper.Visit<DataSet>(ps => { string sqlwhere = ""; string sql = "select ROW_NUMBER() over(order by C.id desc) as rownum,C.*,T.taskname,n.nodename from tb_command C LEFT JOIN tb_task T on C.taskid=T.id left join tb_node n on C.nodeid=n.id where 1=1 "; if (taskid!=-1) { ps.Add("taskid", taskid); sqlwhere = " and C.taskid=@taskid "; } if (nodeid != -1) { ps.Add("nodeid", nodeid); sqlwhere = " and C.nodeid=@nodeid "; } if (commandstate != -1) { ps.Add("commandstate", commandstate); sqlwhere = " and C.commandstate=@commandstate "; } _count = Convert.ToInt32(PubConn.ExecuteScalar("select count(1) from tb_command C where 1=1 " + sqlwhere, ps.ToParameters())); DataSet ds = new DataSet(); string sqlSel = "select * from (" + sql + sqlwhere + ") A where rownum between " + ((pageindex - 1) * pagesize + 1) + " and " + pagesize * pageindex; PubConn.SqlToDataSet(ds, sqlSel, ps.ToParameters()); return ds; }); foreach (DataRow dr in dsList.Tables[0].Rows) { tb_command_model_Ex n = CreateModelEX(dr); Model.Add(n); } count = _count; return Model; }
public tb_command_model_Ex GetOneCommand(DbConn PubConn, int id) { return SqlHelper.Visit(ps => { ps.Add("id", id); string sql = "select C.*,T.taskname from tb_command C LEFT JOIN tb_task T on C.taskid=T.id where C.id=@id"; DataSet ds = new DataSet(); PubConn.SqlToDataSet(ds, sql, ps.ToParameters()); tb_command_model_Ex model = CreateModelEX(ds.Tables[0].Rows[0]); return model; }); }