这两天由于‘销售渠道’系统需要实现新功能,开发了三个页面,三个界面功能大致相同。
功能:分页显示特定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~~