分页存储过程运用

最新推荐文章于 2021-04-04 22:01:37 发布

shoulangwangyue

最新推荐文章于 2021-04-04 22:01:37 发布

阅读量389

点赞数 1

CC 4.0 BY-SA版权

分类专栏： sql 文章标签：存储 parameters filter dataset string sorting

本文链接：https://blog.youkuaiyun.com/shoulangwangyue/article/details/4069991

sql 专栏收录该内容

2 篇文章

订阅专栏

本文介绍了一个使用SQL Server存储过程实现的高效分页查询方法，该方法通过动态SQL及ROWCOUNT来实现灵活的分页查询，并提供了完整的示例代码，包括存储过程定义、中间层调用及业务逻辑层应用。

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

1、存储过程：

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

---------------------------------------------------------------
-- 分页存储过程(使用RowCount) --edit by SiBen
-- summary:
-- 获取表或表集合的分页数据
-- 当多表连接时,sort列必须指定表名
---------------------------------------------------------------

ALTER PROCEDURE [dbo].[Paging_RowCount]
(
@Tables varchar(1000),
@PK varchar(100),
@Sort varchar(200) = NULL,
@PageNumber int = 1,
@PageSize int = 10,
@Fields varchar(1000) = '*',
@Filter varchar(1000) = NULL,
@Group varchar(1000) = NULL,
@RecordCount int = 0 output,
@PageCount int = 0 output --总页数
)
AS

/*Default Sorting*/
IF @Sort IS NULL OR @Sort = ''
SET @Sort = @PK

/*Find the @PK type*/
DECLARE @SortTable varchar(100)
DECLARE @SortName varchar(100)
DECLARE @PKTable varchar(100)
DECLARE @PKName varchar(100)
DECLARE @strSortColumn varchar(200)
DECLARE @operator char(2)
DECLARE @type varchar(100)
DECLARE @prec int

/*Set sorting variables.*/
IF CHARINDEX('DESC',@Sort)>0
BEGIN
  SET @strSortColumn = REPLACE(@Sort, 'DESC', '')
  SET @operator = '<'
END
ELSE
BEGIN
  IF CHARINDEX('ASC', @Sort) > 0
   SET @strSortColumn = REPLACE(@Sort, 'ASC', '')
                ELSE
                        SET @strSortColumn = @Sort

SET @operator = '>'
END

/* Set PK,Sort name */
IF CHARINDEX('.', @strSortColumn) > 0
BEGIN
  SET @SortTable = SUBSTRING(@strSortColumn, 0, CHARINDEX('.',@strSortColumn))
  SET @SortName = SUBSTRING(@strSortColumn, CHARINDEX('.',@strSortColumn) + 1, LEN(@strSortColumn))
END
ELSE
BEGIN
  SET @SortTable = @Tables
  SET @SortName = @strSortColumn
END
IF CHARINDEX('.', @PK) > 0
BEGIN
  SET @PKTable = SUBSTRING(@PK, 0, CHARINDEX('.',@PK))
  SET @PKName = SUBSTRING(@PK, CHARINDEX('.',@PK) + 1, LEN(@PK))
END
ELSE
BEGIN
  SET @PKTable = @Tables
  SET @PKName = @PK
END

SELECT @type=t.name, @prec=c.prec
FROM sysobjects o
JOIN syscolumns c on o.id=c.id
JOIN systypes t on c.xusertype=t.xusertype
WHERE o.name = @SortTable AND c.name = @SortName

IF CHARINDEX('char', @type) > 0
SET @type = @type + '(' + CAST(@prec AS varchar) + ')'

DECLARE @strPageSize varchar(50)
DECLARE @strStartRow varchar(50)
DECLARE @strFilter varchar(1000)
DECLARE @strSimpleFilter varchar(1000)
DECLARE @strGroup varchar(1000)

/*Default Page Number*/
IF @PageNumber < 1
SET @PageNumber = 1

/*Set paging variables.*/
SET @strPageSize = CAST(@PageSize AS varchar(50))
SET @strStartRow = CAST(((@PageNumber - 1)*@PageSize + 1) AS varchar(50))

/*Set filter & group variables.*/
IF @Filter IS NOT NULL AND @Filter != ''
BEGIN
  SET @strFilter = ' WHERE ' + @Filter + ' '
  SET @strSimpleFilter = ' AND ' + @Filter + ' '
END
ELSE
BEGIN
  SET @strSimpleFilter = ''
  SET @strFilter = ''
END
IF @Group IS NOT NULL AND @Group != ''
SET @strGroup = ' GROUP BY ' + @Group + ' '
ELSE
SET @strGroup = ''

