博客园  :: 首页  :: 新随笔  :: 联系 :: 订阅 订阅  :: 管理

sql分页

Posted on 2010-05-16 20:53  moss_tan_jun  阅读(544)  评论(4编辑  收藏  举报

-- 获取指定页的数据 

Create PROCEDURE page 
@tblName  varchar(255),         -- 表名 
@strGetFields varchar(1000= '*',    -- 需要返回的列  
@fldName varchar(255)='',        -- 排序的字段名 
@PageSize     int = 10,            -- 页尺寸 
@PageIndex    int = 1,             -- 页码 
@doCount    bit = 0,     -- 返回记录总数, 非 0 值则返回 
@OrderType bit = 0,    -- 设置排序类型, 非 0 值则降序 
@strWhere    varchar(1500= ''    -- 查询条件 (注意: 不要加 where) 
AS 
declare @strSQL     varchar(5000)         -- 主语句 
declare @strTmp     varchar(110)          -- 临时变量 
declare @strOrder varchar(400)          -- 排序类型 

if @doCount != 0 
    
begin 
      
if @strWhere !='' 
      
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where '+@strWhere 
      
else 
      
set @strSQL = 'select count(*) as Total from [' + @tblName + ']'
end   
--以上代码的意思是如果@doCount传递过来的不是0,就执行总数统计。以下的所有代码都是@doCount为0的情况 
else 
begin 
if @OrderType != 0 
begin 
      
set @strTmp = '<(select min' 
set @strOrder = ' order by [' + @fldName +'] desc'
--如果@OrderType不是0,就执行降序,这句很重要! 
end 
else 
begin 
      
set @strTmp = '>(select max'
      
set @strOrder = ' order by [' + @fldName +'] asc' 
end 
if @PageIndex = 1 
begin 
      
if @strWhere != ''    
      
set @strSQL = 'select top ' + str(@PageSize+' '+@strGetFields+ '    from [' + @tblName + '] where ' + @strWhere + ' ' + @strOrder 
       
else 
       
set @strSQL = 'select top ' + str(@PageSize+' '+@strGetFields+ '    from ['+ @tblName + ''+ @strOrder 
--如果是第一页就执行以上代码,这样会加快执行速度 
end 
else 
begin 
--以下代码赋予了@strSQL以真正执行的SQL代码 
set @strSQL = 'select top ' + str(@PageSize+' '+@strGetFields+ '    from [' 
      
+ @tblName + '] where [' + @fldName + ']' + @strTmp + '(['+ @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize+ ' ['+ @fldName + '] from [' + @tblName + ']' + @strOrder + ') as tblTmp)'+ @strOrder 

if @strWhere != '' 
      
set @strSQL = 'select top ' + str(@PageSize+' '+@strGetFields+ '    from [' 
          
+ @tblName + '] where [' + @fldName + ']' + @strTmp + '([' 
          
+ @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize+ ' [' 
          
+ @fldName + '] from [' + @tblName + '] where ' + @strWhere + ' ' 
          
+ @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder 
end  
end    

exec (@strSQL

GO 
 


 

private string strConn = ConfigurationSettings.AppSettings["db_11"]; 
public int recordNum; // 记录数 
public int pageIndex; // 目前所在页 
public int pageNum; // 总页数 
public int pageEach = 8// 每页记录数 
private void Page_Load(object sender, System.EventArgs e) 

// 在此处放置用户代码以初始化页面 
pageIndex 
= Convert.ToInt32(Request.QueryString["pageIndex"]); 
// 绑定 
DataSet ds 
= new DataSet(); 
string strSQL = "exec GetData '表','字段','条件','排序字段','升序/降序','" + pageEach + "','" + pageIndex + "','0'"
SqlDataAdapter cmd 
= new SqlDataAdapter(strSQL,strConn); 
cmd.Fill(ds); 
parent.DataSource 
= ds.Tables[0].DefaultView; 
Page.DataBind(); 

// 总页码 
strSQL = "exec GetData '表','','条件','','','','','1'"
BaBaoDB db 
= new BaBaoDB(); 
SqlDataReader reader 
= db.SelectReader(strSQL); 
if(reader.Read()) 

     recordNum 
= Convert.ToInt32(reader[0]); 
     if(recordNum == 0
   { 
   // 无记录的情况 
     pageNum = pageIndex;
     } 
   else 
   { 
     if(recordNum % pageEach == 0
       { 
             pageNum 
= recordNum / pageEach; 
        } 
     else 
        { 
            pageNum 
= recordNum / pageEach + 1
         } 
    } 
  } 
else 

      pageNum 
= pageIndex; 

reader.Close(); 
}

 
 

 ******************************************

 

在ms sql server 中,可以充分利用存储过程进行分页的优化,下面是一个不错的例子,其中充分利用了
set rowcount的功能。存储过程中,可以向@startrowindex传入第N页的页码@maximumrow是每页的记录条数

CREATE PROCEDURE [usp_GetProducts] 

@startRowIndex int,
@maximumRows int
@totalRows int OUTPUT

AS

DECLARE @first_id int@startRow int

SET @startRowIndex =  (@startRowIndex - 1)  * @maximumRows+1

 

SET ROWCOUNT @startRowIndex
SELECT @first_id = ProductID FROM Products ORDER BY ProductID
PRINT @first_id
SET ROWCOUNT @maximumRows
SELECT ProductID, ProductName FROM Products WHERE 
ProductID 
>= @first_id 
ORDER BY ProductID
SET ROWCOUNT 0
-- GEt the total rows 
SELECT @totalRows = COUNT(ProductID) FROM Products
GO
 

 

--分页存储过程(效率高,试过100万的数据,游标分页)
CREATE procedure cursorPage
@sqlstr   nvarchar(4000),   --查询字符串  
  @currentpage   int,   --第N页  
  @pagesize   int   --每页行数  
  as  
 
set   nocount   on  
 
declare   @P1   int,   --P1是游标的id  
    @rowcount   int  
 
exec   sp_cursoropen   @P1   output,@sqlstr,@scrollopt=1,@ccopt=1,@rowcount=@rowcount   output  
 
select   ceiling(1.0*@rowcount/@pagesize)   as   总页数,@rowcount   as   总行数,@currentpage   as   当前页    
 
set   @currentpage=(@currentpage-1)*@pagesize+1  
 
exec   sp_cursorfetch   @P1,16,@currentpage,@pagesize    
 
exec   sp_cursorclose   @P1  
 
set   nocount   off  

GO

--调用:示例exec cursorPage 'select * from dingdan','1','10'