输入条件:1.错误级别;2.开始时间;3.结束时间。
分页样式:1.当前页码;2.一页的行数。
step1:获取SQL语句条件部分:
A-> “日志表”左连“用户表”在用户_ID字段上:
FROM sys_log sl LEFT JOIN mem_user mu ON sl.user_id=mu.id
B-> 条件:
WHERE sl.id is not null
1.错误级别;
sql.Append(" AND sl.log_level=@log_level");
sqlParameterList.Add(new SqlParameter("@log_level", condition.LogLevel));
2.开始时间;
sql.Append(" AND sl.record_time>='" + condition.BeginDate.Value.ToString("yyyy-MM-dd") + " 00:00:00'");
3.结束时间
sql.Append(" AND sl.record_time<='" + condition.EndDate.Value.ToString("yyyy-MM-dd") + " 23:59:59'");
step2:获取该条件下记录总行数:(List<SqlParameter> sqlParameterList)
string sqlCount = "SELECT COUNT(*) " + sql.ToString();
step3:获取该条件下 再加分页条件(List<SqlParameter> sqlParameterList此时必须备份克隆,不然会跑“对象被占用”错误)
A->获取拼接后的字段:SELECT sl.* ,mu.name AS user_name
B->设立行号字段:ROW_NUMBER() OVER (ORDER BY sl.record_time) AS rowindex (FROM sys_log sl LEFT JOIN mem_user mu ON sl.user_id=mu.id)
C->分页条件下,行号范围条件: WHERE temptb.rowindex BETWEEN " + page.BeginPageIndex + " AND " + page.EndPageIndex
step4:符合条件的记录存入DataTable--》List
DataTable-------》(DataRow)------------》(LogDetail)---------------》List
DataTable dt =查询的结果;
List<Model.Log.LogDetail> logList = new List<Model.Log.LogDetail>();
foreach (DataRow dr in dt.Rows)
{
Model.Log.LogDetail log = new Model.Log.LogDetail();
log.Level = (Model.Log.LogLevel)Convert.ToInt32(dr["log_level"]);
log.Source = Convert.ToString(dr["source"]);
log.Type = (Model.Log.LogType)Convert.ToInt32(dr["log_level"]);
log.Content = Convert.ToString(dr["content"]);
log.RecordTime = Convert.ToDateTime(dr["record_time"]);
log.UserID = Convert.ToInt32(dr["user_id"]);
log.UserName = Convert.ToString(dr["user_name"]);
logList.Add(log);
}