/// <summary> /// (分页查询)从数据库中获取角色列表 /// </summary> /// <param name="queryModel">查询条件</param> /// <returns>返回角色列表</returns> public DataTable GetRoleList(Entity.System.view.QueryModel queryModel) { try { var roleName = queryModel.roleName ?? ""; var pageNumber = queryModel.offset / queryModel.limit + 1; var pageSize = queryModel.limit; var strSql = new StringBuilder(); strSql.Append("select t.*,COUNT(*) OVER () RESULT_COUNT from ROLEINFO t where t.f_name like '%'||:roleName||'%'"); OracleParameter[] parameters = { new OracleParameter(":roleName", roleName) }; var dbapi = new DbAPI(); dbapi.OpenConn(""); var rst = dbapi.GetDataTable(DbAPI.GeneratePagingSql(strSql.ToString(), pageNumber, pageSize), parameters); dbapi.CloseConn(); return(rst); } catch (Exception ex) { LogBN.WriteLog(typeof(USERROLE_BN), "(分页查询)从数据库中获取角色列表GetRoleList 程序段的异常" + ex); return(null); } }
/// <summary> /// (分页查询)从数据库中获取某个时间段的监控历史记录列表 /// </summary> /// <param name="tableName">表名</param> /// <param name="beginTime">开始时间</param> /// <param name="endTime">结束时间</param> /// <param name="strWhere">其他查询条件</param> /// <param name="pageNumber">页码</param> /// <param name="pageSize">每页记录数</param> /// <returns></returns> private DataTable GetList(string tableName, string beginTime, string endTime, string strWhere, int pageNumber, int pageSize) { var strSql = new StringBuilder(); strSql.Append("select d.devicename, t.* "); strSql.AppendFormat("from {0} t ", tableName); strSql.Append("left join deviceinfo d "); strSql.Append("on t.devicecode = d.devicecode "); strSql.Append("where t.DATETIME between "); strSql.Append("to_date(:beginTime, 'YYYY-MM-DD HH24:MI:SS') and "); strSql.Append("to_date(:endTime, 'YYYY-MM-DD HH24:MI:SS') "); if (!string.IsNullOrWhiteSpace(strWhere)) { strSql.AppendFormat("and {0} ", strWhere); } strSql.Append("order by t.DATETIME desc"); OracleParameter[] parameters = { new OracleParameter(":beginTime", beginTime), new OracleParameter(":endTime", endTime) }; var dbapi = new DbAPI(); dbapi.OpenConn(""); var rst = dbapi.GetDataTable(DbAPI.GeneratePagingSql(strSql.ToString(), pageNumber, pageSize), parameters); dbapi.CloseConn(); return(rst); }
/// <summary> /// 获得数据列表 /// </summary> public DataTablesResult GetList(string logType, string startDate, string endDate, int pageNumber, int pageSize) { StringBuilder strSql = new StringBuilder(); DbAPI dbHelper = new DbAPI(); List <OracleParameter> listParams = new List <OracleParameter>(); strSql.Append(" SELECT LOG_ID,LOGNAME,\"TYPE\",\"CONTENT\",REMARK,DATETIME,US.F_NAME FROM SYSTEMLOG S "); strSql.Append(" LEFT JOIN USERINFO US "); strSql.Append(" ON S.F_ACCOUNT=US.F_ACCOUNT "); strSql.Append(" WHERE 1=1 "); if (!string.IsNullOrEmpty(logType)) { strSql.Append(" AND \"TYPE\" LIKE '%").Append(logType).Append("%' "); //listParams.Add(new OracleParameter(":logType", logType)); } if (!string.IsNullOrEmpty(startDate) && !string.IsNullOrEmpty(endDate)) { strSql.Append(" AND DATETIME BETWEEN TO_DATE(:startDate, 'YYYY-MM-DD HH24:MI:SS') AND TO_DATE(:endDate, 'YYYY-MM-DD HH24:MI:SS') "); listParams.Add(new OracleParameter("startDate", startDate)); listParams.Add(new OracleParameter("endDate", endDate + " 23:59:59")); } strSql.Append(" ORDER BY DATETIME DESC"); try { dbHelper.OpenConn(""); DataTable dt = dbHelper.GetDataTable(DbAPI.GeneratePagingSql(strSql.ToString(), pageNumber, pageSize), listParams.ToArray()); dbHelper.CloseConn(); DataTablesResult result = new DataTablesResult(); result.aaData = dt; result.sEcho = Convert.ToInt16(pageSize); result.iTotalDisplayRecords = GetCount(logType, startDate, endDate, pageNumber, pageSize); result.iTotalRecords = GetCount(logType, startDate, endDate, pageNumber, pageSize).ToString(); return(result); } catch (Exception ex) { LogBN.WriteLog(typeof(SYSTEMLOG_BN), "GetList 程序段的异常" + ex); return(null); } }
/// <summary> /// (分页查询)从数据库中获取某个时间段的监控历史记录列表 /// </summary> /// <param name="tableName">表名</param> /// <param name="devicecode">设备编号</param> /// <param name="beginTime">开始时间</param> /// <param name="endTime">结束时间</param> /// <param name="strWhere">其他查询条件</param> /// <param name="pageNumber">页码</param> /// <param name="pageSize">每页记录数</param> /// <returns></returns> private DataTable GetList(string tableName, string devicecode, string beginTime, string endTime, string strWhere, int pageNumber, int pageSize) { try { var strSql = new StringBuilder(); strSql.Append("select d.devicename, t.*,COUNT(*) OVER () RESULT_COUNT "); strSql.AppendFormat("from {0} t ", tableName); strSql.Append("left join deviceinfo d "); strSql.Append("on t.devicecode = d.devicecode "); strSql.Append("where t.devicecode=:devicecode and t.DATETIME between "); strSql.Append("to_date(:beginTime, 'YYYY-MM-DD HH24:MI:SS') and "); strSql.Append("to_date(:endTime, 'YYYY-MM-DD HH24:MI:SS') "); if (!string.IsNullOrWhiteSpace(strWhere)) { strSql.AppendFormat("and {0} ", strWhere); } strSql.Append("order by t.DATETIME desc"); OracleParameter[] parameters = { new OracleParameter(":devicecode", devicecode), new OracleParameter(":beginTime", beginTime), new OracleParameter(":endTime", endTime) }; var dbapi = new DbAPI(); dbapi.OpenConn(""); var rst = dbapi.GetDataTable(DbAPI.GeneratePagingSql(strSql.ToString(), pageNumber, pageSize), parameters); dbapi.CloseConn(); return(rst); } catch (Exception ex) { LogBN.WriteLog(typeof(MonitorLog), "(分页查询)从数据库中获取某个时间段的监控历史记录列表GetList 程序段的异常" + ex); return(null); } }