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