（再续）.net分页控件webdiyer:aspnetpager与gridview联用

本篇是订单管理的第三篇，此次将原来的sql语句改成存储过程，后台只负责输入几个参数就可以了。想法很天真啊，原来以为直接把sql语句简单放过去就可以了，但好多细节没有考虑清楚，比如日常总结问题（7）中的存储过程里如果使用‘（单引号），这些原来都没有考虑。

分布梳理下这个改造成存储过程的过程

一、SQL优化

  原来的sql语句里将套餐信息和其他信息一起搜索出来，觉得会影响查询质量，于是改成了下面的语句

SELECT price,quantity,total,status,remark ,w1.orderid,w1.receiver1, shopconect FROM sales w1, ( SELECT TOP 20 row_number() OVER (ORDER BY orderid DESC) n, orderid, '国家:'+w3.country+',套餐天数:'+CAST( w3.days as varchar)+'，套餐内容:'+w3.simcontent+',充值参加价:' +CAST( w3.simprice as varchar)+'RMB' as shopconect from simshop w3,sales where sales.orderflag=0 and sales.simid=w3.simID ) w2 WHERE w1.orderid = w2.orderid AND w2.n >0其中w2.n>0的0是页码，top 20的20是每张页的大小，这个就是每20个数据为一页的第一页的数据，20=paindex+pa，n>paindex

     

int pa = AspNetPager1.PageSize; int paindex = pa * (index - 1); 这样就可以达到第几页数据了

PS:在优化的时候，百度到一个sql语句，参考http://www.jb51.net/article/35213.htm（当时是为了编写存储过程，因为下面的sql语句有一个模板的存储过程，所以看了看）

SELECT * FROM ARTICLE w1 WHERE ID in ( SELECT top 30 ID FROM ( SELECT top 1030 ID, YEAR FROM ARTICLE ORDER BY YEAR DESC, ID DESC ) w ORDER BY w.YEAR ASC, w.ID ASC ) ORDER BY w1.YEAR DESC, w1.ID DESC原来也是想改成这个语句的，条件都适配好了，结果发现它是查出第1030的前30的，如果我想查出第二页的30个数据，我不会了，希望有看到这个的大神们帮忙解决下，我一看，有问题了，不好编下去，于是还是采用原来的sql语句了。这里要说明一个row_number（）函数在sqlserver2000的版本是不支持的，因为我这里使用的是sqlserver2008，所以就直接用了。

二、编写存储过程

我们现在需要的是返回两个结果集，一个是gridview里用的数据，另一个是aspnerpager中用的总数好用来分页码

存储过程有三种返回:    1.   用return返回数字型数据    2.   用返回参数返回结果,可以返回各种数据类型(通过游标来循环查询结果每一行)    3.   直接在存储过程中用select返回结果集,可以是任意的select语句,这意味着是任意的返回结果集

详细的请见http://www.cnblogs.com/gossip/archive/2011/04/16/2018548.html

参考之前同事的方法，将后台的cs改为

IDataParameter[] parameters = { new SqlParameter("@sys_Where1", SqlDbType.NVarChar, 50), new SqlParameter("@sys_Where2", SqlDbType.NVarChar, 50), new SqlParameter("@sys_Where3", SqlDbType.NVarChar, 50), new SqlParameter("@group", SqlDbType.NVarChar,1), new SqlParameter("@sys_PageIndex", SqlDbType.Int), new SqlParameter("@sys_PageSize", SqlDbType.Int)}; parameters[0].Value = queryArg.username; parameters[1].Value = queryArg.receiver1; parameters[2].Value = queryArg.orderid; parameters[3].Value = queryArg.group; parameters[4].Value = paindex; parameters[5].Value = pa; DataSet ds = Hippo.RunProcedure("proc_query_order", parameters, "ds"); return ds;这个hippo可以参考我之前写的文章。只有使用过程，原理我不太理解

在数据库中编写的存储过程

