DECLARE @pagenum AS INT, @pagesize AS INT SET @pagenum = 2 SET @pagesize = 3 SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY newsid DESC) AS rownum, newsid, topic, ntime, hits FROM news) AS D WHERE rownum BETWEEN (@pagenum-1)*@pagesize+1 AND @pagenum*@pagesize ORDER BY newsid DESC方法1: 适用于 SQL Server 2000/2005 SELECT TOP 页大小 * FROM table1 WHERE id NOT IN ( SELECT TOP 页大小*(页数-1) id FROM table1 ORDER BY id ) ORDER BY id 方法2: 适用于 SQL Server 2000/2005 SELECT TOP 页大小 * FROM table1 WHERE id > ( SELECT ISNULL(MAX(id),0) FROM ( SELECT TOP 页大小*(页数-1) id FROM table1 ORDER BY id ) A ) ORDER BY id 方法3: 适用于 SQL Server 2005 SELECT TOP 页大小 * FROM ( SELECT ROW_NUMBER() OVER (ORDER BY id) AS RowNumber,* FROM table1 ) A WHERE RowNumber > 页大小*(页数-1)
“以下资料来源于网络,作者不详,请注意” 存储过程 ALTER PROCEDURE [dbo].[pagination] @tblName varchar(255), @strGetFields varchar(1000) = '*', @fldName varchar(255)='', @PageSize int , @PageIndex int,@doCount bit , @OrderType bit , @strWhere varchar(1500) = ''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)
配套方法: /// <summary> /// 获取分页数据 /// </summary> /// <param name="tblName">表名</param> /// <param name="strGetFields">获得字段 如果全部则 "*"</param> /// <param name="fldName">排序字段</param> /// <param name="PageSize">页尺寸</param> /// <param name="PageIndex">页码</param> /// <param name="doCount">是否返回记录总数,0为不返回,1为返回</param> /// <param name="OrderType">设置排序类型,0为升序,非0为降序</param> /// <param name="strWhere">where语句 如无则 ""</param> /// <returns></returns> public static DataSet GetCustomersData(string tblName, string strGetFields, string fldName, int PageSize, int PageIndex, int doCount, int OrderType, string strWhere) { SqlParameter[] param = new SqlParameter[] { new SqlParameter("@tblName",SqlDbType.VarChar), new SqlParameter("@strGetFields",SqlDbType.VarChar), new SqlParameter("@fldName",SqlDbType.VarChar), new SqlParameter("@PageSize",SqlDbType.Int), new SqlParameter("@PageIndex",SqlDbType.Int), new SqlParameter("@doCount",SqlDbType.Int), new SqlParameter("@OrderType",SqlDbType.Int), new SqlParameter("@strWhere",SqlDbType.VarChar) }; param[0].Value = tblName; param[1].Value = strGetFields; param[2].Value = fldName; param[3].Value = PageSize; param[4].Value = PageIndex; param[5].Value = doCount; param[6].Value = OrderType; param[7].Value = strWhere; DataSet ds = SQLHelp.Query(SQLHelp.ConnectionString, CommandType.StoredProcedure, "pagination", param); return ds; }
该存储过程几乎可以涵盖所有查询存储过程(目前本人还没有发现不能使用的)参数:分页大小,第几页,需要得到的字段 ,需要查询的表 , 查询条件,排序的字段名,排序的类型,主键名称输入参数即可查询,无论是查询一条记录还是查询多条记录,无论是分页还是不分页,无论是需要查询条件还是不需要查询条件……,都可使用此通用的存储过程!!! 通用性相当好!! /*通用分页存储过程*/ USE HotelManagementSystem GO IF EXISTS(SELECT * FROM sys.objects WHERE NAME='cndoup_GetPageOfRecords') DROP PROCEDURE cndoup_GetPageOfRecords GO --创建存储过程 CREATE PROCEDURE cndoup_GetPageOfRecords @pageSize int = 20, --分页大小 @currentPage int , --第几页 @columns varchar(1000) = '*', --需要得到的字段 @tableName varchar(100), --需要查询的表 @condition varchar(1000) = '', --查询条件, 不用加where关键字 @ascColumn varchar(100) = '', --排序的字段名 (即 order by column asc/desc) @bitOrderType bit = 0, --排序的类型 (0为升序,1为降序) @pkColumn varchar(50) = '' --主键名称AS BEGIN --存储过程开始 DECLARE @strTemp varchar(300) DECLARE @strSql varchar(5000) --该存储过程最后执行的语句 DECLARE @strOrderType varchar(1000) --排序类型语句 (order by column asc或者order by column desc) BEGIN IF @bitOrderType = 1 --降序 BEGIN SET @strOrderType = ' ORDER BY '+@ascColumn+' DESC' SET @strTemp = '<(SELECT min' END ELSE --升序 BEGIN SET @strOrderType = ' ORDER BY '+@ascColumn+' ASC' SET @strTemp = '>(SELECT max' END IF @currentPage = 1 --第一页 BEGIN IF @condition != '' SET @strSql = 'SELECT TOP '+STR(@pageSize)+' '+@columns+' FROM '+@tableName+ ' WHERE '+@condition+@strOrderType ELSE SET @strSql = 'SELECT TOP '+STR(@pageSize)+' '+@columns+' FROM '+@tableName+@strOrderType END ELSE -- 其他页 BEGIN IF @condition !='' SET @strSql = 'SELECT TOP '+STR(@pageSize)+' '+@columns+' FROM '+@tableName+ ' WHERE '+@condition+' AND '+@pkColumn+@strTemp+'('+@pkColumn+')'+' FROM (SELECT TOP '+STR((@currentPage-1)*@pageSize)+ ' '+@pkColumn+' FROM '+@tableName'where'+@condition+@strOrderType+') AS TabTemp)'+@strOrderType ELSE SET @strSql = 'SELECT TOP '+STR(@pageSize)+' '+@columns+' FROM '+@tableName+ ' WHERE '+@pkColumn+@strTemp+'('+@pkColumn+')'+' FROM (SELECT TOP '+STR((@currentPage-1)*@pageSize)+' '+@pkColumn+ ' FROM '+@tableName+@strOrderType+') AS TabTemp)'+@strOrderType END END EXEC (@strSql) END --存储过程结束 --分页得到客房信息列表测试 EXEC cndoup_GetPageOfRecords 20,2,'房间号=RoomNum, 房间状态=(SELECT RoomTypeDes FROM RoomType WHERE RoomTypeID=Room.RoomTypeID), 房间状态=(SELECT RSDec FROM RoomStatus WHERE RoomStatusID=Room.RoomStatusID), 床位数=BedNum, 楼层=Floors, 描述=RoomDes, 备注=RoomRe','Room','','RoomID',0,'RoomID' --根据房间号得到客房信息测试 EXEC cndoup_GetPageOfRecords 1,1,'房间号=RoomNum, 房间状态=(SELECT RoomTypeDes FROM RoomType WHERE RoomTypeID=Room.RoomTypeID), 房间状态=(SELECT RSDec FROM RoomStatus WHERE RoomStatusID=Room.RoomStatusID), BedNum, Floors, RoomDes, RoomRe','Room','RoomNum=304','RoomID',0,'RoomID'
我怎么在c# asp.net中使用您这存储过程
分页控件怎么调用存储过程??我用datalist
例: 后台: protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { Databind(); } } private void Databind() { //定义一个整型变量,获取记录数 int counts = 0; //实例化一个对象,获取图书信息 BllBook book = new BllBook(); DataTable dt = book.GetBooks("", sort, pageindex, pagesize, ref counts); //给Datalist设置数据源,并绑定 DataList1.DataSource = dt; DataList1.DataBind(); //设置分页控件的参数 AspNetPager1.CurrentPageIndex = pageindex; AspNetPager1.PageSize = 10; if (AspNetPager1.CurrentPageIndex == 1) { AspNetPager1.RecordCount = counts; } AspNetPager1.DataBind(); } protected void AspNetPager1_PageChanged(object sender, EventArgs e) { //设置当前页数为分页控件所选页数 pageindex = AspNetPager1.CurrentPageIndex; Databind(); }//GetBooks的方法: public DataTable GetBooks(string where, string Order, int Pageindex, int Pagesize, ref int Pagecount) { StringBuilder sql = new StringBuilder(); sql.Append("select count(1) from Books where 1=1 "); if (where != "") { sql.Append(where); } sql.Append(";"); sql.Append("select * from (select pt=row_number() over(order by " + Order + "),* from books where 1=1 "+where+")a"); sql.Append(" where pt between " + ((Pageindex - 1) * Pagesize + 1) + " and " + Pagesize * Pageindex); DataSet ds= DBHelp.ExcuteDataSet(sql.ToString(), null); DataTable dt = null; if (ds != null && ds.Tables.Count > 0) { Pagecount = int.Parse(ds.Tables[0].Rows[0][0].ToString()); dt = ds.Tables[1]; } return dt; }
SET @pagenum = 2
SET @pagesize = 3
SELECT *
FROM (SELECT ROW_NUMBER() OVER(ORDER BY newsid DESC) AS rownum,
newsid, topic, ntime, hits
FROM news) AS D
WHERE rownum BETWEEN (@pagenum-1)*@pagesize+1 AND @pagenum*@pagesize
ORDER BY newsid DESC方法1:
适用于 SQL Server 2000/2005
SELECT TOP 页大小 *
FROM table1
WHERE id NOT IN
(
SELECT TOP 页大小*(页数-1) id FROM table1 ORDER BY id
)
ORDER BY id
方法2:
适用于 SQL Server 2000/2005
SELECT TOP 页大小 *
FROM table1
WHERE id >
(
SELECT ISNULL(MAX(id),0)
FROM
(
SELECT TOP 页大小*(页数-1) id FROM table1 ORDER BY id
) A
)
ORDER BY id
方法3:
适用于 SQL Server 2005
SELECT TOP 页大小 *
FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY id) AS RowNumber,* FROM table1
) A
WHERE RowNumber > 页大小*(页数-1)
存储过程
ALTER PROCEDURE [dbo].[pagination] @tblName varchar(255), @strGetFields varchar(1000) = '*', @fldName varchar(255)='', @PageSize int , @PageIndex int,@doCount bit , @OrderType bit , @strWhere varchar(1500) = ''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)
/// <summary>
/// 获取分页数据
/// </summary>
/// <param name="tblName">表名</param>
/// <param name="strGetFields">获得字段 如果全部则 "*"</param>
/// <param name="fldName">排序字段</param>
/// <param name="PageSize">页尺寸</param>
/// <param name="PageIndex">页码</param>
/// <param name="doCount">是否返回记录总数,0为不返回,1为返回</param>
/// <param name="OrderType">设置排序类型,0为升序,非0为降序</param>
/// <param name="strWhere">where语句 如无则 ""</param>
/// <returns></returns>
public static DataSet GetCustomersData(string tblName, string strGetFields, string fldName, int PageSize, int PageIndex, int doCount, int OrderType, string strWhere)
{
SqlParameter[] param = new SqlParameter[] {
new SqlParameter("@tblName",SqlDbType.VarChar),
new SqlParameter("@strGetFields",SqlDbType.VarChar),
new SqlParameter("@fldName",SqlDbType.VarChar),
new SqlParameter("@PageSize",SqlDbType.Int),
new SqlParameter("@PageIndex",SqlDbType.Int),
new SqlParameter("@doCount",SqlDbType.Int),
new SqlParameter("@OrderType",SqlDbType.Int),
new SqlParameter("@strWhere",SqlDbType.VarChar)
}; param[0].Value = tblName;
param[1].Value = strGetFields;
param[2].Value = fldName;
param[3].Value = PageSize;
param[4].Value = PageIndex;
param[5].Value = doCount;
param[6].Value = OrderType;
param[7].Value = strWhere; DataSet ds = SQLHelp.Query(SQLHelp.ConnectionString, CommandType.StoredProcedure, "pagination", param);
return ds; }
通用性相当好!!
/*通用分页存储过程*/
USE HotelManagementSystem
GO
IF EXISTS(SELECT * FROM sys.objects WHERE NAME='cndoup_GetPageOfRecords')
DROP PROCEDURE cndoup_GetPageOfRecords
GO
--创建存储过程
CREATE PROCEDURE cndoup_GetPageOfRecords
@pageSize int = 20, --分页大小
@currentPage int , --第几页
@columns varchar(1000) = '*', --需要得到的字段
@tableName varchar(100), --需要查询的表
@condition varchar(1000) = '', --查询条件, 不用加where关键字
@ascColumn varchar(100) = '', --排序的字段名 (即 order by column asc/desc)
@bitOrderType bit = 0, --排序的类型 (0为升序,1为降序)
@pkColumn varchar(50) = '' --主键名称AS
BEGIN --存储过程开始
DECLARE @strTemp varchar(300)
DECLARE @strSql varchar(5000) --该存储过程最后执行的语句
DECLARE @strOrderType varchar(1000) --排序类型语句 (order by column asc或者order by column desc) BEGIN
IF @bitOrderType = 1 --降序
BEGIN
SET @strOrderType = ' ORDER BY '+@ascColumn+' DESC'
SET @strTemp = '<(SELECT min'
END
ELSE --升序
BEGIN
SET @strOrderType = ' ORDER BY '+@ascColumn+' ASC'
SET @strTemp = '>(SELECT max'
END IF @currentPage = 1 --第一页
BEGIN
IF @condition != ''
SET @strSql = 'SELECT TOP '+STR(@pageSize)+' '+@columns+' FROM '+@tableName+
' WHERE '+@condition+@strOrderType
ELSE
SET @strSql = 'SELECT TOP '+STR(@pageSize)+' '+@columns+' FROM '+@tableName+@strOrderType
END ELSE -- 其他页
BEGIN
IF @condition !=''
SET @strSql = 'SELECT TOP '+STR(@pageSize)+' '+@columns+' FROM '+@tableName+
' WHERE '+@condition+' AND '+@pkColumn+@strTemp+'('+@pkColumn+')'+' FROM (SELECT TOP '+STR((@currentPage-1)*@pageSize)+
' '+@pkColumn+' FROM '+@tableName'where'+@condition+@strOrderType+') AS TabTemp)'+@strOrderType
ELSE
SET @strSql = 'SELECT TOP '+STR(@pageSize)+' '+@columns+' FROM '+@tableName+
' WHERE '+@pkColumn+@strTemp+'('+@pkColumn+')'+' FROM (SELECT TOP '+STR((@currentPage-1)*@pageSize)+' '+@pkColumn+
' FROM '+@tableName+@strOrderType+') AS TabTemp)'+@strOrderType
END END
EXEC (@strSql)
END --存储过程结束
--分页得到客房信息列表测试
EXEC cndoup_GetPageOfRecords 20,2,'房间号=RoomNum,
房间状态=(SELECT RoomTypeDes FROM RoomType WHERE RoomTypeID=Room.RoomTypeID),
房间状态=(SELECT RSDec FROM RoomStatus WHERE RoomStatusID=Room.RoomStatusID),
床位数=BedNum,
楼层=Floors,
描述=RoomDes,
备注=RoomRe','Room','','RoomID',0,'RoomID'
--根据房间号得到客房信息测试
EXEC cndoup_GetPageOfRecords 1,1,'房间号=RoomNum,
房间状态=(SELECT RoomTypeDes FROM RoomType WHERE RoomTypeID=Room.RoomTypeID),
房间状态=(SELECT RSDec FROM RoomStatus WHERE RoomStatusID=Room.RoomStatusID),
BedNum,
Floors,
RoomDes,
RoomRe','Room','RoomNum=304','RoomID',0,'RoomID'
分页控件怎么调用存储过程??我用datalist
后台: protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{ Databind();
}
}
private void Databind()
{
//定义一个整型变量,获取记录数
int counts = 0;
//实例化一个对象,获取图书信息
BllBook book = new BllBook();
DataTable dt = book.GetBooks("", sort, pageindex, pagesize, ref counts); //给Datalist设置数据源,并绑定
DataList1.DataSource = dt;
DataList1.DataBind(); //设置分页控件的参数
AspNetPager1.CurrentPageIndex = pageindex;
AspNetPager1.PageSize = 10;
if (AspNetPager1.CurrentPageIndex == 1)
{
AspNetPager1.RecordCount = counts;
}
AspNetPager1.DataBind();
} protected void AspNetPager1_PageChanged(object sender, EventArgs e)
{
//设置当前页数为分页控件所选页数
pageindex = AspNetPager1.CurrentPageIndex;
Databind();
}//GetBooks的方法:
public DataTable GetBooks(string where, string Order, int Pageindex, int Pagesize, ref int Pagecount)
{
StringBuilder sql = new StringBuilder();
sql.Append("select count(1) from Books where 1=1 ");
if (where != "")
{
sql.Append(where);
}
sql.Append(";");
sql.Append("select * from (select pt=row_number() over(order by " + Order + "),* from books where 1=1 "+where+")a");
sql.Append(" where pt between " + ((Pageindex - 1) * Pagesize + 1) + " and " + Pagesize * Pageindex);
DataSet ds= DBHelp.ExcuteDataSet(sql.ToString(), null);
DataTable dt = null;
if (ds != null && ds.Tables.Count > 0)
{
Pagecount = int.Parse(ds.Tables[0].Rows[0][0].ToString());
dt = ds.Tables[1];
}
return dt;
}