分页控件、存储过程 URL传参数

存储过程

 

USE [TicketCenter]
GO
/****** Object:  StoredProcedure [dbo].[W_TicketCenter_TicketsGet]    Script Date: 06/11/2008 10:16:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- 获取所有Ticket
ALTER proc [dbo].[W_TicketCenter_TicketsGet]
@CategoryId int = 0,
@Subject nvarchar(500) = '',
@CustomerId int = 0,
@IsUnRead int = 0, --是否未读  0获取所有的  1获取未读的
@PageIndex int = 0,
@PageSize int = 20
as
begin
 declare
  @sql nvarchar(4000),  
  @CountSql nvarchar(2000),
  @RowCount int,
  @PageCount int,
   @start int, --每页的起始位置
  @end int,  --每页的终止位置
  @p1 INT
 
  set @sql = '
   select a.id,a.subject,a.updatetime,b.Name as categoryName,
   (
    select count(Id) from C_TicketItem
    where C_TicketItem.IsRead = 0 and C_TicketItem.[Status] = 2
    and C_TicketItem.TicketId = a.Id and C_TicketItem.Type = 2
   ) as UnReadCount
   ,
   ( 
    case when
    exists
    (
     select Id from C_TicketItem where C_TicketItem.TicketId = a.Id  and C_TicketItem.id in
     (select TicketItemId from C_TicketAttachment)
    )
    then 1
    else
    0
    end
   ) as IsHasAtt,
   ROW_NUMBER() OVER(order by a.Id desc) as rownumber
   from C_Ticket a
   left join C_TicketCategory b
   on a.CategoryId = b.Id
   where a.IsDeleted = 0
  '
  
  if @CategoryId > 0
  begin
   set @sql = @sql + '
    and a.CategoryId = ' + str(@CategoryId) + '
   '
  end
  
  if @Subject > ''
  begin
   set @sql = @sql + '
    and a.Subject like ''%'+ @Subject +'%''
   '
  end

  

-- -- 总记录数
-- Set @CountSql = 'SELECT @RowCount = count(1) FROM (' + @sql + ') as items '
-- EXEC sp_executesql  @CountSql, N'@RowCount INT OUT', @RowCount OUT
--
-- IF ISNULL(@PageSize, 0) < 1
--  SET @PageSize = 30
-- SET @PageCount = (@RowCount + @PageSize - 1) / @PageSize
-- IF ISNULL(@PageIndex, 0) < 1
--  SET @PageIndex = 1
-- ELSE IF ISNULL(@PageIndex, 0) > @PageCount
--  SET @PageIndex = @PageCount
--
-- -- 起始页
-- set @start = (@PageIndex-1)*@PageSize + 1
-- -- 结束页
-- set @end = @PageIndex*@PageSize
-- 
-- -- 返回页数和记录条数
--    select @PageCount as [PageCount],@RowCount as [RowCount]
--
-- set @sql = 'select * from (' + @sql + ') as items where RowNumber >=  ' + str( @start ) + ' and RowNumber <= ' + str( @end ) 
--  exec(@sql)

 EXEC sp_cursoropen
  @cursor = @p1 OUTPUT,
  @stmt = @sql,
  @scrollopt = 1,
  @ccopt = 1,
  @rowcount = @RowCount OUTPUT

 IF ISNULL(@PageSize, 0) < 1
  SET @PageSize = 30
 SET @PageCount = (@RowCount + @PageSize - 1) / @PageSize
 IF ISNULL(@PageIndex, 0) < 1
  SET @PageIndex = 1
 ELSE IF ISNULL(@PageIndex, 0) > @PageCount
  SET @PageIndex = @PageCount
 set @start  = (@PageIndex-1)*@PageSize + 1
 set @end = @PageIndex*@PageSize

 set @sql = 'select * from (' + @sql + ') as items where RowNumber >=  ' + str( @start ) + ' and RowNumber <= ' + str( @end ) 
  exec(@sql)
    SELECT @RowCount

end

 

 

页面中调用：

<%@ Register TagPrefix="PH" Namespace="分页控件类的路径 如 项目--文件夹--文件名 PandaHall.SiteManager.Common.Pager "

   Assembly="分页控件类被编译到的程序集名称，不能带后辍名 如 PandaHall.SiteManager " %>

 

<PH:Pager ID="pager" runat="server" ItemSize="30" OnPageIndexChanged="pager_PageIndexChanged" /> 

 

 

CS中调用

 

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
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 PandaHall.SiteManagerLogic;
using PandaHall.SiteManager.Common;

namespace PandaHall.SiteManager.Promotion
{
    public partial class ProductList : PageBase
    {

        /// <summary>
        /// 页码
        /// </summary>
        public int pageNo = 0;
 
        protected void Page_Load(object sender, EventArgs e)
        {
           
        } 

        // 分页
        protected void pager_PageIndexChanged(object sender, PandaHall.SiteManager.Common.Pager.PageChangedEventArgs e)
        {
            BindPromotionProductList();
        }

        #region Method

        /// <summary>
        /// 绑定促销活动的商品列表
        /// </summary>
        private void BindPromotionProductList()
        {
            // 商品名称
            string ProductName = this.txtProductName.Text.Trim();
            // 商品状态
            int State = int.Parse(this.ddlState.SelectedValue);
            // 已售数量
            decimal SalesNum = decimal.Parse(this.ddlSalesNum.SelectedValue);

            int PageCount = 0;
            pageNo = this.pager.PageNo;
            int PageSize = int.Parse(ConfigurationManager.AppSettings["size"].ToString());

            this.gvProduct.DataSource = PromotionProductDAL.GetPromotionProductList(Pid, ProductName, State, SalesNum, this.pager.PageNo + 1, PageSize, out PageCount);
            this.gvProduct.DataBind();

            this.pager.PageCount = PageCount;           
        }
     } 
}

 

附件：分页类