存储过程练习
1 <%@ Page Language="C#" AutoEventWireup="true" CodeFile="procdure.aspx.cs" Inherits="whtest_procdure" %>
2
3 <%@ Register Assembly="AspNetPager" Namespace="Wuqi.Webdiyer" TagPrefix="webdiyer" %>
4 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
5 <html xmlns="http://www.w3.org/1999/xhtml">
6 <head runat="server">
7 <title>存储过程练习</title>
8 </head>
9 <body>
10 <form id="form1" runat="server">
11 <div>
12 <asp:GridView ID="GridView1" runat="server">
13 </asp:GridView>
14 <webdiyer:AspNetPager ID="AspNetPager1" runat="server" OnPageChanged="AspNetPager1_PageChanged"
15 FirstPageText="首页" LastPageText="尾页" NextPageText="下一页" PrevPageText="上一页" ShowPageIndexBox="Never"
16 AlwaysShow="true" UrlPaging="True">
17 </webdiyer:AspNetPager>
18 <asp:Button ID="Button2" runat="server" Text="Button" OnClick="Button2_Click" />
19 </div>
20 </form>
21 </body>
22 </html>
1 protected void Button2_Click(object sender, EventArgs e)
2 {
3 bindData(0, 20);
4 }
5 private void bindData(int pageIndex, int pageSize)
6 {
7 string connsql = ConfigurationManager.ConnectionStrings["connectionstring"].ToString();
8 int count;
9 DataTable dt = new DataTable();
10 using (SqlConnection conn = new SqlConnection(connsql))
11 {
12 SqlDataAdapter da = new SqlDataAdapter();
13 SqlCommand com = new SqlCommand();
14 da.SelectCommand = com;
15 da.SelectCommand.Connection = conn;
16 da.SelectCommand.CommandText = "proc_paged_danpin";
17 da.SelectCommand.CommandType = CommandType.StoredProcedure;
18 SqlParameter[] paras = new SqlParameter[]
19 {
20 new SqlParameter("@pageIndex", pageIndex),
21 new SqlParameter("@pageSize",pageSize)
22 };
23 paras[0].Direction = ParameterDirection.Input;
24 paras[1].Direction = ParameterDirection.Input;
25 SqlParameter para = new SqlParameter("@count", SqlDbType.Int);
26 para.Direction = ParameterDirection.ReturnValue;
27 da.SelectCommand.Parameters.AddRange(paras);
28 da.SelectCommand.Parameters.Add(para);
29 da.Fill(dt);
30 count = Convert.ToInt32(para.Value);
31 }
32 AspNetPager1.RecordCount = count;
33 AspNetPager1.PageSize = 20;
34 GridView1.DataSource = dt;
35 GridView1.DataBind();
36 }
37 protected void AspNetPager1_PageChanged(object sender, EventArgs e)
38 {
39 int page;
40 try
41 {
42 page = Convert.ToInt32(Request.QueryString["page"]);
43 }
44 catch
45 {
46 page = 0;
47 }
48 bindData(page, 20);
49 }
param.Direction = ParameterDirection.Input;//输入参数。
param.Direction = ParameterDirection.Output;//输出参数，如存储过程中写set @c=@a+@b，返回参数为@c的值
param.Direction = ParameterDirection.ReturnValue;//返回的参数，如存储过程中写return @d，返回参数为@d的值
param.Direction = ParameterDirection.InputOutput;//参数技能输入也能输出。
这样也行：
SqlParameter[] paras = new SqlParameter[]
{
new SqlParameter("@pageIndex", pageIndex),
new SqlParameter("@pageSize",pageSize),
new SqlParameter("@count", SqlDbType.Int)
};
paras[0].Direction = ParameterDirection.Input;
paras[1].Direction = ParameterDirection.Input;
paras[2].Direction = ParameterDirection.ReturnValue;
存储过程：
1 create proc usp_getPageData
2 @pageIndex int, --第几页
3 @pageSize int, --每页几条数据
4 @pageCount int output --输出总共有多少页
5 as
6 declare @count int --总数据条数
7 select @count=count(*) from danpin
8 set @pageCount=ceiling(@count*1.0/@pageSize) --得到页数（隐式转换为浮点数）
9 select * from
10 (select *, row_number() over(order by id asc) as num from danpin) at ___
11 where num between (@pageIndex-1)*@pageSize+1 and @pageIndex*@pageSize
12 order by id asc
13 go
14
15
16 create procedure proc_paged_danpin --利用select top and select not in
17 (
18 @pageIndex int, --页索引
19 @pageSize int --每页记录数
20
21 )
22 as
23 begin
24 --set nocount on;
25 declare @sql nvarchar(500)
26 declare @count int --总数据条数
27 select @count=count(*) from danpin
28 set @sql='select top '+str(@pageSize)+' * from danpin where(ID not in(select top '+str(@pageSize*@pageIndex)+' id from danpin order by ID ASC)) order by ID'
29 execute(@sql) --因select top后不支技直接接参数，所以写成了字符串@sql
30 return @count
31 --select datediff(ms,@timediff,GetDate()) as 耗时
32 --set nocount off;
33 end
抄来的常用的分页存储过程：
1，利用select top 和select not in进行分页，具体代码如下：
1create procedure proc_paged_with_notin --利用select top and select not in
2(
3 @pageIndex int, --页索引
4 @pageSize int --每页记录数
5)
6as
7begin
8 set nocount on;
9 declare @timediff datetime --耗时
10 declare @sql nvarchar(500)
11 select @timediff=Getdate()
12 set @sql='select top '+str(@pageSize)+' * from tb_TestTable where(ID not in(select top '+str(@pageSize*@pageIndex)+' id from tb_TestTable order by ID ASC)) order by ID'
13 execute(@sql) --因select top后不支技直接接参数，所以写成了字符串@sql
14 select datediff(ms,@timediff,GetDate()) as 耗时
15 set nocount off;
16end
2,利用select top 和 select max(列键)
1create procedure proc_paged_with_selectMax --利用select top and select max(列)
2(
3 @pageIndex int, --页索引
4 @pageSize int --页记录数
5)
6as
7begin
8set nocount on;
9 declare @timediff datetime
10 declare @sql nvarchar(500)
11 select @timediff=Getdate()
12 set @sql='select top '+str(@pageSize)+' * From tb_TestTable where(ID>(select max(id) From (select top '+str(@pageSize*@pageIndex)+' id From tb_TestTable order by ID) as TempTable)) order by ID'
13 execute(@sql)
14 select datediff(ms,@timediff,GetDate()) as 耗时
15set nocount off;
16end
3,利用select top和中间变量--此方法因网上有人说效果最佳，所以贴出来一同测试
1create procedure proc_paged_with_Midvar --利用ID>最大ID值和中间变量
2(
3 @pageIndex int,
4 @pageSize int
5)
6as
7 declare @count int
8 declare @ID int
9 declare @timediff datetime
10 declare @sql nvarchar(500)
11begin
12set nocount on;
13 select @count=0,@ID=0,@timediff=getdate()
14 select @count=@count+1,@ID=case when @count<=@pageSize*@pageIndex then ID else @ID end from tb_testTable order by id
15 set @sql='select top '+str(@pageSize)+' * from tb_testTable where ID>'+str(@ID)
16 execute(@sql)
17 select datediff(ms,@timediff,getdate()) as 耗时
18set nocount off;
19end
20
4,利用Row_number() 此方法为SQL server 2005中新的方法,利用Row_number()给数据行加上索引
1create procedure proc_paged_with_Rownumber --利用SQL 2005中的Row_number()
2(
3 @pageIndex int,
4 @pageSize int
5)
6as
7 declare @timediff datetime
8begin
9set nocount on;
10 select @timediff=getdate()
11 select * from (select *,Row_number() over(order by ID asc) as IDRank from tb_testTable) as IDWithRowNumber where IDRank>@pageSize*@pageIndex and IDRank<@pageSize*(@pageIndex+1)
12 select datediff(ms,@timediff,getdate()) as 耗时
13set nocount off;
14end
15
5，利用临时表及Row_number
1create procedure proc_CTE --利用临时表及Row_number
2(
3 @pageIndex int, --页索引
4 @pageSize int --页记录数
5)
6as
7 set nocount on;
8 declare @ctestr nvarchar(400)
9 declare @strSql nvarchar(400)
10 declare @datediff datetime
11begin
12 select @datediff=GetDate()
13 set @ctestr='with Table_CTE as
14 (select ceiling((Row_number() over(order by ID ASC))/'+str(@pageSize)+') as page_num,* from tb_TestTable)';
15 set @strSql=@ctestr+' select * From Table_CTE where page_num='+str(@pageIndex)
16end
17 begin
18 execute sp_executesql @strSql
19 select datediff(ms,@datediff,GetDate())
20 set nocount off;
21 end
22
