Jquery serer side datatables in asp.net

In this post we will learn how we can implement jquery server side datatables in asp.net mvc application.

We need to add datatables library to our page, we can add references of libray from datatables <a href="https://datatables.net/" target="_blank">website</a>.

We need to create an html table on which we will apply datatable.

<table class="table table-striped table-bordered table-hover table-checkable order-column dataTable" id="tblInventoryItems">

  <thead>

    <tr>

      <th>Item Name</th>

      <th>Item Price</th>

      <th>Item Quantity</th>

      <th></th>

    </tr>

  </thead>

  <tbody></tbody>

</table>

Then we need the code which will apply the datatable to above html table. Below is the code to apply datatable

$("yourtableid").DataTable({

        "proccessing": true,

        "serverSide": true,

        "bDestroy": true,

        "select":true,

        "ajax": {

            url: "Controller/Action",

            type: 'POST'

            

        },

        "language": {

            "search": "",

            "searchPlaceholder": "Search...",

            "infoFiltered": ""

        },

        "columns": columns,

        "lengthMenu": [2,10, 25, 50, 75, 100]

    });

Datatable posts some data in ajax call which is required for paging,sorting and filtering. We can receive that data in our controller. We will create a class with properties in which datatable posts the data. Below is code for the class.

public class DataTablePostModel

    {

        // properties are not capital due to json mapping

        public int draw { get; set; }

        public int start { get; set; }

        public int length { get; set; }

        public List<Column> columns { get; set; }

        public Search search { get; set; }

        public List<Order> order { get; set; }

        public string loggedInUserId { get; set; }

    }

    public class Column

    {

        public string data { get; set; }

        public string name { get; set; }

        public bool searchable { get; set; }

        public bool orderable { get; set; }

        public Search search { get; set; }

    }

    public class Search

    {

        public string value { get; set; }

        public string regex { get; set; }

    }

    public class Order

    {

        public int column { get; set; }

        public string dir { get; set; }

    }

  

  Now we will receive our ajax call in controller like this

  

  public IActionResult LoadInventoryItems(DataTablePostModel model)

  {

   var data = yourDbCallHere(model);

  }

  

  You can get the values of data posted by datatable like this

  

  string searchValue = model.search.value); 

  int start = model.start); 

  int length = model.length); 

  if (model.order != null) 

  {

     string sortColumn =model.columns[model.order[0].column].name); 

     string sortDir = model.order[0].dir.ToLower()); 

  }

  

  You need to pass these values to you datastore, i have used SQl server, so i will show the stored procedue in which all the logic is written.

  

  

  

  CREATE PROCEDURE [dbo].[usp_GetInventoryItemsList]

@pSearchTerm nvarchar(100) = NULL,

@pPageNo int = 1,

@pPageSize int = 20,

@pSortColumn nvarchar(100)=NULL,

@pSortDir nvarchar(4)='ASC',

@pTotalRecords int out

AS

SELECT it.ItemId,it.ItemName,it.ItemPrice,it.ItemQuantity 

INTO #tblInventoryItems 

FROM tblInventoryItems it 

WHERE (ISNULL(@pSearchTerm,'') = '' OR ItemName like '%'+@pSearchTerm+'%' or ItemPrice like '%'+@pSearchTerm+'%')

SET @pTotalRecords = (SELECT Count(1) FROM #tblInventoryItems)

SELECT ItemId,ItemName,ItemPrice,ItemQuantity 

FROM #tblInventoryItems

ORDER BY 

CASE WHEN @pSortColumn='ItemName' AND @pSortDir='Asc' 

THEN ItemName END,

CASE WHEN @pSortColumn='ItemName' AND @pSortDir='Desc' 

THEN ItemName END DESC,

CASE WHEN @pSortColumn='ItemPrice' AND @pSortDir='Asc' 

THEN ItemPrice END,

CASE WHEN @pSortColumn='ItemPrice' AND @pSortDir='Desc' 

THEN ItemPrice END DESC,

Case WHEN @pSortColumn='ItemQuantity' AND @pSortDir='Asc' 

THEN ItemQuantity END,

CASE WHEN @pSortColumn='ItemQuantity' AND @pSortDir='Desc' 

THEN ItemQuantity END DESC

OFFSET @pPageNo ROWS FETCH NEXT @pPageSize ROWS ONLY;

DROP TABLE #tblInventoryItems

  

  

  That's all how you can implement server side datatables in asp.net c#.

  

  If you have anu questions or suggestion please leave your comments.