asp.net core docker jQuery datatables stored procedure for paging sorting and searching

Link for all dot net and sql server video tutorial playlists
https://www.youtube.com/user/kudvenka...

Link for slides, code samples and text version of the video
http://csharp-video-tutorials.blogspo...

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

asp.net core docker jQuery datatables stored procedure for paging sorting and searching
172 Likes	172 Dislikes
47,196 views views	524K followers
Education	Upload TimePublished on 5 Aug 2015

Related keywords

wcf vs web api,ado.net core,sql server management studio,webkinz,webadvisor,craigslist nj,wcf one piece,asp.net core 3,ajax players,weber grill parts,webtoon,tutorials by hugo,csharp corner,mvc design pattern,asp.net machine account,servers for minecraft,asp.net core dependency injection,ado.net tutorial,services angular,ajax ontario,asp.net mvc tutorial,asp.net cos'è,csharp assembly,tutorialspoint python,sql join,services briefcase,asp.net core web api,ajax jquery,wccftech,craigslist ny,asp.net zero,csharp foreach,server memes,sql date format,services online,chase,serverless architecture,server resume,wcf c#,server books,tutorialspoint javascript,mvcc connect,ado.net mysql,services technologies gps,ajax deadpool,server jobs,cvs,website,mvc tutorial,costco hours,wcf service application,tutorialspoint spring,serverless,wcf soap,wcf cat,cool math games,wcf test client,services & training hse colombia sas,servicestack,citibank,asp.net core identity,sql union,ajax parking,sql database,asp.net core logging,mvconnect,cunyfirst,asp.net guida,wcf nba,csharp download,wcfi foundation,csharp online,wcf authentication,tutorials near me,http://asp.net,server 2019,chernobyl,ado.net descargar,web of dreams,serverminer,ajax cleaner,ado.net visual studio 2019,webassign,ado.net vs entity framework,ado.net visual studio 2017,csharp list,sql like,asp.net mvc,asp.net core tutorial,sqlite,wcfm,ajax roster,mvc architecture,http://ado.net,asp.net core mvc,ajax soccer,server hosting,wcfi,ajax dish soap,capital one,server rack,tutorialspoint html,csharp interface,craigslist,webroot,tutorialspoint reactjs,ajax request,wcf dragon ball,asp.net core 2.2,tutorialspoint python 3,sql developer,webster,services transmission company sas,sql group by,asp.net core signalr,services manager,mvc framework,ajax paving,mvc near me,tutorialspoint spring boot,mvc map,csharp online compiler,asp.net download,sql between,ado.net c# pdf,services tag dell,wcf 2019 nba,csharp switch,ado.net ventajas y desventajas,csharpstar,wcf tutorial,tutorialspoint,ajax meaning,csharp-video-tutorials.blogspot,tutorials dojo,central park 5,csharp string format,ado.net c#,asp.net core github,server status,ajax fc,server jobs nyc,asp.net core swagger,sql formatter,credit karma,services group,server error in '/' application,services windows,asp.net core 3.0,sql injection,tutorialspoint c#,wcf ria services,calculator,ado.net entity data model,sql insert,tutorialspoint tableau,services google play apk,sqlyog,asp.net core 3 release date,sql server,server job description,tutorials by a,servicenow,webcam,mvc hours,webmd symptom,csharp array,csharp enum,ajax call,asp.net core 2. guida completa per lo sviluppatore,asp.net core,server pro,server status ffxiv,cheap flights,webcrims,asp.net core hosting,services sas,tutorialspoint java,tutorialspoint java compiler,webmd,csharp to json,college football,ado.net dataset,csharp dictionary,cnn,website builder,tutorialspoint sql,asp.net web api,server side rendering,weber grills,sql server 2017,tutorialspoint spark,mvc nj,ado.net oracle,asp.net core download,csharp to vb.net,webster bank,webstaurant,tutorialsystems,ajax post,services fms publish announcement,services.msc no abre,ajax jersey,csharp operator,asp.net core razor pages,server duties,asp.net core environment variables,csharp random,century 21,services consultores,services consulting,mvcsd,services.msc,mvcsc,ado.net pdf,asp.net core configuration,ajax marvel,sql update,asp.net tutorial,mvc medical,ado.net entity data model visual studio 2019,wcfs international curriculum,mvc2,ado.net entity data model visual studio 2017,chase online,wcf api,costco,server jobs near me,webex,sql meaning,sql tutorial,sql commands,ado.net entity framework,ajax tavern,tutorialsteacher,ajax javascript,services desk,ajax greek,csharp tutorial,mvc pattern,ado.net sql server,ado.net connection,asp.net identity,mvcu,asp.net core middleware,wcf web service,mvc webadvisor,web store,mvcc,webmail,mvci,mvctc,