/// <summary>
/// 获取分页数据，利用RowNumber()方式
/// </summary>
/// <typeparam name="T">泛型</typeparam>
/// <param name="tableName">数据表名『eg:Orders』</param>
/// <param name="fields">要读取的字段『*:所有列；或者：eg:OrderID,OrderDate,ShipName,ShipCountry』</param>
/// <param name="orderField">依据排序的列『eg:OrderID』</param>
/// <param name="orderBy">排序方式『升序，降序』</param>
/// <param name="sqlWhere">筛选条件『eg:Order=1，若无筛选条件，则""』</param>
/// <param name="pageSize">每页显示多少条数据</param>
/// <param name="pageIndex">当前页码</param>
/// <returns>泛型分页结果</returns>
/// <exception cref="FrameworkException">SQL异常</exception>
public PagedList <T> ExecutePageQuery <T>(string tableName, string fields, string orderField, OrderType orderBy, string sqlWhere, int pageSize, int pageIndex) where T : class
{
string _sql = SqlServerPageScript.TablePageSQLByRowNumber(tableName, fields, orderField, sqlWhere, orderBy, pageSize, pageIndex);
try
{
string[] _sqlBuilder = _sql.Split(';');
List <T> _pageList = ExecuteReader <T>(_sqlBuilder[0], null);
int _totalCount = (int)ExecuteScalar(_sqlBuilder[1], null);
return(new PagedList <T>(_pageList, pageIndex, pageSize, _totalCount));
}
catch (SqlException ex)
{
string _sqlExMessage = ex.GetSqlExceptionMessage();
ex.Data.Add("sqlServerConnectString", connectionString);
ex.Data.Add("sql", _sql);
ex.Data.Add("tableName", tableName);
ex.Data.Add("orderField", orderField);
ex.Data.Add("orderField", orderField);
ex.Data.Add("orderBy", orderBy.ToString());
ex.Data.Add("sqlWhere", sqlWhere);
ex.Data.Add("pageSize", pageSize);
ex.Data.Add("pageIndex", pageIndex);
throw new FrameworkException(_sqlExMessage, ex);
}
}
public void QueryPageListTest()
{
string _sql = SqlServerPageScript.TablePageSQLByRowNumber("Person", "*", "Age", string.Empty, OrderType.Desc, 5, 1);
var _acutal = sqlHelper.QueryPageList <Person>(_sql, 1, 5);
Assert.AreEqual(10, _acutal.TotalCount);
Assert.AreEqual(2, _acutal.TotalPage);
}
/// <summary>
/// 获取分页数据，利用RowNumber()方式
/// </summary>
/// <param name="tableName">数据表名『eg:Orders』</param>
/// <param name="fields">要读取的字段『*:所有列；或者：eg:OrderID,OrderDate,ShipName,ShipCountry』</param>
/// <param name="orderField">依据排序的列『eg:OrderID』</param>
/// <param name="orderBy">排序方式『升序，降序』</param>
/// <param name="sqlWhere">筛选条件『eg:Order=1，若无筛选条件，则""』</param>
/// <param name="pageSize">每页显示多少条数据</param>
/// <param name="pageIndex">当前页码</param>
/// <param name="totalPage">分页总数</param>
/// <param name="totalCount">记录总数</param>
/// <returns>分页DataTable</returns>
/// 时间：2016/9/8 11:25
/// 备注：
/// <exception cref="FrameworkException"></exception>
public DataTable ExecutePageQuery(string tableName, string fields, string orderField, OrderType orderBy, string sqlWhere, int pageSize, int pageIndex, out int totalPage, out int totalCount)
{
totalPage = 0;
totalCount = 0;
string _sql = SqlServerPageScript.TablePageSQLByRowNumber(tableName, fields, orderField, sqlWhere, orderBy, pageSize, pageIndex);
try
{
DataSet _result = ExecuteDataSet(_sql, null);
if (!_result.IsNullOrEmpty())
{
if (!_result.Tables[0].IsNullOrEmpty() && !_result.Tables[1].IsNullOrEmpty())
{
totalCount = _result.Tables[1].Rows[0][0].ToIntOrDefault(0);
totalPage = (int)Math.Ceiling(totalCount / (double)pageSize);
return(_result.Tables[0]);
}
}
}
catch (SqlException ex)
{
string _sqlExMessage = ex.GetSqlExceptionMessage();
ex.Data.Add("sqlServerConnectString", connectionString);
ex.Data.Add("sql", _sql);
ex.Data.Add("tableName", tableName);
ex.Data.Add("orderField", orderField);
ex.Data.Add("orderField", orderField);
ex.Data.Add("orderBy", orderBy.ToString());
ex.Data.Add("sqlWhere", sqlWhere);
ex.Data.Add("pageSize", pageSize);
ex.Data.Add("pageIndex", pageIndex);
throw new FrameworkException(_sqlExMessage, ex);
}
return(new DataTable());
}