public virtual bool Edit(DbConn PubConn, tb_timewatchlog_model model)
{
List <ProcedureParameter> Par = new List <ProcedureParameter>()
{
//数据库本地创建时间
new ProcedureParameter("@sqlservercreatetime", model.sqlservercreatetime),
//日志创建时间
new ProcedureParameter("@logcreatetime", model.logcreatetime),
//耗时
new ProcedureParameter("@time", model.time),
//项目名称
new ProcedureParameter("@projectname", model.projectname),
//耗时日志类型：普通日志=0，api接口日志=1，sql日志=2
new ProcedureParameter("@logtype", model.logtype),
//日志标识,sql类型则为sql哈希,api类型则为url
new ProcedureParameter("@logtag", model.logtag),
//当前url
new ProcedureParameter("@url", model.url),
//当前信息
new ProcedureParameter("@msg", model.msg),
//来源ip(代码执行ip)
new ProcedureParameter("@fromip", model.fromip),
//sqlip地址
new ProcedureParameter("@sqlip", model.sqlip),
//其他记录标记信息
new ProcedureParameter("@remark", model.remark)
};
Par.Add(new ProcedureParameter("@id", model.id));
int rev = PubConn.ExecuteSql("update tb_timewatchlog set sqlservercreatetime=@sqlservercreatetime,logcreatetime=@logcreatetime,time=@time,projectname=@projectname,logtype=@logtype,logtag=@logtag,url=@url,msg=@msg,fromip=@fromip,sqlip=@sqlip,remark=@remark where id=@id", Par);
return(rev == 1);
}
public virtual bool Add(DbConn PubConn, tb_timewatchlog_model model)
{
List <ProcedureParameter> Par = new List <ProcedureParameter>()
{
//数据库本地创建时间
new ProcedureParameter("@sqlservercreatetime", model.sqlservercreatetime),
//日志创建时间
new ProcedureParameter("@logcreatetime", model.logcreatetime),
//耗时
new ProcedureParameter("@time", model.time),
//项目名称
new ProcedureParameter("@projectname", model.projectname),
//耗时日志类型：普通日志=0，api接口日志=1，sql日志=2
new ProcedureParameter("@logtype", model.logtype),
//日志标识,sql类型则为sql哈希,api类型则为url
new ProcedureParameter("@logtag", model.logtag),
//当前url
new ProcedureParameter("@url", model.url),
//当前信息
new ProcedureParameter("@msg", model.msg),
//来源ip(代码执行ip)
new ProcedureParameter("@fromip", model.fromip),
//sqlip地址
new ProcedureParameter("@sqlip", model.sqlip),
//其他记录标记信息
new ProcedureParameter("@remark", model.remark)
};
int rev = PubConn.ExecuteSql(@"insert into tb_timewatchlog(sqlservercreatetime,logcreatetime,time,projectname,logtype,logtag,url,msg,fromip,sqlip,remark)
values(@sqlservercreatetime,@logcreatetime,@time,@projectname,@logtype,@logtag,@url,@msg,@fromip,@sqlip,@remark)" , Par);
return(rev == 1);
}
public List <tb_timewatchlog_model> GetChartJson(DbConn PubConn, string day, string stime, string etime, string keyword)
{
return(SqlHelper.Visit(ps =>
{
List <tb_timewatchlog_model> model = new List <tb_timewatchlog_model>();
string zq = "";
DateTime dateNow = DateTime.Now;
if (DateTime.TryParse(day, out dateNow))
{
zq = dateNow.ToString("yyyyMMdd");
}
else
{
zq = DateTime.Now.ToString("yyyyMMdd");
}
if (!string.IsNullOrWhiteSpace(stime))
{
ps.Add("stime", dateNow.ToString("yyyy-MM-dd ") + stime);
}
else
{
ps.Add("stime", dateNow.ToString("yyyy-MM-dd 00:00:00"));
}
if (!string.IsNullOrWhiteSpace(etime))
{
ps.Add("etime", dateNow.ToString("yyyy-MM-dd ") + etime);
}
else
{
ps.Add("etime", dateNow.ToString("yyyy-MM-dd 23:59:59"));
}
ps.Add("keyword", keyword);
string sql = "select * from tb_timewatchlog" + zq + " where logtag=@keyword and logcreatetime between @stime and @etime";
DataSet ds = new DataSet();
PubConn.SqlToDataSet(ds, sql, ps.ToParameters());
foreach (DataRow dr in ds.Tables[0].Rows)
{
tb_timewatchlog_model m = CreateModel(dr);
m.nodenotes = m.logcreatetime.ToString("HH:mm:ss fff");
model.Add(m);
}
return model;
}));
}
public virtual tb_timewatchlog_model CreateModel(DataRow dr)
{
var o = new tb_timewatchlog_model();
//
if (dr.Table.Columns.Contains("id"))
{
o.id = dr["id"].Toint();
}
//数据库本地创建时间
if (dr.Table.Columns.Contains("sqlservercreatetime"))
{
o.sqlservercreatetime = dr["sqlservercreatetime"].ToDateTime();
}
//日志创建时间
if (dr.Table.Columns.Contains("logcreatetime"))
{
o.logcreatetime = dr["logcreatetime"].ToDateTime();
}
//耗时
if (dr.Table.Columns.Contains("time"))
{
o.time = dr["time"].Todouble();
}
//项目名称
if (dr.Table.Columns.Contains("projectname"))
{
o.projectname = dr["projectname"].Tostring();
}
//耗时日志类型：普通日志=0，api接口日志=1，sql日志=2
if (dr.Table.Columns.Contains("logtype"))
{
o.logtype = dr["logtype"].ToByte();
}
//日志标识,sql类型则为sql哈希,api类型则为url
if (dr.Table.Columns.Contains("logtag"))
{
o.logtag = dr["logtag"].Toint();
}
//当前url
if (dr.Table.Columns.Contains("url"))
{
o.url = dr["url"].Tostring();
}
//当前信息
if (dr.Table.Columns.Contains("msg"))
{
o.msg = dr["msg"].Tostring();
}
//来源ip(代码执行ip)
if (dr.Table.Columns.Contains("fromip"))
{
o.fromip = dr["fromip"].Tostring();
}
//sqlip地址
if (dr.Table.Columns.Contains("sqlip"))
{
o.sqlip = dr["sqlip"].Tostring();
}
//其他记录标记信息
if (dr.Table.Columns.Contains("remark"))
{
o.remark = dr["remark"].Tostring();
}
return(o);
}
public List <tb_timewatchlog_model> GetList(DbConn PubConn, string timebegin, string timeend, double timewatchmin, double timewatchmax, string projectname, int logtype, string logtag, string fromip, string sqlip, string orderby, string url, int pagesize, int pageindex, out int count)
{
int _count = 0;
List <tb_timewatchlog_model> model = new List <tb_timewatchlog_model>();
DataSet dsList = SqlHelper.Visit <DataSet>(ps =>
{
string sqlwhere = " where 1=1 ";
string zq = "";
DateTime dtimebegin = DateTime.Now;
if (DateTime.TryParse(timebegin, out dtimebegin))
{
ps.Add("@dtimebegin", dtimebegin);
sqlwhere += " and sqlservercreatetime>=@dtimebegin";
}
DateTime dtimeend = DateTime.Now;
if (DateTime.TryParse(timeend, out dtimeend))
{
ps.Add("@dtimeend", dtimeend);
sqlwhere += " and sqlservercreatetime<=@dtimeend";
}
if (timewatchmin != -1)
{
ps.Add("@timewatchmin", timewatchmin);
sqlwhere += " and time>=@timewatchmin";
}
if (timewatchmax != -1)
{
ps.Add("@timewatchmax", timewatchmax);
sqlwhere += " and time<=@timewatchmax";
}
if (!string.IsNullOrWhiteSpace(projectname))
{
ps.Add("@projectname", projectname);
sqlwhere += " and projectname<=@projectname";
}
if (!string.IsNullOrWhiteSpace(url))
{
ps.Add("@url", url);
sqlwhere += " and url=@url";
}
if (logtype != -1)
{
ps.Add("@logtype", logtype);
sqlwhere += " and logtype=@logtype";
}
if (!string.IsNullOrWhiteSpace(fromip))
{
ps.Add("@fromip", fromip);
sqlwhere += " and fromip=@fromip";
}
if (!string.IsNullOrWhiteSpace(sqlip))
{
ps.Add("@sqlip", sqlip);
sqlwhere += " and sqlip=@sqlip";
}
if (!string.IsNullOrWhiteSpace(logtag))
{
ps.Add("@logtag", logtag);
sqlwhere += " and logtag=@logtag";
}
string orderbywhere = "";
if (string.IsNullOrWhiteSpace(orderby))
{
orderbywhere = "sqlservercreatetime desc";
}
else
{
orderbywhere = orderby;
}
zq = dtimeend.ToString("yyyyMMdd");
StringBuilder sql = new StringBuilder();
sql.Append("select ROW_NUMBER() over(order by " + orderbywhere + ") as rownum,* from tb_timewatchlog" + zq + " with (nolock) " + sqlwhere);
_count = Convert.ToInt32(PubConn.ExecuteScalar("select count(1) from tb_timewatchlog" + zq + " 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_timewatchlog_model m = CreateModel(dr);
model.Add(m);
}
count = _count;
return(model);
}
