aspnetpager-分页-存储过程

SP：

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

create PROCEDURE [dbo].[newpager]

@tblname VARCHAR(255), -- 表名

@strGetFields nvarchar(1000) = "*", -- 需要返回的列

@fldName varchar(255)='', -- 排序的字段名

@PageSize int = 10, -- 页尺寸

@PageIndex int = 1, -- 页码

@doCount bit = 0, -- 返回, 非0 值则返回记录总数

@OrderType bit = 0, -- 设置排序类型, 非0 值则降序

@strWhere varchar(1500) = '' -- 查询条件(注意: 不要加where)

AS

declare @strSQL varchar(5000) -- 主语句

declare @strTmp varchar(110) -- 临时变量

declare @strOrder varchar(400) -- 排序类型

if @doCount != 0

begin

   if @strWhere !=''

    set @strSQL = 'select count(*) as Total from [' + @tblName + '] where 1=1 '+ @strWhere

   else

    set @strSQL = 'select count(*) as Total from [' + @tblName + ']'

end --以上代码的意思是如果@doCount传递过来的不是，就执行总数统计。以下的所有代码都是@doCount为的情况：

else

begin

   if @OrderType != 0--降序

   begin

    set @strTmp = '<(select min'

    set @strOrder = ' order by [' + @fldName +'] desc'--如果@OrderType不是0，就执行降序，这句很重要！

   end

   else

   begin

    set @strTmp = '>(select max'

    set @strOrder = ' order by [' + @fldName +'] asc'

   end

   if @PageIndex = 1

   begin

    if @strWhere != ''

     set @strSQL = 'select top ' + str(@PageSize) +' ' + @strGetFields + ' from [' + @tblName + '] where 1=1 ' + @strWhere + ' ' + @strOrder

    else

     set @strSQL = 'select top ' + str(@PageSize) +' ' + @strGetFields + ' from [' + @tblName + '] ' + @strOrder--如果是第一页就执行以上代码，这样会加快执行速度

   end

   else

   begin--以下代码赋予了@strSQL以真正执行的SQL代码

    set @strSQL = 'select top ' + str(@PageSize) + ' ' + @strGetFields + ' from ['   + @tblName + '] where [' + @fldName + ']' + @strTmp + '(['+ @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' [' + @fldName + '] from [' + @tblName + ']' + @strOrder + ') as tblTmp)' + @strOrder

    if @strWhere != ''

     set @strSQL = 'select top ' + str(@PageSize) +' '+ @strGetFields+ ' from [' + @tblName + '] where [' + @fldName + ']' + @strTmp + '([' + @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' [' + @fldName + '] from [' + @tblName + '] where 1=1 ' + @strWhere + ' ' + @strOrder + ') as tblTmp) and 1=1 ' + @strWhere + ' ' + @strOrder

   end

   if @strWhere !=''            --得到记录的总行数

    set @strSQL =@strSQL+ '; select count(*) as Total from [' + @tblName + '] where 1=1 '+ @strWhere

   else

    set @strSQL =@strSQL+ '; select count(*) as Total from [' + @tblName + ']'

end

exec (@strSQL)

RETURN

 

 

DAL：

        /// <summary>
       /// 分页存储过程
       /// </summary>
        /// <param name="tblname">指定分页的数据表名</param>
        /// <param name="strGetFields">输出字段，* 为所有字段</param>
        /// <param name="fldName">排序字段</param>
        /// <param name="pagesize">多少条为一页，也就是每页显示的记录数。这里提取AspNetPager控件中记录数</param>
        /// <param name="pageindex">索引页,当前页数</param>
        /// <param name="doCount">这里写死０就行了</param>
        /// <param name="ordertype">这里的１是降序，０为升序</param>
        /// <param name="strwhere">查询条件组合</param>
       /// <returns></returns>
       public DataSet GetPager(string tblname, string strGetFields, string fldName, int pagesize, int pageindex, string doCount, string ordertype, string strwhere)
       {
           return SqlHelper.ExecuteDataSet(CommandType.StoredProcedure, "newpager", new SqlParameter("@tblname", tblname),
           new SqlParameter("@strGetFields", strGetFields), new SqlParameter("@fldName", fldName), new SqlParameter("@pagesize", pagesize),
           new SqlParameter("@pageindex", pageindex), new SqlParameter("@doCount", doCount), new SqlParameter("@ordertype", ordertype), new SqlParameter("@strwhere", strwhere));
       }

 

.CS

    LCommonBLL lcommonbll = new LCommonBLL();
    protected void Page_Load(object sender, EventArgs e)
    {
        bindlist();
    }

    void bindlist()
    {
        DataSet dspager = lcommonbll.GetPager("CarColor", "*", "ColorID", AspNetPager1.PageSize, AspNetPager1.CurrentPageIndex, "0", "1", "");
        AspNetPager1.RecordCount = int.Parse(dspager.Tables[1].Rows[0][0].ToString());
        GridView1.DataSource = dspager.Tables[0].DefaultView;
        GridView1.DataBind();
    }
    protected void AspNetPager1_PageChanging1(object src, Wuqi.Webdiyer.PageChangingEventArgs e)
    {
        AspNetPager1.CurrentPageIndex = e.NewPageIndex;
        bindlist();
    }

 

 

UI

     <style type="text/css">
    .pages { color: #999; }
    .pages a, .pages .cpb { text-decoration:none;float: left; padding: 0 5px; border: 1px solid #ddd;background: #ffff;margin:0 2px; font-size:11px; color:#000;}
    .pages a:hover { background-color: #E61636; color:#fff;border:1px solid #E61636; text-decoration:none;}
    .pages .cpb { font-weight: bold; color: #fff; background: #E61636; border:1px solid #E61636;}
    </style>

 

        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False">
            <Columns>
                <asp:BoundField DataField="colorName" />
                <asp:BoundField DataField="colorValue" />
            </Columns>
        </asp:GridView>
        <webdiyer:aspnetpager id="AspNetPager1" runat="server" OnPageChanging="AspNetPager1_PageChanging1" ShowPageIndexBox="Always" PageIndexBoxType="DropDownList"  CssClass="pages" CurrentPageButtonClass="cpb" >
        </webdiyer:aspnetpager>

 

 

参考：http://hehuafeng1314.blog.163.com/blog/static/520820120098153212231/

         http://www.webdiyer.com/Controls/AspNetPager