.jpg)
Insert
Delete Update Retrieve data with Sql Server using jQuery AJAX in ASP.Net
Note: I will explain in this topic How to
save, delete, update, select data in Sql Server database using Jquery Ajax in Asp.Net and and bind all
data in GridView with each event on a single page in Asp.Net.....
-- query for
Sql Server………..
create database test1
use test1
create table Student
(
RollNo int primary key,
Name varchar(50),
Gender varchar(50),
Course varchar(50)
)
Note: First we create a Web From and
download Jquery 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 runat="server">
<title></title>
<style type="text/css">
.auto-style1
{
width: 100%;
}
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 GridView
function myfunction()
{
$.ajax({
type: "POST",
contentType: "application/json;
charset=utf-8",
url: "Default.aspx/BindDatatable",
data: "{}",
dataType: "json",
success: function (data)
{
$('#GridView1').html('');
for (var i = 0; i <
data.d.length; i++)
{
$("#GridView1").append("<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>");
}
},
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 (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/InsData",
data: "{'rollno':'" + rno + "','name':'" + nm + "','gender':'" + gen + "','course':'" + cou + "'}",
dataType: "json",
success: function (data)
{
var obj = data.d;
//alert(obj);
if (obj == 'Save')
{
myfunction();
reset();
$('#lblmessage').css("color", "#4cff00");
$('#lblmessage').html("Details
Submitted Successfully");
}
else
{
$('#lblmessage').css("color", "#f00");
$('#lblmessage').html(obj);
}
},
error: function (data)
{
alert('Error');
}
});
}
else
{
$('#lblmessage').css("color", "#f00");
$('#lblmessage').html('Please enter all
the fields');
return false;
}
e.preventDefault();
});
//
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();
$('#lblmessage').css("color", "#4cff00");
$('#lblmessage').html("Details
updated Successfully");
}
else
{
$('#lblmessage').css("color", "#f00");
$('#lblmessage').html(obj);
}
},
error: function (data)
{
alert('Error');
}
});
}
else
{
$('#lblmessage').css("color", "#f00");
$('#lblmessage').html('Please enter all
the fields');
return false;
}
e.preventDefault();
});
//
code for Delete Button………
$('#btnDelete').click(function (e)
{
var rno = $('#txtrollno').val();
debugger;
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;
//alert(obj);
if (obj =='Deleted')
{
myfunction();
$('#txtrollno').val('');
$('#lblmessage').css("color", "#4cff00");
$('#lblmessage').html("Details
delete Successfully");
}
else if (obj == 'false')
{
$('#lblmessage').css("color", "#f00");
$('#lblmessage').html("RollNo is
not exist");
}
else
{
$('#lblmessage').css("color", "#f00");
$('#lblmessage').html(obj);
}
},
error: function (data)
{
alert('Error');
}
});
}
else
{
//alert('Please enter all the
fields')
$('#lblmessage').css("color", "#f00");
$('#lblmessage').html('Please enter
RollNo');
return false;
}
e.preventDefault();
});
//
code for Search Button…….
$('#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
{
$('#lblmessage').css("color", "#f00");
$('#lblmessage').html('Please enter
RollNo');
return false;
}
});
});
</script>
</head>
<body>
<form id="form1" runat="server">
<div>
<table class="auto-style1">
<tr>
<td colspan="2"><span style="color:red;font-family:Verdana">Insert Delete Update
Retrieve data with Sql Server using jQuery AJAX in ASP.Net</span></td>
</tr>
<tr>
<td>RollNo</td>
<td>
<asp:TextBox ID="txtrollno" runat="server"></asp:TextBox>
<%-- <input type="text"
id="txtrollno" />--%>
</td>
</tr>
<tr>
<td>Name</td>
<td>
<asp:TextBox ID="txtname" runat="server"></asp:TextBox>
<%-- <input
type="text" id="txtname" />--%>
</td>
</tr>
<tr>
<td>Gender</td>
<td>
<asp:TextBox ID="txtgender" runat="server"></asp:TextBox>
<%-- <input type="text"
id="txtgender" />--%>
</td>
</tr>
<tr>
<td>Course</td>
<td>
<asp:TextBox ID="txtcourse" runat="server"></asp:TextBox>
<%-- <input type="text"
id="txtcourse" />--%>
</td>
</tr>
<tr>
<td colspan="2">
<asp:Button ID="btnSave" runat="server" Text="Save"/>
<asp:Button ID="btnUpdate" runat="server" Text="Update" />
<asp:Button ID="btnDelete" runat="server" Text="Delete" />
<asp:Button ID="btnSelect" runat="server" Text="Search" />
<asp:Button ID="btnReset" runat="server" Text="Reset" />
</td>
</tr>
<tr>
<td colspan="2">
<asp:Label ID="lblmessage" runat="server" Text="Label"></asp:Label>
</td>
</tr>
<tr>
<td colspan="2">
<center> <asp:GridView ID="GridView1" runat="server">
</asp:GridView>
</center>
</td>
</tr>
</table>
</div>
</form>
</body>
</html>
Code for Default.aspx.cs page………….
using System;
using
System.Collections.Generic;
using System.Data;
using
System.Data.SqlClient;
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)
{
if (!IsPostBack)
{
BindDataGridview();
}
}
//
create Student Class.....
public class Student
{
public int RollNo { get; set; }
public string Name { get; set; }
public string Gender { get; set; }
public string Course { get; set; }
}
static SqlConnection con = new SqlConnection(@"Data
Source=DOMAINADMINS8\INDIA;Initial Catalog=test;Integrated Security=True");
//
How to save data in Database Table..................
[WebMethod]
[ScriptMethod(ResponseFormat = ResponseFormat.Json)]
public static string InsData(string rollno, string name, string gender, string course)
{
string msg = string.Empty;
try
{
using (SqlCommand cmd = new SqlCommand("insert into
Student(RollNo,Name,Gender,Course) VALUES(@RollNo,@Name,@Gender,@Course)", con))
{
con.Open();
cmd.Parameters.AddWithValue("@RollNo", Convert.ToInt32(rollno));
cmd.Parameters.AddWithValue("@Name", name);
cmd.Parameters.AddWithValue("@Gender", gender);
cmd.Parameters.AddWithValue("@Course", course);
int i =
cmd.ExecuteNonQuery();
con.Close();
if (i == 1)
{
msg = "Save";
}
else
{
msg = "false";
}
}
}
catch (Exception ex)
{
msg = ex.Message.ToString();
}
return msg;
}
//
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;
try
{
using (SqlCommand cmd = new SqlCommand("update
Student set Name=@Name,Gender=@Gender,Course=@Course where RollNo=@RollNo", con))
{
con.Open();
cmd.Parameters.AddWithValue("@RollNo", Convert.ToInt32(rollno));
cmd.Parameters.AddWithValue("@Name", name);
cmd.Parameters.AddWithValue("@Gender", gender);
cmd.Parameters.AddWithValue("@Course", course);
int i =
cmd.ExecuteNonQuery();
con.Close();
if (i == 1)
{
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;
try
{
using (SqlCommand cmd = new SqlCommand("delete from
Student where RollNo=@RollNo", con))
{
con.Open();
cmd.Parameters.AddWithValue("@RollNo", Convert.ToInt32(rollno));
int i =
cmd.ExecuteNonQuery();
con.Close();
if (i == 1)
{
msg = "Deleted";
}
else
{
msg = "false";
}
}
}
catch (Exception ex)
{
msg = ex.Message.ToString();
}
return msg;
}
//
How to Show all data from Database
Table....
[WebMethod]
public static Student[] BindDatatable()
{
DataTable dt = new DataTable();
List<Student> st = new List<Student>();
using (SqlCommand cmd = new SqlCommand("select
RollNo,Name,Gender,Course from Student", con))
{
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
foreach (DataRow dr in dt.Rows)
{
Student ss = new Student();
ss.RollNo = Convert.ToInt32(dr["RollNo"]);
ss.Name = dr["Name"].ToString();
ss.Gender = dr["Gender"].ToString();
ss.Course = dr["Course"].ToString();
st.Add(ss);
}
}
return st.ToArray();
}
//
How to search data in Database Table with RollNo....
[WebMethod]
[ScriptMethod(ResponseFormat = ResponseFormat.Json)]
public static List<Student> SearchData(string rollno)
{
List<Student> st = new List<Student>();
string msg = string.Empty;
using (SqlCommand cmd = new SqlCommand("select *
from Student where RollNo=@RollNo", con))
{
cmd.Parameters.AddWithValue("@RollNo", Convert.ToInt32(rollno));
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
foreach (DataRow dr in ds.Tables[0].Rows)
{
Student ss = new Student();
ss.RollNo = Convert.ToInt32(dr["RollNo"]);
ss.Name = dr["Name"].ToString();
ss.Gender = dr["Gender"].ToString();
ss.Course = dr["Course"].ToString();
st.Add(ss);
}
}
return st;
}
//
code for how to show all record in GridView…….
private void BindDataGridview()
{
DataTable dt = new DataTable();
dt.Columns.Add("RollNo");
dt.Columns.Add("Name");
dt.Columns.Add("Gender");
dt.Columns.Add("Course");
dt.Rows.Add();
GridView1.DataSource = dt;
GridView1.DataBind();
GridView1.Rows[0].Visible = false;
}
}
Result:
