Sql Server 分页通用存储过程及调用

存储过程

 

CREATE          PROC dbo.procPageViewSelect
@tbname     sysname,                -- 要分页显示的表名
@FieldKey    nvarchar( 1000),       -- 用于定位记录的主键(惟一键)字段,可以是逗号分隔的多个字段
@PageCurrent  int = 1,                -- 要显示的页码
@PageSize    int = 10,                 -- 每页的大小(记录数)
@FieldShow  nvarchar( 1000) = '',       -- 以逗号分隔的要显示的字段列表,如果不指定,则显示所有字段
@FieldOrder  nvarchar( 1000) = '',       -- 以逗号分隔的排序字段列表,可以指定在字段后面指定DESC/ASC
                                         --   用于指定排序顺序
@Where     nvarchar( 4000) = '',      -- 查询条件
@PageCount  int =- 1 OUTPUT,            -- 总页数
@RecordCount  int =- 1 OUTPUT            -- 总记录数
AS
SET NOCOUNT  ON
-- 检查对象是否有效
IF  OBJECT_ID( @tbname)  IS  NULL
BEGIN
     RAISERROR(N ' 对象"%s"不存在 ', 1, 16, @tbname)
     RETURN
END
IF  OBJECTPROPERTY( OBJECT_ID( @tbname),N ' IsTable ') = 0
     AND  OBJECTPROPERTY( OBJECT_ID( @tbname),N ' IsView ') = 0
     AND  OBJECTPROPERTY( OBJECT_ID( @tbname),N ' IsTableFunction ') = 0
BEGIN
     RAISERROR(N ' "%s"不是表、视图或者表值函数 ', 1, 16, @tbname)
     RETURN
END

-- 分页字段检查
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( @FieldOrder,N '') =N ''
     SET  @FieldOrder =N ''
ELSE
     SET  @FieldOrder =N ' ORDER BY  ' + LTRIM( @FieldOrder)
IF  ISNULL( @Where,N '') =N ''
     SET  @Where =N ''
ELSE
     SET  @Where =N ' WHERE ( ' + @Where +N ' ) '

-- 如果@PageCount为-1,则计算总页数(这样设计可以只在第一次计算总页数,以后调用时,把总页数传回给存储过程,避免再次计算总页数,对于不想计算总页数的处理而言,可以给@PageCount赋值)
IF  @PageCount <>- 1
BEGIN
     DECLARE  @sql  nvarchar( 4000)
     SET  @sql =N ' SELECT @PageCount=COUNT(*) '
         +N '  FROM  ' + @tbname
         +N '   ' + @Where
     EXEC sp_executesql  @sql,N ' @PageCount int OUTPUT ', @PageCount OUTPUT
     SET  @RecordCount = @PageCount
     SET  @PageCount =( @PageCount + @PageSize - 1) / @PageSize
END

-- 计算分页显示的TOPN值
DECLARE  @TopN  varchar( 20), @TopN1  varchar( 20)
SELECT  @TopN = @PageSize,
     @TopN1 =( @PageCurrent - 1) * @PageSize

-- 第一页直接显示
IF  @PageCurrent = 1
     EXEC(N ' SELECT TOP  ' + @TopN
         +N '   ' + @FieldShow
         +N '  FROM  ' + @tbname
         +N '   ' + @Where
         +N '   ' + @FieldOrder)
ELSE
BEGIN
     -- 处理别名
     IF  @FieldShow =N ' * '
         SET  @FieldShow =N ' a.* '

     -- 生成主键(惟一键)处理条件
     DECLARE  @Where1  nvarchar( 4000), @Where2  nvarchar( 4000),
         @s  nvarchar( 1000), @Field sysname
     SELECT  @Where1 =N '', @Where2 =N '', @s = @FieldKey

     WHILE  CHARINDEX(N ' , ', @s) > 0
     begin
         SELECT  @Field =LEFT( @s, CHARINDEX(N ' , ', @s) - 1),
             @s = STUFF( @s, 1, CHARINDEX(N ' , ', @s),N ''),
             @Where1 = @Where1 +N '  AND a. ' + @Field +N ' =b. ' + @Field,
             @Where2 = @Where2 +N '  AND b. ' + @Field +N '  IS NULL ',
             @Where = REPLACE( @Where, @Field,N ' a. ' + @Field),
             @FieldOrder = REPLACE( @FieldOrder, @Field,N ' a. ' + @Field),
             @FieldShow = REPLACE( @FieldShow, @Field,N ' a. ' + @Field)
     end
     SELECT  @Where = REPLACE( @Where, @s,N ' a. ' + @s),
         @FieldOrder = REPLACE( @FieldOrder, @s,N ' a. ' + @s),
         @FieldShow = REPLACE( @FieldShow, @s,N ' a. ' + @s),
         @Where1 = STUFF( @Where1 +N '  AND a. ' + @s +N ' =b. ' + @s, 1, 5,N ''),    
         @Where2 = CASE
             WHEN  @Where = ''  THEN N ' WHERE ( '
             ELSE  @Where +N '  AND ( '
             END +N ' b. ' + @s +N '  IS NULL ' + @Where2 +N ' ) '


     EXEC(N ' SELECT TOP  ' + @TopN
         +N '   ' + @FieldShow
         +N '  FROM  ' + @tbname
         +N '  a LEFT JOIN(SELECT TOP  ' + @TopN1
         +N '   ' + @FieldKey
         +N '  FROM  ' + @tbname
         +N '  a  ' + @Where
         +N '   ' + @FieldOrder
         +N ' )b ON  ' + @Where1
         +N '   ' + @Where2
         +N '   ' + @FieldOrder) 

END
GO

 

C#调用

           string sqlWhere =  "";
             if(! string.IsNullOrEmpty(searchCondition)){
                sqlWhere =  " MUSIC_NAME like '% " + searchCondition +  " %' or MUSIC_ACTOR_NAME like '% " + searchCondition +  " %' ";
            }
            SqlParameter[] sqlParameters ={  new SqlParameter( " @tbname ", SqlDbType.NVarChar,  128), 
                                             new SqlParameter( " @FieldKey ", SqlDbType.NVarChar,  128),
                                             new SqlParameter( " @PageCurrent ", SqlDbType.Int), 
                                             new SqlParameter( " @PageSize ", SqlDbType.Int),
                                             new SqlParameter( " @FieldShow ", SqlDbType.NVarChar,  1000),
                                             new SqlParameter( " @FieldOrder ", SqlDbType.NVarChar,  1000),
                                             new SqlParameter( " @Where ", SqlDbType.NVarChar,  4000), 
                                             new SqlParameter( " @PageCount ", SqlDbType.Int), 
                                             new SqlParameter( " @RecordCount ", SqlDbType.Int)
                                         };
            sqlParameters[ 0].Value =  " VIEW_MUSIC_TOTAL ";
            sqlParameters[ 1].Value =  " MUSIC_ID ";
            sqlParameters[ 2].Value = pageNo;
            sqlParameters[ 3].Value = pageSize;
            sqlParameters[ 4].Value =  " * ";
            sqlParameters[ 5].Value =  " TOTAL_NUM DESC ";
            sqlParameters[ 6].Value = sqlWhere;
            sqlParameters[ 7].Value =  0;
            sqlParameters[ 7].Direction = ParameterDirection.InputOutput;
            sqlParameters[ 8].Direction = ParameterDirection.Output;

            Page musicPage =  new Page();
            List<MusicItem> musicItemList =  new List<MusicItem>();
            SqlDataReader dr =  null;
             try {
                dr = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringMusic, CommandType.StoredProcedure,  " procPageViewSelect ", sqlParameters);
                 while (dr.Read()) {
                    MusicItem musicItem =  new MusicItem();
                    musicItem.Id = Convert.ToInt32(dr[ " MUSIC_ID "]);
                    musicItem.Name = dr[ " MUSIC_NAME "].ToString();
                    musicItem.ActorName = dr[ " MUSIC_ACTOR_NAME "].ToString();
                    musicItemList.Add(musicItem);
                }
            }  catch (Exception e) {
                musicItemList =  null;
                response.Result =  false;
                response.desc =  " 失败 ";
                response.MusicPage =  null;
                LogTools.Error( " 根据查询条件获取歌曲列表,出错 ", e,  this.GetType());
            }  finally {
                 if (dr !=  null) {
                    dr.Close();
                }
                 if (musicItemList !=  null) {
                    response.Result =  true;
                    response.desc =  " 成功 ";
                    musicPage.Data = musicItemList;
                    musicPage.TotalPage =  int.Parse(sqlParameters[ 7].Value.ToString());
                    musicPage.PageSize = pageSize;
                    musicPage.TotalCount =  int.Parse(sqlParameters[ 8].Value.ToString());
                    response.MusicPage = musicPage;
                    musicPage.CurrentPageIndex = pageNo;
                }
            }

 

转载于:https://www.cnblogs.com/flyingzqx/archive/2012/01/04/2312245.html