public virtual tb_sqlhash_consult_model CreateModel(DataRow dr)
{
var o = new tb_sqlhash_consult_model();
//
if (dr.Table.Columns.Contains("id"))
{
o.id = dr["id"].Toint();
}
//sql 哈希
if (dr.Table.Columns.Contains("sqlhash"))
{
o.sqlhash = dr["sqlhash"].Tostring();
}
//sql 代码
if (dr.Table.Columns.Contains("sql"))
{
o.sql = dr["sql"].Tostring();
}
//sql 执行次数
if (dr.Table.Columns.Contains("count"))
{
o.count = Convert.ToInt32(dr["count"]);
}
return(o);
}
public List <tb_sqlhash_consult_model> Contrast(DbConn PubConn, string day)
{
return(SqlHelper.Visit(ps =>
{
string sql = "select count(1) count,logtag as sqlhash,msg as sql from tb_timewatchlog" + day + " with (nolock) where logtype=3 group by logtag,msg";
DataSet ds = new DataSet();
PubConn.SqlToDataSet(ds, sql, null);
List <tb_sqlhash_consult_model> model = new List <tb_sqlhash_consult_model>();
foreach (DataRow dr in ds.Tables[0].Rows)
{
tb_sqlhash_consult_model m = CreateModel(dr);
model.Add(m);
}
return model;
}));
}
public virtual bool Edit(DbConn PubConn, tb_sqlhash_consult_model model)
{
List <ProcedureParameter> Par = new List <ProcedureParameter>()
{
//sql 哈希
new ProcedureParameter("@sqlhash", model.sqlhash),
//sql 代码
new ProcedureParameter("@sql", model.sql)
};
Par.Add(new ProcedureParameter("@id", model.id));
int rev = PubConn.ExecuteSql("update tb_sqlhash_consult set sqlhash=@sqlhash,sql=@sql where id=@id", Par);
return(rev == 1);
}
public virtual bool Add(DbConn PubConn, tb_sqlhash_consult_model model)
{
List <ProcedureParameter> Par = new List <ProcedureParameter>()
{
//sql 哈希
new ProcedureParameter("@sqlhash", model.sqlhash),
//sql 代码
new ProcedureParameter("@sql", model.sql),
//count 次数
new ProcedureParameter("@count", model.count),
};
int rev = PubConn.ExecuteSql(@"insert into tb_sqlhash_consult(sqlhash,sql,count)
values(@sqlhash,@sql,@count)" , Par);
return(rev == 1);
}
public List <tb_sqlhash_consult_model> GetChartJson(DbConn PubConn, int count)
{
return(SqlHelper.Visit(ps =>
{
List <tb_sqlhash_consult_model> model = new List <tb_sqlhash_consult_model>();
ps.Add("count", count);
string sql = "select * from tb_sqlhash_consult where count>@count";
DataSet ds = new DataSet();
PubConn.SqlToDataSet(ds, sql, ps.ToParameters());
foreach (DataRow dr in ds.Tables[0].Rows)
{
tb_sqlhash_consult_model m = CreateModel(dr);
m.nodenotes = m.sqlhash.ToString();
model.Add(m);
}
return model;
}));
}
public List <tb_sqlhash_consult_model> GetList(DbConn PubConn, string sqlhash, string sqlcontent, string orderby, int extime, int pagesize, int pageindex, out int count)
{
int _count = 0;
List <tb_sqlhash_consult_model> model = new List <tb_sqlhash_consult_model>();
DataSet dsList = SqlHelper.Visit <DataSet>(ps =>
{
string sqlwhere = " where 1=1 ";
if (extime != 0)
{
ps.Add("extime", extime);
sqlwhere += " and count>@extime ";
}
if (!string.IsNullOrWhiteSpace(sqlhash))
{
ps.Add("sqlhash", sqlhash);
sqlwhere += " and sqlhash = @sqlhash ";
}
if (!string.IsNullOrWhiteSpace(sqlcontent))
{
ps.Add("sqlcontent", sqlcontent);
sqlwhere += " and (sql like '%'+@sqlcontent+'%') ";
}
StringBuilder sql = new StringBuilder();
sql.Append("select ROW_NUMBER() over(order by " + orderby + ") as rownum,* from tb_sqlhash_consult with (nolock)");
_count = Convert.ToInt32(PubConn.ExecuteScalar("select count(1) from tb_sqlhash_consult with (nolock) " + 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_sqlhash_consult_model m = CreateModel(dr);
model.Add(m);
}
count = _count;
return(model);
}
