数据库分页

数据库分页

 

USE [testdb]
GO

/****** Object:  StoredProcedure [dbo].[UP_PageByMulti]    Script Date: 08/31/2019 07:29:14 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO






------------------------------------
--用途：管理通用分页存储过程(单表、多字段排序) 
------------------------------------
CREATE PROCEDURE [dbo].[UP_PageByMulti]
@tbname     varchar(2000),           --要分页显示的表名
@FieldKey   varchar(1000),           --用于定位记录的主键(惟一键)字段,只能是单个字段
@PageCurrent int,             --要显示的页码
@PageSize   int,            --每页的大小(记录数)
@FieldShow  nvarchar(1000),  --以逗号分隔的要显示的字段列表,如果不指定,则显示所有字段
@FieldOrder  nvarchar(1000), --以逗号分隔的排序字段列表,可以指定在字段后面指定 DESC/ASC 用于指定排序顺序
@Where nvarchar(2200),  --查询条件
@PageCount  int OUTPUT,        --总页数
@RecordCount int OUTPUT   ---总记录数
AS
DECLARE @sql nvarchar(4000)
--分页字段检查
IF ISNULL(@FieldKey,N'')=''
BEGIN
RAISERROR(N'分页处理需要主键（或者惟一键）',1,16)
RETURN
END

--其他参数检查及规范
IF ISNULL(@PageCurrent,0)<1 SET @PageCurrent=1
IF ISNULL(@PageSize,0)<1 SET @PageSize=10
IF ISNULL(@FieldShow,N'')=N'' SET @FieldShow=N'*'
IF ISNULL(@Where,N'')=N''
SET @Where=N''
ELSE
SET @Where=N'  WHERE ('+@Where+N')'

--如果@PageCount为NULL值,则计算总页数(这样设计可以只在第一次计算总页数,以后调用时,把总页数传回给存储过程,避免再次计算总页数,对于不想计算总页数的处理而言,可以给@PageCount赋值)
IF @PageCount IS NULL
BEGIN
    SET @sql=N'SELECT @RecordCount=COUNT(*)'
    +N' FROM '+@tbname
    +N' '+@Where
    EXEC sp_executesql @sql,N'@RecordCount int OUTPUT',@RecordCount OUTPUT
    SET @sql=N'SELECT @PageCount=COUNT(*)'
    +N' FROM '+@tbname
    +N' '+@Where
    EXEC sp_executesql @sql,N'@PageCount int OUTPUT',@PageCount OUTPUT
    SET @PageCount=(@PageCount+@PageSize-1)/@PageSize
END
begin
declare  @startRow int,@endRow int
set @startRow=@PageSize*(@PageCurrent-1)+1
set @endRow=@PageSize*@PageCurrent    
set @sql=N'  select RowNumId,'+@FieldShow+' from  '
         +N' (select *,ROW_NUMBER() over(order by '+LTRIM(@FieldOrder)+') as RowNumId '
         +N'  from '+@tbname+ @Where+' ) as ttt'
         +N'  where RowNumId between '+CAST(@startRow as VARCHAR(max))+' and '+CAST(@endRow as varchar(max))  
         print(@sql)    
     Exec(@sql)
end



GO

 

C# 调用帮助类

 public class PageHelperBLL
    {

        PageHelperDAL Page = new PageHelperDAL();
        /// <summary>
        /// 分页方法_多字段排序
        /// </summary>      
        /// <param name="tbname">要分页显示的表名</param>
        /// <param name="FieldKey">用于定位记录的主键(惟一键)字段,只能是单个字段</param>
        /// <param name="FieldShow">以逗号分隔的要显示的字段列表,如果不指定,则显示所有字段</param>
        /// <param name="SearchWhere">查询条件需要进行字典分割处理</param>
        /// <param name="FieldOrder">以逗号分隔的排序字段列表,可以指定在字段后面指定DESC/ASC，用于指定排序顺序 例： Sort desc,Id desc</param>
        /// <param name="PageSize">每页的大小(记录数)</param>
        /// <param name="PageCurrent">当前页码</param>
        /// <param name="PageCount">总页数</param>
        /// <param name="RecordCount">总记录数</param>
        /// <param name="WhereEx">不需DAL字典处理，直接传入存储过程</param>
        /// <returns></returns>
        public  DataTable GetPagerList(string tbname, string FieldKey, string FieldShow, string SearchWhere, string FieldOrder, int PageSize, int PageCurrent, out int PageCount, out int RecordCount, string WhereEx = null)
        {           
            return Page.GetPagerList(tbname, FieldKey, FieldShow, SearchWhere, FieldOrder, PageSize, PageCurrent, out  PageCount, out  RecordCount, WhereEx).Tables[0];
        }
        public string GetBaseSearchSQL(string search)
        { 
            return Page.GetBaseSearchSQL(search);
        }
    }

 

 

 public class PageHelperDAL : BaseRepository<Admin>, IDisposable
    {
        #region 分页方法_多字段排序
        /// <summary>
        /// 分页方法_多字段排序
        /// </summary>      
        /// <param name="tbname">要分页显示的表名</param>
        /// <param name="FieldKey">用于定位记录的主键(惟一键)字段,只能是单个字段</param>
        /// <param name="FieldShow">以逗号分隔的要显示的字段列表,如果不指定,则显示所有字段</param>
        /// <param name="SearchWhere">查询条件需要进行字典分割处理</param>
        /// <param name="FieldOrder">以逗号分隔的排序字段列表,可以指定在字段后面指定DESC/ASC，用于指定排序顺序 例： Sort desc,Id desc</param>
        /// <param name="PageSize">每页的大小(记录数)</param>
        /// <param name="PageCurrent">当前页码</param>
        /// <param name="PageCount">总页数</param>
        /// <param name="RecordCount">总记录数</param>
        /// <param name="WhereEx">不需DAL字典处理，直接传入存储过程</param>
        /// <returns></returns>
        public virtual DataSet GetPagerList(string tbname, string FieldKey, string FieldShow, string SearchWhere, string FieldOrder, int PageSize, int PageCurrent, out int PageCount, out int RecordCount, string WhereEx = null)
        {
            string where = string.Empty;
            if (WhereEx != null && !string.IsNullOrWhiteSpace(WhereEx))
            {
                where = WhereEx;              
            }
            else
            {
                where = GetWhereSQL(SearchWhere);               
            }
            if (where.Length > 0)
            {
                where = "1=1 " + where.Replace("it.", "");
            }
            QiDianEntities db = new QiDianEntities();
            SqlConnection conn = GetSqlConntion(db.Database.Connection.ConnectionString);
            SqlCommand cmd = new SqlCommand("UP_PageByMulti", conn);
            cmd.CommandTimeout = 180;
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter spar;
            //
            spar = cmd.Parameters.Add("@tbname", SqlDbType.VarChar, 2000);
            spar.Direction = ParameterDirection.Input;
            spar.Value = tbname;
            //
            spar = cmd.Parameters.Add("@FieldKey", SqlDbType.VarChar, 50);
            spar.Direction = ParameterDirection.Input;
            spar.Value = FieldKey;
            //
            spar = cmd.Parameters.Add("@FieldShow", SqlDbType.VarChar, 800);
            spar.Direction = ParameterDirection.Input;
            spar.Value = FieldShow;
            //
            spar = cmd.Parameters.Add("@Where", SqlDbType.VarChar, 2200);
            spar.Direction = ParameterDirection.Input;
            spar.Value = where;
            //
            spar = cmd.Parameters.Add("@FieldOrder", SqlDbType.VarChar, 100);
            spar.Direction = ParameterDirection.Input;
            spar.Value = FieldOrder;
            //
            spar = cmd.Parameters.Add("@PageSize", SqlDbType.Int);
            spar.Direction = ParameterDirection.Input;
            spar.Value = PageSize;
            //
            spar = cmd.Parameters.Add("@PageCurrent", SqlDbType.Int);
            spar.Direction = ParameterDirection.Input;
            spar.Value = PageCurrent;
            //
            spar = cmd.Parameters.Add("@PageCount", SqlDbType.Int);
            spar.Direction = ParameterDirection.Output;

            spar = cmd.Parameters.Add("@RecordCount", SqlDbType.Int);
            spar.Direction = ParameterDirection.Output;

            DataSet ds = new DataSet();
            SqlDataAdapter CommadAdp = new SqlDataAdapter();
            CommadAdp.SelectCommand = cmd;
            try
            {
                CommadAdp.Fill(ds);
                PageCount = (int)cmd.Parameters["@PageCount"].Value;
                RecordCount = (int)cmd.Parameters["@RecordCount"].Value;
                CloseSqlConntion(conn);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            return ds;

        }
        #endregion

        public  string GetBaseSearchSQL(string search)
        {
          string Restr= GetWhereSQL(search);
          return Restr;
        }
        public void Dispose()
        {
            throw new NotImplementedException();
        }
    }

 

 

 

   PageHelperBLL pageBLL = new PageHelperBLL();
            var searchSQL = pageBLL.GetBaseSearchSQL(search);
            searchSQL += " "
            page = page.GetInt() < 1 ? 1 : page.GetInt();
            int PageCount, total = 0;
            string FieldShow = "";


  string sTable = "table";
            string FieldOrder =sort+" "+order;
            System.Data.DataTable queryData = pageBLL.GetPagerList(sTable, "ID", FieldShow, search, FieldOrder, rows.GetInt(), page, out PageCount, out total, searchSQL);