SQL Server分页模板

WITH T AS ( SELECT ROW_NUMBER() OVER(ORDER BY AlbumId ) AS row_number, * FROM (SELECT AlbumId,Title,GenreId,ArtistId,Price,AlbumArtUrl FROM albums WHERE 1=1 and GenreId = @GenreId) as A ) SELECT * FROM T WHERE row_number > @StartRowNum AND row_number <= @EndRowNum SELECT COUNT(1) FROM (SELECT AlbumId,Title,GenreId,ArtistId,Price,AlbumArtUrl FROM albums WHERE 1=1 and GenreId = @GenreId) AS B 

这里涉及到多查询结果集的编程处理

IDataReader reader = null; reader = CurrentDatabase.ExecuteReader(dbCommand); using (reader) { objList = GetListFromReader<T>(reader); if (reader.NextResult() && reader.Read()) RecordCount = reader.GetInt32(0); else RecordCount = 0; } return objList;

其中IDataReder这里是Microsoft.Practices.EnterpriseLibrary.Data实现