SQL Server分頁模板

標籤：exec   lse   url   lis   number   lib   分頁   order   結果   

 

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 <= @EndRowNumSELECT 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實現

SQL Server分頁模板