DotNetCurry Logo

Search Data Table using ASP.NET Web API, jQuery and Knockout.js

Posted by: Mahesh Sabnis , on 10/6/2014, in Category ASP.NET MVC
Views: 47099
Abstract: Using a mashup of technologies like ASP.NET Web API, jQuery and Knockout.js, you can create powerful web solutions with ease. This article shows how to create a searchable data table using these technologies

Searching a table is a common requirement in a project. The end-user may select any criteria for search and based on that criteria, the data search is performed and the matching data is displayed in the UI. In this article, we will create a searchable Data Table. We will fetch our data in JSON format using ASP.NET Web API with the help of jQuery Ajax. We will also use Knockout.js library to bind the data with the UI.

 

The Implementation

Step 1: Open Visual Studio 2013 (I am using Ultimate with Update 3). Create an Empty ASP.NET MVC application. In this application, add jQuery and Knockout.js libraries using NuGet Package.

Step 2: To create a Database store, right-click on the App_Data folder and add a new SQL Server database, name it as ‘Application.mdf’. In this database, add a new table with the name ‘PersonInfo’. The Table columns are as shown here:

CREATE TABLE [dbo].[PersonInfo] (
    [PersonId]        INT           IDENTITY (1, 1) NOT NULL,
    [PersonFirstName] VARCHAR (30)  NOT NULL,
    [PersonLastName]  VARCHAR (40)  NOT NULL,
    [Address]         VARCHAR (300) NOT NULL,
    [DateOfBirth]     DATETIME      NOT NULL,
    [Age]             INT           NOT NULL,
    [Gender]          VARCHAR (8)   NOT NULL,
    [City]            VARCHAR (30)  NOT NULL,
    [District]        VARCHAR (30)  NOT NULL,
    [State]           VARCHAR (30)  NOT NULL,
    [MobileNo]        VARCHAR (20)  NOT NULL,
    [Email]           VARCHAR (40)  NOT NULL,
    PRIMARY KEY CLUSTERED ([PersonId] ASC)
);

Step 3: To create a Model, right-click on the Models folder and add an ADO.NET Entity Data Model, name it as ‘ApplicationEDMX.edmx’. In the Entity Framework (EF)  wizard, select the Application.mdf database and select ‘PersonInfo’ table. After completion of the wizard the following mapping gets displayed:

person-info

Step 4: To access data using the EF on the client-side, we will ASP.NET WEB API. This WEB API will have an action method that will return the PersonInfo collection. Right-Click on the Controllers folder and add a new Empty WEB API controller, name it as ‘PeopleSearchController’. In this controller add the below action methods:

using A3_DataSearch.Models;
using System.Collections.Generic;
using System.Linq;
using System.Web.Http;

namespace A3_DataSearch.Controllers
{
    public class PeopleSearchController : ApiController
    {

        ApplicationEntities ctx;

        public PeopleSearchController()
        {
            ctx = new ApplicationEntities(); 
        }

        [Route("Persons/{field}/{value}")]
        public List<PersonInfo> GetPersonInfo(string field, string value)
        {
            List<PersonInfo> Res = new List<PersonInfo>();
            if (value.Length != 0)
            { 
            
            Res = (from per in ctx.PersonInfoes.ToList()
                      where GetPropertyValueForObject(per, field).ToString().StartsWith(value)
                      select per).ToList();
            }

            return Res;
        }

        [Route("Persons")]
        public List<PersonInfo> GetPersonInfo()
        {
            List<PersonInfo> Res = new List<PersonInfo>();
             

                Res = (from per in ctx.PersonInfoes.ToList()
                                              select per).ToList();

            return Res;
        }


        static object GetPropertyValueForObject(object src, string pName)
        {
            var val = src.GetType().GetProperty(pName).GetValue(src, null);
            return val;
        }
    }
}

In the above code, action method has the following role:

  • The method GetPersonInfo() returns info about all persons
  • The method GetPersonInfo(string field, string value) returns the PersonInfo based upon the ‘field’ parameter which accepts values like PersonFirstName, City, etc and the ‘value’ parameters which accepts the search value.
  • The static method GetPropertyValueForObject(object src,string pName) accepts the object from which the property value can be returned.

Step 5: In the Controllers folder, add a new Empty MVC controller with the name ‘SearchController’. Scaffold an empty view from the Index action method of the SearchController, name it as ‘Index.cshtml’.

Step 6: In the Scripts folder, add a new folder named ‘MyScripts’. In the folder add a new JavaScript file and name it as ‘SearchLogic.js’. Add the following code in the file:

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

