此脚本仅获得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.

查看:89
本文介绍了此脚本仅获得7条记录。但我在数据库中有15个。我该怎么做才能实现它。如果将pagesize设置为> 7则仅显示7条记录。的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

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设置为&gt; 7则仅显示7条记录。的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