Repeater调用存储过程+数字分页

来源：互联网发布：淘宝优惠券app 编辑：程序博客网时间：2024/06/05 20:48

Default.aspx.cs

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Collections;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;

public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {
            Bindtext();

        }
    }
    protected void Bindtext()
    {
        int pagesize = 20;
        int ipage;
        if (Request.QueryString["Page"] != null)
        {
             ipage = Convert.ToInt32(Request.QueryString["Page"]);
        }
        else
        {
            ipage = 1;
        }
        SqlConnection cnn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["cnd"].ConnectionString);
        SqlCommand cmd = new SqlCommand("infobuyw", cnn);
        cmd.CommandType = CommandType.StoredProcedure; //这里采用存储过程
        cmd.Parameters.Add("@ipage", ipage);
        cmd.Parameters.Add("@ipagesize", pagesize);
       cmd.Parameters.Add ("@ipagecount", SqlDbType.Int);
       cmd.Parameters["@ipagecount"].Direction = ParameterDirection.Output;
       cnn.Open();
       cmd.ExecuteNonQuery();
       int re = (int)(cmd.Parameters["@ipagecount"].Value);
     a2.Text = Convert.ToString(cmd.Parameters["@ipagecount"].Value);
        SqlDataAdapter Dap = new SqlDataAdapter(cmd);
               DataSet Dst = new DataSet();
       Dap.Fill(Dst,"info");
       PagedDataSource Pgds = new System.Web.UI.WebControls.PagedDataSource();
       Pgds.DataSource = Dst.Tables["info"].DefaultView;
       this.Ret8.DataSource = Pgds;
       this.DataBind();

       //        设置允许分页
       Pgds.AllowPaging = true;
       //        每页显示为20行
       Pgds.PageSize = pagesize;
       //        显示总共页数
       //
       lblTotalPage.Text = Pgds.PageCount.ToString();
       //        当前页
       int CurrentPage;
       //        请求页码为不为null设置当前页，否则为第一页
       if (Request.QueryString["Page"] != null)
       {

CurrentPage = Convert.ToInt32(Request.QueryString["Page"]);
}

else
{

           CurrentPage = 1;
       }
       //   当前页所引为页码-1
       Pgds.CurrentPageIndex = CurrentPage - 1;
       //   显示当前页码
       lblCurrentPage.Text = CurrentPage.ToString();
       //   如果不是第一页，通过参数Page设置上一页为当前页-1，否则不显示连接
       if (ipage>1)
       {
           //            Request.CurrentExecutionFilePath为当前请求虚拟路径
           lnkPrev.NavigateUrl = Request.CurrentExecutionFilePath + "?Page=" + Convert.ToString(CurrentPage - 1);
           First.NavigateUrl = Request.CurrentExecutionFilePath + "?Page=1";

       }
       //        End If
       //   如果不是最后一页，通过参数Page设置下一页为当前页+1，否则不显示连接
       if (ipage<re)
       {
           //    Request.CurrentExecutionFilePath为当前请求虚拟路径
           lnkNext.NavigateUrl = Request.CurrentExecutionFilePath + "?Page=" + Convert.ToString(CurrentPage + 1);
           end.NavigateUrl = Request.CurrentExecutionFilePath + "?Page=" + Convert.ToString(re);

       }
       int pcount = re;

       int pindex = Convert.ToInt32(CurrentPage);
       fenye.Text = strlink(pindex, pcount);
   }
   string strlink(int pindex, int pcount)
   {
       int pageturn = pindex + 10;

int i;
string slink = null;

for (i = pindex; i <= pageturn; i++)
{

           if (i > pcount) break;
           if (i != pindex)
           {
               slink = slink + "<a href=?page=" + i.ToString() + ">" + i.ToString() + "</a> ";
           }

           else
           {
               slink = slink + "" + i.ToString() + " ";
           }

}

       return slink;
   }
    }

default.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
    <title>ADO-Retepter</title>
    <link href="ste.css" _fcksavedurl=""ste.css"" rel="stylesheet" type="text/css" />
</head>
<body>
    <form id="form1" runat="server">
    <div>
          <asp:Repeater ID="Ret8" runat="server">

        <ItemTemplate><div><%#Eval("infoid") %> ---------<%#Eval("info_title") %></div></ItemTemplate></asp:Repeater>
    </div>
    <br />
      <asp:HyperLink ID="First" runat="server">首页</asp:HyperLink>
     <asp:hyperlink id="lnkPrev" runat="server">上页</asp:hyperlink>
    <asp:hyperlink id="lnkNext" runat="server">下页</asp:hyperlink>
        <asp:HyperLink ID="end" runat="server">尾页</asp:HyperLink>第
                  <asp:label id="lblCurrentPage" runat="server"></asp:label>
     页共
     <asp:label id="lblTotalPage" runat="server"></asp:label>页
        <asp:Label ID="fenye" runat="server" Text="Label"></asp:Label>
        <br />
        <br />
        <asp:Label ID="a2" runat="server"></asp:Label>
    </form>
</body>
</html>

存储过程 buyinfow

CREATE PROCEDURE infobuyw
@iPageCount int OUTPUT, --总页数
@iPage int, --当前页号
@iPageSize int --每页记录数

as
set nocount on
begin
--创建临时表

create table #t (ID int IDENTITY, --自增字段
infoid int,info_title varchar(200),cimg varchar(200),userid varchar(50),products_price varchar(50),company_name varchar(100),info_starttime smalldatetime)
--向临时表中写入数据
insert into #t
select infoid,info_title,cimg,userid,products_price,company_name,info_starttime

from dbo.[trade] where info_type='供应' and flag='1'
order by info_starttime desc

--取得记录总数
declare @iRecordCount int
set @iRecordCount = @@rowcount

--确定总页数
IF @iRecordCount%@iPageSize=0
SET @iPageCount=CEILING(@iRecordCount/@iPageSize)
ELSE
SET @iPageCount=CEILING(@iRecordCount/@iPageSize)+1

--若请求的页号大于总页数，则显示最后一页
IF @iPage > @iPageCount
SELECT @iPage = @iPageCount

--确定当前页的始末记录
DECLARE @iStart int --start record
DECLARE @iEnd int --end record
SELECT @iStart = (@iPage - 1) * @iPageSize
SELECT @iEnd = @iStart + @iPageSize + 1

--取当前页记录
select * from #t where ID>@iStart and ID<@iEnd

--删除临时表
DROP TABLE #t

--返回记录总数
return @iRecordCount
end
GO