介绍一下和AspNetPager结合的不错的分页方案

yangying · 发表于 2012-7-19 10:33:33

先说一下存储过程的写法

[SQL] 纯文本查看 复制代码

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[Psp_SetPagination]
(
  @vc_Table varchar(50),--表名
  @vc_SqlWhere varchar(1000)='',--条件
  @vc_SqlOrder varchar(1000)='',--排序规则
  @vc_SqlFile varchar(1000)='*',--选取字段
  @vc_Key varchar(50)='',--关键字
  @vc_FieldType varchar(50)='',--字段类型
  @i_PageSize int=1,--分页大小
  @i_PageIndex int=1,--页面索引
  @bt_Count bit  --在统计吗？
)
As
set nocount on
Declare @vc_Sql varchar(5000)
if @bt_Count=1
BEGIN
    set @vc_Sql='select count(*) from '+@vc_Table+' '+@vc_SqlWhere
END
ELSE
BEGIN
    set @vc_Sql='Declare @indexTable Table(Id decimal identity(1,1),Temp_Key '+@vc_FieldType+ ')'+char(13)
    set @vc_Sql=@vc_Sql+
                'Declare @PageLowerBound int'+char(13)
    set @vc_Sql=@vc_Sql+
                'Declare @PageUpperBound int'+char(13)
    set @vc_Sql=@vc_Sql+
                'set @PageLowerBound='+cast((@i_PageIndex-1)*@i_PageSize as varchar(8))+char(13)
    set @vc_Sql=@vc_Sql+
                'set @PageUpperBound=@PageLowerBound+'+cast(@i_PageSize as varchar(8))+char(13)
    set @vc_Sql=@vc_Sql+
                'set rowcount @PageUpperBound'+char(13)
    set @vc_Sql=@vc_Sql+
                'Insert into @indexTable(Temp_Key)'+
                'Select '+@vc_Key+' from '+@vc_Table+' '+@vc_SqlWhere+' '+@vc_SqlOrder+char(13)
    if @vc_SqlWhere<>''
        begin
            set @vc_SqlWhere=@vc_SqlWhere+' and '+@vc_Key+'=t.Temp_Key and t.Id>@PageLowerBound and t.Id<=@PageUpperBound'
        end
    else
        begin
            set @vc_SqlWhere='Where '+@vc_Key+'=t.Temp_Key and t.Id>@PageLowerBound and t.Id<=@PageUpperBound'
        end
    set @vc_Sql=@vc_Sql+
                'Select  '+@vc_SqlFile+'  from '+@vc_Table+',@indexTable t '+@vc_SqlWhere+' Order by t.Id'
END
EXEC(@vc_Sql)

看完之后有什么想法，是不是很好用啊，你可以把它当成一个共用的过程也可以在前台写好访问的方法，我喜欢在前台写好方法，这样的话，它就和表数据库无关了，只要我做项目，把这个存储过程在数据库里一运行，在前台用写好的方法，调用就行了，没有任何分别，和表和数据库都没有关系，更好的是他是在Sql2000的基础上写的，所以，不管是在Sql2000还是Sql05里都是可以的，程序是一个类也不用变，呵呵这样是不是很好啊，下面我说一下怎么写类，
有两个类一个是 AspNetPagerInfo.cs 这个类是一个Model类主要是属性，用来传参数用的，
关键是AspNetPager.cs这个类，这个类才是我们要用的，下面是代码分别看一下吧
AspNetPagerInfo.cs

