[HttpPost]
public JsonResult GetExtraPersons(int cId,long pId,JQGridSettings gridSettings)
{
List<ExtraPerson> extraPersons = new List<ExtraPerson>();
ExtraPersonviewmodel extraPersonviewmodel = new ExtraPersonviewmodel();
extraPersonviewmodel.CampId = cId;
extraPersonviewmodel.ReferencePatientId = pId;
extraPersons = ExtraPersonService.GetExtraPersons(extraPersonviewmodel.CampId,extraPersonviewmodel.ReferencePatientId);
int pageIndex = gridSettings.pageIndex;
int pageSize = gridSettings.pageSize;
int totalRecords = extraPersons.Count;
int totalPages = (int)Math.Ceiling((float)totalRecords / (float)pageSize);
int startRow = (pageIndex - 1) * pageSize;
int endRow = startRow + pageSize;
var jsonData = new
{
total = totalPages,page = pageIndex,records = totalRecords,rows =
(
extraPersons.Select(e => new
{
Id = e.ExtraPersonId,FirstName = e.FirstName,LastName = e.LastName,MobilePhone = e.MobileNumber,Email = e.EmailId,PersonalNumber = e.PersonNumber,Diabetes = e.Diabetes,BloodPressure = e.BloodPressure,})
).ToArray()
};
return Json(jsonData);
}
以及我在sql server 2008中的存储过程如下,
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[GetExtraPerson] ( @CampId INT,@ReferencePatientId BIGINT ) AS BEGIN SET NOCOUNT ON SELECT PERS.PersonId,PERS.FirstName,PERS.LastName,PERS.MobileNumber,PERS.EmailId,PERS.PersonNumber,E.ExtraPersonId,E.Diabetes,E.BloodPressure FROM ExtraPerson E INNER JOIN Person PERS ON PERS.PersonId=E.PersonId WHERE E.CampId=@CampId AND ReferencePatientId=@ReferencePatientId AND E.IsDeleted = 0 END
现在jqGrid正常工作,除了分页.例如：如果它有15条记录,第一页显示10条记录,剩下的是第二页,但我不能去.
任何人都可以建议我,如何为jqgrid做分页？
解决方法
如果使用sql Server 2012,则可以在ORDER BY之后使用OFFSET和FETCH来实现分页(请参阅here).在这种情况下,sql语句看起来非常接近使用OFFSET和LIMIT的相应MysqL或Postgresql语句.顺便说一下,Microsoft Entity Framework使用Entity SQL Language具有紧密结构(SKIP和LIMIT).如果您使用sql Server 2012或更高版本,可能OFFSET和FETCH将是首选方式.
因为您在问题中包含sql Server 2008标记,所以我不会在我的答案中使用新的sql Server 2012结构.
另一个好方法是使用sp_executesql,它允许您将sql语句构造为带参数的字符串.它允许重用执行计划,这对于最佳性能非常重要.该方法允许您扩展STORED PROCEDURE的代码以实现服务器端过滤(搜索).
我看到需要在sql语句中实现包含返回数据的ID(在您的情况下为PersonId)的分页.所以我决定建议你使用SELECT TOP结合LEFT OUTER JOIN的简化方法.
您存储的过程dbo.GetExtraPerson可以有两个类型为int的附加参数：@skip和@pageSize.如果@skip等于0,则STORED PROCEDURE可以执行
SELECT TOP (@pageSize) PERS.PersonId,E.BloodPressure
FROM ExtraPerson E
INNER JOIN Person PERS ON PERS.PersonId=E.PersonId
WHERE E.CampId=@CampId AND ReferencePatientId=@ReferencePatientId AND E.IsDeleted=0
如果@skip不等于0,则相应的sql语句可以如下
WITH GetAll AS (
SELECT PERS.PersonId,E.BloodPressure
FROM ExtraPerson E
INNER JOIN Person PERS ON PERS.PersonId=E.PersonId
WHERE E.CampId=@CampId AND ReferencePatientId=@ReferencePatientId AND E.IsDeleted=0
),GetFirst AS (
SELECT TOP (@skip) *
FROM GetAll
ORDER BY Name
),GetNext AS (
SELECT TOP (@pageSize) a.*
FROM GetAll AS a
LEFT OUTER JOIN GetFirst AS f ON f.Id=a.Id
WHERE f.Id IS NULL
ORDER BY Name
)
SELECT * FROM GetNext
dbo.GetExtraPerson的完整代码可以是以下内容
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE dbo.GetExtraPerson
@CampId int,@ReferencePatientId bigint,@skip int,@pageSize int
AS
BEGIN
DECLARE @records int;
SET NOCOUNT ON;
SET @records = (SELECT COUNT(*)
FROM ExtraPerson E
INNER JOIN Person PERS ON PERS.PersonId=E.PersonId
WHERE E.CampId=@CampId
AND ReferencePatientId=@ReferencePatientId
AND E.IsDeleted=0);
IF @skip <= 0
SELECT TOP (@pageSize) PERS.PersonId,E.BloodPressure
FROM ExtraPerson E
INNER JOIN Person PERS ON PERS.PersonId=E.PersonId
WHERE E.CampId=@CampId AND ReferencePatientId=@ReferencePatientId
AND E.IsDeleted=0
ELSE
WITH GetAll AS (
SELECT PERS.PersonId,E.BloodPressure
FROM ExtraPerson E
INNER JOIN Person PERS ON PERS.PersonId=E.PersonId
WHERE E.CampId=@CampId AND ReferencePatientId=@ReferencePatientId
AND E.IsDeleted=0
),GetFirst AS (
SELECT TOP (@skip) *
FROM GetAll
ORDER BY Name
),GetNext AS (
SELECT TOP (@pageSize) a.*
FROM GetAll AS a
LEFT OUTER JOIN GetFirst AS f ON f.Id=a.Id
WHERE f.Id IS NULL
ORDER BY Name
)
SELECT * FROM GetNext;
RETURN @records;
END
GO
上述过程另外返回记录总数,您可以使用它来分配totalRecords值.
如果将上述代码与sp_executesql结合使用,则可以轻松修改代码以在所有SELECT TOP语句中包含ORDER BY,以便返回的值与jqGrid中用户请求的排序顺序相对应.
