I'm not good at sql, but I have to do paging for jqGrid in my stored procedure which has many records. My asp.net mvc3 controller code as follows,
[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);
}
as well as my stored procedure in sql server 2008 as follows,
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
Now jqGrid is working properly except paging. For ex: If it has 15 records, the first page shows 10 records, remaining is in second page but I cant go to it. Can anyone suggest me, how to do paging for jqgrid?
There are many way to implement
STORED PROCEDURE
which you need. For example you can useROW_NUMBER
construction inside of CTE SQL statement.If you use SQL Server 2012 you can use
OFFSET
andFETCH
afterORDER BY
to implement pagination (see here). In the case the SQL statement will be look very close to the corresponding MySQL or PostgreSQL statements which usesOFFSET
andLIMIT
. By the way Microsoft Entity Framework use Entity SQL Language having close construct (SKIP
andLIMIT
). ProbablyOFFSET
andFETCH
would be preferred way if you use SQL Server 2012 or higher.Because you included SQL Server 2008 tag in your question I would not use new SQL Server 2012 constructs in my answer.
One more good way would be to use
sp_executesql
which allows you to construct an SQL statement as string with parameters. It allows to reuse execution plans which is very important for the best performance. The approach allows you to extend the code of yourSTORED PROCEDURE
to implement server side filtering (searching).I see that need to implement pagination in the SQL statement which contain ID of the returned data (
PersonId
in your case). So I decide to suggest you to use simplified way which useSELECT TOP
in combination withLEFT OUTER JOIN
.You
STORED PROCEDURE
dbo.GetExtraPerson
can have two additional parameters of typeint
:@skip
and@pageSize
. In case of@skip
is equal to0
theSTORED PROCEDURE
can just executeIf
@skip
is not equal to0
then the corresponding SQL statement can be the followingThe full code of
dbo.GetExtraPerson
could be about the followingThe procedure above returns the total number of records additionally and you can use it to assign
totalRecords
value.If you would use above code in combination with
sp_executesql
you can easy modify the code to includeORDER BY
in allSELECT TOP
statements so that the returned values will corresponds to sorting order requested by the user in jqGrid.