Since we always want to reuse as much code as possible i wrote a set of class to use with DataTables that can be reuse with any table on your database.
You can find all my code at http://code.google.com/p/datatables-asp-mvc/downloads/list with comments. I removed the comments on this post for space.
Before we start
For this project we need the Linq Dynamic library that can be found on the VS 2008 Samples download page.
The library is actually a single file you can include in your project, direct link to the library is here:
C# Dynamic Query Library (included in the \LinqSamples\DynamicQuery directory)
To read more about this library check ScottGu's blog
We can put the Dynamic library in our Models folder
For this example I'm going to use the standard DataTables example schema
I'm using Asp Mvc3 for this example, but it should work on all the previous versions.
First we need to obtain the parameters send by DataTables from the client on our controller, for this we create a model to store the parameters and save it in our Models folder.
For this example i save most of the code in the Models folder inside the MVC Project, but in a real project most of this should be in different libraries.
-------
using System; using System.Collections.Generic; using System.Linq; using System.Web; namespace DataTables.Models { public class DataTablesParam { public int iDisplayStart { get; set; } public int iDisplayLength { get; set; } public int iColumns { get; set; } public string sSearch { get; set; } public bool bEscapeRegex { get; set; } public int iSortingCols { get; set; } public string sEcho { get; set; } public List<bool> bSortable { get; set; } public List<bool> bSearchable { get; set; } public List<string> sSearchColumns { get; set; } public List<int> iSortCol { get; set; } public List<string> sSortDir { get; set; } public List<bool> bEscapeRegexColumns { get; set; } public DataTablesParam() { bSortable = new List<bool>(); bSearchable = new List<bool>(); sSearchColumns = new List<string>(); iSortCol = new List<int>(); sSortDir = new List<string>(); bEscapeRegexColumns = new List<bool>(); } } }
------Now we create our controller and action to handle the Ajax request:
---------using System;using System.Collections.Generic;using System.Linq;using System.Web;using System.Web.Mvc;using DataTables.Models;
namespace DataTables.Controllers
{
public class ajaxController : Controller
{
public ContentResult GetAjax(DataTablesParam dataTableParam)
{
return null; // we still have some work to do before returning anything!
}
}
}
-------Since DataTables send some parameters in a format not recognize by the default Model Binder we have to construct a custom one.
-------using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
namespace DataTables.Models
{
public class DataTablesModelBinding : IModelBinder
{
public object BindModel(ControllerContext controllerContext, ModelBindingContext bindingContext)
{
DataTablesParam obj = new DataTablesParam();
var request = controllerContext.HttpContext.Request.Params;
obj.iDisplayStart = Convert.ToInt32(request["iDisplayStart"]);
obj.iDisplayLength = Convert.ToInt32(request["iDisplayLength"]);
obj.iColumns = Convert.ToInt32(request["iColumns"]);
obj.sSearch = request["sSearch"];
obj.bEscapeRegex = Convert.ToBoolean(request["bEscapeRegex"]);
obj.iSortingCols = Convert.ToInt32(request["iSortingCols"]);
obj.sEcho = request["sEcho"];
for (int i = 0; i < obj.iColumns; i++)
{
obj.bSortable.Add(Convert.ToBoolean(request["bSortable_" + i]));
obj.bSearchable.Add(Convert.ToBoolean(request["bSearchable_" + i]));
obj.sSearchColumns.Add(request["sSearch_" + i]);
obj.bEscapeRegexColumns.Add(Convert.ToBoolean(request["bEscapeRegex_" + i]));
obj.iSortCol.Add(Convert.ToInt32(request["iSortCol_" + i]));
obj.sSortDir.Add(request["sSortDir_" + i]);
}
return obj;
}
}
} 
And add the model binder in global.asax:
------
protected void Application_Start() { AreaRegistration.RegisterAllAreas(); RegisterGlobalFilters(GlobalFilters.Filters); RegisterRoutes(RouteTable.Routes); ModelBinders.Binders.Add(typeof(DataTablesParam), new DataTablesModelBinding()); }
-----Now we create two class to query the databases based on the DataTables parameters. The first class receive a IQueryable with the data you want to apply the DataTables parameters, this class should be created once in the whole project and then call it for each DataTable in the site. To perform the sorting and search query on the database, we need to know the name of the columns we are working on. This is where Linq dynamics kicks in, the column names are passed to the function in a string array, saved in the same order we render then on the site, then use this information to dynamically construct the query on a string. Additional to this, the function receive the type of data each column is storing, so we can threat the columns different on the search function. For example, in this case if the column is a number i use the function StartsWith while if it's a string i use Contains. This can be changed to any behavior you want.
-----using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Linq.Dynamic;
namespace DataTables.Models
{
public enum DataType
{
tInt,
tString,
tnone
}
public class DataTableFilter
{
public IQueryable FilterPagingSortingSearch(DataTablesParam DTParams, IQueryable data, out int totalRecordsDisplay,
string[] columnNames, DataType[] types)
{
if (!String.IsNullOrEmpty(DTParams.sSearch))
{
string searchString = "";
bool first = true;
for (int i = 0; i < DTParams.iColumns; i++)
{
if (DTParams.bSearchable[i])
{
string columnName = columnNames[i];
if (!first)
searchString += " or ";
else
first = false;
if (types[i] == DataType.tInt)
{
searchString += columnName + ".ToString().StartsWith(\"" + DTParams.sSearch + "\")";
}
else
{
searchString += columnName + ".Contains(\"" + DTParams.sSearch + "\")";
}
}
}
data = data.Where(searchString);
}
string sortString = "";
for (int i = 0; i < DTParams.iSortingCols; i++)
{
int columnNumber = DTParams.iSortCol[i];
string columnName = columnNames[columnNumber];
string sortDir = DTParams.sSortDir[i];
if (i != 0)
sortString += ", ";
sortString += columnName + " " + sortDir;
}
totalRecordsDisplay = data.Count();
data = data.OrderBy(sortString);
data = data.Skip(DTParams.iDisplayStart).Take(DTParams.iDisplayLength);
return data;
}
}
}
-----Finally the BLL class that gets called from the controller. This class first query the data you want to filter, and then calls the previous function.
This is the class that should be created for each different table on the site, where we make the query and set the columns we are using.
------using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
namespace DataTables.Models
{
public class AjaxBLL
{
public string ListAjax(DataTablesParam param)
{
string[] columnNames = { "engine", "browser", "platform", "version", "grade" };
DataType[] types = { DataType.tString, DataType.tString, DataType.tString, DataType.tInt, DataType.tString };
DataTables.Models.DataClasses1DataContext db =
new DataTables.Models.DataClasses1DataContext(System.Configuration.ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString);
int totalRecords = db.ajaxes.Count(u => u.id < 100);
var data = db.ajaxes.Where(u=>u.id < 100);
int totalRecordsDisplay;
DataTableFilter filters = new DataTableFilter();
data = filters.FilterPagingSortingSearch(param, data, out totalRecordsDisplay, columnNames, types) as IQueryable<DataTables.Models.ajax>;
var listData = data.ToList();
string jsonResponse;
jsonResponse = "{\"iTotalRecords\":" + totalRecords + ",\"iTotalDisplayRecords\":" + totalRecordsDisplay + ",\"sEcho\":" + param.sEcho +
",\"aaData\":[";
for (int i = 0; i < listData.Count; ++i)
{
var ajx = listData[i];
if (i > 0)
jsonResponse += ",";
jsonResponse += "[\"" + ajx.engine + "\",\"" + ajx.browser + "\",\"" + ajx.platform + "\",\"" + ajx.version + "\",\"" + ajx.grade + "\"]";
}
jsonResponse += "]}";
return jsonResponse;
}
}
}
-----For the last example i use Linq to Sql to query the data, getting the connection string from web.config, inside the configuration tag , but you can use any connection that implements IQueryable.
<connectionStrings>
<add name="MyConnectionString" connectionString="Data Source=127.0.0.1;Initial Catalog=ajaxExample;Persist Security Info=True;User ID=sa;Password=123456;" providerName="System.Data.SqlClient" />
connectionStrings>
