-- 获取指定页的数据 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'