how to create insert update one stored procedure in asp.net.
create proc usp_employee_insert_update
@Empid int,
@Name varchar(50),
@Age int,
@Address varchar(50)
as
begin
if(@Empid=0)
begin
insert into Employee(Name,Age,Address) values(@Name,@Age,@Address)
end
else
begin
update Employee set Name=@Name,Age=@Age,Address=@Address where Empid=@Empid
end
end
create database Jqueryinsertupdate123
use Jqueryinsertupdate123
truncate table Employee
create table Employee
(
Empid int primary key identity(1,1),
Name varchar(50),
Age int,
Address varchar(50)
)
select * from employee
create proc usp_employee_insert
@Name varchar(50),
@Age int,
@Address varchar(50)
as
begin
insert into Employee(Name,Age,Address) values(@Name,@Age,@Address)
end
create proc usp_employee_Getdata
as
begin
select * from Employee
end
create proc usp_Employee_delete
@Empid int
as
begin
delete from Employee where Empid=@Empid
end
create Proc Usp_employee_edit
@Empid int
as
begin
select * from Employee where Empid=@Empid
end
add j query file:-
<script src="jquery-3.2.1.min.js"></script>
How to install Json in asp.net
Install-Package Newtonsoft.Json -Version 6.0.1
.Aspx page code:-
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Registration.aspx.cs" Inherits="Jqueryinsertupdate123.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">
EID = 0;
$(document).ready(function () {
Databind();
});
function Savedata() {
$.ajax({
url: 'Registration.aspx/Insert_Update',
type: 'post',
contentType: 'application/json;charset=utf-8',
datatype: 'json',
data: "{A: '" + $("#textname").val() + "', B: '" + $("#textage").val() + "', C: '" + $("#textaddress").val() + "',E:'"+EID+"'}",
success: function () {
alert('Insert successfull !!');
},
error: function () {
alert('Insert Error !!');
},
});
}
function Databind()
{
$.ajax({
url: 'Registration.aspx/Getdata',
type: 'post',
contentType: 'application/json;charset=utf-8',
datatype: 'json',
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].Age + '</td> <td>' + ds[i].Address + '</td><td><input type="button" id="btndelete" value="Delete" onclick="Delete(' + ds[i].Empid + ')" /></td><td><input type="button" id="btnedit" value="Edit" onclick="Edit('+ds[i].Empid+')" /></td></tr>')
}
},
error: function () {
alert('Get data error!!')
},
});
}
function Delete(Empid) {
$.ajax({
url: 'Registration.aspx/Deletedata',
type: 'post',
contentType: 'application/json;charset=utf-8',
datatype: 'json',
data: "{A: '"+Empid+"'}",
success: function () {
alert('Delete successfull !!');
},
error: function () {
alert('Delete Error !!');
},
});
}
function Edit(Empid) {
$.ajax({
url: 'Registration.aspx/Editdata',
type: 'post',
contentType: 'application/json;charset=utf-8',
datatype: 'json',
data: "{A: '" + Empid + "'}",
success: function (ds) {
ds = JSON.parse(ds.d);
$("#textname").val(ds[0].Name);
$("#textage").val(ds[0].Age);
$("#textaddress").val(ds[0].Address);
$("#btnsave").val("Update");
EID = Empid;
},
error: function () {
alert('Edit Error !!');
},
});
}
</script>
</head>
<body>
<form id="form1" runat="server">
<div>
<table>
<tr>
<td>Name:</td>
<td><input type="text" id="textname" /></td>
</tr>
<tr>
<td>Age:</td>
<td><input type="text" id="textage" /></td>
</tr>
<tr>
<td>Address:</td>
<td><input type="text" id="textaddress" /></td>
</tr>
<tr>
<td></td>
<td><input type="button" id="btnsave" value="Save" onclick="Savedata()" /></td>
</tr>
</table>
<table id="tbl" border="1" style="background-color:pink">
<tr style="background-color:maroon;color:white">
<th>Name</th>
<th>Age</th>
<th>Address</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 Jqueryinsertupdate123
{
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 void Insert_Update(string A, int B, string C,int E)
{
con.Open();
SqlCommand cmd = new SqlCommand("usp_employee_insert_update", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Empid", E);
cmd.Parameters.AddWithValue("@Name", A);
cmd.Parameters.AddWithValue("@Age", B);
cmd.Parameters.AddWithValue("@Address", C);
cmd.ExecuteNonQuery();
con.Close();
}
[WebMethod]
public static string Getdata()
{
string dt = "";
con.Open();
SqlCommand cmd = new SqlCommand("usp_employee_Getdata", 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 Deletedata(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 Editdata(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;
}
}
}
Connection straing:-
<connectionStrings>
<add name="DBCS" connectionString="data source=Shiva;integrated security=true;initial catalog=Jqueryinsertupdate123"/>
</connectionStrings>
1 comment:
well done sir
Post a Comment