/// <summary>
/// SQL 分页查询 SQL 语句
/// </summary>
/// <param name="querySQL">SQL 语句，注意不带排序字段</param>
/// <param name="orderby">排序字段，注意：不要带 order by 关键字，如: order1 desc,order2 asc</param>
/// <param name="pageData">分页数据</param>
/// <returns>返回整理过的分页 SQL 语句</returns>
protected override string GetPagedQuerySQL(string querySQL, string orderby, PagedData pageData)
{
if (querySQL.IsEmpty())
{
throw new Exception("参数 querySQL 不能为空！");
}
if (orderby.IsEmpty())
{
throw new Exception("参数 orderby 不能为空！");
}
if (pageData == null)
{
throw new Exception("参数 pageData 不能为空！");
}
if (pageData.RecordCount == 0)
{
return(querySQL);
}
switch (MyDataBaseType)
{
case DataBaseType.SQLSERVER2000:
{
const string FirstPagedQuerySQLText = "select top {0} * from ({1}) as ___table0 order by {2}";
const string PagedQuerySQLText = "select * from (select top {0} * from (select top {1} * from ({2}) as ___table0 order by {3}) as ___table1 order by {4}) as ___table2 order by {3}";
if (pageData.IsFirstPage)
{
return(string.Format(FirstPagedQuerySQLText, pageData.RecordCountInPage, querySQL, orderby));
}
// 定义一个配置数组用来填充查询语句5748
object[] configFields = new object[5];
configFields[0] = pageData.RecordCountInPage;
configFields[1] = pageData.LastIndexInPage + 1;
configFields[2] = querySQL;
configFields[3] = orderby;
configFields[4] = this.GetOrderbyDesc(orderby); // 和 orderby 的排序相反的排序字段字符串
// 填充查询语句
return(string.Format(PagedQuerySQLText, configFields));
}
case DataBaseType.SQLSERVER2005:
case DataBaseType.SQLSERVER2008:
{
//对sql2005, sql2008，换成下面一种写法，以提高查询效率
const string FirstPagedQuerySQLText = "select top {0} * from ({1}) as ___table0 order by {2}";
const string PagedQuerySQLText = @"select top {0} * from
(select row_number() over(order by {1}) as ___rownumber,* from ({2}) as ___table0) as ___table1
where ___rownumber>={3} order by ___rownumber";
if (pageData.IsFirstPage)
{
//return string.Format(FirstPagedQuerySQLText, pageData.RecordCountInPage, querySQL, orderby);
/* 上面这个写法，当pageData.RecordCountInPage小于10，且orderby中有排序要求时，查询会比较慢（可能是sqlserver2008的一个bug）；
* 故改成了下面这个写法，这时，在业务程序中应注意设置PageSize大于等于10。缪卫华 2015-07-31
*/
return(string.Format(FirstPagedQuerySQLText, pageData.PageSize, querySQL, orderby));
}
// 定义一个配置数组用来填充查询语句
object[] configFields = new object[5];
configFields[0] = pageData.RecordCountInPage; //top
configFields[1] = orderby; //orderby
configFields[2] = querySQL; //table
configFields[3] = pageData.FirstIndexInPage + 1; //firstindexinpage
// 填充查询语句
return(string.Format(PagedQuerySQLText, configFields));
}
default:
throw new Exception("不正确的数据库类型" + MyDataBaseType.ToString());
}
}