分页存储

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'