public IList <SysLogInfo> GetList()
{
StringBuilder sb = new StringBuilder(250);
sb.Append(@"select Id,AppName,MethodName,Message,LastUpdatedDate
from Sys_Log
order by LastUpdatedDate desc " );
IList <SysLogInfo> list = new List <SysLogInfo>();
using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.GzxySiteDbConnString, CommandType.Text, sb.ToString()))
{
if (reader != null && reader.HasRows)
{
while (reader.Read())
{
SysLogInfo model = new SysLogInfo();
model.Id = reader.GetGuid(0);
model.AppName = reader.GetString(1);
model.MethodName = reader.GetString(2);
model.Message = reader.GetString(3);
model.LastUpdatedDate = reader.GetDateTime(4);
list.Add(model);
}
}
}
return(list);
}
public void InsertSysLog(SyslogInfo syslogInfo)
{
try
{
SysLogInfo model = new SysLogInfo();
model.AppName = syslogInfo.AppName;
model.MethodName = syslogInfo.MethodName;
model.Message = syslogInfo.Message;
model.LastUpdatedDate = syslogInfo.LastUpdatedDate;
SysLog bll = new SysLog();
bll.Insert(model);
}
catch (Exception ex)
{
SysLogInfo model = new SysLogInfo();
model.AppName = "应用程序日志服务系统";
model.MethodName = "SysLogService.InsertSysLog";
model.Message = "异常：" + ex.Message + "，请求参数：appName='" + syslogInfo.AppName + "',methodName='" + syslogInfo.MethodName + "',message='" + syslogInfo.Message + "'";
model.LastUpdatedDate = syslogInfo.LastUpdatedDate;
SysLog bll = new SysLog();
bll.Insert(model);
}
}
public IList <SysLogInfo> GetList(string sqlWhere, params SqlParameter[] cmdParms)
{
StringBuilder sb = new StringBuilder(250);
sb.Append(@"select Id,AppName,MethodName,Message,LastUpdatedDate
from Sys_Log ");
if (!string.IsNullOrEmpty(sqlWhere))
{
sb.AppendFormat(" where 1=1 {0} ", sqlWhere);
}
IList <SysLogInfo> list = new List <SysLogInfo>();
using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.GzxySiteDbConnString, CommandType.Text, sb.ToString(), cmdParms))
{
if (reader != null && reader.HasRows)
{
while (reader.Read())
{
SysLogInfo model = new SysLogInfo();
model.Id = reader.GetGuid(0);
model.AppName = reader.GetString(1);
model.MethodName = reader.GetString(2);
model.Message = reader.GetString(3);
model.LastUpdatedDate = reader.GetDateTime(4);
list.Add(model);
}
}
}
return(list);
}
public SysLogInfo GetModel(object Id)
{
SysLogInfo model = null;
StringBuilder sb = new StringBuilder(300);
sb.Append(@"select top 1 Id,AppName,MethodName,Message,LastUpdatedDate
from Sys_Log
where Id = @Id " );
SqlParameter parm = new SqlParameter("@Id", SqlDbType.UniqueIdentifier);
parm.Value = Guid.Parse(Id.ToString());
using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.GzxySiteDbConnString, CommandType.Text, sb.ToString(), parm))
{
if (reader != null)
{
while (reader.Read())
{
model = new SysLogInfo();
model.Id = reader.GetGuid(0);
model.AppName = reader.GetString(1);
model.MethodName = reader.GetString(2);
model.Message = reader.GetString(3);
model.LastUpdatedDate = reader.GetDateTime(4);
}
}
}
return(model);
}
public IList <SysLogInfo> GetList(int pageIndex, int pageSize, out int totalRecords, string sqlWhere, params SqlParameter[] cmdParms)
{
StringBuilder sb = new StringBuilder(250);
sb.Append(@"select count(*) from Sys_Log ");
if (!string.IsNullOrEmpty(sqlWhere))
{
sb.AppendFormat(" where 1=1 {0} ", sqlWhere);
}
totalRecords = (int)SqlHelper.ExecuteScalar(SqlHelper.GzxySiteDbConnString, CommandType.Text, sb.ToString(), cmdParms);
if (totalRecords == 0)
{
return(new List <SysLogInfo>());
}
sb.Clear();
int startIndex = (pageIndex - 1) * pageSize + 1;
int endIndex = pageIndex * pageSize;
sb.Append(@"select * from(select row_number() over(order by LastUpdatedDate desc) as RowNumber,
Id,AppName,MethodName,Message,LastUpdatedDate
from Sys_Log " );
if (!string.IsNullOrEmpty(sqlWhere))
{
sb.AppendFormat(" where 1=1 {0} ", sqlWhere);
}
sb.AppendFormat(@")as objTable where RowNumber between {0} and {1} ", startIndex, endIndex);
IList <SysLogInfo> list = new List <SysLogInfo>();
using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.GzxySiteDbConnString, CommandType.Text, sb.ToString(), cmdParms))
{
if (reader != null && reader.HasRows)
{
while (reader.Read())
{
SysLogInfo model = new SysLogInfo();
model.Id = reader.GetGuid(1);
model.AppName = reader.GetString(2);
model.MethodName = reader.GetString(3);
model.Message = reader.GetString(4);
model.LastUpdatedDate = reader.GetDateTime(5);
list.Add(model);
}
}
}
return(list);
}
public int Insert(SysLogInfo model)
{
StringBuilder sb = new StringBuilder(250);
sb.Append(@"insert into Sys_Log (AppName,MethodName,Message,LastUpdatedDate)
values
(@AppName,@MethodName,@Message,@LastUpdatedDate)
" );
SqlParameter[] parms =
{
new SqlParameter("@AppName", SqlDbType.NVarChar, 20),
new SqlParameter("@MethodName", SqlDbType.VarChar, 100),
new SqlParameter("@Message", SqlDbType.NVarChar, 1000),
new SqlParameter("@LastUpdatedDate", SqlDbType.DateTime)
};
parms[0].Value = model.AppName;
parms[1].Value = model.MethodName;
parms[2].Value = model.Message;
parms[3].Value = model.LastUpdatedDate;
return(SqlHelper.ExecuteNonQuery(SqlHelper.GzxySiteDbConnString, CommandType.Text, sb.ToString(), parms));
}
public int Update(SysLogInfo model)
{
StringBuilder sb = new StringBuilder(250);
sb.Append(@"update Sys_Log set AppName = @AppName,MethodName = @MethodName,Message = @Message,LastUpdatedDate = @LastUpdatedDate
where Id = @Id
" );
SqlParameter[] parms =
{
new SqlParameter("@Id", SqlDbType.UniqueIdentifier),
new SqlParameter("@AppName", SqlDbType.NVarChar, 20),
new SqlParameter("@MethodName", SqlDbType.VarChar, 100),
new SqlParameter("@Message", SqlDbType.NVarChar, 1000),
new SqlParameter("@LastUpdatedDate", SqlDbType.DateTime)
};
parms[0].Value = model.Id;
parms[1].Value = model.AppName;
parms[2].Value = model.MethodName;
parms[3].Value = model.Message;
parms[4].Value = model.LastUpdatedDate;
return(SqlHelper.ExecuteNonQuery(SqlHelper.GzxySiteDbConnString, CommandType.Text, sb.ToString(), parms));
}
/// <summary>
/// 修改数据
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public int Update(SysLogInfo model)
{
return(dal.Update(model));
}
/// <summary>
/// 添加数据到数据库
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public int Insert(SysLogInfo model)
{
return(dal.Insert(model));
}
/// <summary>
/// Method to get system logs information
/// </summary>
/// <param name="fromTime">fromTime</param>
/// <param name="toTime">toTime</param>
/// <param name="eventLevel">eventLevel</param>
/// <param name="eventType">eventType</param>
/// <param name="_operator">_operator</param>
/// <param name="startIndex">startIndex</param>
/// <param name="endIndex">endIndex</param>
/// <param name="rowsCnt">rowsCnt</param>
public List <SysLogInfo> GetSysLogs(DateTime fromTime, DateTime toTime, string[] eventLevel, string[] eventType, string _operator, int startIndex, int endIndex, ref int rowsCnt)
{
try {
var sqlText = new StringBuilder();
sqlText.AppendFormat(@"
DECLARE @LevelTable TABLE([ID] [INT] NOT NULL);
DECLARE @TypeTable TABLE([ID] [INT] NOT NULL);
DECLARE @Pos INT;
SET @Pos = CHARINDEX(',', @EventLevels);
WHILE(@Pos > 0)
BEGIN
INSERT @LevelTable([ID]) VALUES(CAST(LEFT(@EventLevels, @Pos - 1) AS INT));
SELECT @EventLevels = STUFF(@EventLevels, 1, @Pos, ''), @Pos = CHARINDEX(',', @EventLevels);
END
IF(LEN(LTRIM(RTRIM(@EventLevels))) > 0)
BEGIN
INSERT @LevelTable([ID]) VALUES(CAST(@EventLevels AS INT));
END
SET @Pos = CHARINDEX(',', @EventTypes);
WHILE(@Pos > 0)
BEGIN
INSERT @TypeTable([ID]) VALUES(CAST(LEFT(@EventTypes, @Pos - 1) AS INT));
SELECT @EventTypes = STUFF(@EventTypes, 1, @Pos, ''), @Pos = CHARINDEX(',', @EventTypes);
END
IF(LEN(LTRIM(RTRIM(@EventTypes))) > 0)
BEGIN
INSERT @TypeTable([ID]) VALUES(CAST(@EventTypes AS INT));
END
;WITH tpData AS
(
SELECT ROW_NUMBER() OVER (ORDER BY TS.[EventID] DESC) AS RowNo,TS.[EventID],TS.[EventTime],TS.[EventLevel],
TS.[EventType],TS.[Message],TS.[Url],TS.[ClientIP],TS.[Operator] FROM [dbo].[TH_SysLog] TS
INNER JOIN @LevelTable LT ON TS.[EventLevel] = LT.[ID]
INNER JOIN @TypeTable TT ON TS.[EventType] = TT.[ID]
WHERE TS.[EventTime] BETWEEN @FromTime AND @ToTime {0}
),
RowsCnt AS
(
SELECT COUNT(1) AS [TotalCnt] FROM tpData
)
SELECT RC.[TotalCnt],D.[EventID],D.[EventTime],D.[EventLevel],D.[EventType],D.[Message],D.[Url],D.[ClientIP],D.[Operator]
FROM RowsCnt RC CROSS JOIN tpData D WHERE D.RowNo BETWEEN @StartIndex AND @EndIndex;", !String.IsNullOrEmpty(_operator) ? String.Format("AND [Operator] = '{0}'", _operator) : String.Empty);
SqlParameter[] parms = { new SqlParameter("@FromTime", SqlDbType.DateTime),
new SqlParameter("@ToTime", SqlDbType.DateTime),
new SqlParameter("@StartIndex", SqlDbType.Int),
new SqlParameter("@EndIndex", SqlDbType.Int),
new SqlParameter("@EventLevels", SqlDbType.NVarChar, 1024),
new SqlParameter("@EventTypes", SqlDbType.NVarChar, 1024) };
parms[0].Value = fromTime;
parms[1].Value = toTime;
parms[2].Value = startIndex;
parms[3].Value = endIndex;
parms[4].Value = eventLevel != null?String.Join(",", eventLevel) : String.Empty;
parms[5].Value = eventType != null?String.Join(",", eventType) : String.Empty;
var logs = new List <SysLogInfo>();
using (var rdr = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, sqlText.ToString(), parms)) {
while (rdr.Read())
{
var log = new SysLogInfo();
rowsCnt = ComUtility.DBNullInt32Handler(rdr["TotalCnt"]);
log.EventID = ComUtility.DBNullInt32Handler(rdr["EventID"]);
log.EventTime = ComUtility.DBNullDateTimeHandler(rdr["EventTime"]);
log.EventLevel = ComUtility.DBNullSysLogLevelHandler(rdr["EventLevel"]);
log.EventType = ComUtility.DBNullSysLogTypeHandler(rdr["EventType"]);
log.Message = ComUtility.DBNullStringHandler(rdr["Message"]);
log.Url = ComUtility.DBNullStringHandler(rdr["Url"]);
log.ClientIP = ComUtility.DBNullStringHandler(rdr["ClientIP"]);
log.Operator = ComUtility.DBNullStringHandler(rdr["Operator"]);
logs.Add(log);
}
}
return(logs);
} catch { throw; }
}
