【ASP.NET】存储过程分页实例

OStack程序员社区-中国程序员成长平台 › 门户 › 编程› ASP.NET›ASP.NET编程经验

原作者: [db:作者] 来自: [db:来源] 收藏邀请

存错过程代码：

 1 -- =============================================
 2 -- Author:        netboy
 3 -- Create date: 2012年7月27日
 4 -- Description:    存储过程分页
 5 -- =============================================
 6 CREATE PROCEDURE [dbo].[getdatabypageindex] 
 7     @pageindex int,
 8     @pagecount int output
 9 AS
10 BEGIN
11     declare @sql nvarchar(1000)
12     declare @pagec int
13     set @sql='select top 10 * from T_User where id not in (select top ' +CAST(@pageindex*10 as nvarchar(10))+' id from T_user) '
14     
15     select @pagec = COUNT(*) from T_User 
16     
17     set @pagecount = (@pagec+9)/10
18     
19     exec (@sql)
20 END

　　net小伙用的是ASP.NET写的demo：前台界面如下：

　　net小伙用的是repeater控件绑定的数据，具体前台代码如下：

前台代码

 1        <asp:Repeater ID="Repeater1" runat="server">
 2             <HeaderTemplate>
 3                 <table>
 4                     <tr>
 5                         <td style="width:50px;">
 6                             id
 7                         </td>
 8                         <td style="width:150px;">
 9                             姓名
10                         </td>
11                         <td style="width:100px;">
12                             年龄
13                         </td>
14                     </tr>
15                 
16             </HeaderTemplate>
17             <ItemTemplate>
18                 <tr>
19                     <td style=" color:Green;">
20                         <%#Eval("id")%>
21                     </td>
22                     <td style="color:Blue;">
23                         <%#Eval("UserName")%>
24                     </td>
25                     <td style="color:Red;">
26                         <%#Eval("Age")%>
27                     </td>
28                 </tr>
29             </ItemTemplate>
30             <FooterTemplate></table></FooterTemplate>
31         </asp:Repeater>
32 
33         <table>
34             <tr>
35                 <td>
36                     <asp:LinkButton ID="LinkButton1" runat="server" onclick="LinkButton1_Click">首页</asp:LinkButton></td>
37                 <td>
38                     <asp:TextBox ID="TextBox1" runat="server" Width="29px"></asp:TextBox></td>
39                    
40                 <td>
41                     <asp:LinkButton ID="LinkButton5" runat="server" onclick="LinkButton5_Click">GO</asp:LinkButton></td>
42                 <td>
43                     <asp:Label ID="Label1" runat="server" Text="第"></asp:Label></td>
44                 <td>
45                     <asp:Label ID="index" runat="server" Text="1"></asp:Label></td>
46                 <td>
47                     <asp:Label ID="Label3" runat="server" Text="页"></asp:Label></td>
48                 <td>
49                     <asp:Label ID="Label2" runat="server" Text="共"></asp:Label></td>
50                 <td>
51                     <asp:Label ID="labcount" runat="server" Text=""></asp:Label></td>
52                 <td>
53                     <asp:Label ID="Label5" runat="server" Text="页"></asp:Label></td>
54                 <td>
55                     <asp:LinkButton ID="LinkButton2" runat="server" onclick="LinkButton2_Click">上一页</asp:LinkButton></td>
56                 <td>
57                     <asp:LinkButton ID="LinkButton3" runat="server" onclick="LinkButton3_Click">下一页</asp:LinkButton></td>
58                 <td>
59                     <asp:LinkButton ID="LinkButton4" runat="server" onclick="LinkButton4_Click">尾页</asp:LinkButton></td>
60             </tr>
61         </table>

后台逻辑代码如下：

Page_Load

 1        protected void Page_Load(object sender, EventArgs e)
 2         {
 3             if (!IsPostBack)
 4             {
 5                 this.shuju();
 6                 if (index.Text == "1")
 7                 {
 8                     LinkButton1.Enabled = false;
 9                     LinkButton2.Enabled = false;
10                 }
11                 if (index.Text == labcount.Text)
12                 {
13                     LinkButton3.Enabled = false;
14                     LinkButton4.Enabled = false;
15                 }
16             }
17         }

