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 latest knockoutjs from this website.

Getting Started

  • Start Visual Studio
  • Create a new website
  • Provide the name and location of 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

  SELECT UserProfileId, NewMessageCount,

  NewCircleRequestCount, NewEndorsementRequestCount,

  NewNotificationsCount, NewJobNotificationsCount,

  FromProfileId,Subject,IsNew

  FROM   [dbo].[NotificationTable]

  WHERE  UserProfileId =@Userprofileid

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;

         }

    }

   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>

  
Run application

Run application