Select, Update, Highlight using JSON, ASPNET WebService and jQuery

jQuery AJAX can improve application use and response drastically for various reasons as most of the time server is dealing with small segment of the page and producing results just for that segment, this way server can accept more requests. Select, insert, update are some of the very common tasks a data driven web application performs. With the use of AJAX these operations become more easy to handle.

Today we will look at how to bring data on page using JSON and then update it using JSON, all this with the help of ASP.NET web services and jQuery.

Screenshot:

Steps:

  1. Call web service via jQuery AJAX to return JSON results from Product table
  2. Select record that needs to be updated and populate corresponding textboxes
  3. Calling update web method on button click to update record
  4. Once updated, highlight updated record by changing its background color for few seconds and then revert it back to its normal background color

To get started: Create a new website and add ASMX file with name "ProductsLIst.asmx" and add following code:

Note: Make sure to uncomment/add "[System.Web.Script.Services.ScriptService]" in order to enable service callable from JavaScript.

 

[WebMethod]

public List<Products> getProducts()

{

List<Products> products = new List<Products>();

string connectionString=WebConfigurationManager.ConnectionStrings["sampleConnString"].ConnectionString;

using (SqlConnection con=new SqlConnection(connectionString))

{

SqlCommand cmd = new SqlCommand();

cmd.Connection = con;

cmd.CommandType = CommandType.Text;

cmd.CommandText = "Select ProductID,Name,StandardCost,ProductNumber from Products";

con.Open();

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

while (rdr.Read())

{

Products prod = new Products();

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

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

prod.StandardCost = Convert.ToDouble(rdr["StandardCost"]);

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

products.Add(prod);

}

con.Close();

rdr = null;

cmd = null;

}

return products;

}

 

[WebMethod]

public string UpdateProductData(string pid, string pName, string pCost, string pNumber)

{

string connectionString = WebConfigurationManager.ConnectionStrings["sampleConnString "].ConnectionString;

using (SqlConnection connection=new SqlConnection(connectionString))

{

SqlCommand cmd = new SqlCommand();

cmd.Connection = connection;

cmd.CommandType = CommandType.Text;

cmd.CommandText = string.Format("Update Products set Name='{0}',StandardCost={1},ProductNumber='{2}' where ProductID={3}",pName,Convert.ToDouble(pCost),pNumber,Convert.ToInt32(pid));

connection.Open();

cmd.ExecuteNonQuery();

connection.Close();

cmd = null;

}

return pid;

}

 

}

 

 

ASPX Code:

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

 

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

<script src="http://ajax.aspnetcdn.com/ajax/jQuery/jquery-1.7.1.min.js" type="text/javascript"></script>

<script src="http://ajax.microsoft.com/ajax/jquery.templates/beta1/jquery.tmpl.js" type="text/javascript" ></script>

<script src="Scripts/jquery-ui-1.8.16.custom.min.js" type="text/javascript"></script>

<script type="text/javascript">

$(document).ready(function () {

//Function call to bind click event

GetDATA();

 

});

 

function GetDATA() {

$('#btnGetProducts').click(function () {

// function call to web service

ProductData();

});

}

 

function tblEvent(prodid) {

$('table>tbody>tr>td').each(function (i) {

$(this).click(function () {

var tdHtml = $(this).parent().html();

var arr = new Array();

var j = 0;

$(tdHtml + '>td').each(function (i) {

if ($(this).html() != null) {

arr[j] = $(this).html();

j = j + 1;

}

 

});

$('#lblNumber').text(arr[0]);

$('#prodName').val(arr[1]);

$('#prodCost').val(arr[2]);

$('#prodNumber').val(arr[3]);

 

updateRecord(arr[0]);

});

});

if (prodid != null) {

$('table>tbody>tr>td:contains(' + prodid + ')').parent().animate({ backgroundColor: '#FFA500' }, 1000).animate({ backgroundColor: '#FFFFFF' }, 1000);

}

}

 

function updateRecord(pid) {

$('#btnUpdate').unbind();

$('#btnUpdate').click(function () {

$.ajax({

type: "POST",

url: "ProductsLIst.asmx/UpdateProductData",

data: "{'pid':'" + pid + "', 'pName':'" + $('#prodName').val() + "', 'pCost': '" + $('#prodCost').val() + "', 'pNumber':'" + $('#prodNumber').val() + "'}",

dataType: "json",

contentType: "application/json",

async: false,

success: function (message) {

ProductData(message.d);

 

},

error: function () {

alert("Some error occured!");

 

}

});

});

 

}

 

function ProductData(prodid) {

$.ajax({

type: "POST",

url: "ProductsLIst.asmx/getProducts",

contentType: "application/json; charset=utf-8",

dataType: "json",

success: function (message) {

$('#tblProducts').empty();

$('#ProductTemplate').tmpl(message.d).appendTo('#tblProducts');

tblEvent(prodid);

},

error: function () {

alert("Some error occured!");

}

});

 

}

 

</script>

<script id="ProductTemplate" type="text/x-jquery-tmpl">

<tr>

<td>${ProductID}</td>

<td>${Name}</td>

<td>${StandardCost}</td>

<td>${ProductNumber}</td>

</tr>

</script>

</head>

<body>

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

<div>

<input id="btnGetProducts" type="button" value="Get Products" />

<table border="1" cellpadding="2" cellspacing="3">

<thead>

<tr>

<th>

Product ID

</th>

<th>

Product Name

</th>

<th>

Product Cost

</th>

<th>

Product Number

</th>

</tr>

</thead>

<tbody id="tblProducts">

 

</tbody>

</table>

</div>

<div>

<fieldset>

<legend>Update Records for Product Number <span id="lblNumber"></span></legend>

Product Name: <input id="prodName" type="text" /><br />

Product Cost: <input id="prodCost" type="text" /> <br />

Product Number: <input id="prodNumber" type="text" /> <br />

<input id="btnUpdate" type="button" value="Update record" />

</fieldset>

</div>

</form>

</body>

</html>

 

Tags: , , , , ,

AJAX | ASP.NET | jQuery