/*Get rows count.*/
DECLARE @str_Count_SQL nvarchar(500)
SET @str_Count_SQL= 'SELECT @TotalCount=count(*) FROM ' + @Tables + @strFilter
EXEC sp_executesql @str_Count_SQL,N'@TotalCount int=0 output',@RecordCount output
SET @PageCount=CEILING((@RecordCount+0.0)/@PageSize)
/*Execute dynamic query*/
IF @PKTable = @SortTable and @PKName = @SortName
BEGIN
  EXEC(
  '
  DECLARE @SortColumn ' + @type + '
  SET ROWCOUNT ' + @strStartRow + '
  SELECT @SortColumn=' + @strSortColumn + ' FROM ' + @Tables + @strFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + '
  SET ROWCOUNT ' + @strPageSize + '
  SELECT ' + @Fields + ' FROM ' + @Tables + ' WHERE ' + @strSortColumn + @operator + '= @SortColumn ' + @strSimpleFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + '
  '
  )
END
ELSE
BEGIN
                /* Get PK Type */
  DECLARE @pktype varchar(100)
                DECLARE @pkprec int

  SELECT @pktype=t.name, @pkprec=c.prec
  FROM sysobjects o
  JOIN syscolumns c on o.id=c.id
  JOIN systypes t on c.xusertype=t.xusertype
  WHERE o.name = @PKTable AND c.name = @PKName

IF CHARINDEX('char', @pktype) > 0
SET @pktype = @pktype + '(' + CAST(@pkprec AS varchar) + ')'

                /*Execute dynamic query*/
  EXEC(
  '
  DECLARE @SortColumn ' + @type + '
  DECLARE @SortNullValue ' + @type + '
  DECLARE @PKStartValue ' + @pktype + '
  SET @SortNullValue=CAST('''' as '+ @type +')
  SET ROWCOUNT ' + @strStartRow + '
  SELECT @SortColumn= isNull(' + @strSortColumn + ',@SortNullValue), @PKStartValue = '+ @PK +' FROM ' + @Tables + @strFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + ','+ @PK +' Desc
  SET ROWCOUNT ' + @strPageSize + '
  SELECT ' + @Fields + ' FROM ' + @Tables + ' WHERE (isNull(' + @strSortColumn+',@SortNullValue)' + @operator + ' @SortColumn or (isNull(' + @strSortColumn+',@SortNullValue)=@SortColumn and '+ @PK +'<=@PKStartValue))' + @strSimpleFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + ','+ @PK +' Desc
  '
  )
END

2、调用

2.1 中间层

public static DataSet PageList(string connectionString, string tables, string pk, string sort, int pageNumber, int pageSize,
string fields, string filter, string group, out int recordCount, out int pageCount)
        {
            SqlParameter[] parameters ={ new SqlParameter("@Tables",SqlDbType.NVarChar,1000),
                new SqlParameter("@PK",SqlDbType.NVarChar,100),
                new SqlParameter("@Sort",SqlDbType.NVarChar,200),
                new SqlParameter("@PageNumber",SqlDbType.Int),
                new SqlParameter("@PageSize",SqlDbType.Int),
                new SqlParameter("@Fields",SqlDbType.NVarChar,1000),
                new SqlParameter("@Filter",SqlDbType.NVarChar,1000),
                new SqlParameter("@Group",SqlDbType.NVarChar,1000),
                new SqlParameter("@RecordCount",SqlDbType.Int),
                new SqlParameter("@PageCount",SqlDbType.Int)};

            parameters[0].Value = tables;
            parameters[1].Value = pk;
            parameters[2].Value = sort;
            parameters[3].Value = pageNumber;
            parameters[4].Value = pageSize;
            parameters[5].Value = fields;
            parameters[6].Value = filter;
            parameters[7].Value = group;
            parameters[8].Direction = ParameterDirection.Output;
            parameters[9].Direction = ParameterDirection.Output;

DataSet ds = ExecuteDataset(connectionString, "Paging_RowCount", parameters);

            recordCount = (int)parameters[8].Value;
            pageCount = (int)parameters[9].Value;
            return ds;
        }

2.2 dal-----bll

   public DataSet PageList(string sort, int pageNumber, int pageSize, string fields, string filter, string group, out int recordCount, out int pageCount)
        {
            return DbHelperSQL.PageList(DbHelperSQL.ConnectionString, "V_M_Info_List", "InfoId", sort, pageNumber, pageSize, fields, filter, group, out recordCount, out pageCount);
        }

2.3 .cs文件

private void LoadData(int pageindex)
    {
        string Where = GetCondition();
        int RecordCount;
        int pagecount;
        DataSet rs = InfoBll.PageList("infoID DESC", pageindex, 18, "*", Where, "", out RecordCount, out pagecount);
        AspNetPager1.RecordCount = RecordCount;
        this.gv_List.DataSource = rs.Tables[0];
        this.gv_List.DataBind();
    }