Ajax+存储过程真分页实例解析(10W数据毫秒级+项目解析)
周末闲来无事，突然想写个分页的东西玩玩，说走就走
在文章最后我会把整个项目+数据库附上，下载下来直接运行就可以看效果了。整个项目采用的是简单三层模式，开发平开是VS2010+SQL2012
一、我要做什么
很显然，我想完成一个数据的真分页功能，怎么完成呢，干脆就写一个列表页，取名为：学生列表页信息。
列表页当数据量很大的时候，如果采用的假分页，将导致漫长的等待，同时极大的占用服务器资源等等。总之，诸多不便，十分不科学。所以这里我想写一个简洁的功能较完善的列表页，它支持字段搜索，用户自定义排序(选择排序字段、选择升序或降序)以及自定义每页显示行数，当然它还是无刷新的。整个页面的布局如下：
二、我要怎么做
我的整个蓝图已经构建出来了，接下来就是怎么做了。根据我的需求，首先页面要求是无刷新的，所以这里我们肯定要使用到的技术是ajax，说到ajax，肯定离不开json，它用来与服务器打交道来回传输数据。
第二点就是真分页，关于真分页，总所周知就是利用sql的row_number来给数据加索引，然后根据条件去取。但是数据量百万级别的时候，row_number也不见得效率，因为它首先要把你的百万数据整个row_number一遍加上索引，效率可想而知，这里我使用的是另一种方法：in 与 not in。这里我把我用的真分页存储过程贴出来（注：这个存储过程并不是我写，这是我在网上找到的(某位高人写的)，具体链接也不记得了，总之在这里感谢作者），稍后再来看这个存储过程的参数。
ALTER PROC [dbo].[Proc_EWorkFlow_DataPaging] @tbname sysname, --要分页显示的表名 @FieldKey sysname, --用于定位记录的主键(惟一键)字段,只能是单个字段 @PageCurrent int=1, --要显示的页码 @PageSize int=10, --每页的大小(记录数) @FieldShow nvarchar(1000)='', --以逗号分隔的要显示的字段列表,如果不指定,则显示所有字段 @FieldOrder nvarchar(1000)='', --以逗号分隔的排序字段列表,可以指定在字段后面指定DESC/ASC -- 用于指定排序顺序 @Where nvarchar(1000)='', --查询条件 @PageCount int OUTPUT --总页数 AS DECLARE @sql nvarchar(4000) SET NOCOUNT ON --检查对象是否有效 IF OBJECT_ID(@tbname) IS NULL BEGIN RAISERROR(N'对象"%s"不存在',1,16,@tbname) RETURN END IF OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsTable')=0 AND OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsView')=0 AND OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsTableFunction')=0 BEGIN RAISERROR(N'"%s"不是表、视图或者表值函数',1,16,@tbname) RETURN END --分页字段检查 IF ISNULL(@FieldKey,N'')='' BEGIN RAISERROR(N'分页处理需要主键（或者惟一键）',1,16) RETURN END --其他参数检查及规范 IF ISNULL(@PageCurrent,0)<1 SET @PageCurrent=1 IF ISNULL(@PageSize,0)<1 SET @PageSize=10 IF ISNULL(@FieldShow,N'')=N'' SET @FieldShow=N'*' IF ISNULL(@FieldOrder,N'')=N'' SET @FieldOrder=N'' ELSE SET @FieldOrder=N'ORDER BY '+LTRIM(@FieldOrder) IF ISNULL(@Where,N'')=N'' SET @Where=N'' ELSE SET @Where=N'WHERE ('+@Where+N')' --如果@PageCount为NULL值,则计算总页数(这样设计可以只在第一次计算总页数,以后调用时,把总页数传回给存储过程,避免再次计算总页数,对于不想计算总页数的处理而言,可以给@PageCount赋值) IF @PageCount IS NULL BEGIN SET @sql=N'SELECT @PageCount=COUNT(*)' +N' FROM '+@tbname +N' '+@Where EXEC sp_executesql @sql,N'@PageCount int OUTPUT',@PageCount OUTPUT SET @PageCount=(@PageCount+@PageSize-1)/@PageSize END --计算分页显示的TOPN值 DECLARE @TopN varchar(20),@TopN1 varchar(20) SELECT @TopN=@PageSize, @TopN1=@PageCurrent*@PageSize --第一页直接显示 IF @PageCurrent=1 EXEC(N'SELECT TOP '+@TopN +N' '+@FieldShow +N' FROM '+@tbname +N' '+@Where +N' '+@FieldOrder) ELSE BEGIN SELECT @PageCurrent=@TopN1, @sql=N'SELECT @n=@n-1,@s=CASE WHEN @n<'+@TopN +N' THEN @s+N'',''+QUOTENAME(RTRIM(CAST('+@FieldKey +N' as varchar(8000))),N'''''''') ELSE N'''' END FROM '+@tbname +N' '+@Where +N' '+@FieldOrder SET ROWCOUNT @PageCurrent EXEC sp_executesql @sql, N'@n int,@s nvarchar(4000) OUTPUT', @PageCurrent,@sql OUTPUT SET ROWCOUNT 0 IF @sql=N'' EXEC(N'SELECT TOP 0' +N' '+@FieldShow +N' FROM '+@tbname) ELSE BEGIN SET @sql=STUFF(@sql,1,1,N'') --执行查询 EXEC(N'SELECT TOP '+@TopN +N' '+@FieldShow +N' FROM '+@tbname +N' WHERE '+@FieldKey +N' IN('+@sql +N') '+@FieldOrder) END END
有了这个存储过程，基本上就很容实现我们要做的事了，现在来看下存储过程的参数
@tbname sysname, --要分页显示的表名或视图
@FieldKey sysname, --用于定位记录的主键(惟一键)字段,只能是单个字段
@PageCurrent int=1, --要显示的页码
@PageSize int=10, --每页的大小(记录数)
@FieldShow nvarchar(1000)='', --以逗号分隔的要显示的字段列表,如果不指定,则显示所有字段
@FieldOrder nvarchar(1000)='', --以逗号分隔的排序字段列表,可以指定在字段后面指定DESC/ASC
@Where nvarchar(1000)='', --查询条件
根据后面的注释，应该都能看懂，根据我的需求，有几个关键条件是我要用到的：@PageSize、@FieldOrder、@Where、@PagCurrent，根据后面的注释我们知道它们分别是：每页的大小、排序条件、查询条件(及搜索条件)以及要显示的页码，这四个条件都是我的需求里用户可以自主定义的，就是说传进去的条件是根据用户的选择而定的。其他的条件我们都给死的(@tabname后台写表名即可，@FieldKey、@FieldShow也同理)，说到这里应该更清晰了。
现在回过头来理一理我要怎么做：
1.获取用户选择的条件(搜索条件、排序条件、页面大小、显示第几页);
2.掌握并运用ajax、json相关技术来构建桥梁把用户选择的条件传入后台拉取数据
3.获取服务器回传的数据，显示在页面上。
上面三点说的很简单，下面就开始动手做吧！
三、我要动手做
现在就开始正式做了，整个页面的布局在上面已经展示给大家看了，为了方便理解，在这里我把我已经完成了的列表页展示出来：
从列表页看，除了搜索条件是用户输入外，其他的排序、上一页、下一页等等都是让用户点一下就执行，说白了就是页面上所有可点击的按钮以及下拉框选择改变都是调用同一个方法，我命名为GetData();通过这个方法我们要完成上一部分我说的三点：
1.获取用户选择的条件(搜索条件、排序条件、页面大小、显示第几页);
2.掌握并运用ajax、json相关技术来构建桥梁把用户选择的条件传入后台拉取数据
3.获取服务器回传的数据，显示在页面上。
下面我把GetData()方法的伪代码写出来
function GetData(){ //获取搜索条件 var searchFilter=GetSearchFilter(); //获取排序条件 var orderFilter=GetOrderFilter(); //获取页面显示行数 var pageSize=cbxPagSize.value(); //获取要显示的页码 var pageIndex=GetPageIndex(); //通过Jquery 和json把获取到的条件传入后台 $("...",jsonstring,function(result)){ //将获取到的数据加载到页面上 DrawListPageData(result); } }
根据上面的伪代码，我们来分步完成要完成的代码，就不多说了，直接把代码贴出来，重要的是思路顺畅
GetSearchFilter():
<script type="text/javascript">
function GetSearchFilter() {
var studentNo = trim($("#txtStudentNo").val());
var name = trim($("#txtStudentName").val());
var sex = trim($("#cbxSex").val());
var age = trim($("#txtAge").val());
var searchFilter = "";
if (studentNo != "") {
searchFilter += " and studentNo like'%" + studentNo + "%'";
}
if (name != "") {
searchFilter += " and name like'%" + name + "%' ";
}
if (sex != "") {
searchFilter += " and sex ='" + sex + "' ";
} if (age != "") {
searchFilter += " and age ='" + age + "' ";
}
return searchFilter;
}
</script>
GetOrderFilter():
<script type="text/javascript">
function GetOrderFilter() {
var orderFilter = "";
var selectedfield = GetSeletedSpanName("tdSortComlum");
var orderWay = GetSeletedSpanName("tdDescOrAsc");
if (selectedfield != "") {
orderFilter += selectedfield +" " ;
} else {
orderFilter += " studentNo ";
}
if (orderWay != "") {
orderFilter += orderWay;
} else {
orderFilter += " desc";
}
return orderFilter;
}
//tdSortComlum 存放排序列的容器
//tdDescOrAsc 存放升序或降序的容器
function GetSeletedSpanName(containerName) {
var Column = document.getElementById(containerName);
var columns = Column.getElementsByTagName("span");
for (i = 0; i < columns.length; i++) {
if (columns[i].className == "spanOnClick") {
return columns[i].id.substr(2);
}
}
return "";
}
</script>
GetPageIndex():
<script type="text/javascript">
function GetPageIndex(pageIndex) {
var currentPage = $("#hfCurrentPage").val();
var totalPages = $("#hfTotalPages").val();
//返回0 表示无效的点击
if (pageIndex == null) {
return 1;
} else {
//点击上一页
if (pageIndex == "-1") {
if (parseInt(currentPage) + parseInt(pageIndex) <= 0) {
return 0;
} else {
return parseInt(currentPage) + parseInt(pageIndex);
}
}
//点击首页
else if (pageIndex == "0"){
if (currentPage == "1") {
return 0;
} else {
return 1;
}
}
//点击下一页
else if (pageIndex == "1") {
if (currentPage == totalPages) {
return 0;
} else {
return parseInt(currentPage) + 1;
}
}
//点击尾页
else if (pageIndex == "9") {
if (currentPage == totalPages) {
return 0;
} else {
return totalPages;
}
}
//点击跳转到
else if (pageIndex == "5") {
var jumpIndex = trim($("#txtPageCount").val());
if (isNaN(parseInt(jumpIndex)) || parseInt(jumpIndex)!=jumpIndex) {
return 0;
} else if (parseInt(jumpIndex) > parseInt(totalPages) || parseInt(jumpIndex) < 1 || jumpIndex == currentPage) {
return 0
} else {
return jumpIndex;
}
}
return 0;
}
}
</script>
DrawListPageData():
<script type="text/javascrpt">
function DrawListPageData(data) {
var items = "";
//数据展示顶部也画出总记录数
$.each(data[1], function (i, resultData) {
items += "<table width=\"100%\"><tr ><td style=\"width:30%\"></td>";
items += " Total Records:" + resultData[0].totalRecords + " ";
items += resultData[0].currentPage + "/" + resultData[0].pageCounts;
items += "(Current Page/Total Pages)";
items += "</tr></table>";
$("#hfCurrentPage").attr("value", resultData[0].currentPage);
$("#hfTotalPages").attr("value", resultData[0].pageCounts);
});
//画出数据
$.each(data[0], function (i, getData) {
items += "<table width=\"100%\" border=\"1\" cellpadding=\"3\" cellspacing=\"1\" bgcolor=\"#B4B4B4\" style=\"border-collapse: collapse;background-color:#FFFFFF\">";
items += "<thead style=\"background-color:#F1FAFF;\"><tr><td>Student No</td><td>Student Name</td><td>Sex</td><td>Age</td></tr></thead>"
$.each(getData, function (i, resultData) {
items += "<tr><td style=\" width :25%\">" + resultData.studentNo + "</td>";
items += "<td style=\" width :25%\">" + resultData.name + "</td>";
items += "<td style=\" width :25%\">" + resultData.sex + "</td>";
items += "<td style=\" width :25%\">" + resultData.age + "</td></tr>";
});
});
items += "<table>"
//画出数据总记录数，以及分页按钮
$.each(data[1], function (i, resultData) {
items += "<table width=\"100%\"><tr ><td style=\"width:30%\"></td>";
items += " Total Records:" + resultData[0].totalRecords + " ";
items += resultData[0].currentPage + "/" + resultData[0].pageCounts;
items += "(Current Page/Total Pages)";
items += "<td style=\"width:40%\"><span onclick=\"GetData(0)\" class=\"classDisplaySpan\" >FirstPage</span>";
items += "<span onclick=\"GetData(-1)\" class=\"classDisplaySpan\" >PrePage</span>";
items += "<span onclick=\"GetData(1)\" class=\"classDisplaySpan\" >NextPage</span>";
items += "<span onclick=\"GetData(9)\" class=\"classDisplaySpan\" >LastPage</span></td>";
items += "<td style=\"width:30%\">Jump To:<input id=\"txtPageCount\" type=\"text\" style=\"width:40px;\" />";
items += " <input id=\"btnConfirmJump\" type=\"button\" value=\"OK\" onclick=\"GetData(5)\" style=\"width:40px; height:21px\" /></td></tr></table>";
});
$("#divDataDisplay").html(items);
}
</script>
整个GataData()的代码为:
<script type="text/javascript">
//获取数据集
function GetData(index) {
var pageIndex = GetPageIndex(index);
//alert(pageIndex);
if (pageIndex == "0") {
return false
}
$.post("../Ajax/StudentInfoHandler.ashx", { "actionCode": "0", "orderFilter": GetOrderFilter(), "searchFilter": GetSearchFilter(), "pageSize": $("#cbxPageSize").val(), "pageIndex": pageIndex }, function (result) {
// alert(result);
var ss = result.substr(11);
if (ss != "0") {
var resultData = JSON.parse(ss);
DrawListPageData(resultData);
} else {
$("#divDataDisplay").html("No data to display...");
}
});
}
</script>
这里有两点要说明下：
1.在伪代码里面GetData()是没有参数的,为什么完整代码里面GetData(index)有个参数?
试想一下，点击排序和点击上一页、下一页的区别.
点击排序:页面不跳转，比如当前是第四页，我之前选的升序，现在我选降序，那么数据展示会变，但是还是显示第四页的数据,即页码不变
点击上一页:页码是会跳转的，当前页码-1(如果可以).
点击下一页:页码是会跳转的，当前页码+1(如果可以).
点击首页：页码是会跳转的，页码重置为1.
点击尾页：页码是会跳转的，页码重置为最大页码数.
从上面情况分析可知，不同的按钮点击对页码的操作也不同，所以我们需要一个标示来告诉 GetPageIndex(index)方法要怎么对页码进行操作，仔细回头去看下GetPageIndex方法就知道了。
2.DrawListPageData(data)方法，data参数是后台json序列化了的字符串，准确的说是数组字符串，一个数组存放数据信息(总共多少数据、总共多少页、当前页码)，一个存放数据量信息。所以分开遍历画在页面上。
好了，前台该做的操作都做完了，我们现在跳到后台去看看。先说下原理了，后台查出来的数据时Dataset集合，这个dataset里面存放了两个DataTale，一个是存放的数据集，另一个存放的数据量信息。然后通过我们自己写的一个方法DataSetToJson循环遍历dataset返回json字符串.下面是DataSetToJson方法:
/// <summary> /// Dataset转换为json字符串，返回的是json数组 /// </summary> /// <param name="ds"></param> /// <returns></returns> public string DataSetToJson(DataSet ds) { try { System.Text.StringBuilder str = new System.Text.StringBuilder("["); for (int o = 0; o < ds.Tables.Count; o++) { str.Append("{"); str.Append(string.Format("\"{0}\":[", ds.Tables[o].TableName)); for (int i = 0; i < ds.Tables[o].Rows.Count; i++) { str.Append("{"); for (int j = 0; j < ds.Tables[o].Columns.Count; j++) { str.Append(string.Format("\"{0}\":\"{1}\",", ds.Tables[o].Columns[j].ColumnName, ds.Tables[o].Rows[i][j].ToString())); } str.Remove(str.Length - 1, 1); str.Append("},"); } str.Remove(str.Length - 1, 1); str.Append("]},"); } str.Remove(str.Length - 1, 1); str.Append("]"); return str.ToString(); } catch (Exception ex) { throw ex; } }
整个后台操作的代码:
public void GetData(HttpContext context) { try { string whereFilter = context.Request["searchFilter"]; string orderFilter = context.Request["orderFilter"]; string pageSize = context.Request["pageSize"]; string currentPage = context.Request["pageIndex"]; StudentServices service = new StudentServices(); DataSet ds = new DataSet(); DataTable pageTable = new DataTable("pageTable"); pageTable.Columns.Add("totalRecords"); pageTable.Columns.Add("pageCounts"); pageTable.Columns.Add("currentPage"); pageTable.AcceptChanges(); //获取数据集 DataTable dataTable = service.GetStudentDS(" 1=1 "+whereFilter, orderFilter, pageSize, currentPage).Tables[0]; dataTable.TableName = "dataTable"; if (dataTable.Rows.Count > 0) { ds.Tables.Add(dataTable.Copy()); //Get total records int totalRecords = service.GetStudentCounts(whereFilter); //Get page counts int pageCounts = totalRecords % int.Parse(pageSize) ==0 ? totalRecords / int.Parse(pageSize): totalRecords / int.Parse(pageSize)+1; DataRow dr = pageTable.NewRow(); dr["totalRecords"] = totalRecords; dr["pageCounts"] = pageCounts; dr["currentPage"] = currentPage; pageTable.Rows.Add(dr); ds.Tables.Add(pageTable); JsonHelper js = new JsonHelper(); context.Response.Write(js.DataSetToJson(ds)); return; } else { context.Response.Write("0"); return; } } catch (Exception ex) { throw ex; } }
到这里我们的工作就完成了，很多细节都没有拿出来说，比如css+div的配合等等。这里主要是介绍思路以及实现方式，在文章最后我会把整个项目+数据库附上，下载下来直接运行就可以看效果了。整个项目采用的是简单三层模式，开发平开是VS2010+SQL2012
四、总结
整个周末都在弄这玩意儿，极烂的效率就不多说了（两天完成一个页面）,主要是自己对ajax的不熟练使用，虽然花了两天，但是感觉自己进步很大，先总结一下用到的技术以及应该注意的问题
1.使用到的技术:ajax、json、javascript、存储过程、css+div(这个虽然没拿出来说，但自己整了很长事件lol)
2.应该注意的问题:
写这个东西的时候虎头虎脑，想到一点写一点，没有很好的构架自己要干什么，写这篇文章实际是整个东西写完后回过头来再写的，如果开始写这个东西的时候能有写文章的时候的思路完成应该很快，总之摸爬滚打出经验，以后切记自己写东西前要明白自己要干什么以及怎么干.
源码下载:
链接: http://pan.baidu.com/s/1jGoN3bK 密码: 4b6t
