current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite
4

i want to use jQuery DataTables in my ASP.NET MVC 5 Project.

I did actually used the datatable and its working perfectly fine, but problem is i had to manually set the filter and queries, and i have a feeling the way i am trying to implement the datatables in mvc is not quite right. Yes i do get the results but i want to follow and standards plus i want something that i do not have to type the filter and pagination code again and again, i only send parameters to some function or class and i get the result.

This is why i am trying to look for some datatables library with proper documentation.

As i came across this library.

https://github.com/ALMMa/datatables.mvc

but there is no good documentation that i could understand what really is happening in that library or how to use that library?

Yes i tried that library but due to lack of knowledge of c# and asp.net i don't understand how to implement it and i cant find any example related to this library to which i could understand the working of this library..

however i also stumbled across this good documented process.

http://www.codeproject.com/Articles/155422/jQuery-DataTables-and-ASP-NET-MVC-Integration-Part

It did worked great as they provided a very good detailed explanation of how to implement.

I made my Controller Code Something like this.

public ActionResult Index(jQueryDataTableParamModel param = null)
        {
            if (Request.IsAjaxRequest() && param != null)
            {

                var allCategories = _db.Categories.ToList();
                IEnumerable<Category> categories;
                var sortColumnIndex = Convert.ToInt32(Request["iSortCol_0"]);
                var sortDirection = Request["sSortDir_0"]; // asc or desc
                Func<Category,string> orderingFunction = (c => sortColumnIndex==1? c.Name :
                    sortColumnIndex==2? c.SortOrder.ToString(): c.Status.ToString());

                if (!string.IsNullOrEmpty(param.sSearch))
                {
                    if(sortDirection == "desc"){
                    categories = (from category in allCategories
                                  where category.Name.ToLower().Contains(param.sSearch.ToLower())
                                  select category).OrderByDescending(orderingFunction).Skip(param.iDisplayStart).Take(param.iDisplayLength);
                        }
                    else
                    {
                        categories = (from category in allCategories
                                      where category.Name.ToLower().Contains(param.sSearch.ToLower())
                                      select category).OrderBy(orderingFunction).Skip(param.iDisplayStart).Take(param.iDisplayLength);
                    }
                }
                else
                {
                    if (sortDirection == "desc") { 
                    categories = (from category in allCategories
                                  select category).OrderByDescending(orderingFunction).Skip(param.iDisplayStart).Take(param.iDisplayLength);
                    }
                    else{
                        categories = (from category in allCategories
                                      select category).OrderBy(orderingFunction).Skip(param.iDisplayStart).Take(param.iDisplayLength);
                    }
                }
                var actionButtons = "<div class='btn-group'>"+
                    "<button class='btn btn-primary btn-gradient btn-sm' type='button'>"+
                    "<span class='fa fa-pencil'></span>"+
                    "</button>";
                return Json(new
                {
                    sEcho = param.sEcho,
                    iTotalRecords = categories.Count(),
                    iTotalDisplayRecords = categories.Count(),
                    aaData = (from category in categories
                              select new[] { category.CategoryID.ToString(), category.Name, category.SortOrder.ToString(), actionButtons }).ToArray()
                },
                                JsonRequestBehavior.AllowGet);
            }

            return View();
        }

But as you see there is alot of code in just 1 method now if there are more methods for the datatable, i will have to write all the code again and again. so instead is it possible i make some kind of common datatables class or function and call it by providing some parameters and i get the desired result.

I have no experience in ASP.NET MVC5 or C# ,its been some days i am working on MVC 5. So if there are any better ways of achiving the results what i have implemented here in my code please share that too and any suggestion you think you think should be nice for me regarding the datatables.

share|improve this question
    
I dont see why you generate html code in the backend, thats not the point in MVC. If you need an html template that represents some data you want it to, try using Partial Views. –  Martin Solev Nov 2 '14 at 19:34
    
@MartinSolev can i call a partial view in the controller class method?? –  Sizzling Code Nov 2 '14 at 23:22
    
Nah, the point of a controller is the business logic, not client side logic. You can fetch your data and everything and send it to the view, there you can iterate your categories and each of them pass to a partial view which can create your html depending on what you want :) –  Martin Solev Nov 3 '14 at 0:10

1 Answer 1

up vote 2 down vote

I'm using a custom model binder and JsonConverter (for JSON.NET):

Example of a model containing data table request:

public class MyModel
{
   [JsonConverter(typeof(DataTableConverter))]
   public DataTableRequest DataTableRequest { get; set; }
   // other properties 
}

then the action method will look like:

[HttpPost] 
public virtual ActionResult GetDataTableData(MyModel myModel)
{
}

