csharp

jQuery Ajax Crud: Select Insert Edit Update and Delete Using jQuery Ajax in ASP.Net C#

Here I have explained with an example, how to perform jQuery Ajax Crud: Select Insert Edit Update and Delete Using jQuery Ajax in ASP.Net C#. This is a simple jQuery Ajax crud operation using Ajax or without loading the page and binding the data in HTML Table using Ajax
This process is also known as CRUD i.e. Create, Read, Update and Delete using jQuery AJAX in ASP.Net C#.
Step by step learn this article jQuery Ajax Crud: Select Insert Edit Update and Delete Using jQuery Ajax in ASP.Net C#.

Step 1.Database Chamber:
Create Database for perform operation jQuery Ajax Crud: Select Insert Edit Update and Delete Using jQuery Ajax in ASP.Net C#.
[php]
Create database JqueryCrud
Use JqueryCurd
[/php]
Step 2: After that create a table named “tblEmployee” in a database to insert data.
[php]
Create table tblEmployee(
EmpId int primary key identity(1,1),
EmpName varchar(30),
EmpAddress varchar(20),
EmpAge int
)

[/php]
Step 3: After creating a table create the stored procedure for insert/Edit/Update/Delete data into table “tblEmployee” and also create a stored procedure for Retrieve data from the database.
[php]
/* Stored Procedure for Insert Data into tblEmployee*/
create proc sp_tblEmployee_Insert
@EmpName varchar(30),
@EmpAge int,
@EmpAddress varchar(40)
as
begin
insert into tblEmployee(EmpName,EmpAge,EmpAddress) values(@EmpName,@EmpAge,@EmpAddress)
end

/* Stored Procedure for Edit record */
create proc sp_tblEmployee_Edit
@EmpId int
as
begin
select * from tblEmployee where EmpId=@EmpId
end
/* Stored Procedure for Update Record */
create proc sp_tblEmployee_Update
@EmpId int,
@EmpName varchar(40),
@EmpAge int,
@EmpAddress varchar(30)
as
begin
update tblEmployee set EmpName=@EmpName, EmpAge=@EmpAge,EmpAddress=@EmpAddress where EmpId=@EmpId
end
/* Stored Procedure for Delete Data from tblEmployee*/
create proc sp_tblEmployee_Dlete
@EmpId int
as
begin
Delete from tblEmployee where EmpId=@EmpId
end

/* Stored Procedure for Retrieve Data from tblEmployee*/

create proc sp_tblEmployee_Select
as
begin
select * from tblEmployee
end

[/php]

Step 4.Step 1: Create a Web Application
“Start”-“All Program”–>Microsoft VS 2012
“File”–>”New”–>”Project”–>Select Visual C# Web–>ASP.NET Empty website
Give the Project Name like My project Name “JqueryCurd” or whatever you wish
Then Right click on the project then selects “Add New Item” by Default new item name “WebForm1.aspx” Page then click ok.

Step 5: After that, we need to add the jQuery Library to your project. Install the jquery library Click on “ProjectName”
–>Select “Manage NuGet Packages..”–>Select “nuget.org” from the left menu and write “jquery” on search online(ctrl+E) search box.After successful installation, the new script folder is generated in your project.
HTML Code
Step 5. Open .aspx page and write this code.This Html Markup consist three input type field and one button.

[php]
<style type="text/css">
#tbl {
font-family: "Trebuchet MS", Arial, Helvetica, sans-serif;
border-collapse: collapse;
width: 70%;
}

#tbl td, #tbl th {
border: 1px solid #ddd;
padding: 8px;
}

#tbl tr:nth-child(even){background-color: #f2f2f2;}

#tbl tr:hover {background-color: #ddd;}

#tbl th {
padding-top: 12px;
padding-bottom: 12px;
text-align: left;
background-color: #4CAF50;
color: white;
}

</style>
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<table>
<tr><td>
Name:
</td>
<td><input type="text" id="txtName" /></td>
</tr>
<tr>
<td>Age:</td>
<td><input type="text" id="txtAge" /></td>
</tr>
<tr>
<td>Address:</td>
<td><input type="text" id="txtAddress" /></td>
</tr>
<tr>
<td></td>
<td><input type="button" id="btnsubmit" value="Submit" onclick="SaveData()" /></td>
</tr>
</table>
<table id="tbl" >
<tr>
<th>Enmployee Name</th>
<th>Employee Age</th>
<th>Employee Address</th>
<th></th>
<th></th>
</tr>