首次加载数据函数

 1         public void shuju()     //初始化数据
 2         {
 3             using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["data"].ConnectionString))
 4             {
 5                 SqlDataAdapter da = new SqlDataAdapter("getdatabypageindex", con);
 6                 da.SelectCommand.CommandType = CommandType.StoredProcedure;
 7 
 8                 da.SelectCommand.Parameters.Add(new SqlParameter("@pageindex",SqlDbType.Int));
 9                 da.SelectCommand.Parameters["@pageindex"].Value = 0;
10 
11                 da.SelectCommand.Parameters.Add(new SqlParameter("@pagecount", SqlDbType.Int));
12                 da.SelectCommand.Parameters["@pagecount"].Direction = ParameterDirection.Output;
13                 DataSet ds = new DataSet();
14                 da.Fill(ds);
15 
16                 labcount.Text = da.SelectCommand.Parameters["@pagecount"].Value.ToString();     //返回总页数
17                 this.Repeater1.DataSource = ds;
18                 this.Repeater1.DataBind();
19             }
20         }

首页按钮事件

 1         protected void LinkButton1_Click(object sender, EventArgs e)
 2         {
 3             using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["data"].ConnectionString))
 4             {
 5                 SqlDataAdapter da = new SqlDataAdapter("getdatabypageindex", con);
 6                 da.SelectCommand.CommandType = CommandType.StoredProcedure;
 7                 da.SelectCommand.Parameters.Add(new SqlParameter("@pageindex", SqlDbType.Int));
 8                 da.SelectCommand.Parameters["@pageindex"].Value = 0;
 9                 da.SelectCommand.Parameters.Add(new SqlParameter("@pagecount", SqlDbType.Int));
10                 da.SelectCommand.Parameters["@pagecount"].Direction = ParameterDirection.Output;
11                 DataSet ds = new DataSet();
12                 da.Fill(ds);
13                 this.Repeater1.DataSource = ds;
14                 this.Repeater1.DataBind();
15             }
16 
17             index.Text = "1";
18 
19             //把首页和上一页设置为不可操作
20             LinkButton1.Enabled = false;
21             LinkButton2.Enabled = false;
22         }

GO按钮事件

 1         protected void LinkButton5_Click(object sender, EventArgs e)
 2         {
 3             using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["data"].ConnectionString))
 4             {
 5                 string pageindex = TextBox1.Text;
 6                 int intpageindex = Convert.ToInt32(pageindex);
 7 
 8                 int pagecount = Convert.ToInt32(labcount.Text);
 9                 if (intpageindex > pagecount)       //判断，如果输入的数字大于总页数就返回
10                 {
11                     return;
12                 }
13                 else
14                 {
15                     SqlDataAdapter da = new SqlDataAdapter("getdatabypageindex", con);
16                     da.SelectCommand.CommandType = CommandType.StoredProcedure;
17 
18                     da.SelectCommand.Parameters.Add(new SqlParameter("@pageindex", SqlDbType.Int));
19                     da.SelectCommand.Parameters["@pageindex"].Value = intpageindex - 1;
20 
21                     da.SelectCommand.Parameters.Add(new SqlParameter("@pagecount", SqlDbType.Int));
22                     da.SelectCommand.Parameters["@pagecount"].Direction = ParameterDirection.Output;
23                     DataSet ds = new DataSet();
24                     da.Fill(ds);
25                     this.Repeater1.DataSource = ds;
26                     this.Repeater1.DataBind();
27                     if (TextBox1.Text == "1")       //判断如果输入1，首页和上一页不可操作，但是尾页和下一页可以操作
28                     {
29                         LinkButton1.Enabled = false;
30                         LinkButton2.Enabled = false;
31 
32                         LinkButton3.Enabled = true;
33                         LinkButton4.Enabled = true;
34                     }
35                     else if (TextBox1.Text == labcount.Text)        //判断如果输入的数等于总页数，首页和第一页可以操作，但是尾页可下一页不可操作
36                     {
37                         LinkButton3.Enabled = false;
38                         LinkButton4.Enabled = false;
39 
40                         LinkButton1.Enabled = true;
41                         LinkButton2.Enabled = true;
42                     }
43                     else                                           //如果是其他情况，首页第一页，尾页和下一页都可操作
44                     {
45                         LinkButton3.Enabled = true;
46                         LinkButton4.Enabled = true;
47 
48                         LinkButton1.Enabled = true;
49                         LinkButton2.Enabled = true;
50                     }
51                 }      
52             }
53             index.Text = TextBox1.Text;             //设置当前页数等于输入的页数       
54         }

