AspNetPager分页控件的使用

最新推荐文章于 2024-12-02 09:28:36 发布

@CAPRICA@

最新推荐文章于 2024-12-02 09:28:36 发布

阅读量659

点赞数

CC 4.0 BY-SA版权

分类专栏： .net

本文链接：https://blog.youkuaiyun.com/joycesunny/article/details/26376439

.net 专栏收录该内容

87 篇文章

订阅专栏

本文介绍了一个SQL Server中实现分页查询的存储过程UP_GetRecordByPageOrder，该过程允许用户通过输入表名、字段名等参数进行灵活的分页查询，并提供了按不同字段排序的功能。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

创建存储过程 UP_GetRecordByPageOrder

USE [数据库名字]
GO
/****** 对象:  StoredProcedure [dbo].[UP_GetRecordByPageOrder]    脚本日期: 05/20/2014 16:21:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[UP_GetRecordByPageOrder]
 
 @tblName varchar(255),   -- 表名 
 @fldName varchar(255),   -- 显示字段名 
 @OrderfldName varchar(255),  -- 排序字段名 
 @StatfldName varchar(255),  -- 统计字段名 
 @PageSize int = 10,   -- 页尺寸 
 @PageIndex int = 1,   -- 页码 
 @IsReCount bit = 0,   -- 返回记录总数, 非 0 值则返回 
 @OrderType bit = 0,   -- 设置排序类型, 非 0 值则降序 
 @strWhere varchar(1000) = ''  -- 查询条件 (注意: 不要加 where) 
AS 

 declare @strSQL varchar(6000)  -- 主语句 
 declare @strTmp varchar(100)   -- 临时变量 
 declare @strOrder varchar(400)  -- 排序类型 

if @OrderType != 0 
 begin 
  set @strTmp = '<(select min' 
  set @strOrder = ' order by [' + @OrderfldName +'] desc' 
 end 
else 
 begin 
  set @strTmp = '>(select max' 
  set @strOrder = ' order by [' + @OrderfldName +'] asc' 
 end 

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

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

if @PageIndex = 1 
 begin 
  set @strTmp = '' 
  if @strWhere != '' 
  set @strTmp = ' where ' + @strWhere 

  set @strSQL = 'select top ' + str(@PageSize) + ' ' + @fldName + ' from [' 
  + @tblName + ']' + @strTmp + ' ' + @strOrder 
 end 


if @IsReCount != 0 
 set @strSQL = @strSQL+' select count(1) as Total from [' + @tblName + ']'

if @strWhere!=''
 set @strSQL = @strSQL+' where ' + @strWhere
exec (@strSQL)

DAL 参数参考上面的存储过程

 /// <summary>
        /// 分页
        /// </summary>
        /// <param name="PageSize"></param>
        /// <param name="PageIndex"></param>
        /// <param name="strWhere"></param>
        /// <returns></returns>
        public DataSet GetList(int PageSize, int PageIndex, string strWhere)
        {
            SqlParameter[] parameters = {
                        new SqlParameter("@tblName", SqlDbType.VarChar, 255),
                        new SqlParameter("@fldName", SqlDbType.VarChar, 255),
                        new SqlParameter("@OrderfldName", SqlDbType.VarChar, 255),
                        new SqlParameter("@StatfldName", SqlDbType.VarChar, 255),
                        new SqlParameter("@PageSize", SqlDbType.Int),
                        new SqlParameter("@PageIndex", SqlDbType.Int),
                        new SqlParameter("@IsReCount", SqlDbType.Bit),
                        new SqlParameter("@OrderType", SqlDbType.Bit),
                        new SqlParameter("@strWhere", SqlDbType.VarChar,1000),
            };
            parameters[0].Value = "数据表名字";
            parameters[1].Value = "*";
            parameters[2].Value = "用于排序的字段名字";
            parameters[3].Value = "";
            parameters[4].Value = PageSize;
            parameters[5].Value = PageIndex;
            parameters[6].Value = 1;
            parameters[7].Value = 0;
            parameters[8].Value = strWhere;
            return DbHelperSQL.RunProcedure("UP_GetRecordByPageOrder", parameters, "ds");
        }

BLL

        /// <summary>
        /// 分页
        /// </summary>
        /// <param name="PageSize"></param>
        /// <param name="PageIndex"></param>
        /// <param name="strWhere"></param>
        /// <returns></returns>
        public DataSet GetList(int PageSize, int PageIndex, string strWhere)
        {
            return dal.GetList(PageSize, PageIndex, strWhere);
        }

CS 调用演示

        Pz.BLL.Demo pbd = new Pz.BLL.Demo();
        string strSql = string.Empty;
        DataSet ds = new DataSet();

        protected void Page_Load(object sender, EventArgs e)
        {
            strSql = 这里只要select语句 where 后的条件部分;
            ds = pbd.GetList(AspNetPager1.PageSize, AspNetPager1.CurrentPageIndex, strSql);
            AspNetPager1.RecordCount = (int)ds.Tables[1].Rows[0][0];
            bind();
        }

        protected void bind()
        {
            ds = pbd.GetList(AspNetPager1.PageSize, AspNetPager1.CurrentPageIndex, strSql);
            Repeater1.DataSource = ds.Tables[0];
            Repeater1.DataBind();
        }

        protected void AspNetPager1_PageChanged(object sender, EventArgs e)
        {
            bind();
        }

ASPX 演示

<%@ Register Assembly="AspNetPager" Namespace="Wuqi.Webdiyer" TagPrefix="webdiyer" %>

 <webdiyer:AspNetPager ID="AspNetPager1" runat="server" PageSize="30" HorizontalAlign="Center"
            Width="100%" PageIndexBoxType="DropDownList" OnPageChanged="AspNetPager1_PageChanged"
            NumericButtonTextFormatString="<{0}>">
        </webdiyer:AspNetPager>