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 use ROW_NUMBER
construction inside of CTE SQL statement.
If you use SQL Server 2012 you can use OFFSET
and FETCH
after ORDER 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 uses OFFSET
and LIMIT
. By the way Microsoft Entity Framework use Entity SQL Language having close construct (SKIP
and LIMIT
). Probably OFFSET
and FETCH
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 your STORED 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 use SELECT TOP
in combination with LEFT OUTER JOIN
.
You STORED PROCEDURE
dbo.GetExtraPerson
can have two additional parameters of type int
: @skip
and @pageSize
. In case of @skip
is equal to 0
the STORED PROCEDURE
can just execute
SELECT TOP (@pageSize) 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
If @skip
is not equal to 0
then the corresponding SQL statement can be the following
WITH GetAll AS (
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
),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
The full code of dbo.GetExtraPerson
could be about the following
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
,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
ELSE
WITH GetAll AS (
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
),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
The 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 include ORDER BY
in all SELECT TOP
statements so that the returned values will corresponds to sorting order requested by the user in jqGrid.