October 23 2012

Using MVC 4 Web Api with jQuery DataTables

With the release of ASP.NET MVC 4 and Web Api, creating JSON web services has never been easier, which makes it perfect for populating jQuery DataTables via Ajax.

jQuery DataTables is IMHO the best grid/table plug-in available. It's easy to setup and configure, there's loads of documentation available online, and it's the only plug-in of its kind to be hosted on the Microsoft CDN.

To add DataTables to your project you can either use the NuGet package manager or, as I prefer, take advantage of the CDN. There are plenty of examples and instructions to get you up and running on the DataTables website.

To configure your DataTable to retrieve data from the server via ajax do the following:

$(".datatable").dataTable({
    "bJQueryUI": true,
    "bProcessing": true,
    "bServerSide": true,
    "sAjaxSource": "/api/data/get"
});

Then create an ApiController in your MVC 4 project to service the ajax requests:

public class DataController : ApiController
{
    [HttpGet]
    public dynamic Get(
        int sEcho,
        int iDisplayStart,
        int iDisplayLength,
        string sSearch,
        int iSortCol_0,
        string sSortDir_0)
    {
        // Add code here to get results from database / repository / service

        return new
        {
            sEcho = sEcho,
            iTotalRecords = 100,
            iTotalDisplayRecords = 100,
            aaData = new[]
            {
                new[]
                {
                    "Column 1", 
                    "Column 2", 
                    "Column 3" 
                    // ...
                }
            }
        };
    }
}

This example uses the dynamic type to return dummy data in the format required by the DataTables plug-in. To get it to return real data from your database, replace the comment at the top of the method with your own data access code and the dummy values in the return statement with your results.


Sponsored by