[C#] 纯文本查看 复制代码

using System;
using System.Data;
using System.Configuration;
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;

/// <summary>
/// AspNetPagerInfo 的摘要说明
/// </summary>
public class AspNetPagerInfo
{
    private string _TableName;
    private string _SqlWhere;
    private string _SqlOrder;
    private string _SqlFile;
    private string _KeyField;
    private string _FieldType;
    private int _PageSize;
    private int _PageIndex;

    /// <summary>
    /// 信息表或视图
    /// </summary>
    public string TableName
    {
        get
        {
            return _TableName;
        }
        set
        {
            _TableName = value;
        }
    }

    /// <summary>
    /// 信息检索条件
    /// </summary>
    public string SqlWhere
    {
        get
        {
            return _SqlWhere;
        }
        set
        {
            _SqlWhere = value;
        }
    }

    /// <summary>
    /// 排序关键字
    /// </summary>
    public string SqlOrder
    {
        get
        {
            return _SqlOrder;
        }
        set
        {
            _SqlOrder = value;
        }
    }

    /// <summary>
    /// 检索字段，各个字段用‘,’分割
    /// </summary>
    public string SqlFile
    {
        get
        {
            return _SqlFile;
        }
        set
        {
            _SqlFile = value;
        }
    }

    /// <summary>
    /// 关键字名称
    /// </summary>
    public string KeyField
    {
        get
        {
            return _KeyField;
        }
        set
        {
            _KeyField = value;
        }
    }

    /// <summary>
    /// 关键字类型
    /// </summary>
    public string FieldType
    {
        get
        {
            return _FieldType;
        }
        set
        {
            _FieldType = value;
        }
    }

    /// <summary>
    /// 分页大小
    /// </summary>
    public int PageSize
    {
        get
        {
            return _PageSize;
        }
        set
        {
            _PageSize = value;
        }
    }

    /// <summary>
    /// 页面索引
    /// </summary>
    public int PageIndex
    {
        get
        {
            return _PageIndex;
        }
        set
        {
            _PageIndex = value;
        }
    }
}

AspNetPager

[C#] 纯文本查看 复制代码

using System;
using System.Data;
using System.Configuration;
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 System.Data.SqlClient;
using DBUtility;
/// <summary>
/// AspNetPager 的摘要说明
/// </summary>
public class AspNetPager
{
    /// <summary>
        /// 获取记录集总数
        /// </summary>
        /// <returns></returns>
    public int GetRecordCount(AspNetPagerInfo info)
    {
        SqlParameter[] parameters = SqlHelper.GetCachedParameters("Psp_SetPagination");
        if (parameters == null)
        {
            parameters = new SqlParameter[] { 
                    new SqlParameter("@vc_Table",SqlDbType.VarChar,50),
                    new SqlParameter("@vc_SqlWhere",SqlDbType.VarChar,1000),
                    new SqlParameter("@vc_SqlOrder",SqlDbType.VarChar,1000),
                    new SqlParameter("@vc_SqlFile",SqlDbType.VarChar,1000),
                    new SqlParameter("@vc_Key",SqlDbType.VarChar,50),
                    new SqlParameter("@vc_FieldType",SqlDbType.VarChar,50),
                    new SqlParameter("@i_PageSize",SqlDbType.Int),
                    new SqlParameter("@i_PageIndex",SqlDbType.Int),
                    new SqlParameter("@bt_Count",SqlDbType.Bit)
                };

            SqlHelper.CacheParameters("Psp_SetPagination", parameters);
        }

        parameters[0].Value = info.TableName;
        parameters[1].Value = info.SqlWhere;
        parameters[2].Value = info.SqlOrder;
        parameters[3].Value = info.SqlFile;
        parameters[4].Value = info.KeyField;
        parameters[5].Value = info.FieldType;
        parameters[6].Value = info.PageSize;
        parameters[7].Value = info.PageIndex;
        parameters[8].Value = 1;

        return (int)SqlHelper.ExecuteScalar(SqlHelper.ConnectionStringLocal, CommandType.StoredProcedure, "Psp_SetPagination", parameters);
    }

    /// <summary>
    /// 获取分页后列表
    /// </summary>
    /// <param name="info"></param>
    /// <returns></returns>
    public SqlDataReader GetListForDataReader(AspNetPagerInfo info)
    {
        SqlParameter[] parameters = SqlHelper.GetCachedParameters("Psp_SetPagination");
        if (parameters == null)
        {
            parameters = new SqlParameter[] { 
                    new SqlParameter("@vc_Table",SqlDbType.VarChar,50),
                    new SqlParameter("@vc_SqlWhere",SqlDbType.VarChar,1000),
                    new SqlParameter("@vc_SqlOrder",SqlDbType.VarChar,1000),
                    new SqlParameter("@vc_SqlFile",SqlDbType.VarChar,1000),
                    new SqlParameter("@vc_Key",SqlDbType.VarChar,50),
                    new SqlParameter("@vc_FieldType",SqlDbType.VarChar,50),
                    new SqlParameter("@i_PageSize",SqlDbType.Int),
                    new SqlParameter("@i_PageIndex",SqlDbType.Int),
                    new SqlParameter("@bt_Count",SqlDbType.Bit)
                };

            SqlHelper.CacheParameters("Psp_SetPagination", parameters);
        }

        parameters[0].Value = info.TableName;
        parameters[1].Value = info.SqlWhere;
        parameters[2].Value = info.SqlOrder;
        parameters[3].Value = info.SqlFile;
        parameters[4].Value = info.KeyField;
        parameters[5].Value = info.FieldType;
        parameters[6].Value = info.PageSize;
        parameters[7].Value = info.PageIndex;
        parameters[8].Value = 0;

        return SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocal, CommandType.StoredProcedure, "Psp_SetPagination", parameters);
    }

    public DataSet GetListForDataSet(AspNetPagerInfo info)
    {
        SqlParameter[] parameters = SqlHelper.GetCachedParameters("Psp_SetPagination");
        if (parameters == null)
        {
            parameters = new SqlParameter[] { 
                    new SqlParameter("@vc_Table",SqlDbType.VarChar,50),
                    new SqlParameter("@vc_SqlWhere",SqlDbType.VarChar,1000),
                    new SqlParameter("@vc_SqlOrder",SqlDbType.VarChar,1000),
                    new SqlParameter("@vc_SqlFile",SqlDbType.VarChar,1000),
                    new SqlParameter("@vc_Key",SqlDbType.VarChar,50),
                    new SqlParameter("@vc_FieldType",SqlDbType.VarChar,50),
                    new SqlParameter("@i_PageSize",SqlDbType.Int),
                    new SqlParameter("@i_PageIndex",SqlDbType.Int),
                    new SqlParameter("@bt_Count",SqlDbType.Bit)
                };

            SqlHelper.CacheParameters("Psp_SetPagination", parameters);
        }

        parameters[0].Value = info.TableName;
        parameters[1].Value = info.SqlWhere;
        parameters[2].Value = info.SqlOrder;
        parameters[3].Value = info.SqlFile;
        parameters[4].Value = info.KeyField;
        parameters[5].Value = info.FieldType;
        parameters[6].Value = info.PageSize;
        parameters[7].Value = info.PageIndex;
        parameters[8].Value = 0;

        return SqlHelper.ExecuteDataSet(SqlHelper.ConnectionStringLocal, CommandType.StoredProcedure, "Psp_SetPagination", parameters);
    }
}

看完了如果还不会用的话那看看这个用法，吧，以后只要是分页的就可以用这个了，呵呵很方便的，我相信你们会喜欢上他的，呵呵因为只要这一个存储过程以后就再也不用写这样的存储过程和方法了，
用法

[C#] 纯文本查看 复制代码

//在这里先实例化一下
    private AspNetPagerInfo info = new AspNetPagerInfo();
    private AspNetPager Pager = new AspNetPager();
//用法，在什么地方用就放什么地方吧
 private void bindGrid()
    {
        try
        {
            //给存储过程传参数
            info.TableName = "V_Shop";  //表名
            info.PageSize = AspNetPager1.PageSize;
            info.PageIndex = AspNetPager1.CurrentPageIndex;
            info.KeyField = "SID"; //ID
            info.FieldType = "int"; //ID类型
            info.SqlFile = "*"; //要查询的列，
            info.SqlOrder = "order by SID"; //排序
            info.SqlWhere = "where SNumber<=SStockJingGaoNumber"; //这里写条件加Where
            AspNetPager1.RecordCount = Pager.GetRecordCount(info);
            GVShop.DataSource = Pager.GetListForDataSet(info);
            GVShop.DataBind();
            //隐藏过长的数据
            for (int j = 0; j < GVShop.Columns.Count; j++)
            {
                for (int i = 0; i < GVShop.Rows.Count; i++)
                {
[i]                    GVShop.Rows.Cells[j].ToolTip = GVShop.Rows.Cells[j].Text;
                    if (GVShop.Rows.Cells[j].Text.Length > 30)
                    {
                        GVShop.Rows.Cells[j].Text = GVShop.Rows.Cells[j].Text.Substring(0, 30) + "";
                    }
                }
            }
      
        }
        catch (Exception ex)
        {
            ClientScriptManager csm = this.ClientScript;
            csm.RegisterStartupScript(this.GetType(), "admin", "alter('" + ex.Message.ToString().Trim() + "')", true);
        }
    }

我相信大家可以看明白了，要是还有什么不明白的就留言吧，呵呵

心若止水 · 发表于 2012-7-19 13:53:41

不知道效率如何

yangying · 发表于 2012-7-19 16:41:32

心若止水发表于 2012-7-19 13:53
不知道效率如何

效率方法还行吧1000万行以内测试没有任何问题，如果是更多的数据量的话目前没有测试过。

		自动登录	找回密码
密码			马上注册

[Asp.Net] 介绍一下和AspNetPager结合的不错的分页方案