此脚本仅获得7条记录。但我在数据库中有15个。我该怎么做才能实现它。如果将pagesize设置为> 7则仅显示7条记录。 [英] This Script Only Getting 7 Records. But I Have 15 In Database. How Can I Achevie It. if set a pagesize to >7 it shows only 7 records.
问题描述
public List<Model.ReportsDashboard> GetList(string whereCriteria, string orderBy, string direction, int pageSize, int pageNumber)
{
switch (orderBy)
{
case "ReportsDashboardID":
orderBy = "dbo.Reports_Dashboard.id";
break;
case "ReportUrl":
orderBy = "dbo.Reports_Dashboard.ReportUrl";
break;
case "ReportName":
orderBy = "dbo.Reports_Dashboard.ReportName";
break;
default:
orderBy = "dbo.Reports_Dashboard.ReportName";
break;
}
if (String.IsNullOrEmpty(direction))
direction = "ASC";
if (String.IsNullOrEmpty(whereCriteria))
whereCriteria = "dbo.Reports_Dashboard.Active = 1";
string sql = @"
Declare @RowStart int
Declare @RowEnd int
SET @RowStart = @PageSize * @PageNumber + 1;
SET @RowEnd = @RowStart + @PageSize - 1 ;
With Report_Dashboard AS
( SELECT ROW_NUMBER() over (order by " + orderBy + " " + direction + @") as RowNumber,
dbo.Reports_Dashboard.id,
dbo.Reports_Dashboard.ReportName,
dbo.Reports_Dashboard.ReportUrl
FROM dbo.Reports_Dashboard
where " + whereCriteria + @") ,
counts as (select count(*) as TotalRecordCount from Report_Dashboard)
select Report_Dashboard.*,counts.TotalRecordCount
from Report_Dashboard
cross apply counts
Where RowNumber >= @RowStart and RowNumber <= @RowEnd
";
List<SqlParameter> sqlParameters = new List<SqlParameter>();
sqlParameters.Add(new SqlParameter("@PageNumber", pageNumber));
sqlParameters.Add(new SqlParameter("@PageSize", pageSize));
LoadRecordMethod LoadReport = (SqlDataReader dr) =>
{
Model.ReportsDashboard loadreportdashboard = new Model.ReportsDashboard();
LoadField(loadreportdashboard, dr, GetName(() => loadreportdashboard.id));
LoadField(loadreportdashboard, dr, GetName(() => loadreportdashboard.ReportName));
LoadField(loadreportdashboard, dr, GetName(() => loadreportdashboard.ReportUrl));
LoadField(loadreportdashboard, dr, GetName(() => loadreportdashboard.TotalRecordCount));
return loadreportdashboard;
};
List<Model.ReportsDashboard> list = this.ReturnList(new LoadRecordMethod(LoadRecord), sql, sqlParameters);
return list;
}
推荐答案
查看你的sql查询 - 参见
Look at your sql query - see
Where RowNumber >= @RowStart and RowNumber <= @RowEnd
@RowStart
和 @RowEnd
从参数计算 pageSize
AND pageNumber
因此,对于第一页,pageNumber应设置为1,您将从数据库返回前7条记录。对于第二页 pageNumber = 2
,您将检索8到14的记录。
代码清楚按照预期的方式工作 - SQL返回可以在每个页面上显示的记录数,当您处理页面时,它返回下一组记录。
@RowStart
and @RowEnd
are calculated from the parameters pageSize
AND pageNumber
So for the first page pageNumber should be set to 1 and you will return the first 7 records from your database. For the second page pageNumber = 2
and you will retrieve records 8 to 14 inclusive.
The code is clearly working as it was intended to do - the SQL is returning the number of records that can be displayed on each page and as you work through the pages it returns the "next" set of records.
这篇关于此脚本仅获得7条记录。但我在数据库中有15个。我该怎么做才能实现它。如果将pagesize设置为> 7则仅显示7条记录。的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!