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));
database is ready to use. Now we will create a webpage to receive and show dat
<table>
<tbody><tr>
<td colspan="2">
<%--//=== Here we will show error and confirmation messages.--%>
<div class="errMsg">
</div>
</td>
</tr>
<tr>
<td>
<b>Name</b>
</td>
<td>
<asp:textbox runat="server" id="txtName">
</asp:textbox></td>
</tr>
<tr>
<td>
<b>Email</b>
</td>
<td>
<asp:textbox runat="server" id="txtEmail">
</asp:textbox></td>
</tr>
<tr>
<td>
<b>Age</b>
</td>
<td>
<asp:textbox runat="server" id="txtAge">
</asp:textbox></td>
</tr>
<tr>
<td>
</td>
<td>
<input type="button" onclick="saveData()" id="btnSave" value="Save"title="Save">
<%--//==== We have hide our update button at the initial stage so that only save button is visible at startup.--%>
<input type="button" onclick="updateData()" id="btnUpdate"value="Update" title="Update" style="display: none">
<%--//=== We will use this hidden field to store id of selected record during update operation.--%>
<asp:hiddenfield id="hfSelectedRecord" runat="server">
</asp:hiddenfield></td>
</tr>
<tr>
<td colspan="2">
<%--//==== We will show our data in this div--%>
<div id="divData">
</div>
</td>
</tr>
</tbody></table>
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.
1
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.
1
<script type="text/javascript" src="myScript.js"></script>
Output: Your head section will look like this:
1
2
3
<title></title>
<script type="text/javascript" src="myScript.js"></script>
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]
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("<ul><li>Data saved successfully</li></ul>");
}
else {
$(".errMsg").append("<ul><li>Opppps something went wrong.</li></ul>");
}
$(".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 += "<li>Please enter Name.</li>";
}
if (txtEmail.length <= 0) {
errCount++;
errMsg += "<li>Please enter Email.</li>";
}
if (txtAge.length <= 0) {
errCount++;
errMsg += "<li>Please enter Age.</li>";
}
if (errCount > 0) {
$(".errMsg ul").remove()
$(".errMsg").append("<ul>" + errMsg + "</ul>");
$(".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 = "";
for (var i = 0; i <= (msg.length - 1); i++) {
var row = "";
row += '';
row += '';
row += '';
row += '';
row += '';
table += row;
}
table += '<table class="tblResult" id="tblResult"><thead> <tr><th>Name</th><th>Email</th><th>Age</th><th>Actions</th></tr></thead> <tbody><tr><td>'+ msg[i].Name + '</td><td>' + msg[i].Email + '</td><td>' + msg[i].Age + '</td><td><img src="edit.png" title="Edit record." onclick="bindRecordToEdit(' + msg[i].Id + ')"> <img src="delete.png" onclick="deleteRecord(' + msg[i].Id + ')" title="Delete record."></td></tr></tbody></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.
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("<ul><li>Data updated successfully</li></ul>");
}
else {
$(".errMsg").append("<ul><li>Opppps something went wrong.</li></ul>");
}
$(".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("<ul><li>Record successfully delete.</li></ul>");
$(".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:
No comments:
Post a Comment