C#分页
/// <summary>
/// </summary>
/// <param name="field">分页依据的字段（单表一般为主键）</param>
/// <param name="sort">分页的排序规则</param>
/// <param name="pageNo">页码</param>
/// <param name="pageSize">每页条数</param>
/// <param name="total">总记录数</param>
/// <returns></returns>
public DataSet UserTermInfo( string field, string sort, int pageNo, int pageSize, out int total)
{
StringBuilder sbd = new StringBuilder(@"select a.*,b.DeptName from dbo.UserInfo a,dbo.Department b
where a.dept);
string strSql = GetPagerSql(sbd.ToString(), field, sort, pageNo, pageSize);
DataSet ds = SqlHelper.Instance.ExecSqlDataSet(strSql);
total = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
return ds;
}
/// <summary>
/// 获取分页的sql语句
/// </summary>
/// <param name="sql">分页前的查询sql语句</param>
/// <param name="field">分页依据的字段（单表一般为主键）</param>
/// <param name="sort">分页的排序规则</param>
/// <param name="pageNo">页码</param>
/// <param name="pageSize">每页数据条数</param>
/// <returns>实现了分页功能的sql语句</returns>
private static string GetPagerSql(string sql, string field, string sort, int pageNo, int pageSize)
{
return string.Format(
@"select * from (
select *,row_number() over (order by [{0}] {1}) __PagerRowNoField from (
{2}
) s
) t where __PagerRowNoField between {3} and {4};
select count(*) from ({2}) v",
field, sort, sql, (pageNo - 1) * pageSize + 1, pageNo * pageSize);
}