//public override DataTable ExeSqlForDataTable(string selectSQL, SqlParameterCollection sqlParameterCollection, string dataTableName, string orderby, int pageSize, int pageIndex, out int allRecordCount)
//{
// allRecordCount = this.ExeSqlForObject(this.GetRowCountQuerySQL(selectSQL), sqlParameterCollection).ToInt32();
// PagedData pagedData = new PagedData(allRecordCount, pageSize, pageIndex);
// return this.ExeSqlForDataTable(this.GetPagedQuerySQL(selectSQL, orderby, pagedData), sqlParameterCollection, dataTableName);
//}
#region 分页语句
/// <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);
}
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}";
if (pageData.IsFirstPage)
{
return(string.Format(FirstPagedQuerySQLText, pageData.RecordCountInPage, 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));
}
/// <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));
}
// 定义一个配置数组用来填充查询语句
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));
}
// 定义一个配置数组用来填充查询语句
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());
}
}