Introduction:
In this article, we'll explore how to retrieve data from a database and dynamically display it on a webpage using JSON in the form of a table. The illustration below shows the result we aim to achieve:
Understanding JSON:
JSON, or JavaScript Object Notation, is a lightweight data-interchange format that is language-independent, easy to understand, and self-describing.
Creating Users Table in the Master Database:
Before diving into the coding part, let's set up a simple Users table in the master database. This table will store user details such as ID, username, and name.
Create Table users(
@uid int,
@username varchar(25),
@name varchar(25)
)
Insert into users values('1','Username1','Name1')
Insert into users values('2','Username2','Name2')
Insert into users values('3','Username3','Name3')
Insert into users values('4','Username4','Name4')
Insert into users values('5','Username5','Name5')
Insert into users values('6','Username6','Name6')
Insert into users values('7','Username7','Name7')
Insert into users values('8','Username8','Name8')
Insert into users values('9','Username9','Name9')
HTML Design:
The HTML design includes a simple table structure where we will dynamically populate the user details using JSON.
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm2.aspx.cs" Inherits="JsonWebApplication.WebForm2" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
<!-- Add JQuery Script Code Here -->
</head>
<body>
<form id="form1" runat="server">
<table id="tballUser" style="border-collapse:collapse;">
<thead>
<tr>
<th style="background-color:#000000; color:#fff; border:1px solid #fafafa; padding:5px 10px;">ID</th>
<th style="background-color:#000000; color:#fff; border:1px solid #fafafa; padding:5px 10px;">Username</th>
<th style="background-color:#000000; color:#fff; border:1px solid #fafafa; padding:5px 10px;">Name</th>
</tr>
</thead>
<tbody></tbody>
</table>
<br />
</form>
</body>
</html>
JSON Code:
We'll utilize jQuery to make an AJAX call to a server-side method (GetAllUser). This method fetches user details from the database and returns them as JSON.
<script src="jquery-2.2.3.min.js" type="text/javascript"></script>
<script type="text/javascript">
$(document).ready(function () {
$(function () {
$.ajax({
contentType: 'application/json; charset=utf-8',
url: 'WebForm2.aspx/GetAllUser',
dataType: 'json',
method: 'post',
success: function (data) {
for (var i = 0; i < data.d.length; i++) {
var GetAllUser = $('#tballUser tbody');
//$("#divalluser").append("<table border='0'>" + "<tr>" + "<td>Username :</td>" + "<td>" + data.d[i].Username + "</td>" + "</tr>" + "<tr>" + "<td>Name :</td>" + "<td>" + data.d[i].Name + "</td>" + "</tr>" + "</table>");
GetAllUser.append('<tr><td style="border:1px solid #fafafa; padding:5px 10px;">' + data.d[i].UserID + '</td><td style="border:1px solid #fafafa; padding:5px 10px;">' + data.d[i].Username + '</td><td style="border:1px solid #fafafa; padding:5px 10px;">' + data.d[i].Name + '</td></tr>');
//$("#divdisplay").append("<table border='0'>" + "<tr>" + "<td>Username :</td>" + "<td>" + data.d[i].Username + "</td>" + "</tr>" + "<tr>" + "<td>Name :</td>" + "<td>" + data.d[i].Name + "</td>" + "</tr>" + "</table>");
}
},
error: function (result) {
alert('Error');
}
});
return false;
});
});
</script>
C# Coding: GetAllUser Method
The C# code behind includes a GetAllUser method marked as a WebMethod. This method fetches user details from the database and returns a list of user objects serialized as JSON.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.Services;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
namespace JsonWebApplication
{
public partial class WebForm2 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
[WebMethod]
public static List<User> GetAllUser()
{
List<User> listUser = new List<User>();
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["dbCon"].ConnectionString);
SqlDataAdapter da = new SqlDataAdapter("select * from users", con);
DataSet ds = new DataSet();
da.Fill(ds);
if (ds.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
User _user = new User();
_user.UserID = Convert.ToInt16(ds.Tables[0].Rows[i]["uid"]);
_user.Username = ds.Tables[0].Rows[i]["username"].ToString();
_user.Name = ds.Tables[0].Rows[i]["name"].ToString();
listUser.Add(_user);
}
}
return listUser;
}
}
}
Conclusion:
In this tutorial, we explored how to leverage JSON in ASP.NET to dynamically fetch and display user details from a database. Utilizing jQuery for the AJAX call and C# for server-side processing, we created a seamless integration that allows for efficient data retrieval and presentation on a webpage.
0 Comments