/// <summary>
/// Calculates record and page count for the specified query
/// </summary>
private VirtualRecordCount CalculateVirtualRecordCount()
{
var count = new VirtualRecordCount
{
RecordCount = GetQueryVirtualCount(),
RecordsInLastPage = ItemsPerPage
};
// Calculate the virtual number of records from the query
// Calculate the correspondent number of pages
var lastPage = count.RecordCount / ItemsPerPage;
var 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);
}
/// <summary>
/// Prepares and returns the command object for the reader-based query
/// </summary>
private IDbCommand PrepareCommand(VirtualRecordCount countInfo)
{
// Determines how many records are to be retrieved.
// The last page could require less than other pages
var recsToRetrieve = ItemsPerPage;
if (CurrentPageIndex == countInfo.PageCount - 1)
{
recsToRetrieve = countInfo.RecordsInLastPage;
}
var cmdText = GetQueryPageCommandText(recsToRetrieve);
var conn = SqlUtils.GetIDbConnection(WebConfigUtils.IsMySql, WebConfigUtils.ConnectionString);
var cmd = SqlUtils.GetIDbCommand();
cmd.Connection = conn;
cmd.CommandText = cmdText;
return(cmd);
}
/// <summary>
/// Prepares and returns the command object for the reader-based query
/// </summary>
private IDbCommand PrepareCommand(VirtualRecordCount countInfo)
{
// Determines how many records are to be retrieved.
// The last page could require less than other pages
//var recsToRetrieve = ItemsPerPage;
//if (CurrentPageIndex == countInfo.PageCount - 1)
// recsToRetrieve = countInfo.RecordsInLastPage;
//var cmdText = GetQueryPageCommandText(recsToRetrieve);
var orderString = OrderByString;
if (string.IsNullOrEmpty(orderString))
{
orderString = $"ORDER BY {SortField} {SortMode}";
}
var cmdText = SqlUtils.GetPageSqlString(SelectCommand, orderString, ItemsPerPage, CurrentPageIndex, countInfo.PageCount, countInfo.RecordsInLastPage);
var conn = SqlUtils.GetIDbConnection(WebConfigUtils.DatabaseType, WebConfigUtils.ConnectionString);
var cmd = SqlUtils.GetIDbCommand();
cmd.Connection = conn;
cmd.CommandText = cmdText;
return(cmd);
}
