【ASP.NET】存储过程分页实例
存错过程代码：
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 }
写完这个小程序发现代码量真的很大，其实好多核心代码都是复制粘贴的，可能还可以继续加以改进，先分享一下吧！以前用控件分页感觉很爽，一句代码不用写。但是只是知其然不知其所以然。今天算是对分页有了另一种理解！看别人的分页存储过程写了好长一堆代码，本人表示压力很大，都看不懂。今天写一个简单的！以后有时间再研究大牛们的代码吧！
——附：代码经过测试，没有一点问题！
