viernes, 18 de febrero de 2011

JQuery DataTables plug-in with Asp Mvc and Linq

I recently came across with the great DataTables plug-in for Jquery. Using this plug-in with client data is pretty straightforward, but when you face tables with a lot of data you have to transfer the search, paging and sorting function to the server. There are some good scripts for different server side technology's at Data tables site, but none that uses Asp Mvc, and most of this scripts have to be reimplemented for every table and data combination you have on your site. 


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 { getset; }
        public int iDisplayLength { getset; }
        public int iColumns { getset; }
        public string sSearch { getset; }
        public bool bEscapeRegex { getset; }
        public int iSortingCols { getset; }
        public string sEcho { getset; }
        public List<bool> bSortable { getset; }
        public List<bool> bSearchable { getset; }
        public List<string> sSearchColumns { getset; }
        public List<int> iSortCol { getset; }
        public List<string> sSortDir { getset; }
        public List<bool> bEscapeRegexColumns { getset; }
 
        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>

7 comentarios:

  1. This is great and very helpful solution.
    Thanks for it!

    Maybe you know how to handle searchString if in columns we have null values?

    ResponderEliminar
  2. I'm using the code with tables with null values and i don't have any problem, are you getting any particular error?

    ResponderEliminar
  3. When I excluded all null values from db view site works correctly, but with null values I have following error throw In DynamicQueryable library
    -------
    public static int Count(this IQueryable source)
    {
    if (source == null) throw new ArgumentNullException("source");
    return (int)source.Provider.Execute(
    Expression.Call(
    typeof(Queryable), "Count",
    new Type[] { source.ElementType }, source.Expression));
    }
    -----

    I added && " + columnName + "!= null";
    to handle this, but it is still NullReferenceException.

    I acquire data via Linq to SQL dataclass.
    And I use Repository pattern to get data.

    public IList ListAll()
    {
    var sales = from s in dataContext.Sales
    select s;
    return sales.ToList();
    }

    ...


    ISalesRepository sales = new SalesRepository();
    ...

    var data = sales.ListAll().AsQueryable();

    PS
    But sorting works on columns with null values.
    PSPS
    In your example it is also possible to perform LinqInjection attack :] because it allows special signs like " / etc...

    ResponderEliminar
  4. Can i see then stack trace?

    You should not be able to do SQL injection since Linq handle that. You can read more about this at http://www.devx.com/dotnet/Article/34653/0/page/3

    Are you calling ListAll before FilterPagingSortingSearch? When you do .toList(), a query gets executed and now you have all the data from database in memory, so after that if you call FilterPagingSortingSearch, all the filters are done on the data in memory instead of building a query to execute on the Database.

    I call toList() after all the filters at ListAjax()

    ResponderEliminar
  5. hi David
    Jquery is a good. have you seen show/hide details row ?. Could you post an step by step example to that plug, this is is the link..http://datatables.net/examples/api/row_details.html . it needs more explanation in the code.. open not for .net ..Carlos

    ResponderEliminar
  6. Hi David,

    I've been trying to get started with your ASP.NET DataTables implementation, but for some reason I'm always getting a ParseException on line 989 in the DynamicQueryable.cs class (the ParsePrimaryStart method), due to the fact that the value of token.id is "End".

    I've tried debugging it, but that class is quite the monster, so I was hoping you have seen this issue before and know how to resolve it.

    Thanks

    ResponderEliminar