ALTER procedure [dbo].[proc_query_order] ( @sys_Where1 nvarchar(50),--查询条件username @sys_Where2 nvarchar(50),--查询条件receiver1 @sys_Where3 nvarchar(50),--查询条件orderid @group nvarchar(1),--管理员分组group @sys_PageIndex int,--当前页数paindex @sys_PageSize int --页大小pa ) as begin if @sys_PageSize<0 or @sys_PageIndex<0 begin return end declare @new_where1 nvarchar(3000) declare @new_where2 nvarchar(3000) declare @Sql nvarchar(4000) declare @SqlCount nvarchar(4000) declare @SqlWhere nvarchar(4000) declare @Top int declare @an int set @SqlCount=';select COUNT(sales.orderid) from sales ,simshop where sales.simID=simshop.simID and sales.orderflag=0 ' if @group=1 set @new_where1='' else begin if ISNULL(@sys_Where1,'')='' set @new_where1='' else set @new_where1=' and sales.username ='''+@sys_Where1+''' ' end if ISNULL(@sys_Where2,'')='' set @new_where2='' else set @new_where2=' and (sales.receiver1 like ''%' +@sys_Where2+ '%'' or sales.orderid like ''%'+@sys_Where3+'%'') ' set @Top=@sys_PageSize+@sys_PageIndex set @SqlWhere=@new_where1+@new_where2 set @SqlCount=@SqlCount+@SqlWhere+';' set @Sql='SELECT price,quantity,total,status,remark ,w1.orderid,w1.receiver1, shopconect FROM sales w1,(SELECT TOP ' +CONVERT(nvarchar,@Top,1)+' row_number() OVER (ORDER BY orderid DESC) n, orderid,' +'''国家:'''+'+w3.country+'+''',套餐天数:'''+'+CAST( w3.days as varchar)+'+'''，套餐内容:''' +'+w3.simcontent+'+''',充值参加价:'''+'+CAST( w3.simprice as varchar)+'+'''RMB'''+' as shopconect ' +' from simshop w3,sales where sales.orderflag=0 and sales.simid=w3.simID ' +@SqlWhere +' ) w2 WHERE w1.orderid = w2.orderid AND w2.n >' +CONVERT(nvarchar,@sys_PageIndex,1); EXEC (@sql + @sqlCount); end这个存储过程我进行了多次调试，在程序中没有发现bug。

之后和同学讨论了分页的这个sql，发现其实不用select top @top和w2.n的联用，我既然已经选取出了n，n里也有行数，我直接让n大于小于多好

于是将存储过程里改为

begin if @sys_PageSize<0 or @sys_PageIndex<0 begin return end declare @new_where1 nvarchar(3000) declare @new_where2 nvarchar(3000) declare @Sql nvarchar(4000) declare @SqlCount nvarchar(4000) declare @SqlWhere nvarchar(4000) declare @Top int declare @an int set @SqlCount=';select COUNT(sales.orderid) from sales ,simshop where sales.simID=simshop.simID and sales.orderflag=0 ' if @group=1 set @new_where1='' else begin if ISNULL(@sys_Where1,'')='' set @new_where1='' else set @new_where1=' and sales.username ='''+@sys_Where1+''' ' end if ISNULL(@sys_Where2,'')='' set @new_where2='' else set @new_where2=' and (sales.receiver1 like ''%' +@sys_Where2+ '%'' or sales.orderid like ''%'+@sys_Where3+'%'') ' set @Top=@sys_PageSize*(@sys_PageIndex+1)+1 set @an=@sys_PageIndex*@sys_PageSize set @SqlWhere=@new_where1+@new_where2 set @SqlCount=@SqlCount+@SqlWhere+';' set @Sql='SELECT price,quantity,total,status,remark ,w1.orderid,w1.receiver1, shopconect FROM sales w1,(SELECT ' +' row_number() OVER (ORDER BY orderid DESC) n, orderid,' +'''国家:'''+'+w3.country+'+''',套餐天数:'''+'+CAST( w3.days as varchar)+'+'''，套餐内容:''' +'+w3.simcontent+'+''',充值参加价:'''+'+CAST( w3.simprice as varchar)+'+'''RMB'''+' as shopconect ' +' from simshop w3,sales where sales.orderflag=0 and sales.simid=w3.simID ' +@SqlWhere +' ) w2 WHERE w1.orderid = w2.orderid AND w2.n >' +CONVERT(nvarchar,@an,1)+' and w2.n<' +CONVERT(nvarchar,@Top,1) EXEC (@sql + @sqlCount); end这是要将cs后台的paindex进行修改为

int pa = AspNetPager1.PageSize; int paindex = index-1; 这样的选择就可以实现为

0*5--1*5+1这样选取的是0-5个数据

1*5--2*5+1选取6-10的数据

在测试的时候发现我在第2页点击搜索时，搜索结果是显示第2页的，如果搜索结果只有1页可能会出现问题，所以在搜索事件下面添加了一句

AspNetPager1.CurrentPageIndex = 0;

默认分页控件选取第一页