/// <summary> /// 分页显示最后一页的数据。 /// </summary> /// <param name="dbConnection">已打开的数据库连接</param> /// <param name="pageSize">每页显示的数据数量</param> /// <param name="recordsetCount">数据总数量</param> /// <param name="selectColumns">需要输出的字段</param> /// <param name="tableNames">表名</param> /// <param name="where">查询条件</param> /// <param name="order_TablenameReverse">含有表名的倒序排序条件</param> /// <param name="order">排序条件</param> /// <returns></returns> public System.Data.IDataReader GetDataReader(Database.DbConnection dbConnection, int pageSize, int recordsetCount, string selectColumns, string tableNames, string where, string order_TablenameReverse, string order) { int intTopCount = pageSize; if ((recordsetCount % pageSize) > 0) { intTopCount = recordsetCount % pageSize; } string strSql = "SELECT * FROM (SELECT TOP " + intTopCount + " " + selectColumns + " FROM " + tableNames + where + order_TablenameReverse + ") AS T2" + order; return(dbConnection.ExecuteReader(strSql)); }
private void Search(Database.DbConnection dbConn, TaskLogCollection taskLogs, IList <AC.Base.DateRange.MonthlyForTickRange> tickRanges, int[] counts, int countIndex, int startIndex, int endIndex) { if (counts[countIndex] > 0 && this.CountReadIndex <= endIndex) { AC.Base.DateRange.MonthlyForTickRange tickRange = tickRanges[countIndex]; System.Data.IDataReader dr = null; if (startIndex <= this.CountReadIndex && (this.CountReadIndex + counts[countIndex] - 1) <= endIndex) { //读所有数据 string strSql = "SELECT * FROM " + Tables.TaskLog.GetTableName(tickRange.Date) + this.GetSqlWhere(tickRange) + this.GetOrderColumn(this.m_OrderIsDesc); dr = dbConn.ExecuteReader(strSql); } else if (startIndex <= this.CountReadIndex && (this.CountReadIndex + counts[countIndex] - 1) > endIndex) { //读前部数据 dr = dbConn.GetDataReader(endIndex - this.CountReadIndex + 1, "*", Tables.TaskLog.GetTableName(tickRange.Date), this.GetSqlWhere(tickRange), this.GetOrderColumn(this.m_OrderIsDesc)); } else if (startIndex > this.CountReadIndex && (this.CountReadIndex + counts[countIndex] - 1) <= endIndex) { //读后部数据 if ((this.CountReadIndex + counts[countIndex] - startIndex) > 0) { dr = dbConn.GetBottomDataReader((this.CountReadIndex + counts[countIndex] - startIndex), "*", Tables.TaskLog.GetTableName(tickRange.Date), this.GetSqlWhere(tickRange), this.GetOrderColumn(!this.m_OrderIsDesc), this.GetOrderColumn(this.m_OrderIsDesc)); } } else if (startIndex > this.CountReadIndex && (this.CountReadIndex + counts[countIndex] - 1) > endIndex) { //读中间数据 dr = dbConn.GetMiddleDataReader(startIndex - this.CountReadIndex, endIndex - this.CountReadIndex, "*", Tables.TaskLog.GetTableName(tickRange.Date), this.GetSqlWhere(tickRange), this.GetOrderColumn(this.m_OrderIsDesc), this.GetOrderColumn(!this.m_OrderIsDesc), this.GetOrderColumn(this.m_OrderIsDesc)); } if (dr != null) { while (dr.Read()) { taskLogs.Add(DrToTaskLog(dr)); } dr.Close(); } this.CountReadIndex += counts[countIndex]; } }
/// <summary> /// 分页显示从第2页至倒数第2页的数据。 /// </summary> /// <param name="dbConnection">已打开的数据库连接</param> /// <param name="pageSize">每页显示的数据数量</param> /// <param name="pageNum">获取第几页的数据</param> /// <param name="selectColumns">需要输出的字段</param> /// <param name="tableNames">表名</param> /// <param name="where">查询条件</param> /// <param name="order_Tablename">含有表名的排序条件</param> /// <param name="order_Reverse">倒序排序条件</param> /// <param name="order">排序条件</param> /// <returns></returns> public System.Data.IDataReader GetDataReader(Database.DbConnection dbConnection, int pageSize, int pageNum, string selectColumns, string tableNames, string where, string order_Tablename, string order_Reverse, string order) { string strSql = "SELECT * FROM (SELECT TOP " + pageSize + " * FROM (SELECT TOP " + (pageSize * pageNum) + " " + selectColumns + " FROM " + tableNames + where + order_Tablename + ") AS T1" + order_Reverse + ") AS T2" + order; return(dbConnection.ExecuteReader(strSql)); }
/// <summary> /// 分页显示第1页的数据。 /// </summary> /// <param name="dbConnection">已打开的数据库连接</param> /// <param name="pageSize">每页显示的数据数量</param> /// <param name="selectColumns">需要输出的字段</param> /// <param name="tableNames">表名</param> /// <param name="where">查询条件</param> /// <param name="order_Tablename">含有表名的排序条件</param> /// <returns></returns> public System.Data.IDataReader GetDataReader(Database.DbConnection dbConnection, int pageSize, string selectColumns, string tableNames, string where, string order_Tablename) { string strSql = "SELECT TOP " + pageSize + " " + selectColumns + " FROM " + tableNames + where + order_Tablename; return(dbConnection.ExecuteReader(strSql)); }
private int CountReadIndex = 0; //已读取的 Count 最后的索引位置。 /// <summary> /// 开始搜索指定页数的数据。 /// </summary> /// <param name="pageNum">搜索第几页的数据。当此参数为“0”时,则表示不进行分页,将所有符合条件的数据全部读取出。</param> /// <returns>搜索结果集合</returns> public TaskLogCollection Search(int pageNum) { this.CountReadIndex = 0; this.m_PageNum = pageNum; TaskLogCollection taskLogs = new TaskLogCollection(); IList <AC.Base.DateRange.MonthlyForTickRange> lstTickRanges = this.DateRange.GetMonthTickRanges(); int[] intCounts = new int[lstTickRanges.Count]; Database.DbConnection dbConn = this.Application.GetDbConnection(); try { if (this.PageSize > 0) { this.m_RecordsetCount = 0; for (int intIndex = 0; intIndex < lstTickRanges.Count; intIndex++) { AC.Base.DateRange.MonthlyForTickRange range = lstTickRanges[intIndex]; string strTableName = Tables.TaskLog.GetTableName(range.Date); if (dbConn.TableIsExist(strTableName)) { string strSql = "SELECT COUNT(*) FROM " + strTableName + this.GetSqlWhere(range); System.Data.IDataReader dr = dbConn.ExecuteReader(strSql); if (dr.Read()) { intCounts[intIndex] = Function.ToInt(dr[0]); this.m_RecordsetCount += intCounts[intIndex]; } } } this.m_PageCount = this.RecordsetCount / this.PageSize; if ((this.RecordsetCount % this.PageSize) > 0) { this.m_PageCount++; } if (this.PageNum < 1) { this.m_PageNum = 1; } if (this.PageNum > this.PageCount) { this.m_PageNum = this.PageCount; } } if (this.PageSize == 0 || this.PageCount == 1) { this.m_RecordsetCount = 0; if (this.m_OrderIsDesc) { for (int intCountIndex = lstTickRanges.Count - 1; intCountIndex >= 0; intCountIndex--) { AC.Base.DateRange.MonthlyForTickRange tickRange = lstTickRanges[intCountIndex]; string strTableName = Tables.TaskLog.GetTableName(tickRange.Date); if (dbConn.TableIsExist(strTableName)) { string strSql = "SELECT * FROM " + strTableName + this.GetSqlWhere(tickRange) + this.GetOrderColumn(this.m_OrderIsDesc); System.Data.IDataReader dr = dbConn.ExecuteReader(strSql); while (dr.Read()) { this.m_RecordsetCount++; taskLogs.Add(DrToTaskLog(dr)); } dr.Close(); } } } else { for (int intCountIndex = 0; intCountIndex < lstTickRanges.Count; intCountIndex++) { AC.Base.DateRange.MonthlyForTickRange tickRange = lstTickRanges[intCountIndex]; string strTableName = Tables.TaskLog.GetTableName(tickRange.Date); if (dbConn.TableIsExist(strTableName)) { string strSql = "SELECT * FROM " + strTableName + this.GetSqlWhere(tickRange) + this.GetOrderColumn(this.m_OrderIsDesc); System.Data.IDataReader dr = dbConn.ExecuteReader(strSql); while (dr.Read()) { this.m_RecordsetCount++; taskLogs.Add(DrToTaskLog(dr)); } dr.Close(); } } } if (this.m_RecordsetCount == 0) { this.m_PageCount = 0; this.m_PageNum = 0; } else { this.m_PageCount = 1; this.m_PageNum = 1; } } else { int intRsStartIndex = this.RecordsetStartIndex; int intRsEndIndex = this.RecordsetEndIndex; if (this.m_OrderIsDesc) { for (int intCountIndex = intCounts.Length - 1; intCountIndex >= 0; intCountIndex--) { this.Search(dbConn, taskLogs, lstTickRanges, intCounts, intCountIndex, intRsStartIndex, intRsEndIndex); } } else { for (int intCountIndex = 0; intCountIndex < intCounts.Length; intCountIndex++) { this.Search(dbConn, taskLogs, lstTickRanges, intCounts, intCountIndex, intRsStartIndex, intRsEndIndex); } } } for (int intIndex = 0; intIndex < lstTickRanges.Count; intIndex++) { string strLogIds = ""; AC.Base.DateRange.MonthlyForTickRange range = lstTickRanges[intIndex]; foreach (TaskLog log in taskLogs) { if (range.Contains(log.LogId)) { strLogIds += "," + log.LogId.ToString(); } } if (strLogIds.Length > 0) { strLogIds = strLogIds.Substring(1); string strTableName; strTableName = Tables.TaskRunLog.GetTableName(range.Date); if (dbConn.TableIsExist(strTableName)) { string strSql = "SELECT * FROM " + strTableName + " WHERE " + Tables.TaskRunLog.TaskLogId + " IN (" + strLogIds + ")"; System.Data.IDataReader dr = dbConn.ExecuteReader(strSql); while (dr.Read()) { long lngLogId = Function.ToLong(dr[Tables.TaskRunLog.TaskLogId]); foreach (TaskLog log in taskLogs) { if (log.LogId == lngLogId) { log.AddTask(dr); break; } } } } } } } catch (Exception ex) { throw ex; } finally { dbConn.Close(); } return(taskLogs); }