public List <tb_tasklist_model> GetList(DbConn PubConn, string taskid, string keyword, string CStime, string CEtime, int categoryid, int nodeid, int userid, int state, int pagesize, int pageindex, out int count) { int _count = 0; List <tb_tasklist_model> model = new List <tb_tasklist_model>(); DataSet dsList = SqlHelper.Visit <DataSet>(ps => { string sqlwhere = ""; StringBuilder sql = new StringBuilder(); //sql.Append("select ROW_NUMBER() over(order by T.id desc) as rownum,T.*,C.categoryname,N.nodename,U.username from tb_task T "); sql.Append("select T.id as rownum,T.*,C.categoryname,N.nodename,U.username from tb_task T "); sql.Append("left join tb_category C on C.id=T.categoryid "); sql.Append("left join tb_user U on U.id=T.taskcreateuserid "); sql.Append("left join tb_node N on N.id=T.nodeid where 1=1 "); if (!string.IsNullOrWhiteSpace(taskid)) { ps.Add("taskid", taskid); sqlwhere += " and ( T.id =@taskid )"; } if (!string.IsNullOrWhiteSpace(keyword)) { ps.Add("keyword", keyword); sqlwhere += " and ( T.taskname like '%'+@keyword+'%' or T.taskremark like '%'+@keyword+'%' )"; } if (categoryid != -1) { ps.Add("categoryid", categoryid); sqlwhere += " and T.categoryid=@categoryid"; } if (nodeid != -1) { ps.Add("nodeid", nodeid); sqlwhere += " and T.nodeid=@nodeid"; } if (state != -999) { ps.Add("taskstate", state); sqlwhere += " and T.taskstate=@taskstate"; } if (userid != -1) { ps.Add("taskcreateuserid", userid); sqlwhere += " and T.taskcreateuserid=@taskcreateuserid"; } DateTime d = DateTime.Now; if (DateTime.TryParse(CStime, out d)) { ps.Add("CStime", Convert.ToDateTime(CStime)); sqlwhere += " and T.taskcreatetime>=@CStime"; } if (DateTime.TryParse(CEtime, out d)) { ps.Add("CEtime", Convert.ToDateTime(CEtime)); sqlwhere += " and T.taskcreatetime<=@CEtime"; } _count = Convert.ToInt32(PubConn.ExecuteScalar("select count(1) from tb_task T 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; string sqlSel = "select * from (" + sql + sqlwhere + ") A order by rownum desc LIMIT " + ((pageindex - 1) * pagesize) + " , " + pagesize; PubConn.SqlToDataSet(ds, sqlSel, ps.ToParameters()); return(ds); }); foreach (DataRow dr in dsList.Tables[0].Rows) { tb_tasklist_model m = CreateModelList(dr); model.Add(m); } count = _count; return(model); }
public virtual tb_tasklist_model CreateModelList(DataRow dr) { var o = new tb_tasklist_model(); // if (dr.Table.Columns.Contains("id")) { o.id = dr["id"].Toint(); } // if (dr.Table.Columns.Contains("taskname")) { o.taskname = dr["taskname"].Tostring(); } // if (dr.Table.Columns.Contains("categoryid")) { o.categoryid = dr["categoryid"].Toint(); } // if (dr.Table.Columns.Contains("nodeid")) { o.nodeid = dr["nodeid"].Toint(); } // if (dr.Table.Columns.Contains("taskcreatetime")) { o.taskcreatetime = dr["taskcreatetime"].ToDateTime(); } // if (dr.Table.Columns.Contains("taskupdatetime")) { o.taskupdatetime = dr["taskupdatetime"].ToDateTime(); } // if (dr.Table.Columns.Contains("tasklaststarttime")) { o.tasklaststarttime = dr["tasklaststarttime"].ToDateTime(); } // if (dr.Table.Columns.Contains("tasklastendtime")) { o.tasklastendtime = dr["tasklastendtime"].ToDateTime(); } // if (dr.Table.Columns.Contains("tasklasterrortime")) { o.tasklasterrortime = dr["tasklasterrortime"].ToDateTime(); } // if (dr.Table.Columns.Contains("taskerrorcount")) { o.taskerrorcount = dr["taskerrorcount"].Toint(); } // if (dr.Table.Columns.Contains("taskruncount")) { o.taskruncount = dr["taskruncount"].Toint(); } // if (dr.Table.Columns.Contains("taskcreateuserid")) { o.taskcreateuserid = dr["taskcreateuserid"].Toint(); } // if (dr.Table.Columns.Contains("taskstate")) { o.taskstate = dr["taskstate"].ToByte(); } // if (dr.Table.Columns.Contains("taskversion")) { o.taskversion = dr["taskversion"].Toint(); } // if (dr.Table.Columns.Contains("taskappconfigjson")) { o.taskappconfigjson = dr["taskappconfigjson"].Tostring(); } // if (dr.Table.Columns.Contains("taskcron")) { o.taskcron = dr["taskcron"].Tostring(); } // if (dr.Table.Columns.Contains("taskmainclassnamespace")) { o.taskmainclassnamespace = dr["taskmainclassnamespace"].Tostring(); } // if (dr.Table.Columns.Contains("taskremark")) { o.taskremark = dr["taskremark"].Tostring(); } // if (dr.Table.Columns.Contains("nodename")) { o.nodename = dr["nodename"].Tostring(); } // if (dr.Table.Columns.Contains("categoryname")) { o.categoryname = dr["categoryname"].Tostring(); } // if (dr.Table.Columns.Contains("username")) { o.username = dr["username"].Tostring(); } return(o); }