/// <summary> /// 分页获取 /// </summary> /// <param name="p_SqlComm">SQL语句</param> /// <param name="p_OrderID">排序ID</param> /// <param name="p_PageSize">页大小</param> /// <param name="p_PageIndex">页号</param> /// <param name="p_PageCount">查询行数</param> /// <returns>结果集</returns> public static DataTable TablePage(string p_SqlComm, string p_OrderID, int p_PageSize, int p_PageIndex, ref int p_PageCount) { DataTable _Table = new DataTable(); int _SelectCount = TableRowNumb(p_SqlComm); //全部SQL行 int _PageCount = _SelectCount / p_PageSize; //全部多少页 if (_SelectCount % p_PageSize != 0) _PageCount++; p_PageCount = _PageCount; //有余返回最大页码 if (p_PageIndex <= 0) p_PageIndex = 1; //如果p_PageIndex小于1 就返回第1页 int _SystemPageIndex = _SelectCount - (p_PageSize * (p_PageIndex - 1)); // 系统用页码 if (_SystemPageIndex <= 0) return _Table; string _SqlPageCommand = "SELECT TOP " + p_PageSize.ToString() + " * FROM (Select TOP "; _SqlPageCommand += _SystemPageIndex.ToString() + " * FROM(" + p_SqlComm + ")B Order BY " + p_OrderID + " DESC)A ORDER BY " + p_OrderID + " ASC"; return SqlList(_SqlPageCommand); } /// <summary> /// 根据SQL语句获取返回多少行 /// </summary> /// <param name="p_SqlComm">SQL语句</param> /// <returns>返回行数</returns> public static int TableRowNumb(string p_SqlComm) { try { DataTable MyTable = SqlList("SELECT Count(*) FROM (" + p_SqlComm + ") RowNumb"); if (MyTable.Rows.Count == 0 || MyTable.Rows[0][0].ToString().Trim() == "") return 0; return Convert.ToInt32(MyTable.Rows[0][0].ToString()); } catch { return 0; } } public static SqlConnection SqlConn = new SqlConnection("Server=.;Database=Test;UID=sa;PWD=123"); /// <summary> /// 根据SQL语句获取行 /// </summary> /// <param name="SqlComm">SQL语句</param> /// <returns>结果TABLE</returns> public static DataTable SqlList(string p_SqlComm) { DataTable _DateTable = new DataTable(); try { if (SqlConn.State != ConnectionState.Open) SqlConn.Open(); SqlDataAdapter _Command = new SqlDataAdapter(p_SqlComm, SqlConn); _Command.Fill(_DateTable); } catch { return _DateTable; } return _DateTable; } 这个是TOP的分页 当然你也可以用储存过程...
调用存储过程时直接传查询表的名称,要显示的字段名(全部为*),排序字段(必须的,如:id desc),查询条件(如:id=1001,不要加Where,不要条件就为""),每页显示多少条记录,TotalPage是一个Out参数,外面用ref来接,这是返回当前查询表的总记录数,根据这个来算总归有多少页Create Procedure [dbo].[PagingRecord] @TableName varchar(50), --表名 @Fields varchar(5000) = '*', --字段名(全部字段为*) @OrderField varchar(5000), --排序字段(必须!支持多字段) @sqlWhere varchar(5000) = Null,--条件语句(不用加where) @pageSize int, --每页多少条记录 @pageIndex int , --指定当前为第几页 @TotalPage int OUT --返回总页数 as -- declare @pageSize int -- select @pageSize=40 begin print '@PageSize=' print @PageSize Begin Tran --开始事务 Declare @sql nvarchar(4000); Declare @totalRecord int; --计算总记录数
if (@SqlWhere='' or @sqlWhere=NULL) set @sql = 'select @totalRecord = count(*) from ' + @TableName else set @sql = 'select @totalRecord = count(*) from ' + @TableName + ' where ' + @sqlWhere EXEC sp_executesql @sql,N'@totalRecord int OUTPUT',@totalRecord OUTPUT--计算总记录数 --计算总页数
if (@SqlWhere='' or @sqlWhere=NULL) set @sql = 'Select '+ @Fields +' FROM (select ROW_NUMBER() Over(order by ' + @OrderField + ') as rowId,' + @Fields + ' from ' + @TableName else set @sql = 'Select '+ @Fields +' FROM (select ROW_NUMBER() Over(order by ' + @OrderField + ') as rowId,' + @Fields + ' from ' + @TableName + ' where ' + @SqlWhere --处理页数超出范围情况 if @PageIndex<=0 Set @pageIndex = 1 select @TotalPage=CEILING((@totalRecord+0.0)/@PageSize) if @pageIndex>@TotalPage Set @pageIndex = @TotalPage --处理开始点和结束点 Declare @StartRecord int Declare @EndRecord int print @pageIndex set @StartRecord = (@pageIndex-1)*@PageSize + 1 set @EndRecord = @StartRecord + @pageSize - 1 --继续合成sql语句 set @Sql = @Sql + ') as ' + @TableName + ' where rowId between ' + Convert(varchar(50),@StartRecord) + ' and ' + Convert(varchar(50),@EndRecord) print @Sql Exec(@Sql) --------------------------------------------------- If @@Error <> 0 Begin RollBack Tran Return -1 End Else Begin Commit Tran Return @totalRecord ---返回记录总数 End end
上面这个要在sql2005才行的,用的是ROW_NUMBER,效率挺好的
你说是top的分页是不只能分一页啊还是???
这个存储过程还可以我常用 CREATE PROCEDURE sp_page @strTable varchar(50), --表名 @strColumn varchar(50), --按该列来进行分页 @intColType int, --@strColumn列的类型,0-数字类型,1-字符类型,2-日期时间类型 @intOrder int, --排序,0-顺序,1-倒序 @strColumnlist varchar(800), --要查询出的字段列表,*表示全部字段 @intPageSize int, --每页记录数 @intPageNum int, --指定页 @strWhere varchar(800), --查询条件 @intPageCount int OUTPUT --总页数 AS DECLARE @sql nvarchar(4000) --用于构造SQL语句 DECLARE @where1 varchar(800) --构造条件语句 DECLARE @where2 varchar(800) --构造条件语句 IF @strWhere is null or rtrim(@strWhere)='' -- 为了避免SQL关键字与字段、表名等连在一起,首先为传入的变量添加空格 BEGIN --没有查询条件 SET @where1=' WHERE ' SET @where2=' ' END ELSE BEGIN --有查询条件 SET @where1=' WHERE ('+@strWhere+') AND ' SET @where2=' WHERE ('+@strWhere+') ' END set @strColumn = ' ' + @strColumn + ' ' set @strColumnlist = ' ' + @strColumnlist + ' ' --构造SQL语句,计算总页数。计算公式为 总页数 = Ceiling ( 记录个数 / 页大小 ) SET @sql='SELECT @intPageCount=CEILING((COUNT(*)+0.0)/' + CAST(@intPageSize AS varchar) + ') FROM ' + @strTable + @where2 --执行SQL语句,计算总页数,并将其放入@intPageCount变量中 EXEC sp_executesql @sql,N'@intPageCount int OUTPUT',@intPageCount OUTPUT --将总页数放到查询返回记录集的第一个字段前,此语句可省略 --SET @strColumnlist= Cast(@intPageCount as varchar(30)) + ' as PageCount,' + @strColumnlist IF @intOrder=0 --构造升序的SQL SET @sql='SELECT TOP '+ CAST(@intPageSize AS varchar) + @strColumnlist + ' FROM ' + @strTable + @where1 + @strColumn + '>(SELECT MAX('+@strColumn+') '+ ' FROM (SELECT TOP '+ CAST(@intPageSize*(@intPageNum - 1) AS varchar) + @strColumn + ' FROM '+ @strTable+@where2+' ORDER BY '+@strColumn+') t) ORDER BY '+ @strColumn +'asc ' ELSE --构造降序的SQL SET @sql='SELECT TOP '+ CAST(@intPageSize AS varchar) + @strColumnlist+ ' FROM '+ @strTable + @where1 + @strColumn + '<(SELECT MIN('+@strColumn+') '+ ' FROM (SELECT TOP '+ CAST(@intPageSize*(@intPageNum - 1) AS varchar) + @strColumn + ' FROM '+ @strTable+@where2+' ORDER BY '+@strColumn+' DESC) t) ORDER BY '+ @strColumn + ' DESC' IF @intPageNum=1--第一页 SET @sql='SELECT TOP '+CAST(@intPageSize AS varchar) + @strColumnlist + ' FROM '+@strTable+ @where2+' ORDER BY '+@strColumn+CASE @intOrder WHEN 0 THEN '' ELSE ' DESC' END --PRINT @sql EXEC(@sql) GO
/// <summary>
/// 分页获取
/// </summary>
/// <param name="p_SqlComm">SQL语句</param>
/// <param name="p_OrderID">排序ID</param>
/// <param name="p_PageSize">页大小</param>
/// <param name="p_PageIndex">页号</param>
/// <param name="p_PageCount">查询行数</param>
/// <returns>结果集</returns>
public static DataTable TablePage(string p_SqlComm, string p_OrderID, int p_PageSize, int p_PageIndex, ref int p_PageCount)
{
DataTable _Table = new DataTable();
int _SelectCount = TableRowNumb(p_SqlComm); //全部SQL行
int _PageCount = _SelectCount / p_PageSize; //全部多少页
if (_SelectCount % p_PageSize != 0) _PageCount++;
p_PageCount = _PageCount; //有余返回最大页码
if (p_PageIndex <= 0) p_PageIndex = 1; //如果p_PageIndex小于1 就返回第1页 int _SystemPageIndex = _SelectCount - (p_PageSize * (p_PageIndex - 1)); // 系统用页码
if (_SystemPageIndex <= 0) return _Table; string _SqlPageCommand = "SELECT TOP " + p_PageSize.ToString() + " * FROM (Select TOP ";
_SqlPageCommand += _SystemPageIndex.ToString() + " * FROM(" + p_SqlComm + ")B Order BY " + p_OrderID + " DESC)A ORDER BY " + p_OrderID + " ASC"; return SqlList(_SqlPageCommand);
} /// <summary>
/// 根据SQL语句获取返回多少行
/// </summary>
/// <param name="p_SqlComm">SQL语句</param>
/// <returns>返回行数</returns>
public static int TableRowNumb(string p_SqlComm)
{
try
{
DataTable MyTable = SqlList("SELECT Count(*) FROM (" + p_SqlComm + ") RowNumb");
if (MyTable.Rows.Count == 0 || MyTable.Rows[0][0].ToString().Trim() == "") return 0;
return Convert.ToInt32(MyTable.Rows[0][0].ToString());
}
catch
{
return 0;
}
} public static SqlConnection SqlConn = new SqlConnection("Server=.;Database=Test;UID=sa;PWD=123"); /// <summary>
/// 根据SQL语句获取行
/// </summary>
/// <param name="SqlComm">SQL语句</param>
/// <returns>结果TABLE</returns>
public static DataTable SqlList(string p_SqlComm)
{
DataTable _DateTable = new DataTable();
try
{
if (SqlConn.State != ConnectionState.Open) SqlConn.Open();
SqlDataAdapter _Command = new SqlDataAdapter(p_SqlComm, SqlConn);
_Command.Fill(_DateTable);
}
catch
{
return _DateTable;
}
return _DateTable;
}
这个是TOP的分页 当然你也可以用储存过程...
@TableName varchar(50), --表名
@Fields varchar(5000) = '*', --字段名(全部字段为*)
@OrderField varchar(5000), --排序字段(必须!支持多字段)
@sqlWhere varchar(5000) = Null,--条件语句(不用加where)
@pageSize int, --每页多少条记录
@pageIndex int , --指定当前为第几页
@TotalPage int OUT --返回总页数
as
-- declare @pageSize int
-- select @pageSize=40
begin
print '@PageSize='
print @PageSize
Begin Tran --开始事务 Declare @sql nvarchar(4000);
Declare @totalRecord int; --计算总记录数
if (@SqlWhere='' or @sqlWhere=NULL)
set @sql = 'select @totalRecord = count(*) from ' + @TableName
else
set @sql = 'select @totalRecord = count(*) from ' + @TableName + ' where ' + @sqlWhere EXEC sp_executesql @sql,N'@totalRecord int OUTPUT',@totalRecord OUTPUT--计算总记录数 --计算总页数
if (@SqlWhere='' or @sqlWhere=NULL)
set @sql = 'Select '+ @Fields +' FROM (select ROW_NUMBER() Over(order by ' + @OrderField + ') as rowId,' + @Fields + ' from ' + @TableName
else
set @sql = 'Select '+ @Fields +' FROM (select ROW_NUMBER() Over(order by ' + @OrderField + ') as rowId,' + @Fields + ' from ' + @TableName + ' where ' + @SqlWhere --处理页数超出范围情况
if @PageIndex<=0
Set @pageIndex = 1 select @TotalPage=CEILING((@totalRecord+0.0)/@PageSize)
if @pageIndex>@TotalPage
Set @pageIndex = @TotalPage --处理开始点和结束点
Declare @StartRecord int
Declare @EndRecord int
print @pageIndex
set @StartRecord = (@pageIndex-1)*@PageSize + 1 set @EndRecord = @StartRecord + @pageSize - 1
--继续合成sql语句
set @Sql = @Sql + ') as ' + @TableName + ' where rowId between ' + Convert(varchar(50),@StartRecord) + ' and ' + Convert(varchar(50),@EndRecord)
print @Sql
Exec(@Sql)
---------------------------------------------------
If @@Error <> 0
Begin
RollBack Tran
Return -1
End
Else
Begin
Commit Tran
Return @totalRecord ---返回记录总数
End
end
CREATE PROCEDURE sp_page @strTable varchar(50), --表名
@strColumn varchar(50), --按该列来进行分页
@intColType int, --@strColumn列的类型,0-数字类型,1-字符类型,2-日期时间类型
@intOrder int, --排序,0-顺序,1-倒序
@strColumnlist varchar(800), --要查询出的字段列表,*表示全部字段
@intPageSize int, --每页记录数
@intPageNum int, --指定页
@strWhere varchar(800), --查询条件
@intPageCount int OUTPUT --总页数
AS
DECLARE @sql nvarchar(4000) --用于构造SQL语句
DECLARE @where1 varchar(800) --构造条件语句
DECLARE @where2 varchar(800) --构造条件语句
IF @strWhere is null or rtrim(@strWhere)=''
-- 为了避免SQL关键字与字段、表名等连在一起,首先为传入的变量添加空格
BEGIN --没有查询条件
SET @where1=' WHERE '
SET @where2=' '
END
ELSE
BEGIN --有查询条件
SET @where1=' WHERE ('+@strWhere+') AND '
SET @where2=' WHERE ('+@strWhere+') '
END
set @strColumn = ' ' + @strColumn + ' '
set @strColumnlist = ' ' + @strColumnlist + ' '
--构造SQL语句,计算总页数。计算公式为 总页数 = Ceiling ( 记录个数 / 页大小 ) SET @sql='SELECT @intPageCount=CEILING((COUNT(*)+0.0)/'
+ CAST(@intPageSize AS varchar)
+ ') FROM ' + @strTable + @where2
--执行SQL语句,计算总页数,并将其放入@intPageCount变量中
EXEC sp_executesql @sql,N'@intPageCount int OUTPUT',@intPageCount OUTPUT
--将总页数放到查询返回记录集的第一个字段前,此语句可省略
--SET @strColumnlist= Cast(@intPageCount as varchar(30)) + ' as PageCount,' + @strColumnlist IF @intOrder=0 --构造升序的SQL
SET @sql='SELECT TOP '+ CAST(@intPageSize AS varchar) +
@strColumnlist +
' FROM ' + @strTable + @where1 +
@strColumn + '>(SELECT MAX('+@strColumn+') '+
' FROM (SELECT TOP '+ CAST(@intPageSize*(@intPageNum - 1) AS varchar) +
@strColumn + ' FROM '+ @strTable+@where2+' ORDER BY '+@strColumn+') t) ORDER BY '+ @strColumn +'asc '
ELSE --构造降序的SQL
SET @sql='SELECT TOP '+ CAST(@intPageSize AS varchar) +
@strColumnlist+
' FROM '+ @strTable + @where1 +
@strColumn + '<(SELECT MIN('+@strColumn+') '+
' FROM (SELECT TOP '+ CAST(@intPageSize*(@intPageNum - 1) AS varchar) +
@strColumn + ' FROM '+ @strTable+@where2+' ORDER BY '+@strColumn+' DESC) t) ORDER BY '+
@strColumn + ' DESC'
IF @intPageNum=1--第一页
SET @sql='SELECT TOP '+CAST(@intPageSize AS varchar) + @strColumnlist + ' FROM '+@strTable+
@where2+' ORDER BY '+@strColumn+CASE @intOrder WHEN 0 THEN '' ELSE ' DESC'
END
--PRINT @sql
EXEC(@sql)
GO
我这里用2000的啊???