Insert
Delete Update Retrieve data with Sql Server using jQuery AJAX and Entity Framework in ASP.Net...
Note: I will explain in this topic How to
save, delete, update, select data in Sql Server database using Jquery Ajax and
Entity Framework in Asp.Net and and bind all data in table with each event on a
single page in Asp.Net .In this topic we will not take any server side control like
Button and TextBox of Asp.Net, We will take html control because html control
are lightweight control .
-- query for
Sql Server………..
create database test
use test
create table Student
(
RollNo int primary key,
Name varchar(50),
Gender varchar(50),
Course varchar(50)
)
Note: First we create a
Web From in Asp.Net but we will work on html controls not server side controls
and download Jury Files from www.jquery.com
Code for Default.aspx page………….
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
<title></title>
<style type="text/css">
.auto-style1
{
width: 100%;
}
div,table,th
{
font-family:Verdana;
}
td,table,th,td
{
border:1px solid black;
border-collapse:collapse;
}
</style>
<script src="Scripts/jquery-1.7.1.intellisense.js"></script>
<script src="Scripts/jquery-1.7.1.min.js"></script>
<script type="text/javascript">
//
create this method for bind all data from database in Table
function myfunction()
{
$.ajax({
type: "POST",
contentType: "application/json;
charset=utf-8",
url: "Default.aspx/BindDatatable",
data: "{}",
dataType: "json",
success: function (data)
{
var v="<table
width='50%'id='tb1'> <tr><th>RollNo</th> <th>Name</th>
<th>Gender</th> <th>Course</th></tr>"
for (var i = 0; i <
data.d.length; i++)
{
v += "<tr><td>" + data.d[i].RollNo
+ "</td><td>" + data.d[i].Name + "</td><td>" + data.d[i].Gender
+ "</td><td>" + data.d[i].Course + "</td></tr>";
}
v += "</table>";
$('#table').html(v);
},
error: function (result)
{
alert("Error");
}
});
}
//
this method for reset all textbox....
function reset()
{
$('#txtrollno').val('');
$('#txtname').val('');
$('#txtgender').val('');
$('#txtcourse').val('');
}
$(document).ready(function ()
{
myfunction();
//
code for Reset Button
$('#btnReset').click(function (e)
{
reset();
e.preventDefault();
});
//
code for Save Button……..
$('#btnSave').click(function ()
{
var rno = $('#txtrollno').val();
var nm = $('#txtname').val();
var gen = $('#txtgender').val();
var cou = $('#txtcourse').val();
debugger;
if (rno != '' && nm != '' && gen != '' && cou)
{
$.ajax({
type: "POST",
contentType: "application/json;
charset=utf-8",
url: "Default.aspx/InsData",
data: "{'rollno':'" + rno + "','name':'" + nm + "','gender':'" + gen + "','course':'" + cou + "'}",
//dataType: "json",
success: function (data)
{
var obj = data.d;
if (obj == 'Save')
{
$('#result').css("color", "#4cff00");
$('#result').html("Details
Submitted Successfully");
var str = "<tr><td>" + rno + "</td><td>" + nm + "</td><td>" + gen + "</td><td>" + cou + "</td></tr>";
$('#tb1').append(str);
reset();
}
else
{
$('#result').css("color", "#f00");
$('#result').html(obj);
}
},
error: function (data)
{
alert('Error');
}
});
}
else
{
$('#result').css("color", "#f00");
$('#result').html('Please enter all
the fields');
return false;
}
});
//
code for Search Button with RollNo……..
$('#btnSelect').click(function (e)
{
var rno = $('#txtrollno').val();
e.preventDefault();
debugger;
if (rno != '')
{
$.ajax({
type: "POST",
contentType: "application/json;
charset=utf-8",
url: "Default.aspx/SearchData",
data: "{'rollno':'" + rno + "'}",
dataType: "json",
success: function (data)
{
for (var i = 0; i <
data.d.length; i++)
{
$('#txtrollno').val(data.d[i].RollNo);
$('#txtname').val(data.d[i].Name);
$('#txtgender').val(data.d[i].Gender);
$('#txtcourse').val(data.d[i].Course);
}
},
error: function (data)
{
alert('Error');
}
});
}
else
{
$('#result').css("color", "#f00");
$('#result').html('Please enter
RollNo');
return false;
}
});
//
code for Update Button……….
$('#btnUpdate').click(function (e)
{
var rno = $('#txtrollno').val();
var nm = $('#txtname').val();
var gen = $('#txtgender').val();
var cou = $('#txtcourse').val();
debugger;
if (rno != '' && nm != '' && gen != '' && cou)
{
$.ajax({
type: "POST",
contentType: "application/json;
charset=utf-8",
url: "Default.aspx/UpdateData",
data: "{'rollno':'" + rno + "','name':'" + nm + "','gender':'" + gen + "','course':'" + cou + "'}",
dataType: "json",
success: function (data)
{
var obj = data.d;
//alert(obj);
if (obj == 'Updated')
{
myfunction();
reset();
$('#result').css("color", "#4cff00");
$('#result').html("Details
updated Successfully");
}
else
{
$('#result').css("color", "#f00");
$('#result').html(obj);
}
},
error: function (data)
{
alert('Error');
}
});
}
else {
$('#result').css("color", "#f00");
$('#result').html('Please enter all
the fields');
return false;
}
e.preventDefault();
});
//
code for Delete Button with RollNo………………
$('#btnDelete').click(function (e)
{
var rno = $('#txtrollno').val();
if (rno != '')
{
$.ajax({
type: "POST",
contentType: "application/json;
charset=utf-8",
url: "Default.aspx/DeleteData",
data: "{'rollno':'" + rno + "'}",
dataType: "json",
success: function (data)
{
var obj = data.d;
if (obj == 'Deleted')
{
$('#txtrollno').val('');
$('#result').css("color", "#4cff00");
$('#result').html("Details
delete Successfully");
myfunction();
}
else if (obj == 'false')
{
$('#result').css("color", "#f00");
$('#result').html("RollNo is
not exist");
}
else
{
$('#result').css("color", "#f00");
$('#result').html(obj);
}
},
error: function (data)
{
alert('Error');
}
});
}
else
{
//alert('Please enter all the
fields')
$('#result').css("color", "#f00");
$('#result').html('Please enter
RollNo');
return false;
}
e.preventDefault();
});
});
</script>
</head>
<body>
<form id="form1" runat="server">
<table class="auto-style1">
<tr>
<td colspan="2"><center><span style="color:red;font-family:Verdana">Insert Delete Update
Retrieve data with Sql Server using
jQuery AJAX in ASP.Net</span></center></td>
</tr>
<tr><td>RollNo</td><td><input type="text" id="txtrollno" /></td></tr>
<tr><td>Name</td><td><input type="text" id="txtname" /></td></tr>
<tr><td>Gender</td><td><input type="text" id="txtgender" /></td></tr>
<tr><td>Course</td><td><input type="text" id="txtcourse" /></td></tr>
<tr>
<td colspan="2">
<input type="button" id="btnSave" value="Save" />
<input type="button" id="btnUpdate" value="Update" />
<input type="button" id="btnDelete" value="Delete" />
<input type="button" id="btnSelect" value="Search" />
<input type="button" id="btnReset" value="Reset" />
</td>
</tr>
<tr><td colspan="2" id="result"></td></tr>
<tr><td colspan="2"><center><div id="table"></center></div></td></tr>
</table>
</form>
</body>
</html>
Code for Default.aspx.cs page………….
using System;
using
System.Collections.Generic;
using System.Linq;
using System.Web;
using
System.Web.Script.Services;
using
System.Web.Services;
using System.Web.UI;
using
System.Web.UI.WebControls;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
static testEntities tt = new testEntities();
//
How to save data in Database Table....
[WebMethod]
[ScriptMethod(ResponseFormat = ResponseFormat.Json)]
public static string InsData(string rollno, string name, string gender, string course)
{
try
{
Student ss = new Student
{
RollNo = Convert.ToInt32(rollno),
Name = name,
Gender = gender,
Course = course
};
tt.Students.Add(ss);
tt.SaveChanges();
return "Save";
}
catch (Exception ex)
{
return
ex.Message.ToString();
}
}
//
How to show data from Database Table
with RollNo....
[WebMethod]
[ScriptMethod(ResponseFormat = ResponseFormat.Json)]
public static List<Student> SearchData(string rollno)
{
int rno=Convert.ToInt32(rollno);
List<Student> st = new List<Student>();
st= tt.Students.Where(m =>
m.RollNo == rno).ToList();
return st;
}
//
How to show all data from Database
Table....
[WebMethod]
public static List<Student> BindDatatable()
{
List<Student> st = new List<Student>();
st= tt.Students.ToList();
return st;
}
//
How to Upadte data in Database Table....
[WebMethod]
[ScriptMethod(ResponseFormat = ResponseFormat.Json)]
public static string UpdateData(string rollno, string name, string gender, string course)
{
string msg = string.Empty;
int rno = Convert.ToInt32(rollno);
try
{
var
v=tt.Students.Where(m => m.RollNo == rno).FirstOrDefault();
if (v != null)
{
v.Name = name;
v.Gender = gender;
v.Course = course;
tt.SaveChanges();
msg = "Updated";
}
else
{
msg = "false";
}
}
catch (Exception ex)
{
msg = ex.Message.ToString();
}
return msg;
}
//
How to DeleteData data in Database
Table....
[WebMethod]
[ScriptMethod(ResponseFormat = ResponseFormat.Json)]
public static string DeleteData(string rollno)
{
string msg = string.Empty;
int rno = Convert.ToInt32(rollno);
try
{
var v =
tt.Students.Where(m => m.RollNo == rno).FirstOrDefault();
if (v != null)
{
tt.Students.Remove(v);
tt.SaveChanges();
msg = "Deleted";
}
else
{
msg = "false";
}
}
catch (Exception ex)
{
msg = ex.Message.ToString();
}
return msg;
}
}
Result
Your post is really good providing good information
ReplyDeleteactive collagen reviews
active collagen supplements
Buy Lorna Vanderhaeghe
Buy Lorna Vanderhaeghe Estrosmart
Buy Lorna Vanderhaeghe Supplements
Lorna Vanderhaeghe Active Collagen
lorna vanderhaeghe estrosmart
lorna vanderhaeghe health products
I really appreciate information shared above. It’s of great help. If someone want to learn Online (Virtual) instructor lead live training in AJAX, kindly contact us http://www.maxmunus.com/contact
ReplyDeleteMaxMunus Offer World Class Virtual Instructor led training on AJAX. We have industry expert trainer. We provide Training Material and Software Support. MaxMunus has successfully conducted 100000+ trainings in India, USA, UK, Australlia, Switzerland, Qatar, Saudi Arabia, Bangladesh, Bahrain and UAE etc.
For Demo Contact us.
Nitesh Kumar
MaxMunus
E-mail: nitesh@maxmunus.com
Skype id: nitesh_maxmunus
Ph:(+91) 8553912023
http://www.maxmunus.com/