Hi sani.ss501,
Check this sample. now take its reference.
Database
I have made use of the following table Customers with the schema as follows.
I have already inserted few records in the table.
You can download the database table SQL by clicking the download link below.
Download SQL file
HTML
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
<style type="text/css">
.Hide
{
display: none;
}
.Show
{
display: block;
}
</style>
</head>
<body>
<form id="form1" runat="server">
<div>
<table id="tblCustomers">
<thead>
<tr>
<th>Id</th>
<th>Name</th>
<th>Country</th>
<th>Action</th>
</tr>
</thead>
<tbody>
<tr>
<td class="CustomerId">
<span></span>
</td>
<td class="Name">
<span></span>
<input type="text" id="txtEditName" class="Hide" />
</td>
<td class="Country">
<span></span>
<select id="txtEditCountry" name="txtEditCountry" class="Hide">
<option value="United States">United States</option>
<option value="India">India</option>
<option value="France">France</option>
<option value="Russia">Russia</option>
</select>
</td>
<td>
<input type="button" id="btnEdit" value="Edit" class="Edit" />
<input type="button" id="btnUpdate" value="Update" class="Hide Update" />
<input type="button" id="btnDelete" class="Delete" value="Delete" />
<input type="button" id="btnCancel" value="Cancel" class="Hide Cancel" />
</td>
</tr>
</tbody>
</table>
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>
<script type="text/javascript">
$(function () {
BindCustomers();
});
function BindCustomers() {
$.ajax({
type: "POST",
url: "Default.aspx/GetCustomers",
data: '{}',
contentType: "application/json; charset=utf-8",
dataType: "json",
success: function (response) {
var xmlDoc = $.parseXML(response.d);
var xml = $(xmlDoc);
var customers = xml.find("Table");
var row = $("[id*=tblCustomers] > tbody tr:last-child").clone(true);
$("[id*=tblCustomers] tr").not(':has(th)').remove();
$.each(customers, function () {
var customer = $(this);
//Bind CustomerId.
$(".CustomerId", row).find("span").html($(this).find("CustomerId").text());
//Bind Name.
$(".Name", row).find("span").html($(this).find("Name").text());
$(".Name", row).find("input").val($(this).find("Name").text());
//Bind Country.
$(".Country", row).find("span").html($(this).find("Country").text());
$(".Country", row).find("input").val($(this).find("Country").text());
$("[id*=tblCustomers]").append(row);
row = $("[id*=tblCustomers] > tbody tr:last-child").clone(true);
});
}
});
}
//Edit event handler.
$("body").on("click", "[id*=tblCustomers] .Edit", function () {
var rows = $("[id*=tblCustomers] tr");
$(rows).each(function () {
var row = $(this);
$("td", row).each(function () {
if ($(this).find("input").length > 0 || $(this).find("select").length > 0) {
var span = $(this).find("span");
var input = $(this).find("input[type='text']");
var select = $(this).find("select");
input.val(span.html());
span.show();
input.hide();
select.hide();
}
});
row.find(".Edit").show();
row.find(".Delete").show();
row.find(".Update").hide();
row.find(".Cancel").hide();
});
var row = $(this).closest("tr");
$("td", row).each(function () {
if ($(this).find("input").length > 0 || $(this).find("select").length > 0) {
$(this).find("input").show();
if ($(this).attr('class') == "Country") {
$(this).find("select").show();
$(this).find("select").val($(this).find('span').html());
}
$(this).find("span").hide();
}
});
row.find(".Update").show();
row.find(".Cancel").show();
row.find(".Delete").hide();
$(this).hide();
return false;
});
//Update event handler.
$("body").on("click", "[id*=tblCustomers] .Update", function () {
var row = $(this).closest("tr");
$("td", row).each(function () {
if ($(this).find("input").length > 0 || $(this).find("select").length > 0) {
var span = $(this).find("span");
var input = $(this).find("input");
var select = $(this).find("select");
span.html(input.val());
span.show();
input.hide();
select.hide();
}
});
row.find(".Edit").show();
row.find(".Delete").show();
row.find(".Cancel").hide();
$(this).hide();
var customerId = row.find(".CustomerId").find("span").html();
var name = row.find(".Name").find("span").html();
//var country = row.find(".Country").find("span").html();
var country = row.find(".Country").find("option:selected").text();
$.ajax({
type: "POST",
url: "Default.aspx/UpdateCustomer",
data: '{customerId: ' + customerId + ', name: "' + name + '", country: "' + country + '" }',
contentType: "application/json; charset=utf-8",
dataType: "json",
success: function (r) {
BindCustomers();
}
});
return false;
});
//Cancel event handler.
$("body").on("click", "[id*=tblCustomers] .Cancel", function () {
var row = $(this).closest("tr");
$("td", row).each(function () {
if ($(this).find("input").length > 0 || $(this).find("select").length > 0) {
var span = $(this).find("span");
var input = $(this).find("input[type='text']");
var select = $(this).find("select");
input.val(span.html());
span.show();
input.hide();
select.hide();
}
});
row.find(".Edit").show();
row.find(".Delete").show();
row.find(".Update").hide();
$(this).hide();
return false;
});
//Delete event handler.
$("body").on("click", "[id*=tblCustomers] .Delete", function () {
if (confirm("Do you want to delete this record?")) {
var row = $(this).closest("tr");
var customerId = row.find("span").html();
$.ajax({
type: "POST",
url: "Default.aspx/DeleteCustomer",
data: '{customerId: ' + customerId + '}',
contentType: "application/json; charset=utf-8",
dataType: "json",
success: function (response) {
row.remove();
}
});
}
return false;
});
</script>
</div>
</form>
</body>
</html>
Namespaces
C#
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Web.Services;
VB.Net
Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient
Imports System.Web.Services
Code
C#
[WebMethod]
public static string GetCustomers()
{
string query = "SELECT CustomerId, Name, Country FROM Customers";
SqlCommand cmd = new SqlCommand(query);
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlDataAdapter sda = new SqlDataAdapter())
{
cmd.Connection = con;
sda.SelectCommand = cmd;
using (DataSet ds = new DataSet())
{
sda.Fill(ds);
return ds.GetXml();
}
}
}
}
[WebMethod]
public static string UpdateCustomer(int customerId, string name, string country)
{
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand("UPDATE Customers SET Name = @Name, Country = @Country WHERE CustomerId = @CustomerId"))
{
cmd.Parameters.AddWithValue("@CustomerId", customerId);
cmd.Parameters.AddWithValue("@Name", name);
cmd.Parameters.AddWithValue("@Country", country);
cmd.Connection = con;
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}
return "";
}
[WebMethod]
public static void DeleteCustomer(int customerId)
{
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand("DELETE FROM Customers WHERE CustomerId = @CustomerId"))
{
cmd.Parameters.AddWithValue("@CustomerId", customerId);
cmd.Connection = con;
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}
}
VB.Net
<WebMethod()>
Public Shared Function GetCustomers() As String
Dim query As String = "SELECT CustomerId, Name, Country FROM Customers"
Dim cmd As SqlCommand = New SqlCommand(query)
Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Using con As SqlConnection = New SqlConnection(constr)
Using sda As SqlDataAdapter = New SqlDataAdapter()
cmd.Connection = con
sda.SelectCommand = cmd
Using ds As DataSet = New DataSet()
sda.Fill(ds)
Return ds.GetXml()
End Using
End Using
End Using
End Function
<WebMethod()>
Public Shared Function UpdateCustomer(ByVal customerId As Integer, ByVal name As String, ByVal country As String) As String
Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Using con As SqlConnection = New SqlConnection(constr)
Using cmd As SqlCommand = New SqlCommand("UPDATE Customers SET Name = @Name, Country = @Country WHERE CustomerId = @CustomerId")
cmd.Parameters.AddWithValue("@CustomerId", customerId)
cmd.Parameters.AddWithValue("@Name", name)
cmd.Parameters.AddWithValue("@Country", country)
cmd.Connection = con
con.Open()
cmd.ExecuteNonQuery()
con.Close()
End Using
End Using
Return ""
End Function
<WebMethod()>
Public Shared Sub DeleteCustomer(ByVal customerId As Integer)
Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Using con As SqlConnection = New SqlConnection(constr)
Using cmd As SqlCommand = New SqlCommand("DELETE FROM Customers WHERE CustomerId = @CustomerId")
cmd.Parameters.AddWithValue("@CustomerId", customerId)
cmd.Connection = con
con.Open()
cmd.ExecuteNonQuery()
con.Close()
End Using
End Using
End Sub
Screenshot