(function () {
    var SearchViewModel = function () {
        var self = this;

        self.Persons = ko.observableArray([]); //The PersonInfo Array

        self.FilterValue = ko.observable(); //The Filter value for Searching Person

        self.ErrorMessage = ko.observable("");

        var PersonInfo = {
            PersonFirstName: "",
            PersonLastName: "",
            Gender: "",
            City: "",
            District: "",
            State:""
        };

        self.Properties = ko.observableArray([]); //The Array of properties for the selection criteria

        //The Function Call
        loadProperties();

        //This function will read all properties
        //from the person object and display
        //them into the select element on the View
        function loadProperties() {
            for (prop in PersonInfo) {
                if (typeof PersonInfo[prop] !== 'function') {
                    self.Properties.push(prop);
                }
            }
        }

        self.Property = ko.observable("");//For the Property Name selected
        self.SelectedProperty = ko.observable();

        var searchFlag = 0;
        //The Function for Selection of the Property for Search Criteria
        self.SelectedProperty.subscribe(function (val) {
            if (val !== 'undefined') {
                self.Property(val);
                searchFlag = 1;
            }
        });

      
        

        loadPersons(); //Function Call

        //Function to Get All Persons
        function loadPersons() {
            $.ajax({
                url: "/Persons",
                type: "GET"
            }).done(function (resp) {
                self.Persons(resp);
            }).error(function (err) {
                self.ErrorMessage("Error " + err.status);
            });
        }


        //Make an ajax call to WEB API
        //And Put Data in Persons observablearray

        self.FilterValue.subscribe(function (val) {
            if (val !== 'undefined' || val !== '') {
                if (searchFlag === 1) {
                    var url = "/Persons/" + self.Property() + "/" + val;
                    $.ajax({
                        url: url,
                        type: "GET"
                    }).done(function (resp) {
                        self.Persons(resp);
                    }).error(function (err) {
                        self.ErrorMessage("Error " + err.status);
                    });
                } 
            }
            if (val === '')
            {
                loadPersons();
            }
        });

    };

    ko.applyBindings(new SearchViewModel());
})();

The above JavaScript code does the following:

  • The PersonInfo object defines properties for search criteria e.g. FirstName, LastName, City, etc.
  • Persons and Properties observable arrays are declared for storing Persons info and Properties from the ‘PersonInfo’ object.
  • The loadProperties() function reads all the properties from the PersonInfo object and pushes property names in the Properties observable array.
  • The loadPersons() function makes an Ajax call to the WEB API and retrieves all PersonInfo array and puts the data in Persons observable array.
@{
    ViewBag.Title = "Index";
}

<style type="text/css">
    table, td {
      border:double;
    }
    td {
      width:120px;
      font-size:20px;
    }
    thead {
     background-color:red;
    }
</style>
<script src="~/Scripts/jquery-2.1.1.min.js"></script>

<script src="~/Scripts/knockout-3.2.0.js"></script>


<h2>Index</h2>
Select the Search Criteria: (Select Property from List and Enter Value in TextBox)
<table>
    <tr>
        <td>
           <select id="lstprops" data-bind="options:Properties,optionsCaption:'Select Property',value:SelectedProperty"></select>
        </td>
        <td>
            =
        </td>
        <td>
            <input type="text" id="txtval"
                   data-bind="value:FilterValue,valueUpdate:'afterkeydown'"/>
             
        </td>
    </tr>
</table>

<table>
    <thead>
        <tr>
            <td>PersonId</td><td>FirstName</td><td>LastName</td><td>Address</td><td>Date of Birth</td><td>Age</td><td>Gender</td><td>City</td><td>District</td><td>State</td><td>Mobile No</td><td>Email</td>
        </tr>
    </thead>
    <tbody data-bind="foreach:Persons">
        <tr>
            <td><span data-bind="text:PersonId"></span></td>
            <td><span data-bind="text:PersonFirstName"></span></td>
            <td><span data-bind="text:PersonLastName"></span></td>
            <td><span data-bind="text:Address"></span></td>
            <td><span data-bind="text:DateOfBirth"></span></td>
            <td><span data-bind="text:Age"></span></td>
            <td><span data-bind="text:Gender"></span></td>
            <td><span data-bind="text:City"></span></td>
            <td><span data-bind="text:District"></span></td>
            <td><span data-bind="text:State"></span></td>
            <td><span data-bind="text:MobileNo"></span></td>
            <td><span data-bind="text:Email"></span></td>
        </tr>
    </tbody>