上一页按钮事件

 1         protected void LinkButton2_Click(object sender, EventArgs e)
 2         {
 3             using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["data"].ConnectionString))
 4             {
 5                 string pageindex = index.Text;
 6                 int intpageindex = Convert.ToInt32(pageindex);
 7 
 8                 SqlDataAdapter da = new SqlDataAdapter("getdatabypageindex", con);
 9                 da.SelectCommand.CommandType = CommandType.StoredProcedure;
10                 da.SelectCommand.Parameters.Add(new SqlParameter("@pageindex", SqlDbType.Int));
11                 da.SelectCommand.Parameters["@pageindex"].Value = intpageindex - 2;
12 
13                 da.SelectCommand.Parameters.Add(new SqlParameter("@pagecount", SqlDbType.Int));
14                 da.SelectCommand.Parameters["@pagecount"].Direction = ParameterDirection.Output;
15                 DataSet ds = new DataSet();
16                 da.Fill(ds);
17 
18                 labcount.Text = da.SelectCommand.Parameters["@pagecount"].Value.ToString();
19                 this.Repeater1.DataSource = ds;
20                 this.Repeater1.DataBind();
21             }
22             int intindex = Convert.ToInt32(index.Text);
23             index.Text = (intindex - 1).ToString();
24 
25             LinkButton3.Enabled = true;
26             LinkButton4.Enabled = true;
27 
28             if (index.Text == "1")
29             {
30                 LinkButton1.Enabled = false;
31                 LinkButton2.Enabled = false;
32             }
33 
34         }

下一页按钮事件

 1         protected void LinkButton3_Click(object sender, EventArgs e)
 2         {
 3             using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["data"].ConnectionString))
 4             {
 5                 string pageindex = index.Text;
 6                 int intpageindex = Convert.ToInt32(pageindex);
 7 
 8                 SqlDataAdapter da = new SqlDataAdapter("getdatabypageindex", con);
 9                 da.SelectCommand.CommandType = CommandType.StoredProcedure;
10                 da.SelectCommand.Parameters.Add(new SqlParameter("@pageindex", SqlDbType.Int));
11                 da.SelectCommand.Parameters["@pageindex"].Value = intpageindex;
12 
13                 da.SelectCommand.Parameters.Add(new SqlParameter("@pagecount", SqlDbType.Int));
14                 da.SelectCommand.Parameters["@pagecount"].Direction = ParameterDirection.Output;
15                 DataSet ds = new DataSet();
16                 da.Fill(ds);
17 
18                 labcount.Text = da.SelectCommand.Parameters["@pagecount"].Value.ToString();
19                 this.Repeater1.DataSource = ds;
20                 this.Repeater1.DataBind();
21             }
22             int intindex = Convert.ToInt32(index.Text);
23             index.Text = (intindex + 1).ToString();
24 
25             LinkButton1.Enabled = true;
26             LinkButton2.Enabled = true;
27 
28             if (index.Text == labcount.Text)
29             {
30                 LinkButton3.Enabled = false;
31                 LinkButton4.Enabled = false;
32             }
33         }

尾页按钮事件

 1         protected void LinkButton4_Click(object sender, EventArgs e)
 2         {
 3             using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["data"].ConnectionString))
 4             {
 5                 int intindex = Convert.ToInt32(labcount.Text);
 6 
 7                 SqlDataAdapter da = new SqlDataAdapter("getdatabypageindex", con);
 8                 da.SelectCommand.CommandType = CommandType.StoredProcedure;
 9                 da.SelectCommand.Parameters.Add(new SqlParameter("@pageindex", SqlDbType.Int));
10                 da.SelectCommand.Parameters["@pageindex"].Value = intindex - 1;
11                 da.SelectCommand.Parameters.Add(new SqlParameter("@pagecount", SqlDbType.Int));
12                 da.SelectCommand.Parameters["@pagecount"].Direction = ParameterDirection.Output;
13                 DataSet ds = new DataSet();
14                 da.Fill(ds);
15                 this.Repeater1.DataSource = ds;
16                 this.Repeater1.DataBind();
17             }
18             index.Text = labcount.Text;
19 
20 
21             LinkButton1.Enabled = true;
22             LinkButton2.Enabled = true;
23 
24             LinkButton3.Enabled = false;
25             LinkButton4.Enabled = false;
26         }

写完这个小程序发现代码量真的很大，其实好多核心代码都是复制粘贴的，可能还可以继续加以改进，先分享一下吧！以前用控件分页感觉很爽，一句代码不用写。但是只是知其然不知其所以然。今天算是对分页有了另一种理解！看别人的分页存储过程写了好长一堆代码，本人表示压力很大，都看不懂。今天写一个简单的！以后有时间再研究大牛们的代码吧！

　　　　——附：代码经过测试，没有一点问题！