asp.net中常用到的SQL分页存储过程

 

--drop procedure p_page
--go

create procedure p_page

(
@Tables     varchar(1000), --表名如testtable
@PrimaryKey     varchar(100),--表的主键,必须唯一性
@Sort     varchar(200)     =     NULL,--排序字段如f_Name asc或f_name desc(注意只能有一个排序字段)
@CurrentPage     int     =     1,--当前页
@PageSize     int     =     10,---每页大小
@Fields     varchar(1000)     =     '*',--显示的字段列表
@Filter     varchar(1000)     =     NULL,--条件语句,不加where,如 f_id>3
@Group     varchar(1000)     =     NULL,--分组字段
@TotalPage int     output --返回总页数
)

WITH ENCRYPTION ---加密存储

AS



     SET NOCOUNT ON
     Declare @intResult Int    

     Begin Tran

     DECLARE @sql nvarchar(4000)
    
     if @Filter is null or @Filter=''  
         set @Sql = 'select @intResult = count(' + @PrimaryKey + ') from ' +   @Tables
         else
         set @Sql = 'select @intResult = count(' + @PrimaryKey + ') from ' +   @Tables + ' where + ' + @Filter    

     EXEC sp_executesql @sql,N'@intResult int OUTPUT',@intResult OUTPUT--计算总记录数
     select @TotalPage=CEILING((@intResult+0.0)/@PageSize)--计算总页数
    
    
     IF     @Sort     IS     NULL     or     @Sort     =     ''
     SET     @Sort     =     @PrimaryKey

     DECLARE     @SortTable     varchar(100)
     DECLARE     @SortName     varchar(100)
     DECLARE     @strSortColumn     varchar(200)
     DECLARE     @operator     char(2)
     DECLARE     @type     varchar(100)
     DECLARE     @prec     int
    
     IF     CHARINDEX('DESC',@Sort)>0
       BEGIN
         SET     @strSortColumn     =     REPLACE(@Sort,     'DESC',     '')
         SET     @operator     =     '<='
       END
     ELSE
           IF     CHARINDEX('ASC',     @Sort)     >     0
             BEGIN  
               SET     @strSortColumn     =     REPLACE(@Sort,     'ASC',     '')
               SET     @operator     =     '>='
             END
        
         ELSE    
             BEGIN
             SET     @strSortColumn   = @SORT
             SET     @operator     =     '>='
             END
    
     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
    
     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)
    
     IF     @CurrentPage     <     1
     SET     @CurrentPage     =     1
    
     SET     @strPageSize     =     CAST(@PageSize     AS     varchar(50))
     SET     @strStartRow     =     CAST(((@CurrentPage     -     1)*@PageSize     +     1)     AS     varchar(50))
    
     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     =     ''
    
     set @sql = '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     +     '
     '
    
     exec(@sql)
     --print @sql

-------------------------------------------------------------------------------------------------

     If @@Error <> 0
         Begin
             RollBack Tran
             Return -1
         End
     Else
         Begin
             Commit Tran
             Return @intResult ---返回记录总数
         End

GO
---------------------------------------------------------------------------------------------------------
/// <summary>
     /// 绑定数据
     /// </summary>
     protected void cfBindData(string sqlwhere)
     {

         //分页开始
         SqlParameter[] myParms = new SqlParameter[10];

         myParms[0] = new SqlParameter("@Tables", SqlDbType.VarChar, 50);
         myParms[0].Value = "cf_Product";

         myParms[1] = new SqlParameter("@PrimaryKey", SqlDbType.VarChar, 50);
         myParms[1].Value = "cf_id";

         myParms[2] = new SqlParameter("@Sort", SqlDbType.VarChar, 50);
         myParms[2].Value = "cf_id desc";

         myParms[3] = new SqlParameter("@CurrentPage", SqlDbType.Int, 4);
         myParms[3].Value = Convert.ToInt16(pageIndex);

         myParms[4] = new SqlParameter("@pageSize", SqlDbType.Int, 4);
         myParms[4].Value = pageSize;

         myParms[5] = new SqlParameter("@Fields", SqlDbType.VarChar, 500);
         myParms[5].Value = "* ";

         myParms[6] = new SqlParameter("@Filter", SqlDbType.VarChar, 500);
         myParms[6].Value = sqlwhere;

         myParms[7] = new SqlParameter("@Group", SqlDbType.VarChar, 50);
         myParms[7].Value = "";

         myParms[8] = new SqlParameter("@totalPage", SqlDbType.Int, 4);
         myParms[8].Direction = ParameterDirection.Output;

         myParms[9] = new SqlParameter("@totalRecord", SqlDbType.Int, 4);
         myParms[9].Direction = ParameterDirection.ReturnValue;

         SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["connStr"].ToString());
         conn.Open();
         SqlCommand sc = new SqlCommand("up_page", conn);
         sc.CommandType = CommandType.StoredProcedure;
         foreach (SqlParameter parameter in myParms)
         {
             sc.Parameters.Add(parameter);
         }
         sc.ExecuteNonQuery();

         totalPage = Convert.ToInt16(myParms[8].Value);
         totalRecord = Convert.ToInt16(myParms[9].Value);

         SqlDataReader sdr = sc.ExecuteReader();
         GV1.DataSourceID = null;
         GV1.DataSource = sdr;
         GV1.DataBind();

         sc.Dispose();
         conn.Close();
         conn.Dispose();
         //分页结束
     }