/// <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; }
}