the data table DataTableRequest class. The GetOrderByExpression returns Dynamic Linq expression:

public class DataTableRequest
{
    public int PageIndex { get; private set; }
    public int PageSize { get; private set; }
    public string Search { get; private set; }
    private List<SortBy> SortingColumns { get; set; }

    public int SEcho { get; private set; }

    public DataTableRequest(int pageIndex, int pageSize, string search, List<SortBy> sortingColumns, int sEcho)
    {   
        PageIndex = pageIndex;
        PageSize = pageSize;
        Search = search;
        SortingColumns = sortingColumns;
        SEcho = sEcho;
    }

    public string GetOrderByExpression()
    {
        // could be passed to EntityFramework with DynamicLinq like query.OrderBy(dataTableRequest.GetOrderByExpression())
        var columnDirectionPairs = SortingColumns.Select(c => Thread.CurrentThread.CurrentCulture.TextInfo.ToTitleCase(c.Column.Replace("_", ".")) + " " + c.Direction);
        var orderByExpression = string.Join(", ", columnDirectionPairs);
        return orderByExpression;
    }

    public class SortBy
    {
        public SortBy(string column, string direction)
        {
            Guard.ArgumentNotNullOrEmpty(column, "column");
            Guard.ArgumentNotNullOrEmpty(direction, "direction");

            Column = column;
            Direction = direction;
        }

        public string Column { get; set; }
        public string Direction { get; set; }
    }
}

data table converter (used for serialization to json):

public class DataTableConverter : JsonConverter
{
    public override bool CanConvert(Type objectType)
    {
        return objectType == typeof(DataTableRequest);
    }

    public override object ReadJson(JsonReader reader, Type objectType, object existingValue, JsonSerializer serializer)
    {
        var jArray = JArray.Load(reader);

        var tableValueCollection = jArray.Select(x => new { Name = x["name"].Value<string>(), Value = x["value"].Value<string>() }).ToDictionary(x => x.Name, x => x.Value);

        var numberOfColumns = int.Parse(tableValueCollection["iColumns"], CultureInfo.InvariantCulture);

        var columns = new List<string>();

        for (int i = 0; i < numberOfColumns; i++)
        {
            var queryParamName = string.Format("mDataProp_{0}", i.ToString(CultureInfo.InvariantCulture));
            columns.Add(tableValueCollection[queryParamName]);
        }

        var numberOfSortingColumns = int.Parse(tableValueCollection["iSortingCols"], CultureInfo.InvariantCulture);
        var sortingColumns = new List<DataTableRequest.SortBy>();
        for (int i = 0; i < numberOfSortingColumns; i++)
        {
            var sortColQueryParamName = string.Format("iSortCol_{0}", i.ToString(CultureInfo.InvariantCulture));

            if (tableValueCollection[sortColQueryParamName] != null)
            {
                var sortDirQueryParamName = string.Format("sSortDir_{0}", i.ToString(CultureInfo.InvariantCulture));
                var sortingDirection = tableValueCollection[sortDirQueryParamName];

                var sortingColumnIndex = int.Parse(tableValueCollection[sortColQueryParamName], CultureInfo.InvariantCulture);
                var sortingColumnName = columns[sortingColumnIndex];

                sortingColumns.Add(new DataTableRequest.SortBy(sortingColumnName, sortingDirection));
            }
        }

        var displayStart = int.Parse(tableValueCollection["iDisplayStart"], CultureInfo.InvariantCulture);
        var displayLength = int.Parse(tableValueCollection["iDisplayLength"], CultureInfo.InvariantCulture);
        var pageSize = displayLength;
        var pageIndex = displayStart / displayLength;

        string search = null;

        if (tableValueCollection.ContainsKey("sSearch"))
        {
            search = tableValueCollection["sSearch"];
        }

        var sEcho = int.Parse(tableValueCollection["sEcho"], CultureInfo.InvariantCulture);

        var dataTableRequest = new DataTableRequest(pageIndex, pageSize, search, sortingColumns, sEcho);

        return dataTableRequest;
    }

    public override void WriteJson(JsonWriter writer, object value, JsonSerializer serializer)
    {
        throw new NotImplementedException();
    }
}

DataTableModelBinder used to deserialize DataTableRequest object:

public class DataTableModelBinder : DefaultModelBinder
{
    public override object BindModel(ControllerContext controllerContext, ModelBindingContext bindingContext)
    {
        var request = controllerContext.HttpContext.Request;
        var contentType = request.ContentType;
        if (!contentType.StartsWith("application/json", StringComparison.OrdinalIgnoreCase))
            return (null);

        request.InputStream.Seek(0, SeekOrigin.Begin);
        var bodyText = new StreamReader(request.InputStream).ReadToEnd();

        if (string.IsNullOrEmpty(bodyText)) return (null);

        var jsonObj = JObject.Parse(bodyText);
        var jArray = (JArray)jsonObj["aoData"];

        var tableValueCollection = jArray.Select(x => new { Name = x["name"].Value<string>(), Value = x["value"].Value<string>() }).ToDictionary(x => x.Name, x => x.Value);

        var numberOfColumns = int.Parse(tableValueCollection["iColumns"], CultureInfo.InvariantCulture);

        var columns = new List<string>();

        for (int i = 0; i < numberOfColumns; i++)
        {
            var queryParamName = string.Format("mDataProp_{0}", i.ToString(CultureInfo.InvariantCulture));
            columns.Add(tableValueCollection[queryParamName]);
        }

        var numberOfSortingColumns = int.Parse(tableValueCollection["iSortingCols"], CultureInfo.InvariantCulture);
        var sortingColumns = new List<DataTableRequest.SortBy>();
        for (int i = 0; i < numberOfSortingColumns; i++)
        {
            var sortColQueryParamName = string.Format("iSortCol_{0}", i.ToString(CultureInfo.InvariantCulture));

            if (tableValueCollection[sortColQueryParamName] != null)
            {
                var sortDirQueryParamName = string.Format("sSortDir_{0}", i.ToString(CultureInfo.InvariantCulture));
                var sortingDirection = tableValueCollection[sortDirQueryParamName];

                var sortingColumnIndex = int.Parse(tableValueCollection[sortColQueryParamName], CultureInfo.InvariantCulture);
                var sortingColumnName = columns[sortingColumnIndex];

                sortingColumns.Add(new DataTableRequest.SortBy(sortingColumnName, sortingDirection));
            }
        }

        var displayStart = int.Parse(tableValueCollection["iDisplayStart"], CultureInfo.InvariantCulture);
        var displayLength = int.Parse(tableValueCollection["iDisplayLength"], CultureInfo.InvariantCulture);
        var pageSize = displayLength;
        var pageIndex = displayStart / displayLength;

        string search = null;

        if (tableValueCollection.ContainsKey("sSearch"))
        {
            search = tableValueCollection["sSearch"];
        }

        var sEcho = int.Parse(tableValueCollection["sEcho"], CultureInfo.InvariantCulture);

        var dataTableRequest = new DataTableRequest(pageIndex, pageSize, search, sortingColumns, sEcho);

        return dataTableRequest;
    }
}

register model binder on Application_Start in Global.asax.cs:

ModelBinders.Binders.Add(typeof (DataTableRequest), new DataTableModelBinder());

I'm using DataTables 1.9.4 and following json model binder:

public class JsonModelBinder : DefaultModelBinder
{
    public static JsonSerializerSettings GlobalSerializerSettings
    {
        get
        {
            return new JsonSerializerSettings()
            {                    
                ContractResolver = new CamelCasePropertyNamesContractResolver(),                    
                Converters = { new IsoDateTimeConverter() }
            };
        }
    }

    public override object BindModel(ControllerContext controllerContext, ModelBindingContext bindingContext)
    {
        if (!IsJSONRequest(controllerContext))
        {
            return base.BindModel(controllerContext, bindingContext);
        }
        // Get the JSON data that's been posted
        var request = controllerContext.HttpContext.Request;

        request.InputStream.Seek(0, SeekOrigin.Begin);
        var streamReader = new StreamReader(request.InputStream);
        var jsonStringData = streamReader.ReadToEnd();

        if (string.IsNullOrEmpty(jsonStringData))
            return null;

        return JsonConvert.DeserializeObject(jsonStringData, bindingContext.ModelMetadata.ModelType, GlobalSerializerSettings);
    }

    protected static bool IsJSONRequest(ControllerContext controllerContext)
    {
        var contentType = controllerContext.HttpContext.Request.ContentType;
        return contentType.Contains("application/json");
    }
}

replacing the default model binder:

ModelBinders.Binders.DefaultBinder = new JsonModelBinder();
share|improve this answer
1  
Hi, Could you please share a asp.net mvc project with us ? Regards –  umki Jan 9 at 14:43
    
@umki hi, I can't share the whole project but I can explain some parts which you are interested in –  MajoB Jan 15 at 8:38
    
will you be able to make a sample project that works with asp.net mvc jquery datatable server side? that will be great –  Abu Hamzah Feb 14 at 15:34
    
CamelCasePropertyNamesContractResolver is a one way process. github.com/JamesNK/Newtonsoft.Json/issues/391 Not work for deserialize... –  Javier Ros Apr 4 at 21:44

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.