Tabular Data Using jQuery, ASP.NET Web Services and JSON

AJAX provides a powerful functionality to your page with a very light fingerprint. Today we will see how to transfer JSON data from ASP.NET asmx web service to web page and transform it in tabular format using jQuery AJAX call. Just to give you an idea, here is what our end result will look like:

Let's get started with creating a class for holding data from database and we will mark it as serializable so it can be sent on wire.

// Create a class name Product and mark it as serializable

[Serializable]

public class Product

{

// Three public properties for holding product data

public int ProductID { get; set; }

public string Name { get; set; }

public string ProductNumber { get; set; }

}

 

After this we will add following code in our web service:

 

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.Services;

using System.Data.SqlClient;

using System.Web.Configuration;

using System.Data;

 

[WebService(Namespace = "http://tempuri.org/")]

[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]

// To allow this Web Service to be called from script.

[System.Web.Script.Services.ScriptService]

public class ADWDWebService : System.Web.Services.WebService {

// Create GetProduct method, which will

// return product collection of list

[WebMethod]

public List<Product> GetProducts()

{

List<Product> myProducts = new List<Product>();

using (SqlConnection conn=new SqlConnection(WebConfigurationManager.ConnectionStrings["ADWDConnectionString1"].ConnectionString))

{

//Fetching data from Adventure works database

string sqlString = "Select top(10) ProductID,Name,ProductNumber from SalesLT.Product";

using (SqlCommand cmd=new SqlCommand(sqlString,conn))

{

cmd.CommandType = System.Data.CommandType.Text;

conn.Open();

SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

while (rdr.Read())

{

Product prod = new Product();

prod.ProductID = Convert.ToInt32(rdr["ProductID"]);

prod.Name=rdr["Name"].ToString();

prod.ProductNumber=rdr["ProductNumber"].ToString();

myProducts.Add(prod);

}

conn.Close();

return myProducts;

}

}

}

 

 

}

 

On web page, we will create a button to make ajax call (if you want you can do this on page load as well), and a empty HTML table to append our final result:

Style for formatting our HTML table:(note: For demo purposes I have added this on page but it is recommended to use separate CSS file)

<style type="text/css">

*

{

font-family:Georgia;

}

#Button1

{

background-color:lightblue;

border:dotted 1px Gray;

text-transform:capitalize;

}

table

{

border-collapse:collapse;

}

table td,th

{

border:solid 1px Gray;

padding:10px;

text-align:left;

color:Gray;

}

table th

{

background-color:Gray;

color:White;

}

 

</style>

aspx file code:

 

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

 

<!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">

<script type="text/javascript" src="http://code.jquery.com/jquery-latest.min.js"></script>

 

<script type="text/javascript">

$('document').ready(function() {

 

$("#Button1").click(function() {

 

var responseText = " <thead><tr><th>Name</th><th>Product ID</th><th>Product Number</th></tr></thead> <tbody >";

$.ajax({

type: "POST",

url: "ADWDWebService.asmx/GetProducts",

contentType: "application/json",

async: false,

success: function(data) {

$.map(data.d, function(item) {

responseText += '<tr><td>' + item.Name + '</td><td>' + item.ProductID + '</td><td>' + item.ProductNumber + '</td></tr>';

});

responseText += " </tbody>";

$(responseText).appendTo('#ServerResponse');

 

}

});

});

});

 

</script>

</head>

<body>

<form id="form1" runat="server">

<input id="Button1" type="button" value="get data" />

<table id="ServerResponse">

 

</table>

</form>

</body>

</html>

 

Final result:

Tags: , , ,

AJAX | ASP.NET | jQuery