</table>
</div>
</form>
</body>
</html>
[/php]

step 6:NamespacesNeed to Import this namespaces
[php]
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using Newtonsoft.Json;
using System.Web.Services;
[/php]

Step 7: Open web.config file and Establish a connection with database

Note

  • In initial catalog write Database Name
  • In data source you can simply write (.) /your server name/(local)
  • Integrated security for Authentication/Set to SSPI to make the connection with user’s Windows login

[php]
<connectionStrings>
<add name="DBCS" connectionString="data source=.; initial catalog=JqueryCrud;integrated security=true"/>
</connectionStrings>
[/php]

Key Point:
Before Perform jQuery Ajax Crud : Select Insert Edit Update and Delete Using jQuery Ajax in ASP.Net C#.First Install “NewtonSoft.Json” and add your Project. Click this below-given link step by step after successful installation you can see In Reference Folder of your project “Newton.Json” is added.
click this link to install NewtonSoftJson

Step 8: Open “WebForm1.aspx.cs” page.Put This code on “.aspx.cs” Page.
[php]
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
//Need to import following Namespaces
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using Newtonsoft.Json;
using System.Web.Services;

namespace JqueryCurd
{
public partial class WebForm1 : System.Web.UI.Page
{
//Create object of sqlconnection for interacting with database
//"DBCS" is declared in web.config file
static SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString);
protected void Page_Load(object sender, EventArgs e)
{

}
//Create the object of SqlCommand.
//A SqlCommand object allows you to query and send commands to a database
//WebMethod is used for handling following Operation
//InsertData WebMethod is used to insert data into the tblEmployee Table.
[WebMethod]
public static void InsertData(string name,int age,string address)
{
con.Open();
SqlCommand cmd = new SqlCommand("sp_tblEmployee_Insert", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@EmpName",name);
cmd.Parameters.AddWithValue("@EmpAge",age);
cmd.Parameters.AddWithValue("@EmpAddress",address);
cmd.ExecuteNonQuery();
con.Close();
}

//GetEmpData WebMethod is used to fetch data from the tblEmployee Table.

[WebMethod]
public static string GetEmpData()
{
con.Open();
string _data = "";
SqlCommand cmd = new SqlCommand("sp_tblEmployee_Select", con);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
con.Close();
if (ds.Tables[0].Rows.Count > 0)
{
_data = JsonConvert.SerializeObject(ds.Tables[0]);
}
return _data;
}

//Edit WebMethod is used to Edit data from tblEmployee Table.

[WebMethod]
public static string Edit(int Id)
{

string _data = "";
con.Open();
SqlCommand cmd = new SqlCommand("sp_tblEmployee_Edit", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@empid", Id);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
con.Close();
if (ds.Tables[0].Rows.Count > 0)
{
_data = JsonConvert.SerializeObject(ds.Tables[0]);
}
return _data;
}

//Update WebMethod accept Id, Name, Address, Age values. Based on this Id update data in the tblEmployee Table.

[WebMethod]
public static void Update(int ID, string name, int age, string address)
{

con.Open();
SqlCommand cmd = new SqlCommand("sp_tblEmployee_Update", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@EmpId",ID);
cmd.Parameters.AddWithValue("@EmpName",name);
cmd.Parameters.AddWithValue("@EmpAge",age);
cmd.Parameters.AddWithValue("@EmpAddress",address);
cmd.ExecuteNonQuery();
con.Close();

}

//Delete WebMethod is used to delete data from the tblEmployee Table.

[WebMethod]
public static void Delete(int Id)
{
con.Open();
SqlCommand cmd = new SqlCommand("sp_tblEmployee_Dlete", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@EmpId", Id);
cmd.ExecuteNonQuery();
con.Close();
}
}
}
[/php]

After that Go to your “WebForm1.aspx” page and Write this Jquery Code for jQuery Ajax Crud: Select Insert Edit Update and Delete Using jQuery Ajax in ASP.Net C#.

[php]
//Here Add Reference jQuery Library
<script src="Scripts/jquery-3.3.1.min.js"></script>
<script type="text/javascript">
$(document).ready(function(){

GetData();
});
//In url write your page name like"WebForm1.aspx" and Webmethod name "InsertData".
//Write jQuery Ajax to call WebMethod(InsertData)
function SaveData() {
if ($("#btnsubmit").val() == "Submit") {
$.ajax({
url: ‘WebForm1.aspx/InsertData’,
type: ‘post’,
contentType: ‘application/json;charset=utf-8’,
dataType: ‘json’,
data: "{name:’" + $("#txtName").val() + "’,age:’" + $("#txtAge").val() + "’,address:’" + $("#txtAddress").val() + "’}",
success: function () {
alert("Insert data Successfully");
GetData();
},
error: function () {
alert("Insert Error");
}

});

}
else {
$.ajax({
url: ‘WebForm1.aspx/Update’,
type: ‘post’,
contentType: ‘application/json;charset=utf-8’,
dataType: ‘json’,
data: "{ID:’" + idd + "’,name:’" + $("#txtName").val() + "’,age:’" + $("#txtAge").val() + "’,address:’" + $("#txtAddress").val() + "’}",
success: function () {
alert("Update data Successfully");
GetData();
},
error: function () {
alert("Update Error");
}

});
}

}
//Retreive Record

function GetData() {
$.ajax({
url: ‘WebForm1.aspx/GetEmpData’,
type: ‘post’,
contentType: ‘application/json;charset=utf-8’,
dataType: ‘json’,
data:"{}",
success: function (_data) {
_data=JSON.parse(_data.d);
$("#tbl").find("tr:gt(0)").remove();
for(var i=0;i<_data.length;i++){
$("#tbl").append(‘<tr><td>’ + _data[i].EmpName + ‘</td><td>’ + _data[i].EmpAge + ‘</td><td>’ + _data[i].EmpAddress + ‘</td><td><input type="button" id="btndelete" value="Delete" onclick="DeleteData(‘ + _data[i].EmpId + ‘)" /></td> <td><input type="button" id="btnedit" value="Edit" onclick="EditData(‘ + _data[i].EmpId + ‘)" /></td> </tr>’);
}

},
error: function () {
alert("Get Error");
}

});

}
//Edit Record
function EditData(empid) {
$.ajax({
url: ‘WebForm1.aspx/Edit’,
type: ‘post’,
contentType: ‘application/json;charset=utf-8’,
dataType: ‘json’,
data: "{Id : ‘" + empid + "’}",
success: function (_dt) {
_dt = JSON.parse(_dt.d);
$("#txtName").val(_dt[0].EmpName);
$("#txtAddress").val(_dt[0].EmpAddress);
$("#txtAge").val(_dt[0].EmpAge);

$("#btnsubmit").val("Update");
idd = empid;
},
error: function () {
alert(‘edit error !!’);
}
});
}
//Delete Record
function DeleteData(empid) {
$.ajax({
url: ‘WebForm1.aspx/Delete’,
type: ‘post’,
contentType: ‘application/json;charset=utf-8’,
dataType: ‘json’,
data: "{Id : ‘" + empid + "’}",
success: function () {
alert(‘delete success !!’);
GetData();
},
error: function () {
alert(‘delete error !!’);
}
});
}
</script>
[/php]

Finally Write this complete code on “Webform1.aspx” Page

[php]
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<script src="Scripts/jquery-3.3.1.min.js"></script>
<script type="text/javascript">
$(document).ready(function(){

GetData();
});
function SaveData() {
if ($("#btnsubmit").val() == "Submit") {
$.ajax({
url: ‘WebForm1.aspx/InsertData’,
type: ‘post’,
contentType: ‘application/json;charset=utf-8’,
dataType: ‘json’,
data: "{name:’" + $("#txtName").val() + "’,age:’" + $("#txtAge").val() + "’,address:’" + $("#txtAddress").val() + "’}",
success: function () {
alert("Insert data Successfully");
GetData();
},
error: function () {
alert("Insert Error");
}

});

}
else {
$.ajax({
url: ‘WebForm1.aspx/Update’,
type: ‘post’,
contentType: ‘application/json;charset=utf-8’,
dataType: ‘json’,
data: "{ID:’" + idd + "’,name:’" + $("#txtName").val() + "’,age:’" + $("#txtAge").val() + "’,address:’" + $("#txtAddress").val() + "’}",
success: function () {
alert("Update data Successfully");
GetData();
},
error: function () {
alert("Update Error");
}

});
}

}

function GetData() {
$.ajax({
url: ‘WebForm1.aspx/GetEmpData’,
type: ‘post’,
contentType: ‘application/json;charset=utf-8’,
dataType: ‘json’,
data:"{}",
success: function (_data) {
_data=JSON.parse(_data.d);
$("#tbl").find("tr:gt(0)").remove();
for(var i=0;i<_data.length;i++){
$("#tbl").append(‘<tr><td>’ + _data[i].EmpName + ‘</td><td>’ + _data[i].EmpAge + ‘</td><td>’ + _data[i].EmpAddress + ‘</td><td><input type="button" id="btndelete" value="Delete" onclick="DeleteData(‘ + _data[i].EmpId + ‘)" /></td> <td><input type="button" id="btnedit" value="Edit" onclick="EditData(‘ + _data[i].EmpId + ‘)" /></td> </tr>’);
}

},
error: function () {
alert("Get Error");
}

});

}

function EditData(empid) {
$.ajax({
url: ‘WebForm1.aspx/Edit’,
type: ‘post’,
contentType: ‘application/json;charset=utf-8’,
dataType: ‘json’,
data: "{Id : ‘" + empid + "’}",
success: function (_dt) {
_dt = JSON.parse(_dt.d);
$("#txtName").val(_dt[0].EmpName);
$("#txtAddress").val(_dt[0].EmpAddress);
$("#txtAge").val(_dt[0].EmpAge);

$("#btnsubmit").val("Update");
idd = empid;
},
error: function () {
alert(‘edit error !!’);
}
});
}

function DeleteData(empid) {
$.ajax({
url: ‘WebForm1.aspx/Delete’,
type: ‘post’,
contentType: ‘application/json;charset=utf-8’,
dataType: ‘json’,
data: "{Id : ‘" + empid + "’}",
success: function () {
alert(‘delete success !!’);
GetData();
},
error: function () {
alert(‘delete error !!’);
}
});
}
</script>

<style type="text/css">
#tbl {
font-family: "Trebuchet MS", Arial, Helvetica, sans-serif;
border-collapse: collapse;
width: 70%;
}

#tbl td, #tbl th {
border: 1px solid #ddd;
padding: 8px;
}

#tbl tr:nth-child(even){background-color: #f2f2f2;}

#tbl tr:hover {background-color: #ddd;}

#tbl th {
padding-top: 12px;
padding-bottom: 12px;
text-align: left;
background-color: #4CAF50;
color: white;
}

</style>
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<table>
<tr><td>
Name:
</td>
<td><input type="text" id="txtName" /></td>
</tr>
<tr>
<td>Age:</td>
<td><input type="text" id="txtAge" /></td>
</tr>
<tr>
<td>Address:</td>
<td><input type="text" id="txtAddress" /></td>
</tr>
<tr>
<td></td>
<td><input type="button" id="btnsubmit" value="Submit" onclick="SaveData()" /></td>
</tr>
</table>
<table id="tbl" >
<tr>
<th>Enmployee Name</th>
<th>Employee Age</th>
<th>Employee Address</th>
<th></th>
<th></th>
</tr>

</table>
</div>
</form>
</body>
</html>
[/php]

Scrrenshot Describe the output after running the project.

If This article helped you. Humble request to you please donate small amount only 5 Ruppes or 1 Ruppes so that I can help the needy poor people. Send amount this phonepe upi id : 8800846247@ybl or google pay 8800846247. Thank you from my bottom of heart.

Any Question and suggestion related to this article jQuery Ajax Crud Please Comment me.

2 thoughts on “jQuery Ajax Crud: Select Insert Edit Update and Delete Using jQuery Ajax in ASP.Net C#

Leave a Reply

Your email address will not be published.