Saturday 31 January 2015

Insert Delete Update Retrieve data with Sql Server using JQuery Ajax in ASP.Net

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: 



1 comments: