jQuery datatables stored procedure for paging sorting and searching

Link for all dot net and sql server video tutorial playlists
www.youtube.co...
Link for slides, code samples and text version of the video
csharp-video-tu...
Healthy diet is very important both for the body and mind. If you like Aarvi Kitchen recipes, please support by sharing, subscribing and liking our KZbin channel. Hope you can help.
/ @aarvikitchen5572
In this video we will discuss implementing a stored procedure that can perform paging sorting and searching. In our upcoming videos we will discuss writing a generic handler that calls this stored procedure. The generic handler will convert the data to JSON format. The JSON formatted data can then be consumed by the jQuery datatables plugin. All the processing, that is, paging, sorting and searching is done on the server side.
If the dataset is small you can retrieve all the data at once and all the processing (i.e paging, searching, sorting) can be done on the client-side. We discussed this in Part 106 for jQuery tutorial.
However, if the dataset is large (i.e if you have millions of records), loading all data at once is not the best thing to do from a performance standpoint. With large dataset, we only want to load the correct set of rows (sorted, paged and filtered data ) that want to display on the page. So all the processing ((i.e paging, searching and sorting) should be done by the server. So in this and our upcoming videos we will discuss performing server side processing.
SQL Script to create the table and populate it with test data
Create table tblEmployees
(
ID int primary key identity,
FirstName nvarchar(50),
LastName nvarchar(50),
Gender nvarchar(20),
JobTitle nvarchar(20)
)
Go
Insert into tblEmployees values('Mark', 'Hastings','Male','Developer')
Insert into tblEmployees values('Maria', 'Nicholas','Female','Developer')
Insert into tblEmployees values('Robert', 'Stephenson','Male','Sr. Developer')
Insert into tblEmployees values('Mary', 'Quant','Female','Sr. Developer')
Insert into tblEmployees values('John', 'Stenson','Male','Sr. Developer')
Insert into tblEmployees values('Gilbert', 'Sullivan','Male','Developer')
Insert into tblEmployees values('Rob', 'Gerald','Male','Sr. Developer')
Insert into tblEmployees values('Ron', 'Simpson','Male','Developer')
Insert into tblEmployees values('Sara', 'Solomon','Female','Sr. Developer')
Insert into tblEmployees values('Rad', 'Wicht','Male','Sr. Developer')
Insert into tblEmployees values('Julian', 'John','Male','Developer')
Insert into tblEmployees values('James', 'Bynes','Male','Sr. Developer')
Insert into tblEmployees values('Mary', 'Ward','Female','Developer')
Insert into tblEmployees values('Michael', 'Niron','Male','Sr. Developer')
SQL Server stored procedure paging sorting and searching
create proc spGetEmployees
@DisplayLength int,
@DisplayStart int,
@SortCol int,
@SortDir nvarchar(10),
@Search nvarchar(255) = NULL
as
begin
Declare @FirstRec int, @LastRec int
Set @FirstRec = @DisplayStart;
Set @LastRec = @DisplayStart + @DisplayLength;
With CTE_Employees as
(
Select ROW_NUMBER() over (order by
case when (@SortCol = 0 and @SortDir='asc')
then Id
end asc,
case when (@SortCol = 0 and @SortDir='desc')
then Id
end desc,
case when (@SortCol = 1 and @SortDir='asc')
then FirstName
end asc,
case when (@SortCol = 1 and @SortDir='desc')
then FirstName
end desc,
case when (@SortCol = 2 and @SortDir='asc')
then LastName
end asc,
case when (@SortCol = 2 and @SortDir='desc')
then LastName
end desc,
case when (@SortCol = 3 and @SortDir='asc')
then Gender
end asc,
case when (@SortCol = 3 and @SortDir='desc')
then Gender
end desc,
case when (@SortCol = 4 and @SortDir='asc')
then JobTitle
end asc,
case when (@SortCol = 4 and @SortDir='desc')
then JobTitle
end desc
)
as RowNum,
COUNT(*) over() as TotalCount,
Id,
FirstName,
LastName,
Gender,
JobTitle
from tblEmployees
where (@Search IS NULL
Or Id like '%' + @Search + '%'
Or FirstName like '%' + @Search + '%'
Or LastName like '%' + @Search + '%'
Or Gender like '%' + @Search + '%'
Or JobTitle like '%' + @Search + '%')
)
Select *
from CTE_Employees
where RowNum ] @FirstRec and RowNum [= @LastRec
end
Finally test the stored procedure