Support us .Net Basics C# SQL ASP.NET ADO.NET MVC Slides C# Programs Subscribe Buy DVD

jQuery datatables stored procedure for paging sorting and searching

Suggested Videos
Part 106 - jQuery datatables get data from database table
Part 107 - jQuery datatables individual column search
Part 108 - jQuery datatable show hide columns



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.

We want to retrieve data from the following database table tblEmployee 
jQuery datatables stored procedure for paging sorting and searching

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 for 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

jQuery tutorial for beginners

3 comments:

  1. hi
    thank you for tutorial
    can teach update , delete ,... in jquery plugin ?

    ReplyDelete
  2. buen tutorial.
    me podrias pasar la fuente.

    ReplyDelete
  3. very nice, only one piece missing though, if we do type a search term, datatables needs an additional field to show the total unfiltered rows. This will allow it to display "Showing 1 to 10 of 138 entries (filtered from 7,506 total entries)"

    the missing variable here is the 7506 one, I got around this by doing a separate SELECT COUNT(*) without the @Search condition.

    Also out of curiosity, in the final SELECT, wouldnt it be faster if you did SELECT TOP (@DisplayLength) from cte instead of processing the whole CTE set to match the RowNum? In the TOP case,SQL Server will stop processing the rest of the CTE once @DisplayLength records have been found, no?

    ReplyDelete

If you like this website, please share with your friends on facebook and Google+ and recommend us on google using the g+1 button on the top right hand corner.