FIROZ DOT NET: Crud Operations in asp.net and JQuery Ajax And SQL Server

Step 1.Create Table Name as "Student"

SET QUOTED_IDENTIFIER ON
GO

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
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

Step 2.Create asp.net project Name as "ASPDOTNETPROJ" .

<form id="form1" runat="server">
<asp:ScriptManager ID="ScriptManager1" runat="server" EnablePageMethods="True">
</asp:ScriptManager>
<div align="center">
<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>
</div>

</form>

Step 3.Create CSS file in your project name as "CSSFILES"

body
{
font-family: Verdana;
font-size: 11px;
}

.errMsg
{
width: 200px;
text-align: left;
color: green;
font: 12px arial;
background: white;
padding: 5px;
display: none;
}

.tblResult
{
border-collapse: collapse;
width:600px;
}

.tblResult td
{
padding: 5px;
border: 1px solid blue;
width:150px;
}

.tblResult th
{
padding: 5px;
border: 1px solid red;
width:150px;
}

img
{
cursor: pointer;
}

Step 4.Add the following reference in your code behind for WEB METHOD

using System.Web.Services;

using System.Web.Script.Serialization;

Step 5. Create JavaScript file in your project name as  "myScript" the following steps

a.Right click on your project select Add->New Folder

Note:->Folder Name as Script

b.Right click on your Script folder and select Add->New Item->Goto Scripts and Select JavaScript Files Name as "myScript"

Step 6. Create the following method in JavaScript file for save

//==== 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) {
debugger
var txtName = $("#txtName").val();
var txtEmail = $("#txtEmail").val();
var txtAge = $("#txtAge").val();

$.ajax({
type: "POST",
//url: location.pathname + "./ASP/CrudWithJQueryAjax.aspx/saveData",
url:"./CrudWithJQueryAjax.aspx/saveData",
data: "{name:'" + txtName + "',email:'" + txtEmail + "',age:'" + txtAge + "'}",
contentType: "application/json; charset=utf-8",
datatype: "jsondata",
async: "true",

success: function (response) {
debugger
$(".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);
}
});
}
}

Step 6. Go to Code behind and write the following code in WEB METHOD for save

//==== 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.Add(obj);
context.SaveChanges();
status = obj.Id;
}
return status;
}
catch
{
return -1;
}

}

Step 7.Create the following method in JavaScript file for GetData

//==== Get data from database, created HTML table and place inside #divData
function bindData() {

var Name = [];
var Email = [];
var Age = [];
var Id = [];
var j;
debugger
$.ajax({
type: "POST",
//url: location.pathname + "Default.aspx/getData",
url: "./CrudWithJQueryAjax.aspx/getData",
data: "{}",
contentType: "application/json; charset=utf-8",
datatype: "jsondata",
async: "true",
success: function (response) {
debugger

var msg = eval('(' + response.d + ')');
if ($('#tblResult').length != 0) // remove table if it exists
{ $("#tblResult").remove(); }
var table = "";
var table2=""
for (var i = 0; i <= (msg.length - 1) ; i++) {
var row = "";
row += '';
row += '';
row += '';
row += '';
row += '';
table += row;
table2 += '<table class="tblResult" id="tblResult"><tbody><tr><td>' + msg[i].Name + '</td><td>' + msg[i].Email + '</td><td>' + msg[i].Age + '</td><td><img src="../Image/edit.jpg" title="Edit record." onclick="bindRecordToEdit(' + msg[i].Id + ')" style="width:25px;height:20px"> <img src="../Image/delete.png" onclick="deleteRecord(' + msg[i].Id + ')" title="Delete record." style="width:25px;height:20px" align="right"></td></tr></tbody>' + '</table>';
}
debugger
table += '<table class="tblResult" id="tblResult"><thead> <tr><th>Name</th><th>Email</th><th>Age</th><th>Actions</th></tr></thead>' + table2 + '</table>';
$('#divData').html(table);
$("#divData").slideDown("slow");

},
error: function (response) {
alert(response.status + ' ' + response.statusText);
}
});
}

Step 8. Go to Code behind and write the following code in WEB METHOD for BindData

//==== 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;

}

Step 9.Create the following method in JavaScript file for Validations

//==== Method to validate textboxes
function validateData() {

var txtName = $("#txtName").val();
var txtEmail = $("#txtEmail").val();
var txtAge = $("#txtAge").val();
var errMsg = "";
var errCount = 0;
debugger
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;
}

Step 10.Create the following method in JavaScript file for Clear the text

//==== Method to clear input fields
function clear() {
debugger
$("#txtName").val("");
$("#txtEmail").val("");
$("#txtAge").val("");

//=== Hide update button and show save button.
$("#btnSave").show();
$("#btnUpdate").hide();
}

Step 11. Create the following method in JavaScript file for show data on page load

//==== To show data when page initially loads.
$(document).ready(function () {
bindData();

});

Step 12. Create the following method in JavaScript file for Edit Record

//==== Method to bind values of selected record into input controls for update operation.
function bindRecordToEdit(id) {
$.ajax({
type: "POST",
url: "./CrudWithJQueryAjax.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);
}
});
}

Step 13. Go to Code behind and write the following code in WEB METHOD for bind record to Edit

[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;
}
}

Step 14. Create the following method in JavaScript file for update

//==== 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:"./CrudWithJQueryAjax.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);
}
});
}
}

Step 15. Go to Code behind and write the following code in WEB METHOD for update

//==== 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;
}
}

Step 16. Create the following method in JavaScript file for delete

//==== 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:"./CrudWithJQueryAjax.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);
}
});
}
}

Step 17. Go to Code behind and write the following code in WEB METHOD for delete

//==== 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.Remove(obj);
context.SaveChanges();
}
}
catch
{
}
}
}
}

Step 18.Create Entity for DbContext name as "JsonEntities"

a.Write Click on project and go to Add,Select New Item