封装分页存储过程

最新推荐文章于 2024-02-26 16:13:30 发布

weixin_34072458

最新推荐文章于 2024-02-26 16:13:30 发布

阅读量77

点赞数

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/shanshanlaichi/p/6888000.html

封装分页存储过程-查询条件相关信息的类

  /// <summary>
    /// 封装分页存储过程-查询条件相关信息的类
    /// </summary>
    public class PageCriteria
    {
        private string _TableName;
        public string TableName
        {
            get { return _TableName; }
            set { _TableName = value; }
        }

        private string _Fileds = "*";
        public string Fields
        {
            get { return _Fileds; }
            set { _Fileds = value; }
        }

        private string _PrimaryKey = "ID";
        public string PrimaryKey
        {
            get { return _PrimaryKey; }
            set { _PrimaryKey = value; }
        }

        private int _PageSize = 10;
        public int PageSize
        {
            get { return _PageSize; }
            set { _PageSize = value; }
        }

        private int _CurrentPage = 1;
        public int CurrentPage
        {
            get { return _CurrentPage; }
            set { _CurrentPage = value; }
        }

        private string _Sort = string.Empty;
        public string Sort
        {
            get { return _Sort; }
            set { _Sort = value; }
        }

        private string _Condition = string.Empty;
        public string Condition
        {
            get { return _Condition; }
            set { _Condition = value; }
        }

    }

　　分页数据结果

  /// <summary>
    /// 分页数据结果
    /// </summary>
    [DataContract(Name = "Paged{0}List")]
    public class PagedList<T>
    {
        #region 构造函数

        public PagedList()
        {
            this.CurrentPageItems = new List<T>();
        }
        public PagedList(IEnumerable<T> currentPageItems, int pageIndex, int totalItemCount)
        {
            this.CurrentPageItems = currentPageItems;
            this.TotalItemCount = totalItemCount;
            this.CurrentPageIndex = pageIndex;

        }
        #endregion

        /// <summary>
        /// 当前页
        /// </summary>
        [DataMember]
        public int CurrentPageIndex { get; set; }

        /// <summary>
        /// 记录总条数
        /// </summary>
        [DataMember]
        public int TotalItemCount { get; set; }

        /// <summary>
        /// 总页数
        /// </summary>
        [DataMember]
        public int TotalPageCount { get; set; }

        /// <summary>
        /// 结果集
        /// </summary>
        [DataMember]
        public IEnumerable<T> CurrentPageItems { get; set; }

    }

　　存储过程

USE [DSUserCenter]
GO
/****** Object:  StoredProcedure [dbo].[ProcGetPageData]    Script Date: 2017/5/22 9:22:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO






ALTER PROCEDURE [dbo].[ProcGetPageData] 
( @TableName VARCHAR(2000), --表名,多表是请使用 tA a inner join tB b On a.AID = b.AID 
@PrimaryKey NVARCHAR(100), --主键，可以带表头 a.AID 
@Fields NVARCHAR(2000) = '*',--读取字段 
@Condition NVARCHAR(3000) = '',--Where条件 
@CurrentPage INT = 1, --开始页码 
@PageSize INT = 10, --页大小 
@Sort NVARCHAR(200) = '', --排序字段 
@RecordCount INT = 0 OUT 
) 
AS 
DECLARE @strWhere VARCHAR(2000) 
DECLARE @strsql NVARCHAR(3900) 
IF @Condition IS NOT NULL AND len(ltrim(rtrim(@Condition)))>0 
BEGIN 
SET @strWhere = ' WHERE 1=1 ' + @Condition + ' ' 
END 
ELSE 
BEGIN 
SET @strWhere = '' 
END 
 
IF (charindex(ltrim(rtrim(@PrimaryKey)),@Sort)=0) 
BEGIN 
IF(@Sort='') 
SET @Sort = @PrimaryKey + ' DESC ' 
ELSE 
SET @Sort = @Sort+ ' , '+@PrimaryKey + ' DESC ' 
END 
SET @strsql = 'SELECT @RecordCount = Count(1) FROM ' + @TableName + @strWhere 
EXECUTE sp_executesql @strsql ,N'@RecordCount INT output',@RecordCount OUTPUT 
IF @CurrentPage = 1 --第一页提高性能 
BEGIN 
SET @strsql = 'SELECT TOP ' + str(@PageSize) +' '+@Fields 
+ ' FROM ' + @TableName + ' ' + @strWhere + ' ORDER BY '+ @Sort 
END 
ELSE 
BEGIN 
/* Execute dynamic query */ 
DECLARE @START_ID NVARCHAR(50) 
DECLARE @END_ID NVARCHAR(50) 
SET @START_ID = CONVERT(NVARCHAR(50),(@CurrentPage-1) * @PageSize + 1) 
SET @END_ID = CONVERT(NVARCHAR(50),@CurrentPage * @PageSize) 
SET @strsql = ' SELECT * 
FROM (SELECT ROW_NUMBER() OVER(ORDER BY '+@Sort+') AS rownum, 
'+@Fields+ ' 
FROM '+@TableName + @strWhere +') AS XX 
WHERE rownum BETWEEN '+@START_ID+' AND ' +@END_ID +' ORDER BY XX.rownum ASC' 
END 
print @strsql
EXEC(@strsql) 
RETURN

转载于:https://www.cnblogs.com/shanshanlaichi/p/6888000.html