I've faced this need several times over the years:
So, we've got our dashboard, and we need to show customer/user/whatever data in a table so we can search, sort, filter, all that good stuff...
Sound familiar? Well, there are a few ways we could approach this. To start with, we could put it all together by hand. Creating the table structure and binding each individual row from a data source. But that's loads of work! What we need is some kind of helper or plugin to make our lives easier. Something that will turn our ordinary table into a powerhouse of searchable goodness.
For those that don't know, DataTables is a feature-rich jQuery plugin. It allows you to connect an HTML table to a data source. If you want to check it out, you can download it from http://datatables.net. It's got a stack of useful features and settings that we can take advantage of. In this article, we'll look at making a GET request to a Web API service and displaying the data. In the next article, we'll examine what happens when we make a POST request instead. For now, let's keep things simple.
To start with, we'll knock up a quick MVC project with a View that will display our table. Crank open Visual Studio and create a new Web Application.
We'll need Web API and MVC for this project, but no authentication).
In this example, we'll be displaying a list of customers (name, address, telephone number). Open up the Indexview in the Views/Home folder and add an HTML table with the following structure:
<div class="panel panel-primary">
<div class="panel-heading">
<h3 class="panel-title">Customers</h3>
</div>
<div class="panel-body">
<table id="CustomersTable" class="table table-striped table-bordered table-hover responsive" width="100%">
<thead class="thin-border-bottom">
<tr>
<th>Name</th>
<th>Address</th>
<th>Postcode</th>
<th>Tel</th>
</tr>
</thead>
</table>
</div>
</div>
Now add a link to the DataTables script and stylesheet. We'll use the DataTables CDN for now. Open up the _Layout page in Views/Shared. Pop the link tag into the head of the page and the script tag just above the scripts section.
The head section should look like this:
<head>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width" />
<title>@ViewBag.Title</title>
@Styles.Render("~/Content/css")
@Scripts.Render("~/bundles/modernizr")
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/v/dt/dt-1.10.12/datatables.min.css" />
</head>
Here's how the scripts look:
@Scripts.Render("~/bundles/jquery")
@Scripts.Render("~/bundles/bootstrap")
<script type="text/javascript" src="https://cdn.datatables.net/v/dt/dt-1.10.12/datatables.min.js"></script>
@RenderSection("scripts", false)
DataTables contains a handy jQuery plugin, which we'll use to activate our table. We'll give it a URL, which will be the endpoint for our Web API service (/api/CustomerSearch). We'll come to that shortly. Here's the script we'll need. Pop this scripts section at the bottom of the Index View:
@section scripts {
<script>
(function($) {
var generateCustomerTable = $("#CustomerTable")
.dataTable({
"processing": true,
"serverSide": true,
"ajax": {
"url": "/api/customerSearch"
},
"columns": [
{ "data": "companyName" }, { "data": "address" }, { "data": "postcode" },
{ "data": "telephone" }
],
"language": {
"emptyTable": "There are no customers at present.",
"zeroRecords": "There were no matching customers found."
},
"searching": false,
"ordering": true,
"paging": true
});
})(jQuery);
</script>
}
We need to make sure that the id in our script (in our case CustomerTable) matches the table id in the HTML. Now, when we load our page in a browser, the plugin will make a GET request to our Web API controller. At the moment we'll get a 404 error, but it gives us a chance to have a look at the call the plugin makes. We'll need some of the attributes in the query string on the API side shortly. You can ignore the error popup that appears. It's just DataTables telling us that the API Controller doesn't exist yet. We'll fix that in a bit.
If you're using Chrome, F12 brings up the developer tools. You can then click the Network tab and filter on api to see the call:
Let's have a closer look at that URL:
http://localhost:56835/api/customerSearch?draw=1&columns%5B0%5D%5Bdata%5D=companyName&columns%5B0%5D%5Bname%5D=&columns%5B0%5D%5Bsearchable%5D=true&columns%5B0%5D%5Borderable%5D=true&columns%5B0%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B0%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B1%5D%5Bdata%5D=address&columns%5B1%5D%5Bname%5D=&columns%5B1%5D%5Bsearchable%5D=true&columns%5B1%5D%5Borderable%5D=true&columns%5B1%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B1%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B2%5D%5Bdata%5D=postcode&columns%5B2%5D%5Bname%5D=&columns%5B2%5D%5Bsearchable%5D=true&columns%5B2%5D%5Borderable%5D=true&columns%5B2%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B2%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B3%5D%5Bdata%5D=telephone&columns%5B3%5D%5Bname%5D=&columns%5B3%5D%5Bsearchable%5D=true&columns%5B3%5D%5Borderable%5D=true&columns%5B3%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B3%5D%5Bsearch%5D%5Bregex%5D=false&order%5B0%5D%5Bcolumn%5D=0&order%5B0%5D%5Bdir%5D=asc&start=0&length=10&search%5Bvalue%5D=&search%5Bregex%5D=false&_=1479364345110
Looks scary, doesn't it? Don't worry, it's encoded. It has to be like that before the browser makes the call. Let's do a couple of replacements to make it easier to read. We'll replace %5B with [ and %5D with ]. Here's how it looks now:
http://localhost:56835/api/customerSearch?draw=1&columns[0][data]=companyName&columns[0][name]=&columns[0][searchable]=true&columns[0][orderable]=true&columns[0][search][value]=&columns[0][search][regex]=false&columns[1][data]=address&columns[1][name]=&columns[1][searchable]=true&columns[1][orderable]=true&columns[1][search][value]=&columns[1][search][regex]=false&columns[2][data]=postcode&columns[2][name]=&columns[2][searchable]=true&columns[2][orderable]=true&columns[2][search][value]=&columns[2][search][regex]=false&columns[3][data]=telephone&columns[3][name]=&columns[3][searchable]=true&columns[3][orderable]=true&columns[3][search][value]=&columns[3][search][regex]=false&order[0][column]=0&order[0][dir]=asc&start=0&length=10&search[value]=&search[regex]=false&_=1479364345110
What have we got here? For starters, it contains a bunch of info about the columns in our table (it's an array, hence the [0] etc - we'll use that in the next article). It also contains a reference to the column we're using for ordering. We've got paging and filtering info at the end (Start, Length and Search). We'll look at those in part 3 of this series.
We've also got a Draw counter. This is important, because DataTables keeps a count of the number of times it redraws the table. Every time you interact with the table, DataTables fetches the data again from the server. It also increments that counter. When the call comes back with the data, the Draw parameter needs to be there with that same value or we'll get an error. You can read more about the parameters here: DataTables: Server Side
A couple of things to note about using GET (as opposed to POST) to make this request. It's less secure than POST, because anyone keeping an eye on the data is it goes across the wire could read and/or change it. In our case, they might be able to change the names of some columns or some such. Not a massive issue. A bigger issue is that this query string is 1038 characters. The greatest length supported by browsers is 2048 characters. If we need to show more columns in our table, it'll add a bunch more parameters onto our query string. It doesn't take long before it goes over the max length. To begin with, making a GET request is great for us though. It shows us how DataTables structures the data that it sends across. We'll need that knowledge on the other side. Let's go there now.
Let's add a Web API controller to our project. We'll call it CustomerSearchController. We'll also need a bunch of classes to hold the data that we'll pass back and forth.
Let's start by looking at the data classes:
public class SearchRequest
{
public int Draw { get; set; }
}
public abstract class SearchDetail
{
}
public class CustomerSearchDetail : SearchDetail
{
public string CompanyName { get; set; }
public string Address { get; set; }
public string Postcode { get; set; }
public string Telephone { get; set; }
}
public abstract class SearchResponse<T> where T : SearchDetail
{
public int Draw { get; set; }
public int RecordsTotal { get; set; }
public int RecordsFiltered { get; set; }
public IList<T> Data { get; set; }
}
public class CustomerSearchResponse : SearchResponse<CustomerSearchDetail>
{
}
I've left CustomerData.cs out for the moment. I'll come back to it when I talk about the Controller code. What's going on here? Well, we can see the Draw property on both the SearchRequest and SearchResponse. This keeps DataTables happy. We've got a couple of counters on the SearchResponse. They tell DataTables whether we've filtered any results out through searching. We'll cover that in part 3 of this series. For now, they'll both contain the total customers. We've also using a bit of generics to control which type we use for the Data property on the SearchResponse.
Web API services are RESTful by nature. We're making a GET request, so we'll be adding a method called Get to our controller. In the next article we'll add POST support via, you guessed it, a method called Post. Let's have a look at the Controller code now:
public class CustomerData
{
public IList<CustomerSearchDetail> Data { get; set; }
}
public class CustomerSearchController : ApiController
{
private const string CustomerData = @"
{
""Data"": [
{
""CompanyName"": ""Microsoft"",
""Address"": ""1 Microsoft Way, London"",
""Postcode"": ""N1 1NN"",
""Telephone"": ""020 7100 1000""
},
{
""CompanyName"": ""Nokia"",
""Address"": ""2 Nokia Way, London"",
""Postcode"": ""N2 2NN"",
""Telephone"": ""020 7200 2000""
},
{
""CompanyName"": ""Apple"",
""Address"": ""3 Apple Way, London"",
""Postcode"": ""N3 3NN"",
""Telephone"": ""020 7300 3000""
},
{
""CompanyName"": ""Google"",
""Address"": ""4 Google Way, London"",
""Postcode"": ""N4 4NN"",
""Telephone"": ""020 7400 4000""
},
{
""CompanyName"": ""Samsung"",
""Address"": ""5 Samsung Way, London"",
""Postcode"": ""N5 5NN"",
""Telephone"": ""020 7500 5000""
}
]
}";
public IHttpActionResult Get([FromUri]SearchRequest request)
{
var allCustomers = JsonConvert.DeserializeObject<CustomerData>(CustomerData);
var response = new CustomerSearchResponse
{
Data = allCustomers.Data,
Draw = request.Draw,
RecordsFiltered = allCustomers.Data.Count,
RecordsTotal = allCustomers.Data.Count
};
return Ok(response);
}
}
All we're doing here is turning a json string into a CustomerData object. We're using the Json.NET library for this, via the DeserializeObject method. If this were a production system, we'd be pulling our data from a database of some kind. This is perfect for our example though. The CustomerData object has a structure that matches the structure in our json string. The Json.NET library turns it into something our code can work with.
The FromUri attribute next to the request tells our Controller to try and create a SearchRequest object from the QueryString data in the URL.