public virtual bool Add(DbConn PubConn, tb_timewatchlog_sql_dayreport_model model) { List <ProcedureParameter> Par = new List <ProcedureParameter>() { //sql 哈希 new ProcedureParameter("@sqlhash", model.sqlhash), //日期 new ProcedureParameter("@date", model.date), //平均耗时 new ProcedureParameter("@avgtime", model.avgtime), //最大耗时 new ProcedureParameter("@maxtime", model.maxtime), //最小耗时 new ProcedureParameter("@mintime", model.mintime), //上次扫描最大id new ProcedureParameter("@lastmaxid", model.lastmaxid), //上次扫描更新时间 new ProcedureParameter("@lastupdatetime", model.lastupdatetime), //调用次数 new ProcedureParameter("@count", model.count) }; int rev = PubConn.ExecuteSql(@"insert into tb_timewatchlog_sql_dayreport(sqlhash,date,avgtime,maxtime,mintime,lastmaxid,lastupdatetime,count) values(@sqlhash,@date,@avgtime,@maxtime,@mintime,@lastmaxid,@lastupdatetime,@count)" , Par); return(rev == 1); }
public virtual bool Edit(DbConn PubConn, tb_timewatchlog_sql_dayreport_model model) { List <ProcedureParameter> Par = new List <ProcedureParameter>() { //sql 哈希 new ProcedureParameter("@sqlhash", model.sqlhash), //日期 new ProcedureParameter("@date", model.date), //平均耗时 new ProcedureParameter("@avgtime", model.avgtime), //最大耗时 new ProcedureParameter("@maxtime", model.maxtime), //最小耗时 new ProcedureParameter("@mintime", model.mintime), //上次扫描最大id new ProcedureParameter("@lastmaxid", model.lastmaxid), //上次扫描更新时间 new ProcedureParameter("@lastupdatetime", model.lastupdatetime), //调用次数 new ProcedureParameter("@count", model.count) }; Par.Add(new ProcedureParameter("@id", model.id)); int rev = PubConn.ExecuteSql("update tb_timewatchlog_sql_dayreport set sqlhash=@sqlhash,date=@date,avgtime=@avgtime,maxtime=@maxtime,mintime=@mintime,lastmaxid=@lastmaxid,lastupdatetime=@lastupdatetime,count=@count where id=@id", Par); return(rev == 1); }
public virtual tb_timewatchlog_sql_dayreport_model CreateModel(DataRow dr) { var o = new tb_timewatchlog_sql_dayreport_model(); // if (dr.Table.Columns.Contains("id")) { o.id = dr["id"].Toint(); } //sql 哈希 if (dr.Table.Columns.Contains("sqlhash")) { o.sqlhash = dr["sqlhash"].Tostring(); } //日期 if (dr.Table.Columns.Contains("date")) { o.date = dr["date"].ToDateTime(); } //平均耗时 if (dr.Table.Columns.Contains("avgtime")) { o.avgtime = dr["avgtime"].Todouble(); } //最大耗时 if (dr.Table.Columns.Contains("maxtime")) { o.maxtime = dr["maxtime"].Todouble(); } //最小耗时 if (dr.Table.Columns.Contains("mintime")) { o.mintime = dr["mintime"].Todouble(); } //上次扫描最大id if (dr.Table.Columns.Contains("lastmaxid")) { o.lastmaxid = dr["lastmaxid"].Toint(); } //上次扫描更新时间 if (dr.Table.Columns.Contains("lastupdatetime")) { o.lastupdatetime = dr["lastupdatetime"].ToDateTime(); } //调用次数 if (dr.Table.Columns.Contains("count")) { o.count = dr["count"].Toint(); } //调用次数 if (dr.Table.Columns.Contains("sql")) { o.sql = dr["sql"].Tostring(); } return(o); }
public List <tb_timewatchlog_sql_dayreport_model> GetList(DbConn PubConn, string sqlhash, string sqlcontent, string timebegin, string timeend, string orderby, int pagesize, int pageindex, out int count) { int _count = 0; List <tb_timewatchlog_sql_dayreport_model> model = new List <tb_timewatchlog_sql_dayreport_model>(); DataSet dsList = SqlHelper.Visit <DataSet>(ps => { string sqlwhere = " where 1=1 "; DateTime dtimebegin = DateTime.Now; if (DateTime.TryParse(timebegin, out dtimebegin)) { ps.Add("timebegin", dtimebegin); sqlwhere += " and d.date>=@timebegin "; } DateTime dtimeend = DateTime.Now; if (DateTime.TryParse(timeend, out dtimeend)) { ps.Add("timeend", timeend); sqlwhere += " and d.date<=@timeend "; } if (!string.IsNullOrWhiteSpace(sqlhash)) { ps.Add("sqlhash", sqlhash); sqlwhere += " and d.sqlhash=@sqlhash "; } if (!string.IsNullOrWhiteSpace(sqlcontent)) { ps.Add("sqlcontent", sqlcontent); sqlwhere += " and s.sql like '%'+@sqlcontent+'%' "; } if (string.IsNullOrWhiteSpace(orderby)) { orderby = "date"; } StringBuilder sql = new StringBuilder(); sql.Append("select ROW_NUMBER() over(order by d." + orderby + " desc) as rownum,d.*,s.sql from tb_timewatchlog_sql_dayreport" + " d with (nolock) left join tb_sqlhash_consult s with (nolock) on d.sqlhash=s.sqlhash " + sqlwhere); _count = Convert.ToInt32(PubConn.ExecuteScalar("select count(1) from tb_timewatchlog_sql_dayreport" + " d with (nolock) left join tb_sqlhash_consult s with (nolock) on d.sqlhash=s.sqlhash " + 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_timewatchlog_sql_dayreport_model m = CreateModel(dr); //调用次数 if (dr.Table.Columns.Contains("sql")) { m.sql = dr["sql"].Tostring(); } model.Add(m); } count = _count; return(model); }
public bool StatisSqlMinitor() { tb_timewatchlog_sql_dayreport_model staticDayReport = new tb_timewatchlog_sql_dayreport_model(); string day = ""; string date = ""; List <string> sqlConnList = new List <string>(); using (DbConn PubConn = DbConfig.CreateConn(XXFConfig.MonitorPlatformConnectionString)) { PubConn.Open(); DateTime dateNow = PubConn.GetServerDate(); day = dateNow.ToString("yyyyMMdd"); date = dateNow.ToString("yyyy-MM-dd"); sqlConnList = new tb_database_config_dal().GetDataBaseSqlConnList(PubConn, (int)DataBaseType.Timewatch); } foreach (string conn in sqlConnList) { using (DbConn PubConn = DbConfig.CreateConn(conn)) { PubConn.Open(); int oldMaxID = GetOldDayReportMaxID(PubConn, date); Dictionary <string, tb_timewatchlog_sql_dayreport_model> oldDayReportList = GetOldDayReport(PubConn, date); List <tb_timewatchlog_sql_dayreport_model> newDayReportList = GetNewDayReport(PubConn, day, oldMaxID); foreach (tb_timewatchlog_sql_dayreport_model m in newDayReportList) { if (oldDayReportList.Keys.Contains(m.sqlhash)) { tb_timewatchlog_sql_dayreport_model oldStaticDayReport = oldDayReportList[m.sqlhash]; tb_timewatchlog_sql_dayreport_model newStaticDayReport = new tb_timewatchlog_sql_dayreport_model() { id = oldStaticDayReport.id, avgtime = (oldStaticDayReport.avgtime * oldStaticDayReport.count + m.avgtime * m.count) / (oldStaticDayReport.count + m.count), maxtime = oldStaticDayReport.maxtime > m.maxtime ? oldStaticDayReport.maxtime : m.maxtime, mintime = oldStaticDayReport.mintime < m.mintime ? oldStaticDayReport.mintime : m.mintime, lastmaxid = m.lastmaxid, count = oldStaticDayReport.count + m.count, lastupdatetime = DateTime.Now }; Update(PubConn, newStaticDayReport); } else { Insert(PubConn, m); } } } } return(true); }
public Dictionary <string, tb_timewatchlog_sql_dayreport_model> GetOldDayReport(DbConn PubConn, string day) { return(SqlHelper.Visit(ps => { ps.Add("day", day); string sql = "select * from tb_timewatchlog_sql_dayreport with (nolock) where date=@day"; DataSet ds = new DataSet(); PubConn.SqlToDataSet(ds, sql, ps.ToParameters()); Dictionary <string, tb_timewatchlog_sql_dayreport_model> dic = new Dictionary <string, tb_timewatchlog_sql_dayreport_model>(); foreach (DataRow dr in ds.Tables[0].Rows) { tb_timewatchlog_sql_dayreport_model m = CreateModel(dr); dic.Add(m.sqlhash, m); } return dic; })); }
public int Update(DbConn PubConn, tb_timewatchlog_sql_dayreport_model model) { return(SqlHelper.Visit(ps => { ps.Add("id", model.id); ps.Add("avgtime", model.avgtime); ps.Add("maxtime", model.maxtime); ps.Add("mintime", model.mintime); ps.Add("lastmaxid", model.lastmaxid); ps.Add("count", model.count); string sql = @"update tb_timewatchlog_sql_dayreport set avgtime=@avgtime,maxtime=@maxtime,mintime=@mintime, lastmaxid=@lastmaxid,lastupdatetime=getdate(),count=@count where id=@id"; int rev = PubConn.ExecuteSql(sql, ps.ToParameters()); return rev; })); }
public int Insert(DbConn PubConn, tb_timewatchlog_sql_dayreport_model model) { return(SqlHelper.Visit(ps => { ps.Add("sqlhash", model.sqlhash); ps.Add("avgtime", model.avgtime); ps.Add("maxtime", model.maxtime); ps.Add("mintime", model.mintime); ps.Add("lastmaxid", model.lastmaxid); ps.Add("count", model.count); string sql = @"insert into tb_timewatchlog_sql_dayreport (sqlhash,date,avgtime,maxtime,mintime,lastmaxid,lastupdatetime,count) values (@sqlhash,getdate(),@avgtime,@maxtime,@mintime,@lastmaxid,getdate(),@count)"; int rev = PubConn.ExecuteSql(sql, ps.ToParameters()); return rev; })); }
private List <tb_timewatchlog_sql_dayreport_model> GetNewDayReport(DbConn PubConn, string day, int oldmaxid) { return(SqlHelper.Visit(ps => { ps.Add("id", oldmaxid); ps.Add("day", day); string sql = "select logtag as sqlhash,AVG(time) avgtime,MAX(time) maxtime,MIN(time) mintime,count(1) count,MAX(id) lastmaxid from tb_timewatchlog" + day + " with (nolock) where logtype=3 and id>@id Group By LogTag"; DataSet ds = new DataSet(); PubConn.SqlToDataSet(ds, sql, ps.ToParameters()); List <tb_timewatchlog_sql_dayreport_model> model = new List <tb_timewatchlog_sql_dayreport_model>(); foreach (DataRow dr in ds.Tables[0].Rows) { tb_timewatchlog_sql_dayreport_model m = CreateModel(dr); model.Add(m); } return model; })); }