Overview

This post is directed towards .Net developers interested in setting up a server-side processed DataTable.  I’m assuming that if you’re reading this post, you’re already familiar with DataTables, a very powerful jQuery plugin that allows a developer to create responsive, full-featured data grids with pagination, filtering, etc.

The problem with most implementations of DataTables written by .NET developers is that they’re typically written using some sort of repeater.  In Web Forms, you can use an ASP.Net Repeater.  In MVC with Razor, you can use a @foreach loop to display multiple rows.

These implementations work best with limited data sets.  Larger sets of data leads to slow-loading pages, and consequently, end-user dissatisfaction.  If this is the problem that you’ve run into, read on for a solution.

First, a little about myself.  I am a career Microsoft developer who transitioned from Visual Basic 4 to C#.  I have a passing familiarity with jQuery, but I am no expert to say the least.  It was a little intimidating to work with DataTables at first because you must know jQuery to use it, especially to make server -side processing a reality.  It uses Ajax to make calls for data for display.  As well, my implementation needed to be C# specific, as I intended this solution to use Web API.

Thankfully, the road to server-side processing has been paved by many other developers.  C# Web API support?  Not so much.  I also had a very specific implementation in mind that wasn’t met by the samples I was able to discover.

Those are for a different post, however.  In this post, I will cover what is needed to create a bare-bones implementation.

Requirements

For this implementation, I needed to create a way to display paged data that can be sorted and filtered across all applicable records.  In simple terms, if filters or sorting is applied, it needs to be done across all records, not just the records displayed.

The website is a Web Forms implementation written in C#.  The database is a SQL Server 2012 Standard installation. 

The base resultset returned would exceed 2,000 records, which is way beyond the point where a basic .NET repeater implementation would slow to a crawl.  The website using this functionality also contains several existing Datatables.NET implementations that could use a migration to server-side processing.

The solution I needed to create needed to be flexible, reusable, and since the table I was implementing would be hosted on a home page, it needed to be fast and not place undue load on the database server.

With these requirements, I decided to implement a Web API 2.0 project returning JSON, calling a stored procedure.

Database

I’m not going to go into detail regarding how I get the data from my database.  In this implementation, all of the search and sort logic will be performed in the .NET code.  All that you need to provide is a Generic list of a given class.  In a production environment, it may behoove you to limit the resultset, but other than that, the code below should work well.

In this example, my business objects will be returning a Generic List of type SearchItem.  My endpoint, SampleDTSearch, will return JSON to the caller.

My philosophy is that the database is responsible for returning data, and the .NET classes should be responsible for manipulating that data.

Web API

Datatables will post a specific object to your web service. The form posted will match the following class definitions:

    public class DataTableSearchParam
    {
        public int draw { get; set; }

        public int start { get; set; }
        public int length { get; set; }
        
        public DataTableColumnSearch  /*Dictionary<string, string>*/ search { get; set; }
        public List<DataTableColumnOrder/*Dictionary<string, string>*/> order { get; set; }
        public List<DataTableColumn/*Dictionary<string, string>*/> columns { get; set; }

    }
    
    public enum DataTableColumnOrderDirection
    {
        asc, desc
    }

    public class DataTableColumnOrder
    {
        public int column { get; set; }
        public DataTableColumnOrderDirection dir { get; set; }
    }
    public class DataTableColumnSearch
    {
        public string value { get; set; }
        public string regex { get; set; }
    }

    public class DataTableColumn
    {
        public string data { get; set; }
        public string name { get; set; }
        public Boolean searchable { get; set; }
        public Boolean orderable { get; set; }
        public DataTableColumnSearch search { get; set; }
    }
    public class SearchItem
    {
        public int DealerId { get; set; }
        public string DealerName { get; set; }
        public int ColOne { get; set; }
        public int ColTwo { get; set; }
        public int ColThree { get; set; }
        public int ColFour { get; set; }
        public int ColFive { get; set; }
        public int ColSix { get; set; }
    }

Here is the code for the endpoint:

MemoryCache _memCache = MemoryCache.Default;

