top分页方法_MsSql_脚本之家,SQL手写分页

 数据库     |      2019-11-23 07:41

但是福利也可能有弊,它须求排序字段必须结合唯大器晚成记录,且select后的字段列表中,不容许现身与排序字段同名的字段。就算sql2k5及以上版本现已提供了rownum()来进展分页管理,但是利用sql2k开展付出的照旧非常多的复制代码 代码如下:uusing system.collections.generic;sing system;using system.text;/// /// 构造分页后的sql语句/// public static class paginghelper{ /// /// 获取分页sql语句,排序字段须要组合唯大器晚成记录 /// /// 记录总的数量 /// 每页记录数 /// 当前页数 /// sql查询语句 /// 排序字段,四个则用“,”隔断/// 分页sql语句 public static string createpagingsql(int _recordcount, int _pagesize, int _pageindex, string _safesql, string _order田野同志) { //重新整合排序字段,防止有不当 string[] arrstrorders = _orderfield.split(new char[] { ',' }, stringsplitoptions.removeemptyentries); stringbuilder sboriginalorder = new stringbuilder(); //原排序字段 stringbuilder sbreverseo教程rder = new stringbuilder(); //与原排序字段相反,用于分页 for (int i = 0; i < arrstrorders.length; i++) { arrstrorders[i] = arrstrorders[i].trim(); //去除前后空格 if { sboriginalorder.append; sbreverseorder.append; } sboriginalorder.append;

//实现层

int index = arrstrorders[i].indexof; //判定是或不是有起伏标志 if { //替换升降标志,分页所需 bool flag = arrstrorders[i].indexof(" desc", stringcomparison.ordinalignorecase) != -1; sbreverseorder.appendformat("{0} {1}", arrstrorders[i].remove, flag ? "asc" : "desc"); } else { sbreverseorder.appendformat("{0} desc", arrstrorders[i]); } }

---------------------------------------------------------分割线---------------------------------------------------------

//计算总页数 _pagesize = _pagesize == 0 ? _recordcount : _pagesize; int pagecount = (_recordcount + _pagesize - 1) / _pagesize;

public List<UserModel> ShowListPage(int pageindex, int pagesize)
{
string sql = string.Format("select top *from (select ROW_NUMBER() over (order by Id)iid,*from UserType)fy where iid>*{2})", pagesize, pageindex, pagesize);
DataTable dt = DBHelper.GetTable;
var str = JsonConvert.SerializeObject;
return JsonConvert.DeserializeObject<List<UserModel>>;
}

//检查当前页数 if { _pageindex = 1; } else if (_pageindex > pagecount) { _pageindex = pagecount; }

//控制器

stringbuilder sbsql = new stringbuilder(); //第大器晚成页时,直接利用top n,而不举办分页查询 if { sbsql.appendformat(" select top {0} * ", _pagesize); sbsql.appendformat as t ", _safesql); sbsql.appendformat(" order by {0} ", sboriginalorder.tostring; } //最后生机勃勃页时,降低叁个top n else if (_pageindex == pagecount) { sbsql.append; sbsql.append; sbsql.appendformat(" select top {0} * ", _recordcount - _pagesize * ; sbsql.appendformat as t ", _safesql); sbsql.appendformat(" order by {0} ", sbreverseorder.tostring as t "); sbsql.appendformat(" order by {0} ", sboriginalorder.tostring; } //前半页数时的分页 else if (_pageindex < (pagecount / 2 + pagecount % 2)) { sbsql.append; sbsql.append; sbsql.appendformat(" select top {0} * from ", _pagesize); sbsql.append; sbsql.appendformat(" select top {0} * ", _pagesize * _pageindex); sbsql.appendformat as t ", _safesql); sbsql.appendformat(" order by {0} ", sboriginalorder.tostring as t "); sbsql.appendformat(" order by {0} ", sbreverseorder.tostring as t "); sbsql.appendformat(" order by {0} ", sboriginalorder.tostring; } //后半页数时的分页 else { sbsql.appendformat(" select top {0} * from ", _pagesize); sbsql.append; sbsql.appendformat(" select top {0} * ", ((_recordcount % _pagesize) + _pagesize * (pagecount - _pageindex))); sbsql.appendformat as t ", _safesql); sbsql.appendformat(" order by {0} ", sbreverseorder.tostring as t "); sbsql.appendformat(" order by {0} ", sboriginalorder.tostring; }

---------------------------------------------------------分割线---------------------------------------------------------

return sbsql.tostring(); }

public ActionResult ShowPage(int pageindex=1)
{
//豆蔻梢头页突显2条数据
int pagesize = 2;
//记录总的条数
int pagecount = idal.ShowCount();

/// /// 获取记录总量sql语句 /// /// 约束记录数 /// sql查询语句 /// 记录总的数量sql语句 public static string createtopnsql(int _n, string _safesql) { return string.format(" select top {0} * from as t ", _n, _safesql); }

//当条数为总的数量时
if (pagecount % pagecount == 0)
{
ViewBag.fy = pagecount / pagesize;
}
else
{
ViewBag.fy = (pagecount / pagesize) + 1;
}
//页数
ViewBag.pageindex = pageindex;
ViewBag.pagesize = pagesize;
ViewBag.pagecount = pagecount;
ViewBag.list = idal.ShowListPage(pageindex, pagesize);
var resulr = idal.ShowCount();
return View;
}