Display SQL Data Using Knockout in ASP.Net

This article explains how to fetch and display data from SQL Server using Knockout in ASP.Net.

Knockout is a JavaScript library that helps you to create rich, responsive displays and editor user interfaces with a clean underlying data model. Read more here knockoutjs.

You can download the latest knockoutjs from this website.

Getting Started

Start Visual Studio
Create a new website
Provide the name and location of the website
Click "Next"

This is database table data.

Database table

Web. config.

<connectionStrings>
    <clear />
    <add name="ConnectionString" connectionString="Data Source=SERVER NAME;Initial Catalog=DATABASE NAME;User ID=USERID;Password=PASSWORD" providerName="System.Data.SqlClient" />
</connectionStrings>

Stored procedure

CREATE PROCEDURE [dbo].[GetNotifications] (
    @Userprofileid BIGINT
)
AS
BEGIN
    SELECT UserProfileId, NewMessageCount, NewCircleRequestCount,
           NewEndorsementRequestCount, NewNotificationsCount,
           NewJobNotificationsCount, FromProfileId, Subject, IsNew
    FROM [dbo].[NotificationTable]
    WHERE UserProfileId = @Userprofileid
END

DAL

public DataTable GetNotifications(Int64 UserProfileID)
{
    objDB = new SqlDatabase(strConnectionString);
    DataSet ds = new DataSet();
    DataTable dt = new DataTable();
    
    using (DbCommand objCMD = objDB.GetStoredProcCommand("GetNotifications"))
    {
        try
        {
            objDB.AddInParameter(objCMD, "@UserProfileId", DbType.Int64, UserProfileID);     
            ds = objDB.ExecuteDataSet(objCMD);
            dt = ds.Tables[0];
        }
        catch (Exception ex)
        {
            ds = null;
            dt = null;
            EventLog objLog = new EventLog();
            objLog.LogError(ex);
            throw;
        }
    }
    
    return dt;
}

BAL

public DataTable GetNotifications(Int64 UserProfileID)
{
    UserActivityDAL _objUserMessage = new UserActivityDAL();
    
    try
    {
        DataTable dtRecord = _objUserMessage.GetNotifications(UserProfileID);
        return dtRecord;
    }
    catch (Exception)
    {
        _objUserMessage = null;
        throw;
    }
}

Handler

private string _strResponse = string.Empty;

public void ProcessRequest(HttpContext context)
{
    _strResponse = "ERROR";

    try
    {
        switch (HttpContext.Current.Request["StrMethod"]?.ToUpper())
        {
            case "GETNOTIFICATION":
                _strResponse = GetAllNotification();
                break;
        }
    }
    catch (Exception)
    {
        throw;
    }
}

private string GetAllNotification()
{
    UserActivity _objUserMessage = new UserActivity();
    string strResponse = string.Empty;

    try
    {
        DataTable dt = _objUserMessage.GetNotifications(Int64.Parse(HttpContext.Current.Request.QueryString["ProfileID"]));
        strResponse = Newtonsoft.Json.JsonConvert.SerializeObject(dt);
    }
    catch (Exception)
    {
        throw;
    }

    return strResponse;
}

UI

<head runat="server">
    <title></title>
    <script src="Scripts/jquery-1.8.3.min.js" type="text/javascript"></script>
    <script src="Scripts/knockout-3.0.0.js" type="text/javascript"></script>
    <script type="text/javascript">
        var SiteUrl = '<%= ResolveUrl("~") %>';
        $(document).ready(function () {
            GetAllNotification();
        });

        function GetAllNotification() {
            var NotificationProfileId = 61769;
            viewModel = {
                lookupCollection: ko.observableArray()
            };
            var StrMethod = "GETNOTIFICATION";
            debugger;
            $.ajax({
                type: "GET",
                url: SiteUrl + "Handlers/MessageHandler.ashx?StrMethod=" + StrMethod,
                async: false,
                contentType: "application/json; charset=utf-8",
                dataType: "json",
                success: function (response) {
                    if (response != "") {
                        $(response).each(function (index, element) {
                            viewModel.lookupCollection.push(element);
                        });
                        ko.applyBindings(viewModel);
                    }
                }
            });
        }
    </script>
</head>
<body>
    <form id="form1" runat="server">
        <div>
            <h1>Knockout Notification Sample</h1>
            <table>
                <tbody data-bind="foreach: lookupCollection">
                    <tr>
                        <td><b>Message</b></td>
                        <td data-bind="text: NewMessageCount"></td>
                        <td><b>Circle Requests</b></td>
                        <td data-bind="text: NewCircleRequestCount"></td>
                        <td><b>Jobs Notifications</b></td>
                        <td data-bind="text: NewJobNotificationsCount"></td>
                        <td><b>Notifications</b></td>
                        <td data-bind="text: NewNotificationsCount"></td>
                    </tr>
                </tbody>
            </table>
        </div>
    </form>
</body>

Getting Started

Web. config.

Stored procedure

DAL

BAL

Handler

UI

Run application