DotNetCurry Logo

HTML Data Grid with CRUD Operations in ASP.NET MVC using Knockout.js, Require.js and WEB API

Posted by: Mahesh Sabnis , on 9/11/2015, in Category ASP.NET MVC
Views: 29755
Abstract: Implement a Data Grid like functionality in a HTML table in an MVC application using Knockout.js, jQuery and Require.js

Typically, HTML tables in an application are simply used to display a collection of data from external sources. But what if we are able to extend the behavior of the HTML table to replicate a basic Data Grid like functionality with CRUD operations, as found in server-side solutions? In this article we will use the Knockout.js library for Model creation and Databinding and jQuery Library for managing Ajax calls to a service created using ASP.NET WEB API.

 

Step 1: Open the free Visual Studio 2013 Community edition and create a new empty ASP.NET MVC application. Name this application as ‘MVC5_HTmlTableCRUD’. In this application, in the App_Data folder add a new Sql Server database of name Application.mdf. In this database, add a new EmployeeInfo table with the schema as shown in the following script:

CREATE TABLE [dbo].[EmployeeInfo] (
    [EmpNo]       INT          IDENTITY (1, 1) NOT NULL,
    [EmpName]     VARCHAR (50) NOT NULL,
    [DeptName]    VARCHAR (50) NOT NULL,
    [Designation] VARCHAR (50) NOT NULL,
    [Salary]      DECIMAL (18) NOT NULL,
    PRIMARY KEY CLUSTERED ([EmpNo] ASC)
);

Step 2: In this table, add test some data using the following script:

INSERT INTO [dbo].[EmployeeInfo] ([EmpNo], [EmpName], [DeptName], [Designation], [Salary]) 
VALUES (1, N'MS', N'Technical', N'Manager Technology', CAST(780000 AS Decimal(18, 0)))
INSERT INTO [dbo].[EmployeeInfo] ([EmpNo], [EmpName], [DeptName], [Designation], [Salary]) 
VALUES (2, N'LS', N'Administration', N'Manager', CAST(556000 AS Decimal(18, 0)))

Step 3: To create data access component in the project, add a new ADO.NET Entity Data model. Name this as ApplicationModel.edmx. This will start a wizard. In this wizard, start with the Database first approach i.e. Generate from Database. (If you are using VS2013 Ultimate with Update 4, then the option will say EF Designer from Database). Follow steps in the wizard, select database as Application.mdf and table as EmployeeInfo. After completion of the wizard, the following mapping will be displayed:

ef-model-mapping

Build the project.

Step 4:  In order to make use of ASP.NET WEB API, we need to add Web API Controller in the Controllers folder. On the Controllers folder, right click and add WEB API 2 Controller based on Entity Framework as shown in the following image:

webapi2-controller

Select the Model and Context Class for the WEB API as shown in the following image:

webapi2-controller-model

Once the ‘Add’ button is clicked, we will get a WEB API controller with ready Http action method for performing CRUD operations.

Step 5: In the Controllers, add a new Empty MVC Controller of name EmployeeController as shown in the following image:

empty-mvc-controller

This will generate the MVC controller with Index method. Right-Click on this method and add an Empty View with the name Index.cshtml.

Since we need to perform DataGrid operations on an HTML table, we need to reference jQuery, Knockout, Bootstrap and Require.js JavaScript libraries using NuGet Package Manager. To do so. right-click on the project and select Manage NuGet Packages option. From the NuGet Package Manager window, select the required JavaScript libraries as shown in the following image:

jQuery

jq-nuget

BootStrap

bootstrap-nuget

Knockout

knockout-nuget

Require

require-nuget

Why Require.js?

You must be wondering why Require.js is used here, so here are some of the reasons.

  • When we want to implement the separation of Script Logic from the UI elements, we need to use JavaScript library supporting MVVM or MVC. In the current article, we will be implementing MVVM using Knockout.js.
  • When we want to use Knockout.js for a view, we require the DOM to be pre-loaded. Only then can we bind Knockout model, functions, etc. with the UI elements. In this case, we write code for knockout ViewModel and the HTML for the view in the same HTML/cshtml file. This increases coding complexity. So a nice solution here is to use RequireJS for Asynchronous Module Definition (AMD) in case of Knockout.js.

Asynchronous Module Definition (AMD), is used in case of implementing application modularity. This helps to define loosely coupled modules for greater maintainability. ADM with RequireJS provides define() function to defining the modules and dependencies which can be used to define well-scoped objects, which in turn helps to avoid polluting the global namespace. require () is used to load modules defined using define().

For further details read about RequireJS for www.requirejs.org. You may also want to read Modules in EcmaScript 6 to learn about some alternative libraries

Step 6: When the necessary JavaScript libraries are added in the project, the Scripts folder getsadded in the project. In this folder, add ViewModel.js and main.js files. In the ViewModel.js we will be defining the Knockout ViewModel for application logic.

Add the following code in the ViewModel.js

//The model definition for jQuery and knockout dependencies
define("jquery", function () {
    return $;
});

define("knockout", function () {
    return ko;
});

//The viewModel definition
define("viewModel", ['jquery',"knockout"], function ($,ko) {

    var viewModel = function () {
        var self = this;
        //Boolean flag to check wheather the current operation is for 
        //Edit and add  New Record
        var isNewRecord = false;
        self.Message = ko.observable();

        //Observable Array
        self.Employees = ko.observableArray([]);

        loadData();

        //Function to Load Data using WEB API
        function loadData() {
            $.ajax({
                url: "/api/EmployeeInfoAPI",
                type:"GET"
            }).done(function (resp) {
                self.Employees(resp);
            }).fail(function (err) {
                self.Message("Error " + err.status);
            });
        };

        //The Employee Object used for Add, Edit, Delete operations
        function Employee(eno, ename, dname, desig, sal) {
            return {
                EmpNo: ko.observable(eno),
                EmpName: ko.observable(ename),
                DeptName: ko.observable(dname),
                Designation: ko.observable(desig),
                Salary: ko.observable(sal)
            }
        };

        //Observables for Templates
        self.readonlyTemplate=ko.observable("readonlyTemplate"),
        self.editTemplate = ko.observable()

        //Function to set the Template      
        self.setCurrentTemplate = function (tmpl) {
            return tmpl === this.editTemplate() ? 'editTemplate' : this.readonlyTemplate();
        }.bind(self);

        //Function to cancel edit effect
        self.reset = function (t) {
            self.editTemplate("readonlyTemplate");
        };

        //Function to add a new Empty row in table
        self.addRecord = function () {
            self.Employees.push(new Employee(0, "", "", "", 0.0));
            isNewRecord = true; //Set the Check for the New Record
        };

        //Function to save record
        self.save = function (e) {
            var Emp = {}; 
            Emp.EmpNo=e.EmpNo;
            Emp.EmpName=e.EmpName;
            Emp.Salary=e.Salary;
            Emp.DeptName=e.DeptName;
            Emp.Designation = e.Designation;

            if (isNewRecord === false) {

                $.ajax({
                    type: "PUT",
                    url: "/api/EmployeeInfoAPI/" + e.EmpNo,
                    data: Emp})
                    .done(function (resp) {
                        self.Message("Record Updated Successfully ");
                        self.reset();
                    })
                    .fail(function (err) {
                        self.Message("Error Occures, Please Reload the Page and Try Again " + err.status);
                        self.reset();
                    });
            }

            if (isNewRecord === true) {
                isNewRecord = false;
                $.ajax({
                    type: "POST",
                    url: "/api/EmployeeInfoAPI",
                    data: Emp})
                    .done(function (resp) {
                        self.Message("Record Added Successfully ");
                        self.reset();
                        loadData();
                    }).fail(function (err) {
                        self.Message("Error Occures, Please Reload the Page and Try Again " + err.status);
                        self.reset();
                    });
            }
        };

        //Function to Delete the Record
        self.delete = function (d) {
             
            $.ajax({
                type: "DELETE",
                url: "/api/EmployeeInfoAPI/" + d.EmpNo})
                .done(function (resp) {
                    self.Message("Record Deleted Successfully " + resp.status);
                    self.reset();
                    loadData();
                })
                .fail(function (err) {
                    self.Message("Error Occures, Please Reload the Page and Try Again " + err.status);
                    self.reset();
                });
        };


    };

    return new viewModel();
});

The above script code defines well-scoped objects for jQuery as $ and ko for knockout using define() function. The file then defines ViewModel, this is knockout ViewModel having dependencies on jQuery for ajax calls and Knockout for observable and observable arrays. This ViewModel contains loadData () function for retrieving Employees data from WEB API using Ajax call. The Employee object is used for performing CRUD operations. Since we will be using HTML table for CRUD operations, we need to define Html templates for Row Adding and editing. To set the edit and read only template, the ViewModel contains setCurrentTemplate () function. The function addRecord () pushes a new Employee object in the Employees observable array. The save () and delete () functions are used for add, edit and delete operations using Ajax calls.

Step 7: Open main.js and add the following JavaScript code in it:

//Define module loading for jQuey, knockout and view Model
require(['jquery','knockout', 'viewModel'], function ($,ko, viewModel) {
    $(document).ready(function () {

        //Instantiate page view model
        ko.applyBindings(viewModel);

    });
});

The above code uses require () function to load necessary modules on the UI. Currently we have jQuery, Knockout libraries and viewModel created using knockout.

Step 8: Open Index.cshtml and add the following markup with HTML templates and Databinding:

<h1>HTML Table with Fundamental DataGrid Capabilities using Knockout.js DataBinding.</h1>
<h2 class="center-block">Performing Add, Update, and Delete Operations</h2>

 
<script src="~/Scripts/jquery-2.1.4.min.js"></script>
<script src="~/Scripts/bootstrap.min.js"></script>
<script src="~/Scripts/knockout-3.3.0.js"></script>

<link href="~/Content/bootstrap.min.css" rel="stylesheet" />



<script src="~/Scripts/require.js"></script>
<script src="~/Scripts/ViewModel.js"></script>
<script src="~/Scripts/main.js"></script>



<script type="text/javascript" data-main="~/Scripts/main.js" src="~/Scripts/require.js"></script>


<!--html templates -->
<script type="text/template" id="readonlyTemplate">
    <tr>
        <td>
            <span data-bind="text:EmpNo"></span>
        </td>
        <td>
            <span data-bind="text:EmpName"></span>
        </td>
        <td>
            <span data-bind="text: DeptName"></span>
        </td>
        <td>
            <span data-bind="text: Designation"></span>
        </td>
        <td>
            <span data-bind="text: Salary"></span>
        </td>
        <td>
            <button class="btn btn-lg glyphicon glyphicon-edit" 
                    title="Click here to edit record" data-bind="click: function () { $root.editTemplate($data);}" />
        </td>
        <td>
            <button class="btn btn-lg glyphicon glyphicon-trash"
                    title="Click here to delete record"
                    data-bind="click:function () { $root.delete($data); }"></button>
        </td>
    </tr>
</script>

<script type="text/html" id="editTemplate">
    <tr>
        <td>
            <input type="text" class="form-control" data-bind="value: $data.EmpNo" id="txteno" disabled="disabled" />
        </td>
        <td>
            <input type="text" class="form-control" data-bind="value: $data.EmpName" id="txtename" />
        </td>
        <td>
            <input type="text" class="form-control" data-bind="value: $data.DeptName" id="txtdname" />
        </td>
        <td>
            <input type="text" class="form-control" data-bind="value: $data.Designation" id="txtdesig" />
        </td>
        <td>
            <input type="text" class="form-control" data-bind="value: $data.Salary" id="txtsal" />
        </td>
        <td>
            <button class="btn btn-lg glyphicon glyphicon-floppy-save" title="Click here to save record"
                    data-bind="click:$root.save"></button>
        </td>
        <td>
            <button class="btn btn-lg glyphicon glyphicon-remove-circle" 
                    title="Click here to cancel" data-bind="click:$root.reset"></button>
        </td>
    </tr>
</script>
<!--ends here-->


<button type="button" class="btn btn-lg glyphicon glyphicon-plus-sign"
        title="Click here to add record"
        data-bind="click:$root.addRecord"></button>

<table class="table table-bordered table-condensed table-striped">
    <thead>
        <tr>
            <th class="text-center">
                EmpNo
            </th>
            <th class="text-center">
                EmpName
            </th>
            <th class="text-center">
                DeptName
            </th>
            <th class="text-center">
                Desigation
            </th>
            <th class="text-center">
                Salary
            </th>
            <th class="text-center">
            </th>
            <th class="text-center">
            </th>
        </tr>
    </thead>
    <tbody data-bind="template: { name: setCurrentTemplate, foreach: Employees }"></tbody>
</table>
<hr />
<div>
    <span data-bind="value:Message"></span>
</div>

The above markup contains Html templates of id readOnlyTemplate and editTemplate. These are used for displaying table row in read-only style and edit row style. Buttons are bound with required functions defined in the ViewModel. The HTML markup is styled using required classes from Bootstrap.

Step 9: Open the RouteConfig.cs file from the App_Start folder and change the default controller value to Employee as shown in the following code: (highlighted)

public static void RegisterRoutes(RouteCollection routes)
{
    routes.IgnoreRoute("{resource}.axd/{*pathInfo}");

    routes.MapRoute(
        name: "Default",
        url: "{controller}/{action}/{id}",
        defaults: new { controller = "Employee", action = "Index", id = UrlParameter.Optional }
    );
}

Run the application, the page will be displayed as shown the in following image

html-table

Click on the plus (+) button on the top of the table, this will append a new read-only row in the table as shown in the following image:

html-edit-knockout

To make the row editable, click on the edit button in the sixth column of the table for the row, the row will become editable as shown in the following image

html-edit-record

The editable row shows the Save (floppy image) and Cancel (Cross circle image) buttons. We can enter data in cells and click on the Save button, the record will be added as shown in the following image

edited-row

Similarly Delete functionality can be tested. I leave it as an exercise for you. If you face any issues, post them in the comments section.

Conclusion: The Knockout.js library along with the jQuery provides an excellent feature for implementing MVVM based rich UX applications using standard HTML UI elements. In case of such patterns it is advisable to use the Asynchronous Module Definition (AMD) for the managing module loading. This can be implemented using RequireJS.

Download the entire source code of this article (Github)

Was this article worth reading? Share it with fellow developers too. Thanks!
Share on LinkedIn
Share on Google+
Further Reading - Articles You May Like!
Author
Mahesh Sabnis is a DotNetCurry author and Microsoft MVP having over 17 years of experience in IT education and development. He is a Microsoft Certified Trainer (MCT) since 2005 and has conducted various Corporate Training programs for .NET Technologies (all versions). Follow him on twitter @maheshdotnet


Page copy protected against web site content infringement 	by Copyscape




Feedback - Leave us some adulation, criticism and everything in between!