Data binding in ASP .NET using AJAX & SQL Database

-
This post is illustrate the process of data sharing from server to client using AJAX.

There are lots of advantages of using AJAX. The Main advantage of using AJAX is we just need to transfer data which is required. For example when HTML page loaded then for each subsequent operation (Excluding the redirect) we don't need to reload page again and again.

Let's see how this can be implemented in ASP .NET Webform as well as ASP .NET MVC.

Created simple product table, we will be retrieving data from this table only.


I am using ADO.NET for database connection.

We will use JQuery library to make an ajax call to the server.


WebForm

In case of WebForm, we will create WebMethod which will be called using AJAX.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web.Script.Serialization;
using System.Web.Script.Services;
using System.Web.Services;
namespace Webform
{
public partial class Default : System.Web.UI.Page
{
[WebMethod()]
[ScriptMethod(UseHttpGet = true)]
public static string GetData()
{
/*It's not good practice to put connection string here. Always put connection string in Web.Config*/
var con = new SqlConnection("Data Source=MAYUR\\SQLEXPRESS;Initial Catalog=Demo;Integrated Security=True");
var command = new SqlCommand("select * from products", con);//Not good practice,use services/repository pattern instead along with stored procedures
var sqlDataAdapter = new SqlDataAdapter(command);
var dataset = new DataSet();
sqlDataAdapter.Fill(dataset);
var jsSerializer = new JavaScriptSerializer();
var tableObject = dataset.Tables[0].AsEnumerable().Select(p => new
{
ProductID = p["ProductID"],
ProductName = p["ProductName"],
Price = p["Price"],
StockQty = p["StockQty"],
Status = Convert.ToBoolean(p["isActive"] ?? 0) ? "Active" : "Inactive"
});
return jsSerializer.Serialize(tableObject);
}
}
}
view raw Default.aspx.cs hosted with ❤ by GitHub

On Client side above webmethod is called using JQuery AJAX.

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="Webform.Default" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<table border="1" style="border-collapse: collapse">
<thead>
<tr>
<th>Product Id</th>
<th>Product Name</th>
<th>Price</th>
<th>Stock</th>
<th>Status</th>
</tr>
</thead>
<tbody>
</tbody>
</table>
</div>
</form>
<script src="https://code.jquery.com/jquery-3.3.1.min.js"></script>
<script>
$(function () {
$.ajax({
url: "Default.aspx/GetData",
type: "GET",
contentType: "application/json;charset=utf-8",
dataType: "json",
success: function (result) {
var data = JSON.parse(result.d);
$.each(data, function (i, v) {
$("tbody").append(
"<tr>" +
"<td> " + v.ProductID + "</td > " +
"<td> " + v.ProductName + "</td > " +
"<td> " + v.Price + "</td > " +
"<td> " + v.StockQty + "</td > " +
"<td> " + v.Status + "</td > " +
"</tr>"
);
})
}
});
})
</script>
</body>
</html>
view raw Default.aspx hosted with ❤ by GitHub
The result is


MVC
In case on MVC, we will create Method in controller which will return JsonResult.

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Web.Script.Serialization;
namespace MVC.Controllers
{
public class DefaultController : Controller
{
// GET: Default
public ActionResult Index()
{
return View();
}
[HttpGet]
public ActionResult GetData()
{
/*It's not good practice to put connection string here. Always put connection string in Web.Config*/
var con = new SqlConnection("Data Source=MAYUR\\SQLEXPRESS;Initial Catalog=Demo;Integrated Security=True");
var command = new SqlCommand("select * from products", con);//Not good practice,use services/repository pattern instead along with stored procedures
var sqlDataAdapter = new SqlDataAdapter(command);
var dataset = new DataSet();
sqlDataAdapter.Fill(dataset);
var tableObject = dataset.Tables[0].AsEnumerable().Select(p => new
{
ProductID = p["ProductID"],
ProductName = p["ProductName"],
Price = p["Price"],
StockQty = p["StockQty"],
Status = Convert.ToBoolean(p["isActive"] ?? 0) ? "Active" : "Inactive"
});
return Json(tableObject, JsonRequestBehavior.AllowGet);
}
}
}
view raw DefaultController.cs hosted with ❤ by GitHub
On Client side in Razor it will be called using JQuery AJAX.

@{
ViewBag.Title = "Index";
}
<table class="table table-bordered table-condensed">
<thead>
<tr>
<th>Product Id</th>
<th>Product Name</th>
<th>Price</th>
<th>Stock</th>
<th>Status</th>
</tr>
</thead>
<tbody></tbody>
</table>
<script src="https://code.jquery.com/jquery-3.3.1.min.js"></script>
<script>
$(function () {
$.ajax({
url: "Default/GetData",
type: "GET",
contentType: "application/json;charset=utf-8",
dataType: "json",
success: function (result) {
$.each(result, function (i, v) {
$("tbody").append(
"<tr>" +
"<td> " + v.ProductID + "</td > " +
"<td> " + v.ProductName + "</td > " +
"<td> " + v.Price + "</td > " +
"<td> " + v.StockQty + "</td > " +
"<td> " + v.Status + "</td > " +
"</tr>"
);
})
}
});
})
</script>
view raw index.cshtml hosted with ❤ by GitHub
The result is


For any queries comment below.

Comments

Post a Comment

Popular posts from this blog

Memoization: Speed up expensive functions in ASP .NET

-
Image
This article is about Optimizing ASP .NET server side execution using Memoization technique. Before understanding Memoization, let’s discuss the simple but time consuming scenario first. Consider the simple example in ASP .NET Web-form, I want to create Table control populated with list of students and bind that control to asp:panel. The reason to consider this example is it’s really simple but time consuming when data to be populated is increased. Obviously we can populate gridview which in-turn will be rendered as table, but to demonstrate the memoization I’m going to populate data manually. Normally we will create Table WebControl and add TableRow WebControl for each student. First we want simple student entity class,which is as follows. public class Student { public string name { get ; set ; } public string description { get ; set ;} } Then added some method to get TableRow let’s say dataToRow, which is wrapped in HTMLtable ...
Read more

LINQ :Querying in-memory collections/objects in ASP .NET

-
Image
  This article is about Querying in-memory collection/objects in ASP .NET(C#) Using LINQ. NOTE:"objects" in this article refers to any in-memory collection like array,list etc or class objects.   LINQ (Language Integrated Query) introduced from .NET 3.5 provides very important programming features to developers. Using LINQ developer can perform query on in-memory objects data,just like we query in database like SQL Server. All developers may face scenario where they want to query in-memory objects like selecting data from DataTable according to specific criteria. This also avoids to write database query/stored procedures for each and every criteria if the data retrieved from database. We can write the same query in C# for in-memory objects thanks to LINQ. Query can be performed only on objects which implements IEnumerable<T>. Here is simple LINQ query: int [] marks = { 20 , 55 , 34 , 89 , 70 , 60 }; var over60 = from m in marks where m >= 60...
Read more