In this article i have explain how to insert data into database using ajax and jquery, update data using ajax jquery, delete partcular row using ajax jquery and display data using ajax jquey.
Create the test database into sql server and create the table.
create tblProduct(
id int identity(1,1) primary key,
title nvarchar(200),
code nvarchar(200),
quantity int,
price int,
decription nvarchar(200),
image nvarchar(200),
);
Create the product class for get and set product attributes
public class Product
{
public int id { get; set; }
public string title { get; set; }
public string code { get; set; }
public int quantity { get; set; }
public int price { get; set; }
public string decription { get; set; }
public string image { get; set; }
}
Create productBL class for performing business logic
public class ProductBL
{
public static string StringCon = null;
SqlConnection conn = null;
public ProductBL()
{
StringCon = ConfigurationSettings.AppSettings["ConnectionString"];
conn = new SqlConnection(StringCon);
}
public bool insertProduct(Product p)
{
try
{
conn.Open();
string sql = "insert into tblProduct(" +
"title," +
"code," +
"quantity," +
"price," +
"decription," +
"image" +
") values(" +
"@title," +
"@code," +
"@quantity," +
"@price," +
"@decription," +
"@image" +
")";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@title", p.title);
cmd.Parameters.AddWithValue("@code", p.code);
cmd.Parameters.AddWithValue("@quantity", p.quantity);
cmd.Parameters.AddWithValue("@price", p.price);
cmd.Parameters.AddWithValue("@decription", p.decription);
cmd.Parameters.AddWithValue("@image", p.image);
int i = cmd.ExecuteNonQuery();
if(i>0)
{
return true;
}
} catch(Exception ex)
{
} finally
{
conn.Close();
}
return false;
}
public bool updateProduct(Product p)
{
try
{
conn.Open();
string sql = "update tblProduct set " +
"title=@title," +
"code=@code," +
"quantity=@quantity," +
"price=@price," +
"decription=@decription," +
"image=@image where id=@id";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@title", p.title);
cmd.Parameters.AddWithValue("@code", p.code);
cmd.Parameters.AddWithValue("@quantity", p.quantity);
cmd.Parameters.AddWithValue("@price", p.price);
cmd.Parameters.AddWithValue("@decription", p.decription);
cmd.Parameters.AddWithValue("@image", p.image);
cmd.Parameters.AddWithValue("@id", p.id);
int i = cmd.ExecuteNonQuery();
if (i > 0)
{
return true;
}
}
catch (Exception ex)
{
}
finally
{
conn.Close();
}
return false;
}
public List<Product> GetProducts()
{
List<Product> pList = new List<Product>();
try
{
conn.Open();
string sql = "select * from tblProduct";
SqlCommand cmd = new SqlCommand(sql, conn);
SqlDataReader sRd = cmd.ExecuteReader();
if (sRd.HasRows)
{
while(sRd.Read())
{
Product p = new Product();
p.id = (int)sRd["id"];
p.title = sRd["title"].ToString();
p.code = sRd["code"].ToString();
p.quantity =(int)sRd["quantity"];
p.price =(int)sRd["price"];
p.decription = sRd["decription"].ToString();
p.image = sRd["image"].ToString();
pList.Add(p);
}
return pList;
}
} catch(Exception ex)
{
} finally
{
conn.Close();
}
return pList;
}
public Product productDetails(int pId)
{
Product p = new Product();
try
{
conn.Open();
string sql = "select * from tblProduct where id="+pId+"";
SqlCommand cmd = new SqlCommand(sql, conn);
SqlDataReader sRd = cmd.ExecuteReader();
if (sRd.HasRows)
{
sRd.Read();
p.id = (int)sRd["id"];
p.title = sRd["title"].ToString();
p.code = sRd["code"].ToString();
p.quantity = (int)sRd["quantity"];
p.price = (int)sRd["price"];
p.decription = sRd["decription"].ToString();
p.image = sRd["image"].ToString();
}
}
catch (Exception ex)
{
}
finally
{
conn.Close();
}
return p;
}
public bool deleteProduct(int pId)
{
try
{
conn.Open();
string sql = "delete from tblProduct where id="+pId+"";
SqlCommand cmd = new SqlCommand(sql, conn);
int i = cmd.ExecuteNonQuery();
if(i>0)
{
return true;
}
} catch(Exception ex)
{
} finally
{
conn.Close();
}
return false;
}
}
Add the asp page and paste the following code.
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>CURD Operation In Asp.net Using Ajax And Jquery </title>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<link rel="icon" href="icon.png" type="image/gif" sizes="16x16"/>
<script src="js/CURD-Ajax-Jquery.js"></script>
<link rel="stylesheet" href="css/bootstrap.min.css"/>
<style>
.input-box{
width: 100%;
padding: 1rem;
border-radius: 5px;
margin: .5rem;
border:1px solid #ddd;
}
.btn_{
padding: 1rem 3rem;
border: 1px solid;
border-radius: 5px;
margin: .5rem .5rem;
}
</style>
</head>
<body>
<div class="container">
<div class="row">
<div class="col-lg-12">
<div class="panel-heading">
<h2>Add Product Details</h2>
<asp:Label ID="lblWarning" runat="server" style="color:red;"></asp:Label>
<input type="hidden" id="txtpId" />
</div>
</div>
</div>
<div class="row">
<div class="col-lg-6">
<input type="text" id="txtTitle" placeholder="Title" class="input-box" />
</div>
<div class="col-lg-6">
<input type="text" id="txtCode" placeholder="Code" class="input-box" />
</div>
<div class="col-lg-6">
<input type="text" id="txtQty" placeholder="Quantity" class="input-box"/>
</div>
<div class="col-lg-6">
<input type="text" id="txtPrice" placeholder="Price" class="input-box"/>
</div>
<div class="col-lg-6">
<input type="text" id="txtDescription" placeholder="Description" class="input-box"/>
</div>
</div>
<div class="row">
<div class="col-lg-6">
<input type="submit" id="btnSave" class="btn_ btn-primary" title="Save" value="Save" />
<input type="submit" id="btnUpdate" class="btn_ btn-primary" title="Update" value="Update" />
</div>
</div>
<div class="row">
<div class="col-lg-12">
<div id="productList">
</div>
</div>
</div>
</div>
</body>
</html>
C# Code
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Script.Serialization;
using System.Web.UI;
using System.Web.UI.WebControls;
namespace Tutorials
{
public partial class CURD_Using_Ajax_Jquery : System.Web.UI.Page
{
public static Product p = null;
public static ProductBL pBL = new ProductBL();
public static List<Product> pList = new List<Product>();
protected void Page_Load(object sender, EventArgs e)
{
}
[System.Web.Services.WebMethod]
public static string insertProduct(string pTitle, string pCode,string pPrice,string pQty,string pDescription)
{
p = new Product();
p.title = pTitle;
p.code = pCode;
p.price = Int32.Parse(pPrice);
p.quantity =Int32.Parse(pQty);
p.decription = pDescription;
p.image = "";
if (pBL.insertProduct(p))
{
//;//projectDetailsMessage
return "1";
}
else
{
return "0";
}
}
[System.Web.Services.WebMethod]
public static string updateProduct(int pId, string pTitle, string pCode, string pPrice, string pQty, string pDescription)
{
p = new Product();
//p.id =Int32.Parse(pId);
p.id = pId;
p.title = pTitle;
p.code = pCode;
p.price = Int32.Parse(pPrice);
p.quantity = Int32.Parse(pQty);
p.decription = pDescription;
p.image = "";
if (pBL.updateProduct(p))
{
//;//projectDetailsMessage
return "1";
}
else
{
return "0";
}
}
[System.Web.Services.WebMethod]
public static string deleteProduct(int Id)
{
if (pBL.deleteProduct(Id))
{
return "1";
}
return "0";
}
[System.Web.Services.WebMethod]
public static string getProducts()
{
pList = pBL.GetProducts();
var jsonSerializer = new JavaScriptSerializer();
string data = jsonSerializer.Serialize(pList);
return data;
}
[System.Web.Services.WebMethod]
public static string getProductById(string Id)
{
int id = Int32.Parse(Id);
p = pBL.productDetails(id);
var jsonSerializer = new JavaScriptSerializer();
string data = jsonSerializer.Serialize(p);
return data;
}
}
}
Create the javascript file and paste the following code.
getProducts();
$(function () {
$("#btnUpdate").hide();
$("#btnSave").click(function () {
insertRecord();
});
$("#btnUpdate").click(function () {
updateRecord();
$("#btnUpdate").hide();
$("#btnSave").show();
});
$(".edit").click(function () {
// clearTextBox();
$("#btnSave").hide();
$("#btnUpdate").show();
var id = $.trim($(this).attr("pId"));
getProductById(id);
});
$(".delete").click(function () {
var id = $.trim($(this).attr("pId"));
deleteRecord(id);
});
});
//Clear All Text Box
function clearTextBox() {
$("#txtTitle").val("");
$("#txtCode").val("");
$("#txtQty").val("");
$("#txtPrice").val("");
$("#txtDescription").val("");
}
//Insert Record
function insertRecord() {
var pTitle = $("#txtTitle").val();
var pCode = $("#txtCode").val();
var pQty = $("#txtQty").val();
var pPrice = $("#txtPrice").val();
var pDescription = $("#txtDescription").val();
if (pTitle === "") {
alert("Product title are required!");
return;
}
if (pCode === "") {
alert("Product code are required!");
return;
}
if (pQty === "") {
alert("Product Quantity are required!");
return;
}
if (pPrice === "") {
alert("Product price are required!");
return;
}
if (pDescription === "") {
alert("Product description are required!");
return;
}
var obj = {};
obj.pTitle = pTitle;
obj.pCode = pCode;
obj.pPrice = pPrice;
obj.pQty = pQty;
obj.pDescription = pDescription;
$.ajax({
url: 'CURD-Using-Ajax-Jquery.aspx/insertProduct',
type: "POST",
contentType: "application/json; charset=utf-8",
dataType: "json",
data: JSON.stringify(obj),
success: function (d) {
getProducts();
clearTextBox();
},
error: function (xhr, textStatus, errorThrown) {
console.log("error: " + errorThrown);
}
});
}
//update record
function updateRecord() {
var pId = $("#txtpId").val();
var pTitle = $("#txtTitle").val();
var pCode = $("#txtCode").val();
var pQty = $("#txtQty").val();
var pPrice = $("#txtPrice").val();
var pDescription = $("#txtDescription").val();
if (pTitle === "") {
alert("Product title are required!");
return;
}
if (pCode === "") {
alert("Product code are required!");
return;
}
if (pQty === "") {
alert("Product Quantity are required!");
return;
}
if (pPrice === "") {
alert("Product price are required!");
return;
}
if (pDescription === "") {
alert("Product description are required!");
return;
}
var obj = {};
obj.pId = pId;
obj.pTitle = pTitle;
obj.pCode = pCode;
obj.pPrice = pPrice;
obj.pQty = pQty;
obj.pDescription = pDescription;
$.ajax({
url: 'CURD-Using-Ajax-Jquery.aspx/updateProduct',
type: "POST",
contentType: "application/json; charset=utf-8",
dataType: "json",
data: JSON.stringify(obj),
success: function (d) {
getProducts();
clearTextBox();
},
error: function (xhr, textStatus, errorThrown) {
console.log("error: " + errorThrown);
}
});
}
//delete record
function deleteRecord(id) {
$.ajax({
url: 'CURD-Using-Ajax-Jquery.aspx/deleteProduct',
data: "{ 'Id': '" + id + "'}",
type: "POST",
contentType: "application/json; charset=utf-8",
dataType: "json",
success: function (response) {
getProducts();
},
error: function (data, status, jqXHR) { alert(jqXHR); }
});
}
//get all records
function getProducts() {
$.ajax({
type: "POST",
url: "CURD-Using-Ajax-Jquery.aspx/getProducts",
data: '{}',
contentType: "application/json; charset=utf-8",
dataType: "json",
success: function OnSuccess(response) {
var parsed = $.parseJSON(response.d);
var html = "";
html += "<table style='width:100%;padding:1rem;'><tr><th>Title</th><th>Code</th><th>Price</th><th>Action</th></tr>";
$.each(parsed, function (i, jsondata) {
html += "<tr><td>" + jsondata.title + "</td><td>" + jsondata.code + "</td><td>" + jsondata.price + "</td><td><a href='javascript:void(0);' pId='" + jsondata.id + "' class='edit'>Edit</a> || <a href='javascript:void(0);' pId='" + jsondata.id + "' class='delete'>Delete</a> </td></tr>";
});
html += "</table>";
$("#productList").html(html);
},
failure: function (response) {
alert(response.d);
}
});
}
//get particular record
function getProductById(id) {
$.ajax({
url: 'CURD-Using-Ajax-Jquery.aspx/getProductById',
data: "{ 'Id': '" + id + "'}",
type: "POST",
contentType: "application/json; charset=utf-8",
dataType: "json",
success: function (response) {
var p = $.parseJSON(response.d);
$("#txtpId").val(p.id);
$("#txtTitle").val(p.title);
$("#txtCode").val(p.code);
$("#txtQty").val(p.quantity);
$("#txtPrice").val(p.price);
$("#txtDescription").val(p.decription);
},
error: function (data, status, jqXHR) { alert(jqXHR); }
});
}
