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 --总页数
ASDECLARE @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():

GetOrderFilter():

GetPageIndex():

DrawListPageData():

整个GataData()的代码为:

这里有两点要说明下：
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 = ; o < ds.Tables.Count; o++)
{
str.Append("{");
str.Append(string.Format("\"{0}\":[", ds.Tables[o].TableName));
for (int i = ; i < ds.Tables[o].Rows.Count; i++)
{
str.Append("{");
for (int j = ; 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 - , ); str.Append("},");
}
str.Remove(str.Length - , ); str.Append("]},");
}
str.Remove(str.Length - , );
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[];
dataTable.TableName = "dataTable";
if (dataTable.Rows.Count > )
{
ds.Tables.Add(dataTable.Copy());
//Get total records
int totalRecords = service.GetStudentCounts(whereFilter);
//Get page counts
int pageCounts = totalRecords % int.Parse(pageSize) == ? totalRecords / int.Parse(pageSize): totalRecords / int.Parse(pageSize)+; 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("");
return;
}
}
catch (Exception ex)
{
throw ex;
}
}