分页用户控件(1)

1.存储过程
  Create Procedure  GetProduct
(@perpage   int ,
 @currentpage   int ,
 @howmanypages  int  output)
as
declare @Producttable  table
(RowNumber  int,
 Name  nvarchar(50),
 Price  money)
insert into @Producttable
 select  row_number()   over  (order  by  ProductID),Price,Money  from  Product
 
select  @howmanypages=count(RowNumber) from @producttable
select  RowNumber,Name,Price  from  @Producttable  where RowNumber>@perpage*(@currentpage-1) and RowNumber<=@perpage*@currentpage
2.表现层部分代码
   <asp:LinkButton ID="LinkButton1" runat="server" onclick="LinkButton1_Click">首页</asp:LinkButton>
        <asp:Label ID="Label1" runat="server" Text=""></asp:Label>
        <asp:LinkButton ID="LinkButton2" runat="server" onclick="LinkButton2_Click">下一页</asp:LinkButton>
        <asp:LinkButton ID="LinkButton3" runat="server" onclick="LinkButton3_Click">最后一页</asp:LinkButton>
       
        <br />
        <br />
        <asp:GridView ID="GridView1" runat="server">
        </asp:GridView>
3.逻辑业务层代码
  //声明两个全局变量,用来保存数据

      private   string id;
      private int Howmanypages;
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!String.IsNullOrEmpty(Request.QueryString["id"]))
            { id = Request.QueryString["id"]; }
            else
            {
             //默认为1
           id = "1";
            }
             GridView1.DataSource = populatepage(id);
             GridView1.DataBind();
             ShowPager(Howmanypages, Int16.Parse(id), 6);
             showbutton();
        }
        private void ShowPager(int howmanypages, int currentpage, int showhowmanynumber)
        {
           
            if (howmanypages <= showhowmanynumber)
            {
                for (int i = 1; i <= howmanypages; i++)
                {
                    Label1.Text += "<a href='Default.aspx?id=" + i + "'>" + i + "</a>";
                }
            }
            else
            {
                for (int i = 1; i <= showhowmanynumber ; i++)
                {
                    if (i != Int32.Parse(id))
                    {
                        Label1.Text += "<a href='Default.aspx?id=" + i + "'>" + i + "</a>";
                    }
                    else
                    {
                        Label1.Text += i;
                    }

                }
                Label1.Text += "…";
            }
        }

        protected void LinkButton1_Click(object sender, EventArgs e)
        {
           
            Response.Redirect("Default.aspx?id=1");
        }

        protected void LinkButton2_Click(object sender, EventArgs e)
        {
           int  ID=Int32 .Parse(id)+1;
           Response.Redirect("Default.aspx?id=" + ID);
        }

        protected void LinkButton3_Click(object sender, EventArgs e)
        {
            Response .Redirect ("Default.aspx?id="+Howmanypages);
        }
        private DataTable  populatepage(string currentpage )
        {
            DbProviderFactory dbProviderFactory = DbProviderFactories.GetFactory("System.Data.SqlClient");
            DbConnection dbConnection = dbProviderFactory.CreateConnection();
            dbConnection.ConnectionString = "data source=.;initial catalog=BalloonShop;User=yike;Password=6694030;" ;
            DbCommand dbCommand = dbConnection.CreateCommand();
            dbCommand.CommandType = CommandType.StoredProcedure;
            dbCommand.CommandText = "mytext";
            DbParameter par = dbCommand.CreateParameter();
            par.ParameterName = "@currentPage";
            par.DbType = DbType.Int32;
            par.Value = currentpage;
            dbCommand.Parameters.Add(par);

            par = dbCommand.CreateParameter();
            par.ParameterName = "@perpage";
            par.DbType = DbType.Int32;
            par.Value = 6;
            dbCommand.Parameters.Add(par);

            par = dbCommand.CreateParameter();
            par.ParameterName = "@howmanypages";
            par.Direction=ParameterDirection .Output ;
            par.DbType = DbType.Int32;
            dbCommand.Parameters.Add(par);
           

            DataTable datatable = new DataTable();

            try
            {
                if (dbCommand.Connection.State != ConnectionState.Closed)
                {
                    dbCommand.Connection.Close();
                }
                dbCommand.Connection.Open();
                DbDataReader dbDataReader = dbCommand.ExecuteReader();
                datatable.Load(dbDataReader);
                dbDataReader.Close();
                int HowmanyProduct=Int32.Parse(dbCommand.Parameters["@howmanypages"].Value.ToString());
                Howmanypages =(int) Math.Ceiling(((double)HowmanyProduct / 6));
            }
            catch (Exception e)
            {

                throw e;
            }
            finally
            {
                dbCommand.Connection.Close();
            }
            return datatable;

        }
        private void showbutton()
        {
            if (id =="1")
            {
                LinkButton1.Enabled = false;
               
            }
           if (Int16.Parse(id) == Howmanypages)
            {
                LinkButton2.Enabled = false;
                LinkButton3.Enabled = false ;
            }
        }