SQL Server分页模板

 

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实现