// *********************************************************************** // *********************************************************************** // PRIVATE PrepareCommand // Prepares and returns the command object for the reader-based query private SqlCommand PrepareCommand(VirtualRecordCount countInfo) { // No sort field specified: figure it out 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; } // Determines how many records are to be retrieved. // The last page could require less than other pages 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 "ASC", // Default to ascending order "DESC"); SqlConnection conn = new SqlConnection(ConnectionString); SqlCommand cmd = new SqlCommand(cmdText, conn); return cmd; }
// *********************************************************************** // *********************************************************************** // PRIVATE CalculateVirtualRecordCount // Calculates record and page count for the specified query private VirtualRecordCount CalculateVirtualRecordCount() { VirtualRecordCount count = new VirtualRecordCount(); // Calculate the virtual number of records from the query count.RecordCount = GetQueryVirtualCount(); count.RecordsInLastPage = ItemsPerPage; // Calculate the correspondent number of pages int lastPage = count.RecordCount / ItemsPerPage; int remainder = count.RecordCount % ItemsPerPage; if (remainder > 0) lastPage++; count.PageCount = lastPage; // Calculate the number of items in the last page if (remainder > 0) count.RecordsInLastPage = remainder; return count; }