AspNetPager分页控件的应用(二)

关于AspNetPager分页控件的应用，需要引用dll文件

前台控件

 <webdiyer:AspNetPager ID="anpDeviceContract" runat="server"
                    HorizontalAlign="left"
                    PageSize="15"
                    CssClass="pages" CurrentPageButtonClass="cpb" FirstPageText="首页"
            LastPageText="尾页" NextPageText="下一页" PrevPageText="上一页"
                    ShowBoxThreshold="11" TextAfterInputBox="" AlwaysShow="true"
            TextBeforeInputBox="" onpagechanged="anpDeviceContract_PageChanged"
              SubmitButtonStyle="width:30px">
        </webdiyer:AspNetPager>

 

后台应用(借助PageList类)，实现单表查询

        PageList pagelist = new PageList();

        pagelist.TableName = "T_DeviceContract";
        pagelist.ReturnFields = "*";
        pagelist.Orderfld = "Id";
        pagelist.PageIndex = this.anpDeviceContract.CurrentPageIndex;
        pagelist.PageSize = this.anpDeviceContract.PageSize;
        pagelist.OrderType = 0;
        pagelist.Where = this.GetWhere();

        int recordCount = 0;
        DataSet ds = PageListBiz.DataPageList(pagelist, out recordCount);
        this.anpDeviceContract.RecordCount = recordCount;

对结果ds进行读取即可.

 

PageListBiz中的方法

 public static DataSet DataPageList(PageList pageList, out int recordCount)
        {
            ///@TableName nvarchar(3000), -- 表名
            //@ReturnFields nvarchar(3000) = '*', -- 需要返回的列
            //@PageSize int = 10, -- 每页记录数
            //@PageIndex int = 0, -- 当前页码
            //@Where nvarchar(3000) = '', -- 查询条件
            //@Orderfld nvarchar(200), -- 排序字段名 最好为唯一主键
            //@OrderType int = 1 -- 排序类型 1:降序 其它为升序
            DataSet dsResult = null;
            recordCount = 0;

            DataAccessBase dataAccess = DataAccessFactory.GetDataAccess();
            try
            {
                BindParams(ref dataAccess, ref pageList);
                dataAccess.AddParam("TotalRecord", recordCount, ParameterDirection.Output);
                dsResult = dataAccess.ExecuteDataSet("P_GetPageList", CommandType.StoredProcedure);
                recordCount = (int)dataAccess.GetCommandParam("TotalRecord").Value;
            }
            catch (Exception ex) { throw ex; }
            finally { dataAccess.CompletedCommand(); }
            return dsResult;
        }

 

SQL中的存储过程

 

ALTER PROCEDURE [dbo].[P_GetPageList]
(
@TableName nvarchar(3000), -- 表名
@ReturnFields nvarchar(3000) = '*', -- 需要返回的列 
@PageSize int = 10, -- 每页记录数
@PageIndex int = 0, -- 当前页码
@Where nvarchar(3000) = '', -- 查询条件
@Orderfld nvarchar(200), -- 排序字段名 最好为唯一主键
@OrderType int = 1, -- 排序类型 1:降序 其它为升序
@TotalRecord int=0 output
)
AS
begin

DECLARE @StartPageSize int
DECLARE @EndPageIndex int
DECLARE @OrderBy nvarchar(255)
DECLARE @CountSql nvarchar(4000) 
DECLARE @Sql nvarchar(4000) 

if @OrderType = 1
BEGIN
	IF CHARINDEX('asc',@Orderfld) = 0 and  CHARINDEX('desc',@Orderfld) = 0 AND CHARINDEX('ASC',@Orderfld) = 0 and  CHARINDEX('DESC',@Orderfld) = 0
	begin
		set @OrderBy = ' Order by ' + REPLACE(@Orderfld,',',' desc,') + ' desc '
	end
	ELSE 
	BEGIN
		SET @OrderBy=' ORDER BY '+@Orderfld
	END
END
else
BEGIN
	IF CHARINDEX('asc',@Orderfld) = 0 and  CHARINDEX('desc',@Orderfld) = 0 AND CHARINDEX('ASC',@Orderfld) = 0 and  CHARINDEX('DESC',@Orderfld) = 0
	begin
		set @OrderBy = ' Order by ' + REPLACE(@Orderfld,',',' asc,') + ' asc ' 
	end
	ELSE
	BEGIN
		SET @OrderBy=' ORDER BY '+@Orderfld
	END
END

set @Where = ' where 1=1 '+isnull(@Where,'');

DECLARE @OldTableName nvarchar(3000);
SET @OldTableName = @TableName
DECLARE @tname nvarchar(50);--拆分后表名
--拆分表名
WHILE @OldTableName IS NOT NULL AND @OldTableName <>''
BEGIN
	exec CSplitString1 @OldTableName output,@tname output;
	set @Where = @Where+' and '+@tname+'.OPFlag <> 2 '
END

-- 总记录
set @CountSql='SELECT @TotalRecord=Count(*) From '+@TableName+' '+@Where
--print @CountSql
execute sp_executesql @CountSql,N'@TotalRecord int out',@TotalRecord out
--设置开始编号和结束编号
IF @PageSize IS NULL OR @PageSize =''
BEGIN
	SET @PageSize=10;
END
IF @PageIndex IS NULL OR @PageIndex = ''
BEGIN
	SET @PageIndex=1
END
SET @StartPageSize=(@PageIndex-1)*@PageSize
set @EndPageIndex=@PageIndex*@PageSize
--拼接查询语句
set @Sql='SELECT ROW_NUMBER() OVER ('+ @OrderBy +') AS ROWNUM, '+@ReturnFields+' FROM '+@TableName+' '+@Where
set @Sql = 'SELECT * FROM ('+ @Sql + ') AS TempTable WHERE TempTable.ROWNUM > '+CONVERT( nvarchar(10),@StartPageSize)+' and TempTable.ROWNUM <= '+CONVERT( nvarchar(10),@EndPageIndex)+' ';
--执行查询语句
--PRINT @Sql
EXEC sp_executesql @Sql
--PRINT @Sql
-- 返回总记录
SELECT @TotalRecord 
--print @Sql
end

PageList类

 public class PageList
    {
        public PageList()
        {
            this.pageSize = 10;
            this.pageIndex = 1;
            this.orderType = 1;
        }
        #region 变量
        private string tableName;
        private string returnFields;
        private int pageSize;
        private int pageIndex;
        private string where;
        private string orderfld;
        private int orderType;
        #endregion

        #region 属性
        /// <summary>
        /// 表名
        /// </summary>
        public string TableName
        {
            get { return tableName; }
            set { tableName = value; }
        }
        /// <summary>
        /// 需要返回的列 
        /// </summary>
        public string ReturnFields
        {
            get { return returnFields; }
            set { returnFields = value; }
        }
        /// <summary>
        /// 每页记录数
        /// </summary>
        public int PageSize
        {
            get { return pageSize; }
            set { pageSize = value; }
        }
        /// <summary>
        /// 当前页码
        /// </summary>
        public int PageIndex
        {
            get { return pageIndex; }
            set { pageIndex = value; }
        }
        /// <summary>
        /// 查询条件
        /// </summary>
        public string Where
        {
            get { return where; }
            set { where = value; }
        }
        /// <summary>
        /// 排序字段名 最好为唯一主键
        /// </summary>
        public string Orderfld
        {
            get { return orderfld; }
            set { orderfld = value; }
        }
        
        /// <summary>
        /// 排序类型 1:降序 其它为升序
        /// </summary>
        public int OrderType
        {
            get { return orderType; }
            set { orderType = value; }
        }
        #endregion
    }