asp.net存储过程分页+GridView控件 几百万数据 超快

存储过程：---亲测275万数据，分页速度N快

create   PROCEDURE  PageList 
@tablename   nvarchar ( 50 ),
@fieldname   nvarchar ( 50 ) = ' * ' ,
@pagesize   int , -- 每页显示记录条数
@currentpage   int , -- 第几页
@orderid   nvarchar ( 50 ), -- 主键排序
@sort   int , -- 排序方式，1表示升序，0表示降序排列
@rowcount   int  output, -- 总记录数，共有几条信息
@pagecount   int  output -- 总页数，共有多少页
AS
declare   @countsql   nvarchar ( 50 )
declare   @sql   nvarchar ( 200 )
declare   @subsql   nvarchar ( 100 ) -- not in子sql语句
declare   @tmpOrderid   nvarchar ( 50 ) 
-- 返回总记录数，并赋值给输出参数@rowcount
set   @countsql = ' select @totalcount=count(*) from  ' + @tablename
exec  sp_executesql  @countsql ,N ' @totalcount int out ' , @rowcount  output

-- 判断字段名是否为空
if   @fieldname   is   null   or   @fieldname = ''
set   @fieldname = '  *  '

-- 判断是否排序及排序方式
if   @orderid   is   null   or   @orderid = ''
set   @tmpOrderid = '   '
else
begin  
if   @sort = 0
set   @tmpOrderid = ' order by  ' + @orderid + '  desc '
else
set   @tmpOrderid = ' order by  ' + @orderid + '  asc '
end

-- 计算页数
if   @rowcount % @pagesize > 0
set   @pagecount   = ( @rowcount / @pagesize ) + 1 ;
else
set   @pagecount = @rowcount / @pagesize ;

-- 分页算法实现
set   @subsql = ' select top ' + str ( @pagesize * ( @currentpage - 1 )) + '   ' + @orderid + '  from  ' + @tablename + '   ' + @tmpOrderid
set   @sql = ' select top ' + str ( @pagesize ) + '   ' + @fieldname + '  from  ' + @tablename + '  where  ' + @orderid + '  not in ( ' + @subsql + ' ) ' + @tmpOrderid
exec ( @sql )

 

 aspx文件：

 

< asp:GridView ID = " GridView1 "  runat = " server " >
         </ asp:GridView >
         </ div >
        第 < asp:Label ID = " lblCurrent "  runat = " server " ></ asp:Label > 页  |  每页 < asp:Label ID = " lblPageSize "
            runat = " server " ></ asp:Label > 条  |  共有 < asp:Label ID = " lblPageTotal "  runat = " server " ></ asp:Label > 页  |   共有 < asp:Label ID = " lblRowsTotal "
            runat = " server " ></ asp:Label > 条信息  |
         < asp:HyperLink ID = " hlFirst "  runat = " server " > 首页 </ asp:HyperLink >
         |
         < asp:HyperLink ID = " hlPrev "  runat = " server " > 上一页 </ asp:HyperLink >
         |
         < asp:HyperLink ID = " hlNext "  runat = " server " > 下一页 </ asp:HyperLink >
         |
         < asp:HyperLink ID = " hlLast "  runat = " server " > 尾页 </ asp:HyperLink >< br  />

 

 

.cs文件：

 

protected   void  Page_Load( object  sender, EventArgs e)
    {
         int  CurrentPage,RowCount,PageCount;
         int  PageSize  =   2 ;
         if  (Request[ " CurrentPage " ]  ==   null || Convert.ToInt32(Request[ " CurrentPage " ]) <   1 )
        {
            CurrentPage = 1 ;
        }
         else
        {
            CurrentPage = Convert.ToInt32(Request[ " CurrentPage " ]);
        }

         // 数据库操作
        SqlConnection sqlconn  =   new  SqlConnection(ConfigurationManager.AppSettings[ " ConnStr " ].ToString());
        SqlCommand cmd  =   new  SqlCommand( " pagelist " , sqlconn);
        cmd.CommandType  =  CommandType.StoredProcedure;
        cmd.Connection  =  sqlconn;
        SqlParameter[] prams  = {
                                new  SqlParameter( " @tablename " ,SqlDbType.NVarChar, 50 ),
                                new  SqlParameter( " @fieldname " ,SqlDbType.NVarChar, 50 ),
                                new  SqlParameter( " @pagesize " ,SqlDbType.Int),
                                new  SqlParameter( " @currentpage " ,SqlDbType.Int),
                                new  SqlParameter( " @orderid " ,SqlDbType.NVarChar, 50 ),
                                new  SqlParameter( " @sort " ,SqlDbType.Int),
                                new  SqlParameter( " @rowcount " ,SqlDbType.Int),
                                new  SqlParameter( " @pagecount " ,SqlDbType.Int)};
        prams[ 0 ].Value  =   " news " ; // 表名
        prams[ 1 ].Value  = " * " ; // 字段名
        prams[ 2 ].Value  = PageSize; // 每页显示条数
        prams[ 3 ].Value  = CurrentPage; // 当前页数
        prams[ 4 ].Value  = " id " ; // 主键
        prams[ 5 ].Value  = 1 ; // 排序方式,0表示降序，1表示升序
        prams[ 6 ].Direction  =  ParameterDirection.Output; // 总记录数
        prams[ 7 ].Direction  =  ParameterDirection.Output; // 总页数
         foreach (SqlParameter pram  in  prams)
        {
            cmd.Parameters.Add(pram);
        }
        sqlconn.Open();
        SqlDataAdapter sda  =   new  SqlDataAdapter();
        DataSet ds  =   new  DataSet();
        sda.SelectCommand  =  cmd;
        sqlconn.Close();
        sda.Fill(ds);
         // 数据库操作结束

        RowCount  =  ( int )cmd.Parameters[ " @rowcount " ].Value;
        PageCount  = ( int )cmd.Parameters[ " @pagecount " ].Value;
        
         if  (CurrentPage > PageCount)
        {
            Response.Redirect( " CutPage.aspx?CurrentPage= " + Convert.ToString(PageCount));
            Response.End();
        }
         this .lblCurrent.Text  =  Convert.ToString(CurrentPage);
         this .lblPageTotal.Text  =  Convert.ToString(PageCount);
         this .lblRowsTotal.Text  =  Convert.ToString(RowCount);
         this .lblPageSize.Text  =  Convert.ToString(PageSize);

         this .hlFirst.NavigateUrl  =   " CutPage.aspx?CurrentPage=1 " ;
         this .hlPrev.NavigateUrl  =   " CutPage.aspx?CurrentPage= "   +  Convert.ToString(CurrentPage - 1 );
         this .hlNext.NavigateUrl  =   " CutPage.aspx?CurrentPage= "   +  Convert.ToString(CurrentPage + 1 );
         this .hlLast.NavigateUrl  =   " CutPage.aspx?CurrentPage= "   +  Convert.ToString(PageCount);
         if  (Convert.ToInt32(CurrentPage)  ==   1 )
        {
             this .hlPrev.Enabled  =   false ;
             this .hlFirst.Enabled  =   false ;
        }
         if  (Convert.ToInt32(CurrentPage)  ==  PageCount)
        {
             this .hlNext.Enabled  =   false ;
             this .hlLast.Enabled  =   false ;
        }

        GridView1.DataSource  =  ds.Tables[ 0 ];
        GridView1.DataBind();
    }

 

 

转载于:https://www.cnblogs.com/hfzsjz/p/3178455.html