自己琢磨的一个Gridview的分页

时间:2011-6-17  |   0  |  标签:, ,

本分页实现没使用Gridview的自带分页,因为我很懒,自己根据公司现有的情况写了一个,很烂,是个回发分页,想看url分页的就当打酱油了,贴上来求大牛指点一下,我还停留在代码的阶段,泪奔...

数据库有一个获取数据的存储过程,我感觉写的很好,我的这个自定义分页完全依托与这个存储过程。

先上存储过程

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

create   PROCEDURE [dbo].[sp_PageData]
	@TableName NVARCHAR(50),
	@SelectFields NVARCHAR(500),
	@PageIndex INT,
	@PageSize INT,
	@Condition NVARCHAR(4000),
	@Sort NVARCHAR(100),
	@RowCount INT output
AS

BEGIN

	IF LEN(@Condition) > 0
	BEGIN
		SET @Condition = ' WHERE ' + @Condition;
	END

	IF LEN(@Sort) = 0
	BEGIN
		SET @Sort = 'id DESC';
	END

	--获取@RowCount信息
	DECLARE @sql NVARCHAR(4000)
	SET @sql = 'SELECT @TEMP = COUNT(id) FROM ' + @TableName + @Condition
	EXEC sp_executesql @sql, N'@TEMP INT OUTPUT', @RowCount OUTPUT

	DECLARE @StartRowIndex INT;

	--根据页码和每页大小计算起始行
	IF @PageIndex < 1
	BEGIN
		SET @PageIndex = 1
	END

	DECLARE @PageCount INT
	SET @PageCount = ceiling(cast(@RowCount as float) / @PageSize)

	IF @PageIndex > @PageCount
	BEGIN
		SET @PageIndex = @PageCount
	END

	SET @StartRowIndex = ( @PageIndex - 1 )* @PageSize + 1 
	 
	EXEC
	('
	 --声明一个读取主键的游标
	 DECLARE PagingCursor CURSOR DYNAMIC READ_ONLY FOR
	 --这里只读取主键,并插入表里
	 SELECT id FROM ' + @TableName + @Condition + ' ORDER BY ' + @Sort
	)

	DECLARE @IDs NVARCHAR(2000)
	SET @IDs = '0'
	
	DECLARE @PK NVARCHAR(50)

	--打开游标
	OPEN PagingCursor
	--直接跳到起始行
	FETCH RELATIVE @StartRowIndex FROM PagingCursor INTO @PK
	 
	--不返回统计的行数
	SET NOCOUNT ON
	--开始循环读取记录
	WHILE @PageSize > 0 AND @@FETCH_STATUS = 0
	BEGIN
	   --INSERT @tblPK (PK)  VALUES ( @PK ) 
	   SET @IDs = @IDs + ',' + @PK
	   FETCH NEXT FROM PagingCursor INTO @PK
	   SET @PageSize = @PageSize - 1
	END
	 
	CLOSE       PagingCursor
	DEALLOCATE  PagingCursor
	EXEC('SELECT ' + @SelectFields + ' FROM ' + @TableName + ' WHERE ID IN (' + @IDs+ ') ORDER BY ' + @Sort )
END

aspx页面代码

     
            
            
            
            
                
                
                
                
                
                
                
                    
                        
                        
                    
                
            
            
                
共有< %# grRowCount%>条记录   第页 共

aspx.cs代码

    public int grRowCount = 0;
    public int currentIndex = 0;
    public int grPageCount = 0;
  protected void LinkClickPage(object sender, EventArgs e)
    {
        LinkButton clickBtn = sender as LinkButton;
        int pageIndex = Convert.ToInt32(clickBtn.CommandArgument);
        pageIndex = (pageIndex > -1) ? pageIndex : Convert.ToInt32((clickBtn.Parent.FindControl("txtNewPageIndex") as TextBox).Text) - 1;
        pageIndex = (pageIndex > -1) ? pageIndex : 0;
        BandData(pageIndex);
    }
    private void BandData(int pageIndex)
    {
        GridView gr = this.FindControl("GridView1") as GridView;
        DataTable source = new DataTable();
        source = GetPagerData(ConnectString, gr.PageSize, string.Empty, pageIndex + 1, string.Empty, "*", TableName, out grRowCount);
        grPageCount = (grRowCount - (grRowCount % gr.PageSize)) / gr.PageSize + (((grRowCount % gr.PageSize) == 0) ? 0 : 1);
        pageIndex = (pageIndex < 0) ? 0 : pageIndex;
        pageIndex = (pageIndex + 1 > grPageCount) ? grPageCount - 1 : pageIndex;
        currentIndex = pageIndex;
        hidPageIndex.Value = currentIndex.ToString();
        //绑定数据
        gr.DataSource = source;
        gr.DataBind();
    }
       public  DataTable GetPagerData(string connstr,int pageSize, string condition, int pageIndex, string sortField, string selectField, string tableName, out int rowCount)
        {
            DataTable da = null;
            SqlParameter[] parms = new SqlParameter[] { 
            new SqlParameter("@TableName",tableName),
            new SqlParameter("@SelectFields",selectField),
            new SqlParameter("@PageIndex",pageIndex),
            new SqlParameter("@PageSize",pageSize),
            new SqlParameter("@Condition",condition),
            new SqlParameter("@Sort",sortField),
            new SqlParameter("@RowCount",SqlDbType.Int)
            };
            parms[parms.Length - 1].Direction = ParameterDirection.Output;
            da = SqlHelper.ExecuteDatatable(connstr, CommandType.StoredProcedure, "sp_PageData", parms);
            rowCount = Convert.ToInt32(parms[parms.Length - 1].Value);
            return da;
        }
//强制让gridview的PagerTemplate显示
protected void GridView1_DataBound(object sender, EventArgs e)
    {
        if (GridView1.Rows.Count != 0)
        {
            Control table = GridView1.Controls[0];
            int count = table.Controls.Count;
            table.Controls[count - 1].Visible = true;
        }
    }

实现原理,这是一个回发分页,通过LinkButton的点击事件处理分页,以CommandArgument来获取pageIndex。还通过一个hidden控件保存了pageIndex,避免别的控件引起回发(例如LinkButton删除某条记录)后无法绑定当前页数据。






无觅相关文章插件,快速提升流量