public List <tb_sqlhash_url_contrast_model> GetList(DbConn PubConn, string sqlhash, string sqlcontent, string url, string orderby, int pagesize, int pageindex, out int count) { int _count = 0; List <tb_sqlhash_url_contrast_model> model = new List <tb_sqlhash_url_contrast_model>(); DataSet dsList = SqlHelper.Visit <DataSet>(ps => { string sqlwhere = " where 1=1 "; if (!string.IsNullOrWhiteSpace(sqlhash)) { ps.Add("sqlhash", sqlhash); sqlwhere += " and C.sqlhash = @sqlhash "; } if (!string.IsNullOrWhiteSpace(url)) { ps.Add("url", url); sqlwhere += " and C.url=@url"; } if (!string.IsNullOrWhiteSpace(sqlcontent)) { ps.Add("sqlcontent", sqlcontent); sqlwhere += " and (E.sql like '%'+@sqlcontent+'%') "; } if (string.IsNullOrWhiteSpace(orderby)) { orderby = "C.url"; } string sql = @"select ROW_NUMBER() over(order by " + orderby + @" desc) as rownum,C.*,ISNULL(D.avgtime,0) avgtime,ISNULL(D.maxtime,0) maxtime,ISNULL(D.mintime,0) mintime,ISNULL(D.date,getdate()) date,E.sql,ISNULL(D.count,0) count from tb_sqlhash_url_contrast C with (nolock) left hash join (select * from tb_timewatchlog_sql_dayreport with (nolock) where id in (select MAX(id) from tb_timewatchlog_sql_dayreport with (nolock) group by sqlhash)) D on C.sqlhash=D.sqlhash LEFT HASH JOIN tb_sqlhash_consult E on C.sqlhash=E.sqlhash "; _count = Convert.ToInt32(PubConn.ExecuteScalar("select count(1) from (" + sql + sqlwhere + ") A", 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_sqlhash_url_contrast_model m = CreateModel(dr); m.avgtime = Convert.ToDecimal(dr["avgtime"]); m.maxtime = Convert.ToDecimal(dr["maxtime"]); m.mintime = Convert.ToDecimal(dr["mintime"]); m.date = Convert.ToDateTime(dr["date"]); m.sql = dr["sql"].ToString(); m.count = Convert.ToInt32(dr["count"]); model.Add(m); } count = _count; return(model); }
public virtual bool Add(DbConn PubConn, tb_sqlhash_url_contrast_model model) { List <ProcedureParameter> Par = new List <ProcedureParameter>() { // new ProcedureParameter("@sqlhash", model.sqlhash), // new ProcedureParameter("@url", model.url) }; int rev = PubConn.ExecuteSql(@"insert into tb_sqlhash_url_contrast(sqlhash,url) values(@sqlhash,@url)" , Par); return(rev == 1); }
public virtual bool Edit(DbConn PubConn, tb_sqlhash_url_contrast_model model) { List <ProcedureParameter> Par = new List <ProcedureParameter>() { // new ProcedureParameter("@sqlhash", model.sqlhash), // new ProcedureParameter("@url", model.url) }; Par.Add(new ProcedureParameter("@id", model.id)); int rev = PubConn.ExecuteSql("update tb_sqlhash_url_contrast set sqlhash=@sqlhash,url=@url where id=@id", Par); return(rev == 1); }
public virtual tb_sqlhash_url_contrast_model CreateModel(DataRow dr) { var o = new tb_sqlhash_url_contrast_model(); // if (dr.Table.Columns.Contains("id")) { o.id = dr["id"].Toint(); } // if (dr.Table.Columns.Contains("sqlhash")) { o.sqlhash = dr["sqlhash"].Toint(); } // if (dr.Table.Columns.Contains("url")) { o.url = dr["url"].Tostring(); } return(o); }