ASP.NET--Repeater控件分页功能实现

这两天由于‘销售渠道’系统需要实现新功能，开发了三个页面，三个界面功能大致相同。

功能：分页显示特定sql查询结果，点击上一页下一页均可显示。单击某记录可以选定修改某特定字段<DropDownList可选项>点击Upadate按钮之后提交。

因为Repeater没有自带的分页功能，这里使用的是：AspNetPager.dll这个插件，首先引用到项目中，注册:

<%@ Register Assembly="AspNetPager" Namespace="Wuqi.Webdiyer" TagPrefix="webdiyer" %>

<webdiyer:AspNetPager ID="AspPage" runat="server" FirstPageText="FirstPage" LastPageText="LastPage"
                                    NextPageText="Next" OnPageChanged="AspPage_PageChanged" PageSize="10" PrevPageText="Prev">
                                </webdiyer:AspNetPager>

事件：AspPage_PageChanged，
后台代码：Page_Load事件，btnBack_Click点击返回键事件，以及AspPage_PageChanged事件中都要调用intopage()方法：

    protected void AspPage_PageChanged(object sender, EventArgs e)
    {
        intopage();
    }

AspNetPager插件的属性：CurrentPageIndex ，PageSize（设定为10）.

注：不同于起始值为0的DataGrid控件的CurrentPageIndex和GridView控件的PageIndex属性，AspNetPager的CurrentPageIndex值是从1开始的。

以下是intopage()方法的实现代码：

    private void intopage()
    {
        this.AspPage.RecordCount = dcpu.GetCount();//获取sql记录总数
        this.Repeater1.DataSource = dcpu.GetCBProduct_CPUList(this.AspPage.CurrentPageIndex, AspPage.PageSize);//CurrentPageIndex:当前页索引值；PageSize：分页每页所能容纳值
        this.Repeater1.DataBind();
    }

调用的GetCBProduct_CPUList(int pageindex, int pagesize)方法：

public List<CBProduct> GetCBProduct_CPUList(int pageindex, int pagesize)
    {
        //string sql = string.Format("select top {0} * from [DMCD].[dbo].[CB_Product] where Part_Number not in(select top {1} Part_Number from [DMCD].[dbo].[CB_Product])", countpage, (page - 1) * countpage);
        string procedureName = "usp_SELECT_CBProductCPU_page";

        SqlParameter para1 = new SqlParameter("@I_PageIndex", SqlDbType.Int);
        para1.Direction = ParameterDirection.Input;
        para1.Value = pageindex;
        SqlParameter para2 = new SqlParameter("@I_PageSize", SqlDbType.Int);
        para2.Direction = ParameterDirection.Input;
        para2.Value = pagesize;
        SqlParameter[] paras={para1,para2};

        var result = DBUtility.ExecuteProcedure(procedureName, CommandType.StoredProcedure, DBServerType.PAP_Channel, paras);

        List<CBProduct> list = new List<CBProduct>();
        if (SqlHelper.ValidDataSetHasData(result))
        {
            var table = result.Tables[0];
            foreach (DataRow row in table.Rows)
            {
                CBProduct cbproduct = new CBProduct();
                cbproduct.part_number = row["Part_Number"].ToString();
                cbproduct.product_line_group = row["PRODUCT_LINE_GROUP"].ToString();
                cbproduct.bu_code = row["BU_Code"].ToString();
                cbproduct.include_in_amount = row["INCLUDE_IN_AMOUNT"].ToString();
                cbproduct.include_in_quantity = row["INCLUDE_IN_QUANTITY"].ToString();
                cbproduct.part_description = row["Part_Description"].ToString();
                cbproduct.create_ns = row["Create_NS"].ToString();
                cbproduct.cpu = row["CPU"].ToString();
                list.Add(cbproduct);
            }
        }
        return list;
    }

 正如上面注释的sql查询语句，如果是简单的查询整张表，并且表建立了主键索引，查询显示效率很快。

但是如果是复杂的查询，用这样的嵌套select语句就很慢，十几秒都不会完成。所以我这里选择的是建立Procedure查询，select查询出来的作为临时表<用IDENTITY(int,1,1)给临时表建立标识列>，将CurrentPageIndex和PageSize作为参数传入，结果大大提高了效率。

以下是Procedure脚本：

USE [PCPDB]
GO

/****** Object:  StoredProcedure [dbo].[usp_SELECT_CBProductCPU_page]    Script Date: 2016/3/16 14:28:36 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:        Ammy.Guo
-- Create date: 2016-03-24
-- Description:    test asp.net repeater pagedepart 
-- =============================================
CREATE PROCEDURE [dbo].[usp_SELECT_CBProductCPU_page]
    -- Add the parameters for the stored procedure here
                @I_PageIndex  int,
                @I_PageSize   int
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    
    SELECT  id=IDENTITY(int,1,1)
      ,DMProduct.[Part_Number]
      ,PRODUCT_LINE_GROUP
      ,DMProduct.[BU_Code]
      ,DMProduct.[INCLUDE_IN_AMOUNT]
      ,DMProduct.[INCLUDE_IN_QUANTITY]
      ,PCPDBProduct.Part_Description,PCPDBProduct.Create_NS,DMProduct.CPU    INTO #TEMP_CBProduct
  FROM [DMCD].[dbo].[CB_Product] as DMProduct
  inner join PCPDB.dbo.CB_PRODUCT as PCPDBProduct on DMProduct.[Part_Number] collate chinese_prc_ci_as  =PCPDBProduct.[Part_Number]
  left join PCPDB.dbo.CB_CPU as CPU_Table on DMProduct.CPU collate chinese_prc_ci_as =CPU_Table.CPU
  where DMProduct.PRODUCT_LINE_GROUP in ('Notebook','NetBook','Desktop','Tablet PC','Smart Handheld','Smart Handset','Server')
  and CPU_Table.[CPU_Brand] is null
  and DMProduct.[INCLUDE_IN_QUANTITY] = 'Yes'
  and DMProduct.Part_Number in (SELECT PART_NUMBER FROM [DMCD].[dbo].FS_CHANNEL_SELLOUT union SELECT PART_NUMBER FROM [DMCD].[dbo].FS_CHANNEL_INVENTORY)
  order by PRODUCT_LINE_GROUP,Part_Number
 

  SELECT [Part_Number]
      ,PRODUCT_LINE_GROUP
      ,[BU_Code]
      ,[INCLUDE_IN_AMOUNT]
      ,[INCLUDE_IN_QUANTITY]
      ,Part_Description
      ,Create_NS
      ,CPU  
      FROM #TEMP_CBProduct 
  WHERE id>=(@I_PageIndex-1)*@I_PageSize + 1 AND id<=(@I_PageIndex-1)*@I_PageSize + @I_PageSize

  --SELECT MAX(id) FROM #TEMP_CBProduct

  DROP TABLE #TEMP_CBProduct

END


GO

 

以下是页面效果：

END~~