public List<tb_sqlhash_noparam_model> GetList(DbConn PubConn, string sqlhash, string sqlcontent, string orderby ,int pagesize, int pageindex, out int count)
{
int _count = 0;
List<tb_sqlhash_noparam_model> model = new List<tb_sqlhash_noparam_model>();
DataSet dsList = SqlHelper.Visit<DataSet>(ps =>
{
string sqlwhere = " where 1=1 ";
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_noparam with (nolock) " + sqlwhere);
_count = Convert.ToInt32(PubConn.ExecuteScalar("select count(1) from tb_sqlhash_noparam with (nolock) " + sqlwhere, ps.ToParameters()));
DataSet ds = new DataSet();
string sqlSel = "select * from (" + sql + ") 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_noparam_model m = CreateModel(dr);
model.Add(m);
}
count = _count;
return model;
}
public virtual tb_sqlhash_noparam_model CreateModel(DataRow dr)
{
var o = new tb_sqlhash_noparam_model();
//
if (dr.Table.Columns.Contains("id"))
{
o.id = dr["id"].Toint();
}
//
if (dr.Table.Columns.Contains("sqlhash"))
{
o.sqlhash = dr["sqlhash"].Tostring();
}
//
if (dr.Table.Columns.Contains("sql"))
{
o.sql = dr["sql"].Tostring();
}
//
if (dr.Table.Columns.Contains("count"))
{
o.count = dr["count"].Toint();
}
return(o);
}
public virtual bool Add(DbConn PubConn, tb_sqlhash_noparam_model model)
{
List <ProcedureParameter> Par = new List <ProcedureParameter>()
{
//
new ProcedureParameter("@sqlhash", model.sqlhash),
//
new ProcedureParameter("@sql", model.sql),
//
new ProcedureParameter("@count", model.count)
};
int rev = PubConn.ExecuteSql(@"insert into tb_sqlhash_noparam(sqlhash,sql,count)
values(@sqlhash,@sql,@count)" , Par);
return(rev == 1);
}
public List<tb_sqlhash_noparam_model> GetChartJson(DbConn PubConn, int count)
{
return SqlHelper.Visit(ps =>
{
List<tb_sqlhash_noparam_model> model = new List<tb_sqlhash_noparam_model>();
ps.Add("count", count);
string sql = "select * from tb_sqlhash_noparam where count=@count";
DataSet ds = new DataSet();
PubConn.SqlToDataSet(ds, sql, ps.ToParameters());
foreach (DataRow dr in ds.Tables[0].Rows)
{
tb_sqlhash_noparam_model m = CreateModel(dr);
m.nodenotes = m.sqlhash.ToString();
model.Add(m);
}
return model;
});
}
public virtual bool Edit(DbConn PubConn, tb_sqlhash_noparam_model model)
{
List <ProcedureParameter> Par = new List <ProcedureParameter>()
{
//
new ProcedureParameter("@sqlhash", model.sqlhash),
//
new ProcedureParameter("@sql", model.sql),
//
new ProcedureParameter("@count", model.count)
};
Par.Add(new ProcedureParameter("@id", model.id));
int rev = PubConn.ExecuteSql("update tb_sqlhash_noparam set sqlhash=@sqlhash,sql=@sql,count=@count where id=@id", Par);
return(rev == 1);
}
public bool SqlHashContrast(int sqljudgmentbase)
{
string day = "";
tb_sqlhash_consult_dal condal = new tb_sqlhash_consult_dal();
tb_sqlhash_noparam_dal nodal = new tb_sqlhash_noparam_dal();
List <string> sqlConnList = new List <string>();
using (DbConn PubConn = DbConfig.CreateConn(XXFConfig.MonitorPlatformConnectionString))
{
PubConn.Open();
day = PubConn.GetServerDate().ToString("yyyyMMdd");
sqlConnList = new tb_database_config_dal().GetDataBaseSqlConnList(PubConn, (int)DataBaseType.Timewatch);
}
foreach (string conn in sqlConnList)
{
using (DbConn PubConn = DbConfig.CreateConn(conn))
{
PubConn.Open();
condal.DeleteAll(PubConn);
nodal.DeleteAll(PubConn);
List <tb_sqlhash_consult_model> sqlHashList = Contrast(PubConn, day);
foreach (tb_sqlhash_consult_model m in sqlHashList)
{
if (m.count > sqljudgmentbase)
{
condal.Add(PubConn, m);
}
else
{
tb_sqlhash_noparam_model nomodel = new tb_sqlhash_noparam_model()
{
sql = m.sql,
sqlhash = m.sqlhash,
count = m.count,
};
nodal.Add(PubConn, nomodel);
}
}
}
}
return(true);
}
