• 企业400电话
  • 微网小程序
  • AI电话机器人
  • 电商代运营
  • 全 部 栏 目

    企业400电话 网络优化推广 AI电话机器人 呼叫中心 网站建设 商标✡知产 微网小程序 电商运营 彩铃•短信 增值拓展业务
    asp.net中如何调用sql存储过程实现分页

    首先看下面的代码创建存储过程

    1、创建存储过程,语句如下:

     CREATE PROC P_viewPage
     @TableName VARCHAR(200), --表名
     @FieldList VARCHAR(2000), --显示列名,如果是全部字段则为*
     @PrimaryKey VARCHAR(100), --单一主键或唯一值键
     @Where VARCHAR(2000), --查询条件 不含'where'字符,如id>10 and len(userid)>9
     @Order VARCHAR(1000), --排序 不含'order by'字符,如id asc,userid desc,必须指定asc或desc     
                   --注意当@SortType=3时生效,记住一定要在最后加上主键,否则会让你比较郁闷
     @SortType INT,   --排序规则 1:正序asc 2:倒序desc 3:多列排序方法
     @RecorderCount INT,  --记录总数 0:会返回总记录
     @PageSize INT,   --每页输出的记录数
     @PageIndex INT,   --当前页数
     @TotalCount INT OUTPUT,  --记返回总记录
     @TotalPageCount INT OUTPUT  --返回总页数
    AS
     SET NOCOUNT ON
    
     IF ISNULL(@TotalCount,'') = '' SET @TotalCount = 0
     SET @Order = RTRIM(LTRIM(@Order))
     SET @PrimaryKey = RTRIM(LTRIM(@PrimaryKey))
     SET @FieldList = REPLACE(RTRIM(LTRIM(@FieldList)),' ','')
    
     WHILE CHARINDEX(', ',@Order) > 0 OR CHARINDEX(' ,',@Order) > 0
     BEGIN
      SET @Order = REPLACE(@Order,', ',',')
      SET @Order = REPLACE(@Order,' ,',',') 
     END
    
     IF ISNULL(@TableName,'') = '' OR ISNULL(@FieldList,'') = '' 
      OR ISNULL(@PrimaryKey,'') = ''
      OR @SortType  1 OR @SortType >3
      OR @RecorderCount  0 OR @PageSize  0 OR @PageIndex  0 
     BEGIN 
      PRINT('ERR_00')  
      RETURN
     END 
    
     IF @SortType = 3
     BEGIN
      IF (UPPER(RIGHT(@Order,4))!=' ASC' AND UPPER(RIGHT(@Order,5))!=' DESC')
      BEGIN PRINT('ERR_02') RETURN END
     END
    
     DECLARE @new_where1 VARCHAR(1000)
     DECLARE @new_where2 VARCHAR(1000)
     DECLARE @new_order1 VARCHAR(1000)  
     DECLARE @new_order2 VARCHAR(1000)
     DECLARE @new_order3 VARCHAR(1000)
     DECLARE @Sql VARCHAR(8000)
     DECLARE @SqlCount NVARCHAR(4000)
    
     IF ISNULL(@where,'') = ''
      BEGIN
       SET @new_where1 = ' '
       SET @new_where2 = ' WHERE '
      END
     ELSE
      BEGIN
       SET @new_where1 = ' WHERE ' + @where 
       SET @new_where2 = ' WHERE ' + @where + ' AND '
      END
    
     IF ISNULL(@order,'') = '' OR @SortType = 1 OR @SortType = 2 
      BEGIN
       IF @SortType = 1 
       BEGIN 
        SET @new_order1 = ' ORDER BY ' + @PrimaryKey + ' ASC'
        SET @new_order2 = ' ORDER BY ' + @PrimaryKey + ' DESC'
       END
       IF @SortType = 2 
       BEGIN 
        SET @new_order1 = ' ORDER BY ' + @PrimaryKey + ' DESC'
        SET @new_order2 = ' ORDER BY ' + @PrimaryKey + ' ASC'
       END
      END
     ELSE
      BEGIN
       SET @new_order1 = ' ORDER BY ' + @Order
      END
    
     IF @SortType = 3 AND CHARINDEX(','+@PrimaryKey+' ',','+@Order)>0
      BEGIN
       SET @new_order1 = ' ORDER BY ' + @Order
       SET @new_order2 = @Order + ','  
       SET @new_order2 = REPLACE(REPLACE(@new_order2,'ASC,','{ASC},'),'DESC,','{DESC},')  
       SET @new_order2 = REPLACE(REPLACE(@new_order2,'{ASC},','DESC,'),'{DESC},','ASC,')
       SET @new_order2 = ' ORDER BY ' + SUBSTRING(@new_order2,1,LEN(@new_order2)-1)  
       IF @FieldList > '*'
        BEGIN  
         SET @new_order3 = REPLACE(REPLACE(@Order + ',','ASC,',','),'DESC,',',')     
         SET @FieldList = ',' + @FieldList   
         WHILE CHARINDEX(',',@new_order3)>0
         BEGIN
          IF CHARINDEX(SUBSTRING(','+@new_order3,1,CHARINDEX(',',@new_order3)),','+@FieldList+',')>0
          BEGIN 
          SET @FieldList = 
           @FieldList + ',' + SUBSTRING(@new_order3,1,CHARINDEX(',',@new_order3))   
          END
          SET @new_order3 = 
          SUBSTRING(@new_order3,CHARINDEX(',',@new_order3)+1,LEN(@new_order3))
         END
         SET @FieldList = SUBSTRING(@FieldList,2,LEN(@FieldList))   
        END  
      END
    
     SET @SqlCount = 'SELECT @TotalCount=COUNT(*),@TotalPageCount=CEILING((COUNT(*)+0.0)/'
         + CAST(@PageSize AS VARCHAR)+') FROM ' + @TableName + @new_where1
     IF @RecorderCount = 0
      BEGIN
        EXEC SP_EXECUTESQL @SqlCount,N'@TotalCount INT OUTPUT,@TotalPageCount INT OUTPUT',
             @TotalCount OUTPUT,@TotalPageCount OUTPUT
      END
     ELSE
      BEGIN
        SELECT @TotalCount = @RecorderCount  
      END
    
     IF @PageIndex > CEILING((@TotalCount+0.0)/@PageSize)
      BEGIN
       SET @PageIndex = CEILING((@TotalCount+0.0)/@PageSize)
      END
    
     IF @PageIndex = 1 OR @PageIndex >= CEILING((@TotalCount+0.0)/@PageSize)
      BEGIN
       IF @PageIndex = 1 --返回第一页数据
        BEGIN
         SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ' 
             + @TableName + @new_where1 + @new_order1
        END
       IF @PageIndex >= CEILING((@TotalCount+0.0)/@PageSize) --返回最后一页数据
        BEGIN
         SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM (' 
             + 'SELECT TOP ' + STR(ABS(@PageSize*@PageIndex-@TotalCount-@PageSize)) 
             + ' ' + @FieldList + ' FROM '
             + @TableName + @new_where1 + @new_order2 + ' ) AS TMP '
             + @new_order1   
        END 
      END 
     ELSE
      BEGIN
       IF @SortType = 1 --仅主键正序排序
        BEGIN
         IF @PageIndex = CEILING((@TotalCount+0.0)/@PageSize)/2 --正向检索
          BEGIN
           SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ' 
               + @TableName + @new_where2 + @PrimaryKey + ' > '
               + '(SELECT MAX(' + @PrimaryKey + ') FROM (SELECT TOP '
               + STR(@PageSize*(@PageIndex-1)) + ' ' + @PrimaryKey 
               + ' FROM ' + @TableName
               + @new_where1 + @new_order1 +' ) AS TMP) '+ @new_order1
          END
         ELSE --反向检索
          BEGIN
           SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM (' 
               + 'SELECT TOP ' + STR(@PageSize) + ' ' 
               + @FieldList + ' FROM '
               + @TableName + @new_where2 + @PrimaryKey + '  '
               + '(SELECT MIN(' + @PrimaryKey + ') FROM (SELECT TOP '
               + STR(@TotalCount-@PageSize*@PageIndex) + ' ' + @PrimaryKey 
               + ' FROM ' + @TableName
               + @new_where1 + @new_order2 +' ) AS TMP) '+ @new_order2 
               + ' ) AS TMP ' + @new_order1
          END
        END
       IF @SortType = 2 --仅主键反序排序
        BEGIN
         IF @PageIndex = CEILING((@TotalCount+0.0)/@PageSize)/2 --正向检索
          BEGIN
           SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ' 
               + @TableName + @new_where2 + @PrimaryKey + '  '
               + '(SELECT MIN(' + @PrimaryKey + ') FROM (SELECT TOP '
               + STR(@PageSize*(@PageIndex-1)) + ' ' + @PrimaryKey 
               +' FROM '+ @TableName
               + @new_where1 + @new_order1 + ') AS TMP) '+ @new_order1     
          END 
         ELSE --反向检索
          BEGIN
           SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM (' 
               + 'SELECT TOP ' + STR(@PageSize) + ' ' 
               + @FieldList + ' FROM '
               + @TableName + @new_where2 + @PrimaryKey + ' > '
               + '(SELECT MAX(' + @PrimaryKey + ') FROM (SELECT TOP '
               + STR(@TotalCount-@PageSize*@PageIndex) + ' ' + @PrimaryKey 
               + ' FROM ' + @TableName
               + @new_where1 + @new_order2 +' ) AS TMP) '+ @new_order2 
               + ' ) AS TMP ' + @new_order1
          END 
        END    
       IF @SortType = 3 --多列排序,必须包含主键,且放置最后,否则不处理
        BEGIN
         IF CHARINDEX(',' + @PrimaryKey + ' ',',' + @Order) = 0 
         BEGIN PRINT('ERR_02') RETURN END
         IF @PageIndex = CEILING((@TotalCount+0.0)/@PageSize)/2 --正向检索
          BEGIN
           SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ( '
               + 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ( '
               + ' SELECT TOP ' + STR(@PageSize*@PageIndex) + ' ' + @FieldList
               + ' FROM ' + @TableName + @new_where1 + @new_order1 + ' ) AS TMP '
               + @new_order2 + ' ) AS TMP ' + @new_order1 
          END
         ELSE --反向检索
          BEGIN
           SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ( ' 
               + 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ( '
               + ' SELECT TOP ' + STR(@TotalCount-@PageSize*@PageIndex+@PageSize) + ' ' + @FieldList
               + ' FROM ' + @TableName + @new_where1 + @new_order2 + ' ) AS TMP '
               + @new_order1 + ' ) AS TMP ' + @new_order1
          END
        END
      END
     PRINT(@Sql)
     EXEC(@Sql)
    GO

    2、SQL Server 中调用测试代码

    --执行存储过程
    
    declare @TotalCount int,
        @TotalPageCount int
    exec P_viewPage 'T_Module','*','ModuleID','','',1,0,10,1,@TotalCount output,@TotalPageCount output
    Select @TotalCount,@TotalPageCount;

    asp.net 代码实现:

    #region ===========通用分页存储过程===========
      public static DataSet RunProcedureDS(string connectionString, string storedProcName, IDataParameter[] parameters, string tableName)
      {
     using (SqlConnection connection = new SqlConnection(connectionString))
     {
       DataSet dataSet = new DataSet();
       connection.Open();
       SqlDataAdapter sqlDA = new SqlDataAdapter();
       sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
       sqlDA.Fill(dataSet, tableName);
       connection.Close();
       return dataSet;
     }
      }
      /// summary>
      /// 通用分页存储过程
      /// /summary>
      /// param name="connectionString">/param>
      /// param name="tblName">/param>
      /// param name="strGetFields">/param>
      /// param name="primaryKey">/param>
      /// param name="strWhere">/param>
      /// param name="strOrder">/param>
      /// param name="sortType">/param>
      /// param name="recordCount">/param>
      /// param name="PageSize">/param>
      /// param name="PageIndex">/param>
      /// param name="totalCount">/param>
      /// param name="totalPageCount">/param>
      /// returns>/returns>
      public static DataSet PageList(string connectionString, string tblName, string strGetFields, string primaryKey, string strWhere, string strOrder, int sortType, int recordCount,
     int PageSize, int PageIndex,ref int totalCount,ref int totalPageCount)
      {
     SqlParameter[] parameters ={ new SqlParameter("@TableName ",SqlDbType.VarChar,200),
      new SqlParameter("@FieldList",SqlDbType.VarChar,2000),
      new SqlParameter("@PrimaryKey",SqlDbType.VarChar,100),
      new SqlParameter("@Where",SqlDbType.VarChar,2000),
      new SqlParameter("@Order",SqlDbType.VarChar,1000),
      new SqlParameter("@SortType",SqlDbType.Int),
      new SqlParameter("@RecorderCount",SqlDbType.Int),
      new SqlParameter("@PageSize",SqlDbType.Int),
      new SqlParameter("@PageIndex",SqlDbType.Int),
      new SqlParameter("@TotalCount",SqlDbType.Int),
      new SqlParameter("@TotalPageCount",SqlDbType.Int)};
    
     parameters[0].Value = tblName;
     parameters[1].Value = strGetFields;
     parameters[2].Value = primaryKey;
     parameters[3].Value = strWhere;
     parameters[4].Value = strOrder;
     parameters[5].Value = sortType;
     parameters[6].Value = recordCount;
     parameters[7].Value = PageSize;
     parameters[8].Value = PageIndex;
     parameters[9].Value = totalCount;
     parameters[9].Direction = ParameterDirection.Output;
     parameters[10].Value = totalPageCount;
     parameters[10].Direction = ParameterDirection.Output;
    
     DataSet ds = RunProcedureDS(connectionString, "P_viewPage", parameters, "PageListTable");
     totalCount = int.Parse(parameters[9].Value.ToString());
     totalPageCount = int.Parse(parameters[10].Value.ToString());
     return ds;
      }
      #endregion
    DataSet ds = SqlHelper.PageList(SqlHelper.LocalSqlServer, "T_User", "*", "UserID", "", "", 1, 0, pageSize, 1, ref totalCount, ref totalPageCount);
    this.RptData.DataSource = ds;
    this.RptData.DataBind();

    以上内容就是本文介绍asp.net中如何调用sql存储过程实现分页的全部内容,希望对大家今后的学习有所帮助,当然方法不止本文所述,欢迎与大家分享好的方案。

    您可能感兴趣的文章:
    • ASP.NET存储过程实现分页效果(三层架构)
    • SQL Server 分页查询通用存储过程(只做分页查询用)
    • 通用SQL存储过程分页以及asp.net后台调用的方法
    上一篇:sql server关键字详解大全(图文)
    下一篇:SQL Server 2005附加数据库时Read-Only错误的解决方案
  • 相关文章
  • 

    © 2016-2020 巨人网络通讯 版权所有

    《增值电信业务经营许可证》 苏ICP备15040257号-8

    asp.net中如何调用sql存储过程实现分页 asp.net,中,如何,调用,sql,