// ***********************************************************************
// ***********************************************************************
// 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;
}