[HttpPost]
[Route("api/Test/SampleDTSearch")]
public IHttpActionResult SampleDTSearch([FromBody]DataTableSearchParam param)
{

	try
	{
		var connStr = ConfigurationManager.ConnectionStrings["YourConnectionString"].ToString();
		var dealerBO = new DealerBO(connStr);
		var sb = new StringBuilder();
		var sessionKey = $"prefix_{param.SessionKey}";
		var searchVariables = $"d.Active = 1|d.SomeKey = {param.UserId}";

		try
		{
			// in this section, we authenticate access
			var bo = new UserBO(connStr);

			if (!bo.AuthenticateSession(param.SessionKey, ""))
			{
				throw new Exception("Invalid session key");
			}

			List<SearchItem> resultset = new List<SearchItem>();
			
			// attempt to pull data from the cache
			var cache = _memCache.Get(sessionKey);
			
			// if the cache comes back null, retrieve fresh data
			if (cache == null)
			{
				resultset = dealerBO.SearchDTItems(searchVariables);
				// setting cache to expire in 5 minutes
				_memCache.Add(sessionKey, resultset, DateTimeOffset.UtcNow.AddMinutes(5));
			}
			else
			{
				resultset = (List<SearchItem>) cache;
			}

			var retval = resultset.AsQueryable();
			var tcount = retval.Count();
			var fcount = 0;

			int draw = param.draw;
			int pageSize = param.length;
			int skip = param.start;

			// process search box
			var searchValue = param.search.value;
			if (!string.IsNullOrEmpty(searchValue))
			{
				searchValue = searchValue.ToLower();

				retval = retval.Where(x => (x.DealerName != null && x.DealerName.ToLower().Contains(searchValue)));
			}

			fcount = retval.ToList().Count;
			
			// dynamically sort the data based on selected column
			var sortColumn = param.columns[param.order[0].column].name;
			var sortDir = param.order[0].dir;

			if (!string.IsNullOrEmpty(sortColumn))
			{
				var desc = sortDir == DataTableColumnOrderDirection.desc;

				retval = GetSortedData(retval, sortColumn, desc);
			}
			
			// return only the data from the selected starting page, and restrict returning data to the page size
			var retdata = retval.Skip(skip).Take(pageSize).ToList();

			return Ok(new { draw, recordsTotal = tcount, recordsFiltered = fcount, data = retdata });
		}
		catch (NoMatchException)
		{
			return NotFound();
		}
		catch (Exception ex)
		{
			return Content(HttpStatusCode.BadRequest, ex);
		}

	}
	catch (Exception ex)
	{
		return Content(HttpStatusCode.BadRequest, ex);
	}
}

The code attempts to retrieve data from the memory cache at first. If not available, the code will retrieve a fresh copy of the data. I’ve set an absolute expiration of 5 minutes for this data because I do not want the data to get stale.

This snippet of code processes the default Search box in DataTables:

// process search box
var searchValue = param.search.value;
if (!string.IsNullOrEmpty(searchValue))
{
	searchValue = searchValue.ToLower();

	retval = retval.Where(x => (x.DealerName != null && x.DealerName.ToLower().Contains(searchValue)));
}

I’m limiting it to search by DealerName from my resultset. You can use the searchValue to filter by any other properties in your list of objects.

There is a separate function that sorts the resultset. We know what column to sort by based on the array of order columns provided by DataTables. We can choose to sort by more than one column, but this is beyond the scope of this post.

The sort method is listed below:

        IQueryable<SearchItem> GetSortedData(IQueryable<SearchItem> result, String orderby, bool desc)
        {
            switch (orderby.ToLower())
            {
                case "dealername": return result.OrderBy(c => c.DealerName, desc);
                case "colone": return result.OrderBy(c => c.ColOne, desc);
                case "coltwo": return result.OrderBy(c => c.ColTwo, desc);
                case "colthree": return result.OrderBy(c => c.ColThree, desc);
                case "colfour": return result.OrderBy(c => c.ColFour, desc);
                case "colfive": return result.OrderBy(c => c.ColFive, desc);
                case "colsix": return result.OrderBy(c => c.ColSix, desc);
                default: return result.OrderBy(c => c.ColThree, desc).ThenByDescending(c=>c.ColTwo).ThenByDescending(c=>c.ColOne)
                        .ThenByDescending(c=>c.ColSix).ThenByDescending(c=>c.ColFive).ThenByDescending(c=>c.ColFour);
            }
        }

JavaScript and HTML

The JavaScript and HTML used for this example is pretty straightforward. The main difference between a repeater driven implementation and a server-side implementation is the “ajax” property of the DataTable:

script type="text/javascript">
    var table = $('#example1').DataTable({
        "processing": true,
        "serverSide": true,
        "stateSave": true,
        "filter": true,
        "pageLength": 25,
        "columns": [
           { "data": "DealerName", "name": "DealerName", "autoWidth": false, "width": '40%'},
           { "data": "ColOne", "name": "ColOne", "autoWidth": false, "width": '10%' },
           { "data": "ColTwo", "name": "ColTwo", "autoWidth": false, "width": '10%'  },
           { "data": "ColThree", "name": "ColThree", "autoWidth": false, "width": '10%'  },
           { "data": "ColFour", "name": "ColFour", "autoWidth": false, "width": '10%'  },
           { "data": "ColFive", "name": "ColFive", "autoWidth": false, "width": '10%'  },
           { "data": "ColSix", "name": "ColSix", "autoWidth": false, "width": '10%'  },
        ],
        "ajax":
        {
            url: "http://yoururl/api/Test/SampleDTSearch",
            type: "post",
            "datatype": "json",
            "data": function (d) {
                d.SessionKey = "put your user session here";
                d.UserId = "put your user id here";
            }
        },
        "autoWidth": false,
        "order": [[3, "desc"], [2, "desc"], [1, "desc"], [6, "desc"], [5, "desc"], [4, "desc"]],
    }
    );

        table.ajax.reload();
</script>

In that property, you define the URL, method, datatype, and specify the custom properties that you wish to post to the server.

When completed, the final product should look like this:

Sample implementaton

Conclusion

This example, while not exactly simple, is a stripped down implementation of a DataTables.net server-side processed table. While deliberately simplified, the sample code contains all you need to search, sort, and add custom properties to your posted data.

In a future post, I’ll cover how to add custom links to your rows. I’ll also cover how to custom render columns. In addition, I’ll discuss how I replicated an ASP.Net Web Forms grid using DataTables.net.

Leave a Reply

Your email address will not be published. Required fields are marked *