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]()