// *********************************************************************** #endregion #region 方法 格式化查询语句 // *********************************************************************** /// <summary> /// 方法 格式化查询语句 /// </summary> /// <param name="countInfo">记录统计</param> /// <returns>command对象</returns> private SqlCommand PrepareCommand(VirtualRecordCount countInfo) { // 如排序字段没有定义 if (SortField == "") { // Get metadata for all columns and choose either the primary key // or the // 取得所有列的数据,并取任意一列数据为主键 string text = "SET FMTONLY ON;" + SelectCommand + ";SET FMTONLY OFF;"; SqlDataAdapter adapter = new SqlDataAdapter(text, ConnectionString); DataTable t = new DataTable(); adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey; adapter.Fill(t); DataColumn col = null; if (t.PrimaryKey.Length > 0) col = t.PrimaryKey[0]; else col = t.Columns[0]; SortField = col.ColumnName; } // 确定要得到多少条数据 // 最后一页的数据不会多于前页 int recsToRetrieve = ItemsPerPage; if (CurrentPageIndex == countInfo.PageCount - 1) recsToRetrieve = countInfo.RecordsInLastPage; string cmdText = String.Format(QueryPageCommandText, recsToRetrieve, // {0} --> page size ItemsPerPage * (CurrentPageIndex + 1), // {1} --> size * index SelectCommand, // {2} --> base query SortField, // {3} --> key field in the query SortMode, // {4} --> 排序模式 AlterSortMode(SortMode)); SqlConnection conn = new SqlConnection(ConnectionString); SqlCommand cmd = new SqlCommand(cmdText, conn); return cmd; }
// *********************************************************************** #endregion #region 方法 计算记录统计 // *********************************************************************** /// <summary> /// 方法 计算记录统计 /// 计算指定查询的记录及页面数 /// </summary> /// <returns>记录统计</returns> private VirtualRecordCount CalculateVirtualRecordCount() { VirtualRecordCount count = new VirtualRecordCount(); // 计算记录数 count.RecordCount = GetQueryVirtualCount(); count.RecordsInLastPage = ItemsPerPage; // 计算交互记录信息 int lastPage = count.RecordCount / ItemsPerPage; int remainder = count.RecordCount % ItemsPerPage; if (remainder > 0) lastPage++; count.PageCount = lastPage; // 计算最后一页的记录数 if (remainder > 0) count.RecordsInLastPage = remainder; return count; }