Database code:-
create database country_state_test
use country_state_test
create table Country
(
Cid int primary key identity(1,1),
Cname varchar(50)
)
create proc usp_country_get
as
begin
select * from country
end
insert into Country(cname) values('India'),('Japan'),('USA'),('Chaina')
alter table Employee Add Gender int
select * from Employee
create table Employee
(
Empid int primary key identity(1,1),
Name varchar(50),
Country int,
Age int,
Address varchar(50)
)
create alter proc usp_employee_ins_upd
@Empid int,
@Name varchar(50),
@Gender int,
@Country int,
@Age int,
@Address varchar(50)
as
begin
if(@Empid=0)
begin
insert into Employee(name,Gender,country,age,address) values(@name,@Gender,@country,@age,@address)
end
else
begin
update Employee set name=@name,Gender=@Gender,country=@country,age=@age,address=@address where empid=@empid
end
end
create proc usp_employee_get
as
begin
select Employee.*,Country.* from Employee inner join country on Employee.Country=Country.Cid
end
select min(salary) from
(select top(3) salary from Employee order by salary desc) as T
create proc usp_employee_delete
@Empid int
as
begin
delete from Employee where Empid=@Empid
end
create alter proc usp_employee_edit
@Empid int
as
begin
select * from Employee where Empid=@Empid
end
create alter proc usp_emp_search
@Search varchar(50),
@column varchar(50)
as
begin
select Employee.*,Country.* from Employee inner join country on Employee.Country=Country.Cid where Name like '%'+@Search+'%' or Cname like '%'+@column+'%'
end
Registrtion.Apsx page Code:-
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Registration.aspx.cs" Inherits="jquery_countray_test.Registration" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
<script src="jquery-3.2.1.min.js"></script>
<script type="text/javascript">
function Validation() {
var errorsummery = "";
errorsummery += checkname();
errorsummery += checkage();
errorsummery += checkaddress();
if (errorsummery != "") {
alert(errorsummery);
return false;
}
else {
Savedata();
}
}
function checkname()
{
var TBN = $("#txtname").val();
if (TBN == "") {
return 'Plese enter your name !!\n'
}
else {
return "";
}
}
function checkage() {
var TBA = $("#txtage").val();
if (TBA == "") {
return 'Plese enter your age !!\n'
}
else {
return "";
}
}
function checkaddress() {
var TAD = $("#txtaddress").val();
if (TAD == "") {
return 'Plese enter your address !!\n'
}
else {
return "";
}
}
var EID = 0;
$(document).ready(function () {
country_bind();
Data_bind();
});
function country_bind()
{
$.ajax({
url: 'Registration.aspx/country_get',
type: 'post',
contentType: 'application/json;charset=utf-8',
datatype: 'json',
async:false,
data: "{}",
success: function (ds) {
ds = JSON.parse(ds.d);
for (var i = 0; i < ds.length; i++)
{
$("#ddlcountry").append($('<option/>').attr("value", ds[i].Cid).text(ds[i].Cname));
}
},
error: function () {
alert('Country bind error !!');
},
});
}
function Savedata() {
$.ajax({
url: 'Registration.aspx/Insert',
type: 'post',
contentType: 'application/json;charset=utf-8',
datatype: 'json',
data: "{F: '" + EID + "',A:'" + $("#txtname").val() + "', B:'" + $('input:radio[name=A]:checked').val() + "', C:'" + $("#ddlcountry").val() + "', D:'" + $("#txtage").val() + "',E:'" + $("#txtaddress").val() + "'}",
success: function () {
if ($("#btnsave").val() == "Save") {
alert('Insert data successfull !!');
}
else if ($("#btnsave").val == "Update")
{
alert('Update successfull !!');
}
window.location.reload(true);
},
error: function () {
if ($("#btnsave").val() == "Save") {
alert('insert data error !!');
}
else if ($("#btnsave").val() == "Update")
{
alert('update data error !!');
}
},
});
}
function Data_bind() {
$.ajax({
url: 'Registration.aspx/Getdata',
type: 'post',
contentType: 'application/json;charset=utf-8',
datatype: 'json',
async: false,
data: "{}",
success: function (ds) {
ds = JSON.parse(ds.d);
$("#tbl").find("tr:gt(0)").remove();
for (var i = 0; i < ds.length; i++) {
$("#tbl").append('<tr><td>' + ds[i].Name + '</td> <td>' + (ds[i].Gender == "1" ? "Male" : ds[i].Gender == "2" ? "Female" : "Other") + '</td> <td>' + ds[i].Cname + '</td> <td>' + ds[i].Age + '</td> <td>' + ds[i].Address + '</td> <td><input type="button" id="btndelete" value="Delete" onclick="Delete_data(' + ds[i].Empid + ')" /></td> <td><input type="button" id="btnedit" value="Edit" onclick="Edit_data(' + ds[i].Empid + ')" /></td></tr>')
}
},
error: function () {
alert('Data bind error !!');
},
});
}
function Delete_data(Empid) {
$.ajax({
url: 'Registration.aspx/data_delet',
type: 'post',
contentType: 'application/json;charset=utf-8',
datatype: 'json',
data: "{A:'" + Empid + "'}",
success: function () {
alert('Delete data successfull !!');
Data_bind();
},
error: function () {
alert('Delete data error !!');
},
});
}
function Edit_data(Empid) {
$.ajax({
url: 'Registration.aspx/data_edit',
type: 'post',
contentType: 'application/json;charset=utf-8',
datatype: 'json',
async: false,
data: "{A:'"+Empid+"'}",
success: function (ds) {
ds = JSON.parse(ds.d);
$("#txtname").val(ds[0].Name);
$("#ddlcountry").val(ds[0].Country);
$("#txtage").val(ds[0].Age);
$("#txtaddress").val(ds[0].Address);
$("#btnsave").val("Update");
EID = Empid;
},
error: function () {
alert('Data edit error !!');
},
});
}
function SearchData() {
$.ajax({
url: 'Registration.aspx/Search',
type: 'post',
contentType: 'application/json;charset=utf-8',
datatype: 'json',
async: false,
data: "{A: '" + $("#txtsearch").val() + "',B: '" + $("#ddlcountry").val() + "'}",
success: function (ds) {
ds = JSON.parse(ds.d);
$("#tbl").find("tr:gt(0)").remove();
for (var i = 0; i < ds.length; i++) {
$("#tbl").append('<tr><td>' + ds[i].Name + '</td> <td>' + (ds[i].Gender == "1" ? "Male" : ds[i].Gender == "2" ? "Female" : "Other") + '</td> <td>' + ds[i].Cname + '</td> <td>' + ds[i].Age + '</td> <td>' + ds[i].Address + '</td> <td><input type="button" id="btndelete" value="Delete" onclick="Delete_data(' + ds[i].Empid + ')" /></td> <td><input type="button" id="btnedit" value="Edit" onclick="Edit_data(' + ds[i].Empid + ')" /></td></tr>')
}
},
error: function () {
alert('search data error !!');
},
});
}
</script>
</head>
<body>
<form id="form1" runat="server">
<div>
<table>
<tr>
<td>Name:</td>
<td><input type="text" id="txtname" /></td>
</tr>
<tr>
<td>Gender:</td>
<td><input type="radio" name="A" value="1" />Male
<input type="radio" name="A" value="2" />Female
<input type="radio" name="A" value="3" />Other
</td>
</tr>
<tr>
<td>Country:</td>
<td><select id="ddlcountry">
<option value="0">--select--</option>
</select></td>
</tr>
<tr>
<td>Age:</td>
<td><input type="text" id="txtage" /></td>
</tr>
<tr>
<td>Address:</td>
<td><input type="text" id="txtaddress" /></td>
</tr>
<tr>
<td></td>
<td><input type="button" id="btnsave" value="Save" onclick="return Validation()" /></td>
</tr>
<tr>
<td></td>
<td><input type="text" id="txtsearch" /><input type="button" id="btnsearch" value="Search" onclick="SearchData()" /></td>
</tr>
</table>
<table id="tbl" border="1" style="background-color:pink">
<tr style="background-color:maroon;color:white">
<th>Name</th>
<th>Gender</th>
<th>Country</th>
<th>Age</th>
<th>Address</th>
<th>Delete</th>
<th>Edit</th>
<th></th>
</tr>
</table>
</div>
</form>
</body>
</html>
.CS page code:-
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web.Services;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
namespace jquery_countray_test
{
public partial class Registration : System.Web.UI.Page
{
static SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString);
protected void Page_Load(object sender, EventArgs e)
{
}
[WebMethod]
public static string country_get()
{
string _dt = "";
con.Open();
SqlCommand cmd = new SqlCommand("usp_country_get", con);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
con.Close();
if (ds.Tables[0].Rows.Count > 0)
{
_dt = JsonConvert.SerializeObject(ds.Tables[0]);
}
return _dt;
}
[WebMethod]
public static void Insert(int F, string A, int B, int C, int D, string E)
{
con.Open();
SqlCommand cmd = new SqlCommand("usp_employee_ins_upd", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Empid", F);
cmd.Parameters.AddWithValue("@Name", A);
cmd.Parameters.AddWithValue("@Gender", B);
cmd.Parameters.AddWithValue("@Country", C);
cmd.Parameters.AddWithValue("@Age", D);
cmd.Parameters.AddWithValue("@Address", E);
cmd.ExecuteNonQuery();
con.Close();
}
[WebMethod]
public static string Getdata()
{
string _dt = "";
con.Open();
SqlCommand cmd = new SqlCommand("usp_employee_get", con);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
con.Close();
if (ds.Tables[0].Rows.Count > 0)
{
_dt = JsonConvert.SerializeObject(ds.Tables[0]);
}
return _dt;
}
[WebMethod]
public static void data_delet(int A)
{
con.Open();
SqlCommand cmd = new SqlCommand("usp_employee_delete", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Empid", A);
cmd.ExecuteNonQuery();
con.Close();
}
[WebMethod]
public static string data_edit(int A)
{
string _dt = "";
con.Open();
SqlCommand cmd = new SqlCommand("usp_employee_edit", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Empid", A);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
con.Close();
if (ds.Tables[0].Rows.Count > 0)
{
_dt = JsonConvert.SerializeObject(ds.Tables[0]);
}
return _dt;
}
[WebMethod]
public static string Search(string A,string B)
{
string _dt = "";
con.Open();
SqlCommand cmd = new SqlCommand("usp_emp_search", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@search", A);
cmd.Parameters.AddWithValue("@column", B);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
con.Close();
if (ds.Tables[0].Rows.Count > 0)
{
_dt = JsonConvert.SerializeObject(ds.Tables[0]);
}
return _dt;
}
}
}
No comments:
Post a Comment