Hope you have enjoyed my previous articles regarding add, edit, update and delete operations using Listview http://www.codingfusion.com/Post/ListView-Edit-Update-Delete-in-Asp-net-using-Ado and Repeater http://www.codingfusion.com/Post/Repeater-Edit-Update-Delete-in-Asp-net-using-Ado control.
In this article we will see how to perform CRUD operations like add, edit, update and delete using JQUERY, JSON, JQUERY-AJAX and entity framework in asp .net. Let's begin with creating database.
CREATE TABLE [dbo].[Student] (
[Id] INT IDENTITY (1, 1) NOT NULL,
[Name] NVARCHAR (100) NULL,
[Email] NVARCHAR (100) NULL,
[Age] NCHAR (10) NULL,
PRIMARY KEY CLUSTERED ([Id] ASC)
);
Above query will create a new table in your database like this:
Our database is ready to use. Now we will create a webpage to receive and show data.
Output will be like this:
Now we have designed our database and page. We are ready now to add JQUERY into our project.
Step1: Add this code between head tags of your page.
This will add JQUERY in your project using CDN. You can also download latest JQUERY file from internet and add to your project.
Note: If you have added JQUERY using the above code it will only work if you have internet connection.
Step2: Create a new Jquery file myScript.Js in your project to write your custom jquery functions.
Step3: Add reference of your Jquery file to your page. Add these lines between head section of your page.
Output: Your head section will look like this:
Lets create our save function in Default.aspx.cs page. To call your method from JQUERY you need to be create method of type [WebMethod]
Step1: Add Name space: using System.Web.Services;
Step2: Save Method:
//using System.Web.Services;
//==== Method to save data into database.
[WebMethod]
public static int saveData(string name, string email, string age)
{
try
{
int status = 0;
using (JsonEntities context = new JsonEntities())
{
Student obj = new Student();
obj.Name = name;
obj.Email = email;
obj.Age = age;
context.Students.AddObject(obj);
context.SaveChanges();
status = obj.Id;
}
return status;
}
catch
{
return -1;
}
}
Step3: Create a save method in your myScript.JS file to call this saveData() method.
//==== Method to save data into database.
function saveData() {
//==== Call validateData() Method to perform validation. This method will return 0
//==== if validation pass else returns number of validations fails.
var errCount = validateData();
//==== If validation pass save the data.
if (errCount == 0) {
var txtName = $("#txtName").val();
var txtEmail = $("#txtEmail").val();
var txtAge = $("#txtAge").val();
$.ajax({
type: "POST",
url: location.pathname + "Default.aspx/saveData",
data: "{name:'" + txtName + "',email:'" + txtEmail + "',age:'" + txtAge + "'}",
contentType: "application/json; charset=utf-8",
datatype: "jsondata",
async: "true",
success: function (response) {
$(".errMsg ul").remove();
var myObject = eval('(' + response.d + ')');
if (myObject > 0) {
bindData();
$(".errMsg").append("");
}
else {
$(".errMsg").append("
- Opppps something went wrong.
");
}
$(".errMsg").show("slow");
clear();
},
error: function (response) {
alert(response.status + ' ' + response.statusText);
}
});
}
}
We have used 3 methods in this save method they are:
1) validateData() method to validate our data.
//==== Method to validate textboxes
function validateData() {
var txtName = $("#txtName").val();
var txtEmail = $("#txtEmail").val();
var txtAge = $("#txtAge").val();
var errMsg = "";
var errCount = 0;
if (txtName.length <= 0) {
errCount++;
errMsg += "Please enter Name.";
}
if (txtEmail.length <= 0) {
errCount++;
errMsg += "
Please enter Email.";
}
if (txtAge.length <= 0) {
errCount++;
errMsg += "
Please enter Age.";
}
if (errCount > 0) {
$(".errMsg ul").remove()
$(".errMsg").append("
");
$(".errMsg").slideDown('slow');
}
return errCount;
}
2) bindData() method. This method will create a dynamic html table and inserts into the page to show records we have entered.
//==== Get data from database, created HTML table and place inside #divData
function bindData() {
$.ajax({
type: "POST",
url: location.pathname + "Default.aspx/getData",
data: "{}",
contentType: "application/json; charset=utf-8",
datatype: "jsondata",
async: "true",
success: function (response) {
var msg = eval('(' + response.d + ')');
if ($('#tblResult').length != 0) // remove table if it exists
{ $("#tblResult").remove(); }
var table = " | Name | Email | Age | Actions |
|---|
";
for (var i = 0; i <= (msg.length - 1); i++) {
var row = "";
row += '| ' + msg[i].Name + ' | ';
row += '' + msg[i].Email + ' | ';
row += '' + msg[i].Age + ' | ';
row += '  | ';
row += '
';
table += row;
}
table += '
';
$('#divData').html(table);
$("#divData").slideDown("slow");
},
error: function (response) {
alert(response.status + ' ' + response.statusText);
}
});
}
bindData() method will call getData() method to get data from the database. So Create this method in your Default.aspx.cs page. You will need to add namespace using System.Web.Script.Serialization;
//==== Method to fetch data from database.
//using System.Web.Script.Serialization;
[WebMethod]
public static string getData()
{
string data = string.Empty;
try
{
using (JsonEntities context = new JsonEntities())
{
var obj = (from r in context.Students select r).ToList();
JavaScriptSerializer serializer = new JavaScriptSerializer();
data = serializer.Serialize(obj);
}
return data;
}
catch
{
return data;
}
}
3) clear() Method. This method will clear all the values from textbox controls after data is successfully saved.
//==== Method to clear input fields
function clear() {
$("#txtName").val("");
$("#txtEmail").val("");
$("#txtAge").val("");
//=== Hide update button and show save button.
$("#btnSave").show();
$("#btnUpdate").hide();
}
Output: If you have followed all the steps carefully you will see output like this:
Edit operation.
This method will bind values of selected row into textbox, hides save button, show update button and stores primary key value of selected record in hiddenfield.
Step1: Code behind method in your default.aspx.cs page.
//==== Method to get values of selected record and bind in input controls for update.
[WebMethod]
public static string bindRecordToEdit(int id)
{
string data = string.Empty;
try
{
using (JsonEntities context = new JsonEntities())
{
var obj = context.Students.FirstOrDefault(r => r.Id == id);
JavaScriptSerializer serializer = new JavaScriptSerializer();
data = serializer.Serialize(obj);
}
return data;
}
catch
{
return data;
}
}
Step2: Jquery funtion:
//==== Method to bind values of selected record into input controls for update operation.
function bindRecordToEdit(id) {
$.ajax({
type: "POST",
url: location.pathname + "Default.aspx/bindRecordToEdit",
data: "{id:'" + id + "'}",
contentType: "application/json; charset=utf-8",
datatype: "jsondata",
async: "true",
success: function (response) {
var msg = eval('(' + response.d + ')');
$("#txtName").val(msg.Name);
$("#txtEmail").val(msg.Email);
$("#txtAge").val(msg.Age);
//=== store id of the selected record in hidden field so that we can use it later during
//=== update process.
$("#hfSelectedRecord").val(id);
//=== Hide save button and show update button.
$("#btnSave").hide();
$("#btnUpdate").css("display", "block");
},
error: function (response) {
alert(response.status + ' ' + response.statusText);
}
});
}
Update operation.
Step1: Code behind method in your default.aspx.cs page.
//==== Method to update data.
[WebMethod]
public static int updateData(string name, string email, string age, int id)
{
try
{
int status = 0;
using (JsonEntities context = new JsonEntities())
{
Student obj = context.Students.FirstOrDefault(r => r.Id == id);
obj.Name = name;
obj.Email = email;
obj.Age = age;
context.SaveChanges();
status = obj.Id;
}
return status;
}
catch
{
return -1;
}
}
Step2: Jquery Method.
//==== Method to update record.
function updateData() {
//==== Call validateData() Method to perform validation. This method will return 0
//==== if validation pass else returns number of validations fails.
var errCount = validateData();
//==== If validation pass save the data.
if (errCount == 0) {
var txtName = $("#txtName").val();
var txtEmail = $("#txtEmail").val();
var txtAge = $("#txtAge").val();
var id = $("#hfSelectedRecord").val();
$.ajax({
type: "POST",
url: location.pathname + "Default.aspx/updateData",
data: "{name:'" + txtName + "',email:'" + txtEmail + "',age:'" + txtAge + "',id:'" + id + "'}",
contentType: "application/json; charset=utf-8",
datatype: "jsondata",
async: "true",
success: function (response) {
$(".errMsg ul").remove();
var myObject = eval('(' + response.d + ')');
if (myObject > 0) {
bindData();
$(".errMsg").append("- Data updated successfully
");
}
else {
$(".errMsg").append("
- Opppps something went wrong.
");
}
$(".errMsg").show("slow");
clear();
},
error: function (response) {
alert(response.status + ' ' + response.statusText);
}
});
}
}
Delete Operation:
Step1: Code behind method in your default.aspx.cs page.
//==== Method to Delete a record.
[WebMethod]
public static void deleteRecord(int id)
{
try
{
using (JsonEntities context = new JsonEntities())
{
var obj = context.Students.FirstOrDefault(r => r.Id == id);
context.Students.DeleteObject(obj);
context.SaveChanges();
}
}
catch
{
}
}
Step2: Jquery Method.
//==== Method to delete a record
function deleteRecord(id) {
//=== Show confirmation alert to user before delete a record.
var ans = confirm("Are you sure to delete a record?");
//=== If user pressed Ok then delete the record else do nothing.
if (ans == true) {
$.ajax({
type: "POST",
url: location.pathname + "Default.aspx/deleteRecord",
data: "{id:'" + id + "'}",
contentType: "application/json; charset=utf-8",
datatype: "jsondata",
async: "true",
success: function (response) {
//=== rebind data to remove delete record from the table.
bindData();
$(".errMsg ul").remove();
$(".errMsg").append("- Record successfully delete.
");
$(".errMsg").show("slow");
clear();
},
error: function (response) {
alert(response.status + ' ' + response.statusText);
}
});
}
}
To show data on page load create this method in your myScript.Js file:
//==== To show data when page initially loads.
$(document).ready(function () {
bindData();
});
CSS Used:
Final Output:
