DotNetCurry Logo

Building a DataGrid using HTML, jQuery, Knockout.js and ASP.NET MVC & WEB API

Posted by: Mahesh Sabnis , on 2/28/2015, in Category ASP.NET MVC
Views: 33495
Abstract: This article shows how to mix server-side technologies like ASP.NET MVC & WEB API with client-side technologies like HTML, jQuery and Knockout.js to easily create complex UI's like the DataGrid.

In this article, we will represent a standard HTML Table element to behave like a DataGrid. We will be dividing our efforts in 3 different steps:

  • Creating ASP.NET MVC Project, creating Database, creating Model and WEB API.
  • Displaying Data in Table and performing Add, Edit and Delete Operations.
  • Pagination
  • Column Sorting

 

The final look of the solution will be as follows:

html-data-grid

Step 1: Open the Free Visual Studio 2013 Community Edition and create an Empty MVC application. Name this project as ‘A5_HTML_Table_DataGrid’.

Step 2: In this project, in the App_Data folder add a new Sql Server database of the name ‘Application.mdf’. In this database add the following table:

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

Step 3: To create a Model using EntityFramework, right click on the Models folder and add an ADO.NET Entity Data Model, name it as ‘ApplicationEDMX’. Complete the Wizard by selecting Application.mdf as database and select Employee table. After completing wizard the following table mapping will be displayed:

table-mapping

Step 4: To create a Web API controller, right click on the controllers folder and select Add new controller, select WEB API 2 Controller with actions, using Entity Framework. Select the Model Class, Data Context Class and Controller name as shown here:

webapi-controller

Step 5: To create an ASP.NET MVC controller, right click on the Controllers folder and add an empty MVC controller, name it as ‘EmployeeController’. This will have an Index action method. Scaffold and empty view from this Index method.

Displaying Employee Data on the View using HTML Table

Step 6: To implement the logic for displaying Data on the View we can use client-side libraries. Right click on the project and using Manage NuGet Package, add the jQuery and knockout libraries in the project, this will be added in the Scripts folder. (Note: Since we are adding the latest jQuery, remove the script reference of the jQuery from the _Layout.cshtml from the Views/Shared folder.)

Step 7: In the Scripts folder add a new folder of the name ‘MyScripts’. In this folder add a new JavaScript file of name DataGridLogic.js. In this file add the following JavaScript code:

/// <reference path="../jquery-2.1.1.min.js" />
/// <reference path="../knockout-3.2.0.js" />

var self = this;
//S1:Flag to check whether the operation is for Edit and New Record
var IsNewRecord = false;

self.Employees = ko.observableArray([]);
self.Message = ko.observable(); //The Observable for displaying Message

loadEmployees();
//Logic for CRUD Operations

//S2:Method to Load all Employees by making Ajax call to WEB API GET method
function loadEmployees() {
    alert("In Load");
    $.ajax({
        type: "GET",
        url: "/api/EmployeesAPI",
        success: function (data) {
            self.Message("Success");
            self.Employees(data);
        },
        error: function (err) {
            alert(err.status + " <--------------->");
        }
    });

};

//S3:The Employee Object
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)
    }
};

//S4:The ViewModel where the Templates are initialized
var OperationsViewModel = {
    displayOnlyTemplate: ko.observable("displayOnlyTemplate"),
    updateTemplate: ko.observable()
};

//S5:Method to decide which Template is used (displayOnlyTemplate or updateTemplate)
OperationsViewModel.currentTemplate = function (tmpl) {
    return tmpl === this.updateTemplate() ? 'updateTemplate' : this.displayOnlyTemplate();
}.bind(OperationsViewModel);

//S6:Method to create a new Blank entry When the Add New Record button is clicked
OperationsViewModel.addnewRecord = function () {
    self.Employees.push(new Employee(0, "", "", "", 0.0));
    IsNewRecord = true; //Set the Check for the New Record
};


//S7:Method to Save the Record (This is used for Edit and Add New Record)
OperationsViewModel.saveEmployee = function (d) {

    var Emp = {};
    Emp.EmpNo = d.EmpNo;
    Emp.EmpName = d.EmpName;
    Emp.DeptName = d.DeptName;
    Emp.Designation = d.Designation;
    Emp.Salary = d.Salary;
    //Edit teh Record
    if (IsNewRecord === false) {
        $.ajax({
            type: "PUT",
            url: "/api/EmployeesAPI/" + Emp.EmpNo,
            data: Emp,
            success: function (data) {
                self.Message("Record Updated Successfully");
                OperationsViewModel.reset();
            },
            error: function (err) {
                self.Message("Error Occures, Please Reload the Page and Try Again " + err.status);
                OperationsViewModel.reset();
            }
        });
    }
    //The New Record
    if (IsNewRecord === true) {
        IsNewRecord = false;
        $.ajax({
            type: "POST",
            url: "/api/EmployeesAPI",
            data: Emp,
            success: function (data) {
                self.Message("Record Added Successfully " + data.status);
                OperationsViewModel.reset();
                loadEmployees();
            },
            error: function (err) {
                alert("Error Occures, Please Reload the Page and Try Again " + err.status);
                OperationsViewModel.reset();
            }
        });
    }
};

//S8:Method to Delete the Record
OperationsViewModel.deleteEmployee = function (d) {

    $.ajax({
        type: "DELETE",
        url: "/api/EmployeesAPI/" + d.EmpNo,
        success: function (data) {
            self.Message("Record Deleted Successfully ");
            OperationsViewModel.reset();
            loadEmployees();
        },
        error: function (err) {
            self.Message("Error Occures, Please Reload the Page and Try Again " + err.status);
            OperationsViewModel.reset();
        }
    });
};

//S9:Method to Reset the template 
OperationsViewModel.reset = function (t) {
    this.updateTemplate("displayOnlyTemplate");
};
ko.applyBindings(OperationsViewModel);
//Ends Here

The above Javascript has the following specification:

  • Declares Knockout objects
    • Employees: An ObservableArray to store Employees data received from the Ajax call made using WEB API.
    • Message: An Observable to show status message on UI.
    • IsNewRecord: The flag variable declaration to check New or Editable record.
  • loadEmployees: The function to make Ajax call to WEB API to read Employees record. On successful completion of the call, the data will be put in Employees observable array.
  • Employee: The object which set properties for Employee.
  • OperationsViewModel: The Knockout view model. This declare the default template for data display. These templates will be used in future on the view for data display in table.
  • currentTemplate : The function which will change the UI template based upon the selection of the template (Update/Display) by the end-user.
  • addNewRecord: The function used to add new Employee record in the Employees array.
  • saveEmployee: The function performs either Create new or Edit operation for the Employees based upon the IsNewRecord flag. This method makes an Ajax call to WEB API for performing Http POST (New cord) or Http PUT (edit existing record) operation.
  • deleteEmployee: The function makes an Ajax call to WEB API to delete the Employee object.

Step 8: In the Index.chtml add the following styles and HTML markup:

<style type="text/css">
    #dvDataGrid {
       height:500px;
       width:1200px;
       overflow-x:scroll;
       overflow-y:scroll;
    }
    #tblDatagrid,td,th {
      border:double;
    }
    th {
     background-color:darkgrey; cursor:pointer;
     }
    .header {
      background-color:chocolate;
      font-size:15px;   
    }
    .cedit {
      background-color:azure;
    }

</style>

<h2>Performing DataGrid Like Operations using HTML Table</h2>
<script src="~/Scripts/jquery-2.1.1.min.js"></script>
<script src="~/Scripts/knockout-3.2.0.js"></script>

<table>
    <tr>
        <td>
            <input type="button" value="Add Record" data-bind="click: function () { OperationsViewModel.addnewRecord(); }" />
        </td>
        <td>
      
        </td>
    </tr>
</table>

<div id="dvDataGrid">
    <table>
        <thead>
            <tr>
                <th class="chead">
                    EmpNo
                </th>
                <th class="chead">
                    EmpName
                </th>
                <th class="chead">
                    DeptName
                </th>
                <th class="chead">
                    Desigation
                </th>
                <th class="chead">
                    Salary
                </th>
                <th>
                </th>
                <th>
                </th>
            </tr>

 

        <tbody data-bind="template: { name: currentTemplate, foreach: Employees}">
            <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>
            </tr>
        </tbody>
         
    </table>
</div>
<div>
    <span data-bind="text:Message"></span>
</div>



<script type="text/html" id="displayOnlyTemplate">
    <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>
            <input type="button" value="Edit" data-bind="click: function () { OperationsViewModel.updateTemplate($data);}" />
        </td>
        <td>
            <input type="button" value="Delete" data-bind="click: function () { OperationsViewModel.deleteEmployee($data); }" />
        </td>
    </tr>
</script>

<script type="text/html" id="updateTemplate">
    <tr class="cedit">
        <td>
            <input type="text" data-bind="value: $data.EmpNo" id="txteno" disabled="disabled" />
        </td>
        <td>
            <input type="text" data-bind="value: $data.EmpName" id="txtename" />
        </td>
        <td>
            <input type="text" data-bind="value: $data.DeptName" id="txtdname" />
        </td>
        <td>
            <input type="text" data-bind="value: $data.Designation" id="txtdesig" />
        </td>
        <td>
            <input type="text" data-bind="value: $data.Salary" id="txtsal" />
        </td>
        <td>
            <input type="button" value="Save" data-bind="click: OperationsViewModel.saveEmployee" />
        </td>
        <td>
            <input type="button" value="Cancel" data-bind="click: function () { OperationsViewModel.reset(); }" />
        </td>
    </tr>
</script>




<script src="~/Scripts/MyScripts/DataGridLogic.js"></script>



In a production app, keep the css in a separate file of its own and add a reference of the css file in your html.

The above HTML has the following features:

- The first table contains a <button>. This is ‘click’ bound with the ‘addnewRecord’ function declared in the ViewModel.

- The <div> with id of name ‘dvDataGrid’ contains table which contains header. This header displays header for Employee information e.g. EmpNo, EmpName, etc.

- The <tbody> is ‘template’ bound with ‘currenttemplate’, this is defined in the ViewModel. The <tbody> is also ‘foreach’ bound with the ‘Employees’ observable array declared in the ViewModel. The current template function works around the Html templates declared on the View. These templates are declare on the view.

- The ‘displayOnlyTemplate’, defines <tr> with <td> in it. Each <td> contains <span> which is ‘text’ bound with the properties from Employee object. This template by default displays Employee Data in read-only form. This template define <button> for Edit and Delete which are ‘click’ bound with ‘updateTemplete’ observable and ‘deleteEmployee’ function declared in the View Model. When the ‘Edit’ button in the Table row is clicked, the current row will be replaced by the ‘updateTemplate’ HTML template. This template is used to display data in editable form.

- The ‘updateTemplate’, is same as ‘displayOnlyTemplate’, the only difference is instead of <span>, it contains <input type=’text’>, each of this is ‘value’ bound with the Employee properties. The <input type=’button’ value=’Save’> is ‘click’ bound with the ‘saveEmployee’ function. This will either add a new Employee or Edit an existing Employee. The <input type=’button’ value=’Cancel’> is ‘click’ bound with the ‘reset’ function, this will cancel the Edit effect for the current row.

Run the Application. The following View will be displayed:

datagrid-add-edit

Click on the ‘Add Record’ Button, a new row will be appended to the Table:

datagrid-edit-delete

Click on the ‘Edit’ button for the row and it will be editable as shown here:

crud-datagrid

Enter Data in the textboxes, and click on ‘Save’. The data will be added in the table:

datagrid-save

Likewise, Edit, Delete and Cancel functionality can be tested.

Implementation Pagination in DataGrid

To implementation Pagination in the Table, we need to make the following changes in the JavaScript.

Step 9: Open DataGridLogic.js and add the following JavaScript in it:

//The Logic for Pagination Here
self.pageRowSize = ko.observable(5); // The Default No of Rows on the Table.
self.currentPage = ko.observable(0); // The current Page.
self.paginationEmployee = ko.observableArray(); // The declaration for Paginated data storing .

//The computed declaration for the number of display of records
self.page = ko.computed(function () {
    //Logic for displaying number of rows in the table
    if (self.pageRowSize() == "all") {
        self.paginationEmployee(self.Employees.slice(0));
    } else {
        var pgSize = parseInt(self.pageRowSize(), 10),
         fisrt = pgSize * self.currentPage(),
         last = fisrt + pgSize;

        self.paginationEmployee(self.Employees.slice(fisrt, last));
    }

}, self);

//The function for the total number of pages
self.totalPages = function () {
    var totpages = self.Employees().length / self.pageRowSize() || 1;
    return Math.ceil(totpages);
}

//The function for Next Page
self.nextPage = function () {
    if (self.currentPage() < self.totalPages() - 1) {
        self.currentPage(self.currentPage() + 1);
    }
}
//The function for Previous Page
self.previousPage = function () {
    if (self.currentPage() > 0) {
        self.currentPage(self.currentPage() - 1);
    }
}

//The First Page
self.firstPage = function () {
    self.currentPage(0);
}

//The Last Page
self.lastPage = function () {
    self.currentPage(self.totalPages() + 1)
}


//Ends Here

The above JavaScript has the following features:

  • ‘pageRowSize’: the observable used to specify default number of rows to be shown in the table.
  • currentPage: the observable representing the current page.
  • paginationEmployee: an observable array, this will store all Employees retrieved from the WEB API.
  • Page: the knockout computed declaration, this contains logic for pagination.
  • totalPages: the observable for defining total number of pages based upon the data in the Employees observable array. Total pages are calculated based upon the pageRowSize.
  • nextPage, previousPage, firstPage, lastPage: these observables are used for pagination operations.

Change the ‘addnewRecord’ function as follows:

OperationsViewModel.addnewRecord = function () {
   self.paginationEmployee.push(new Employee(0, "", "", "", 0.0));
    IsNewRecord = true; //Set the Check for the New Record
};