</table>

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

  • The self.FilterValue.subscribe() function makes an Ajax call to WEB API and based upon the criteria, retrieves the PersonInfo and puts the data in the ‘Persons’ observable array.

Step 7: In the Index.cshtml add the following script references, HTML Markup with the DataBinding

@{
    ViewBag.Title = "Index";
}

<style type="text/css">
    table, td {
      border:double;
    }
    td {
      width:120px;
      font-size:20px;
    }
    thead {
     background-color:red;
    }
</style>
<script src="~/Scripts/jquery-2.1.1.min.js"></script>

<script src="~/Scripts/knockout-3.2.0.js"></script>


<h2>Index</h2>
Select the Search Criteria: (Select Property from List and Enter Value in TextBox)
<table>
    <tr>
        <td>
           <select id="lstprops" data-bind="options:Properties,optionsCaption:'Select Property',value:SelectedProperty"></select>
        </td>
        <td>
            =
        </td>
        <td>
            <input type="text" id="txtval"
                   data-bind="value:FilterValue,valueUpdate:'afterkeydown'"/>
             
        </td>
    </tr>
</table>

<table>
    <thead>
        <tr>
            <td>PersonId</td><td>FirstName</td><td>LastName</td><td>Address</td><td>Date of Birth</td><td>Age</td><td>Gender</td><td>City</td><td>District</td><td>State</td><td>Mobile No</td><td>Email</td>
        </tr>
    </thead>
    <tbody data-bind="foreach:Persons">
        <tr>
            <td><span data-bind="text:PersonId"></span></td>
            <td><span data-bind="text:PersonFirstName"></span></td>
            <td><span data-bind="text:PersonLastName"></span></td>
            <td><span data-bind="text:Address"></span></td>
            <td><span data-bind="text:DateOfBirth"></span></td>
            <td><span data-bind="text:Age"></span></td>
            <td><span data-bind="text:Gender"></span></td>
            <td><span data-bind="text:City"></span></td>
            <td><span data-bind="text:District"></span></td>
            <td><span data-bind="text:State"></span></td>
            <td><span data-bind="text:MobileNo"></span></td>
            <td><span data-bind="text:Email"></span></td>
        </tr>
    </tbody>
</table>

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

The above HTML Markup has the below specification:

  • The <select> with ‘lstprops’ is bound with ‘options’ binding to the ‘Properties’ observable array.
  • The TextBox with ‘txtval’ is ‘value’ bound with the ‘FilterValue’ observable. The value for the property is set using valueUpdate:’afterkeydown’. This means that when the end-user enters data in the textbox the value for the ‘FilterValue’ is set to the value entered into the textbox.
  • The <table> is bound with the ‘foreach’ binding to the ‘Persons’ observable array.

Step 8: Run the application and the Index view will be displayed as shown here:

aspnet-datasearch-knockout

Select the Property Name from the DropDown and enter the corresponding value in the TextBox, e.g. Property is ‘City’ and Value in the TextBox in ‘Pune’, the data will be displayed as shown here:

mvc-datasearch

Conclusion: Using a mashup with technologies like ASP.NET Web API, jQuery and Knockout.js, you can create some powerful solutions with ease. Using the JSON response format from WEB API, data can be easily fetched using a jQuery Ajax call and the ViewModel declared using Knockout library provides a quick and effective mechanism of data refresh notification.

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 mgm on Wednesday, October 15, 2014 2:20 PM
Hi:
Getting the following error when routing the methods.  What's missing?

[Route("Persons/{field}/{value}")]
public List<PersonInfo> GetPersonInfo(string field, string value)
Error   2   'System.Web.Routing.Route' is not an attribute class

Thanks,   
Comment posted by Alex on Monday, November 3, 2014 8:55 PM
Thank you for this post I'm getting this error when running your sample. Any idea?
HTTP 404. The resource you are looking for (or one of its dependencies) Request RUL: /
Comment posted by MAhesh Sabnis on Wednesday, November 12, 2014 3:17 AM
Hi mgm,

  Hope you have open the solution in the VS2013 with the target framework 4.5.1. the [Route] is the Attribute based routing in MVC 5. So to execute it you need to add routes.MapMvcAttributeRoutes(); in the RouteConfig.cs file in App_start folder.
Regards
Mahesh Sabnis
Comment posted by vinoth kumar on Wednesday, December 24, 2014 12:55 AM
for the me also error 404 occurs...i don't know how to config the RouteConfig.cs file in App_start folder.