number over() 简单的分页示例

/// <summary> /// 获取Paging列表 /// </summary> public List<HousesAgentEntity> GetPageList(int pageIndex, int pageSize, out int amount, string where) { int count = 0; //总页数 string sqlCount = "Select Count(*) From HousesAgent" + where;//根据条件计算总页数 List<HousesAgentEntity> list = new List<HousesAgentEntity>(); #region 组装分页的sql语句 string sql = string.Format(@"Select * From ( Select Row_Number() Over(Order By UserId desc) Rows, * From HousesAgent {0} ) tb Where Rows > @Begin And Rows <= @End", where); SqlParameter[] parameters = { new SqlParameter("@Begin", (pageIndex-1) * pageSize), new SqlParameter("@End", pageIndex * pageSize) }; #endregion try { SqlDataReader dr = SqlSimpleHelper.ExecuteReader(SqlSimpleHelper.ConnectionString, CommandType.Text, sql, parameters); list = DataReaderToList(dr); Object obj = SqlSimpleHelper.ExecuteScalar(SqlSimpleHelper.ConnectionString, CommandType.Text, sqlCount); if (obj != null) { int.TryParse(obj.ToString(), out count); } amount = count; } catch (Exception ex) { //记录异常日志 LogHelper.WriteErrorLog(GetErrorMethod("GetPageList"), ex.Message); amount = 0; } return list; }

，