Here we are using the ‘paginationEmployee’ observable array for pushing new Employee record.

Step 10: To implementation pagination, we need to make the following changes in the view.

Add the <select> element on the right side of the ‘Add Record’ button. This <select> will be value bound with the ‘pageRowSize’ observable as shown here:

<td>
    Select the Page Size:
    <select id="lstpagrec" data-bind="value:pageRowSize">
                <option value="5">5</option>
                <option value="10">10</option>
                <option value="15">15</option>
                <option value="all">All</option>
    </select>
</td>

Change the <tbody> template DataBinding to ‘paginationEmployee’ observable array instead of Employees observable array:

<tbody data-bind="template: { name: currentTemplate, foreach: paginationEmployee}">

In the <table>, add the <tfoot> with navigation links:

<tfoot>
    <tr>
        <td colspan="7">
            <nav>
                <a href="#" title="First" data-bind="click: firstPage"><<</a>
                <a href="#" title="Previous" data-bind="click: previousPage"><</a>
                <a href="#" title="Next" data-bind="click:nextPage">></a>
                <a href="#" title="Last" data-bind="click:lastPage">>></a>
            </nav>
        </td>
    </tr>
</tfoot>

The navigation links are used for pagination operations like First, Previous, Next and Last. These links are ‘click’ bound with ‘firstPage’,’previousPage’,’nextPage’ and ‘lastPage’ function defined in the ViewModel respectively.

Run the Page. This will show the pagination links in the table footer and the Page Size <select> as shown below:

datagrid-knockout-pagination

Click on the Next link to achieve the following result:

 

datagrid-nextlink

The Other links like Previous, First and last can also be tested.

Data Sorting the DataGrid based upon the Columns

Step 11: To implement data sorting in the table, we need to add some more logic in the DataGridLogic.js file. Open the JavaScript file and add the following code. (Note: Add this logic below the Pagination logic.)

//Logic for Sorting based on Column Header

//S1: An Observable Array declaration for
self.tableHeadersCaptions = ko.observableArray([
    { caption: 'EmpNo', sortKeyName: 'EmpNo', ascending: true },
    { caption: 'EmpName', sortKeyName: 'EmpName', ascending: true },
    { caption: 'DeptName', sortKeyName: 'DeptName', ascending: true },
    { caption: 'Designation', sortKeyName: 'Designation', ascending: true },
    { caption: 'Salary', sortKeyName: 'Salary', ascending: true }
]);

//S2: The Default Sort
self.defaultSort = self.tableHeadersCaptions[0];


//S3: The function for sort
self.sortAscDesc = function (h, e) {

if (self.defaultSort === h) {
        h.ascending = !h.ascending; //toggle across asc and desc Soring 
} else {
        self.defaultSort = h; //first click store as the default action
}
var key = self.defaultSort.sortKeyName;


//The Ascending Sort
var sortingAsc = function (l, r) { return l[key] < r[key] ? -1 : l[key] > r[key] ? 1 : l[key] == r[key] ? 0 : 0; };

//The Descending Sort
var sortingDesc = function (l, r) { return l[key] > r[key] ? -1 : l[key] < r[key] ? 1 : l[key] == r[key] ? 0 : 0; };
  

//The Sorting condition
var sorting = self.defaultSort.ascending ? sortingAsc : sortingDesc;


//Apply the effect on the Array
self.paginationEmployee.sort(sorting);
};

//Ends Here

The above JavaScript has the following features:

  • tableHeadersCaptions is an observable array of objects and defines header captions for the table using Employee properties e.g. EmpNo, EmpName, etc. This array also defines the SortKey based upon which the sorting can be implemented.
  • sortAscDesc is a function that defines logic for sorting the data in ascending or descending order based upon the key from array.

Step 12: Change the View as follows. In the <table> which is showing the Employee data, we have hard-coded the header <th> for Employee property, we need to change it to show clickable headers :

<tr data-bind="foreach: tableHeadersCaptions">
<th data-bind="click: sortAscDesc, text: caption"></th></tr>

The <tr> is ‘foreach’ bound with the ‘tableHeadersCaptions’ array declared in the view model. This contains an array of objects which provides captions for Employee properties. The <th> is ‘text’ bound with the ‘caption’ from the ‘tableHeadersCaptions’. The ‘<th>’ is ‘click’ bound with the ‘sortAscDesc’ function defined in the ViewModel.

Run the View and click on the EmpNo column header, the data will be sorted in a Descending order:

datagrid-sorting

Conclusion: Using a mix of server-side technologies like ASP.NET MVC and Web API, and client side technologies like Knockout.js, HTML Template, jQuery etc, we can easily implement a complex UI like the DataGrid.

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!
Comment posted by Pranil on Wednesday, March 18, 2015 1:04 PM
Nice!