Click here to Skip to main content
12,622,568 members (32,154 online)
Click here to Skip to main content
Articles » Web Development » ASP.NET » Samples » Downloads

Stats

411.8K views
21.3K downloads
200 bookmarked
Posted

ASP.NET MVC Editable Table (jQuery DataTables and ASP.NET MVC integration - Part II)

, 6 Nov 2011 CPOL
This article shows how a datatable with standard CRUD functionalites (select/delete data, update single cell, and add new record) can be created in ASP.NET MVC using the jQuery DataTables Editable plug-in.
DataTableEditable
bin
Content
dataTables
images
back_disabled.jpg
back_enabled.jpg
favicon.ico
forward_disabled.jpg
forward_enabled.jpg
Sorting icons.psd
sort_asc.png
sort_asc_disabled.png
sort_both.png
sort_desc.png
sort_desc_disabled.png
jAlert
themes
base
images
ui-bg_flat_0_aaaaaa_40x100.png
ui-bg_flat_75_ffffff_40x100.png
ui-bg_glass_55_fbf9ee_1x400.png
ui-bg_glass_65_ffffff_1x400.png
ui-bg_glass_75_dadada_1x400.png
ui-bg_glass_75_e6e6e6_1x400.png
ui-bg_glass_95_fef1ec_1x400.png
ui-bg_highlight-soft_75_cccccc_1x100.png
ui-icons_222222_256x240.png
ui-icons_2e83ff_256x240.png
ui-icons_454545_256x240.png
ui-icons_888888_256x240.png
ui-icons_cd0a0a_256x240.png
smoothness
images
ui-bg_flat_0_aaaaaa_40x100.png
ui-bg_flat_75_ffffff_40x100.png
ui-bg_glass_55_fbf9ee_1x400.png
ui-bg_glass_65_ffffff_1x400.png
ui-bg_glass_75_dadada_1x400.png
ui-bg_glass_75_e6e6e6_1x400.png
ui-bg_glass_95_fef1ec_1x400.png
ui-bg_highlight-soft_75_cccccc_1x100.png
ui-icons_222222_256x240.png
ui-icons_2e83ff_256x240.png
ui-icons_454545_256x240.png
ui-icons_888888_256x240.png
ui-icons_cd0a0a_256x240.png
Controllers
DataTableEditable.csproj.user
Global.asax
Models
obj
Debug
DataTableEditable.dll
TempPE
Properties
Scripts
Views
Company
Shared
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using DataTableEditable.Models;

namespace DataTableEditable.Controllers
{
    public class CompanyController : Controller
    {
        public ActionResult Index()
        {
            var companies = DataRepository.GetCompanies();
            return View(companies);
        }

        public ActionResult Ajax()
        {
            return View();
        }

        public ActionResult Customization()
        {
            var companies = DataRepository.GetCompanies();
            return View(companies);
        }

        public ActionResult IndividualColumnEditing()
        {
            var companies = DataRepository.GetCompanies();
            return View(companies);
        }

        #region Ajax Data Provider

        public ActionResult AjaxDataProvider(JQueryDataTableParamModel param)
        {
            var allCompanies = DataRepository.GetCompanies();
            IEnumerable<Company> filteredCompanies;
            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);
        }


        #endregion


        #region "CRUD operations"


        /// <summary>
        /// Method called but plugin when a row is deleted
        /// </summary>
        /// <param name="id">Id of the row</param>
        /// <returns>"ok" if delete is successfully performed - any other value will be considered as an error mesage on the client-side</returns>
        public string DeleteData(int id)
        {
            try
            {
                var company = DataRepository.GetCompanies().FirstOrDefault(c => c.ID == id);
                if (company == null)
                    return "Company cannot be found";
                DataRepository.GetCompanies().Remove(company);
                return "ok";
            }
            catch (Exception ex)
            {
                return ex.Message;
            }
        }


        /// <summary>
        /// Action that updates data
        /// </summary>
        /// <param name="id">Id of the record</param>
        /// <param name="value">Value that shoudl be set</param>
        /// <param name="rowId">Id of the row</param>
        /// <param name="columnPosition">Position of the column(hidden columns are not counted)</param>
        /// <param name="columnId">Position of the column(hidden columns are counted)</param>
        /// <param name="columnName">Name of the column</param>
        /// <returns>value if update suceed - any other value will be considered as an error message on the client-side</returns>
        public string UpdateData(int id, string value, int? rowId, int? columnPosition, int? columnId, string columnName)
        {
            var companies = DataRepository.GetCompanies();

            if (columnPosition == 0 && companies.Any(c => c.Name.ToLower().Equals(value.ToLower())))
                return "Company with a name '" + value + "' already exists";
            var company = companies.FirstOrDefault(c => c.ID == id);
            if (company == null)
            {
                return "Company with an id = " + id + " does not exists";
            }
            switch (columnPosition)
            {
                case 0:
                    company.Name = value;
                    break;
                case 1:
                    company.Address = value;
                    break;
                case 2:
                    company.Town = value;
                    break;
                default:
                    break;
            }
            return value;
        }

        public int AddData(string name, string address, string town, int? country)
        {
            var companies = DataRepository.GetCompanies();
            if (companies.Any(c => c.Name.ToLower().Equals(name.ToLower())))
            {
                Response.Write("Company with the name '" + name + "' already exists");
                Response.StatusCode = 404;
                Response.End();
                return -1;
            }

            var company = new Company();
            company.Name = name;
            company.Address = address;
            company.Town = town;
            companies.Add(company);
            return company.ID;
        }
        #endregion
    }

    /// <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; }


    }
}

By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.

If a file you wish to view isn't highlighted, and is a text file (not binary), please let us know and we'll add colourisation support for it.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Jovan Popovic
Program Manager Microsoft
Serbia Serbia
Graduated from Faculty of Electrical Engineering, Department of Computer Techniques and Informatics, University of Belgrade, Serbia.
Currently working in Microsoft as Program Manager on SQL Server product.
Member of JQuery community - created few popular plugins (four popular JQuery DataTables add-ins and loadJSON template engine).
Interests: Web and databases, Software engineering process(estimation and standardization), mobile and business intelligence platforms.

You may also be interested in...

| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.161128.1 | Last Updated 6 Nov 2011
Article Copyright 2011 by Jovan Popovic
Everything else Copyright © CodeProject, 1999-2016
Layout: fixed | fluid