예제 #1
0
 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);
        }
예제 #4
0
 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);
        }