Sunday, April 1, 2012

J query Datatable in asp.net c#, Optimoze Gridview with datatable J query


Controller

HomeController.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using JQueryDataTables.Models;
using JQueryDataTables.Models.Repository;

namespace JQueryDataTables.Controllers
{
    public class HomeController : Controller
    {
        /// <summary>
        /// Action that handles initiall request and returns empty view
        /// </summary>
        /// <returns>Home/Index view</returns>
        public ActionResult Index()
        {
            return View();
        }


        /// <summary>
        /// Returns data by the criterion
        /// </summary>
        /// <param name="param">Request sent by DataTables plugin</param>
        /// <returns>JSON text used to display data
        /// <list type="">
        /// <item>sEcho - same value as in the input parameter</item>
        /// <item>iTotalRecords - Total number of unfiltered data. This value is used in the message:
        /// "Showing *start* to *end* of *iTotalDisplayRecords* entries (filtered from *iTotalDisplayRecords* total entries)
        /// </item>
        /// <item>iTotalDisplayRecords - Total number of filtered data. This value is used in the message:
        /// "Showing *start* to *end* of *iTotalDisplayRecords* entries (filtered from *iTotalDisplayRecords* total entries)
        /// </item>
        /// <item>aoData - Twodimensional array of values that will be displayed in table.
        /// Number of columns must match the number of columns in table and number of rows is equal to the number of records that should be displayed in the table</item>
        /// </list>
        /// </returns>
        public ActionResult AjaxHandler(JQueryDataTableParamModel param)
        {
            var allCompanies = DataRepository.GetCompanies();
            IEnumerable<Company> filteredCompanies;
            //Check whether the companies should be filtered by keyword
            if (!string.IsNullOrEmpty(param.sSearch))
            {
                //Used if particulare columns are filtered
                var nameFilter = Convert.ToString(Request["sSearch_1"]);
                var addressFilter = Convert.ToString(Request["sSearch_2"]);
                var townFilter = Convert.ToString(Request["sSearch_3"]);

                //Optionally check whether the columns are searchable at all
                var isNameSearchable = Convert.ToBoolean(Request["bSearchable_1"]);
                var isAddressSearchable = Convert.ToBoolean(Request["bSearchable_2"]);
                var isTownSearchable = Convert.ToBoolean(Request["bSearchable_3"]);

                filteredCompanies = DataRepository.GetCompanies()
                   .Where(c => isNameSearchable && c.Name.ToLower().Contains(param.sSearch.ToLower())
                               ||
                               isAddressSearchable && c.Address.ToLower().Contains(param.sSearch.ToLower())
                               ||
                               isTownSearchable && c.Town.ToLower().Contains(param.sSearch.ToLower()));
            }
            else
            {
                filteredCompanies = allCompanies;
            }

            var isNameSortable = Convert.ToBoolean(Request["bSortable_1"]);
            var isAddressSortable = Convert.ToBoolean(Request["bSortable_2"]);
            var isTownSortable = Convert.ToBoolean(Request["bSortable_3"]);
            var sortColumnIndex = Convert.ToInt32(Request["iSortCol_0"]);
            Func<Company, string> orderingFunction = (c => sortColumnIndex == 1 && isNameSortable ? c.Name :
                                                           sortColumnIndex == 2 && isAddressSortable ? c.Address :
                                                           sortColumnIndex == 3 && isTownSortable ? c.Town :
                                                           "");

            var sortDirection = Request["sSortDir_0"]; // asc or desc
            if (sortDirection == "asc")
                filteredCompanies = filteredCompanies.OrderBy(orderingFunction);
            else
                filteredCompanies = filteredCompanies.OrderByDescending(orderingFunction);

            var displayedCompanies = filteredCompanies.Skip(param.iDisplayStart).Take(param.iDisplayLength);
            var result = from c in displayedCompanies select new[] { Convert.ToString(c.ID), c.Name, c.Address, c.Town };
            return Json(new
                            {
                                sEcho = param.sEcho,
                                iTotalRecords = allCompanies.Count(),
                                iTotalDisplayRecords = filteredCompanies.Count(),
                                aaData = result
                            },
                        JsonRequestBehavior.AllowGet);
        }

    }
}
 


Models 


Company.cs


namespace JQueryDataTables.Models
{
    /// <summary>
    /// Company class contains information that shoudl be shown in table
    /// </summary>
    public class Company
    {
        static int nextID = 17;

        public Company()
        {
            ID = nextID++;
        }
        public int ID { get; set; }
        public string Name { get; set; }
        public string Address { get; set; }
        public string Town { get; set; }
    }

}



JQueryDataTableParamModel.cs




namespace JQueryDataTables.Models
{
    /// <summary>
    /// Class that encapsulates most common parameters sent by DataTables plugin
    /// </summary>
    public class JQueryDataTableParamModel
    {
        /// <summary>
        /// Request sequence number sent by DataTable, same value must be returned in response
        /// </summary>      
        public string sEcho{ get; set; }

        /// <summary>
        /// Text used for filtering
        /// </summary>
        public string sSearch{ get; set; }

        /// <summary>
        /// Number of records that should be shown in table
        /// </summary>
        public int iDisplayLength{ get; set; }

        /// <summary>
        /// First record that should be shown(used for paging)
        /// </summary>
        public int iDisplayStart{ get; set; }

        /// <summary>
        /// Number of columns in table
        /// </summary>
        public int iColumns{ get; set; }

        /// <summary>
        /// Number of columns that are used in sorting
        /// </summary>
        public int iSortingCols{ get; set; }

        /// <summary>
        /// Comma separated list of column names
        /// </summary>
        public string sColumns{ get; set; }

    }
}



View


Inde.chtml

@{
    Layout = null;
}

<!DOCTYPE html>
<html>
    <head>
        <title>JQuery DataTables/ASP.NET MVC Integration</title>
        <link href="@Url.Content("~/Content/dataTables/demo_table.css")" rel="stylesheet" type="text/css" />
        <script src="@Url.Content("~/Scripts/jquery-1.4.4.min.js")" type="text/javascript"></script>
        <script src="@Url.Content("~/Scripts/jquery.dataTables.min.js")" type="text/javascript"></script>
        <script src="@Url.Content("~/Scripts/index.js")" type="text/javascript"></script>
    </head>

    <body>
        <div id="container">
            <div id="demo">
                <h2>Index</h2>
                <table id="myDataTable" class="display">
                    <thead>
                        <tr>
                            <th>ID</th>
                            <th>Company name</th>
                            <th>Address</th>
                            <th>Town</th>
                        </tr>
                    </thead>
                    <tbody>
                    </tbody>
                </table>
            </div>
        </div>
    </bod



Use JQueryDataTables.dll library