public class xxxDal
{
private SqlHelper _sqlHelper = new SqlHelper();
/// <summary>
/// 获取xxx的日志
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public List<LogModel> GetxxxLog(SM_LogModel model)
{
StringBuilder sql = new StringBuilder();
List<SqlParameter> sqlParameters = new List<SqlParameter>();
StringBuilder sqlWhere = new StringBuilder();
if (!string.IsNullOrWhiteSpace(model.LogStartTime))
{
sqlParameters.Add(new SqlParameter("@LogStartTime", model.LogStartTime));
sqlWhere.Append(@" AND h.LogDate > @LogStartTime");
}
if (!string.IsNullOrWhiteSpace(model.LogEndTime))
{
sqlParameters.Add(new SqlParameter("@LogEndTime", model.LogEndTime));
sqlWhere.Append(@" AND h.LogDate < @LogEndTime");
}
if (!string.IsNullOrWhiteSpace(model.LogLevel))
{
sqlParameters.Add(new SqlParameter("@LogLevel", model.LogLevel));
sqlWhere.Append(@" AND h.LogLevel = @LogLevel");
}
if (!string.IsNullOrWhiteSpace(model.LogModule))
{
sqlParameters.Add(new SqlParameter("@LogModule", model.LogModule));
sqlWhere.Append(@" AND h.LogModule = @LogModule");
}
sql.AppendFormat(@"
WITH t AS ( SELECT ROW_NUMBER() OVER ( ORDER BY id DESC ) AS IndexNum ,
[Id] ,
CONVERT(VARCHAR, [LogDate], 21) AS [LogDate] ,
[UserName] ,
SUBSTRING([Description], 0, 150) AS [Description] ,
SUBSTRING([LogMsg], 0, 200) AS [LogMsg] ,
[LogLevel] ,
[LogModule]
FROM [LogSystem].[dbo].[xxxLog] h
WHERE 1 = 1
{0}
)
SELECT *
FROM t
WHERE IndexNum > @startIndex
AND indexnum < @endIndex", sqlWhere);
sqlParameters.Add(new SqlParameter("@startIndex", model.Start));
sqlParameters.Add(new SqlParameter("@endIndex", model.Start + model.Length));
DataTable dt = _sqlHelper.ExecuteDataTable(sql.ToString(), sqlParameters.ToArray());
return DataTableTools<LogModel>.DataTableToList(dt);
}
public int GetxxxLogTotalCount(SM_LogModel model)
{
StringBuilder sql = new StringBuilder(); List<SqlParameter> sqlParameters = new List<SqlParameter>();
sql.Append(@"
SELECT COUNT(*)
FROM [HdPubLog] h where 1=1 ");
if (!string.IsNullOrWhiteSpace(model.LogStartTime))
{
sqlParameters.Add(new SqlParameter("@LogStartTime", model.LogStartTime));
sql.Append(@" AND h.LogDate > @LogStartTime");
}
if (!string.IsNullOrWhiteSpace(model.LogEndTime))
{
sqlParameters.Add(new SqlParameter("@LogEndTime", model.LogEndTime));
sql.Append(@" AND h.LogDate < @LogEndTime");
}
if (!string.IsNullOrWhiteSpace(model.LogLevel))
{
sqlParameters.Add(new SqlParameter("@LogLevel", model.LogLevel));
sql.Append(@" AND h.LogLevel = @LogLevel");
}
if (!string.IsNullOrWhiteSpace(model.LogModule))
{
sqlParameters.Add(new SqlParameter("@LogModule", model.LogModule));
sql.Append(@" AND h.LogModule = @LogModule");
}
return _sqlHelper.ExecuteScalar<int>(sql.ToString(), sqlParameters.ToArray());
}
[HttpPost]
public LogModel GetxxxxSignelLog(int id)
{
string sql = @"
SELECT [Id] ,
CONVERT(VARCHAR(30), [LogDate], 21) AS [LogDate] ,
[UserName] ,
[Description] ,
[LogMsg] ,
[LogLevel] ,
[LogModule] ,
[Id] IndexNum
FROM [LogSystem].[dbo].[xxxxLog] h
WHERE h.id = @Id";
var row = _sqlHelper.ExecuteDataRow(sql, new SqlParameter("@Id", id));
return DataTableTools<LogModel>.DataRowToModel(row);
}
}