Employee Table
-------------------------
Stored Procedure for Employee
------------------------------------------------
create proc usp_Employee
(
@EId int=null,
@Name varchar(50)=null,
@DId int=null,
@type char(2)=null
)
as
if(@type='in')
begin
insert Employee (Name,DId) values(@Name,@DId)
end
if(@type='up')
begin
update Employee set Name=@Name,DId=@DId where EId=@EId
end
if(@type='de')
begin
delete Employee where EId=@EId
end
if(@type='se')
begin
select Name,DId from Employee where EId=@EId
end
if(@type='mx')
begin
Select Max(EId) from Employee
end
if(@type='s')
begin
select EId from Employee
end
go
in Web.config
-----------------
<connectionStrings>
<add name ="constr" connectionString="Server=yourservername; User id=sa; Password=yourpassword; Initial Catalog=ASPDB" providerName="System.Data.SqlClient"/>
</connectionStrings>
Designing of Insert page
EmployeeInsert.aspx
---------------------------
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="EmployeeInsert.aspx.cs" Inherits="Example1.EmployeeInsert" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
<link href="Content/bootstrap.css" rel="stylesheet" />
<style>
.divbox{
margin:10px;
}
.box{
border:groove;
padding:20px 20px 20px 60px;
align-content:center;
margin:40px 100px;
}
</style>
</head>
<body>
<form id="form1" runat="server">
<div class="container">
<h3>Employee Insert</h3>
<div class="box">
<div class="row">
<div class="col-lg-3">
<asp:Label ID="Label3" runat="server" Text="Employee Id"></asp:Label>
</div>
<div class="divbox col-lg-3">
<asp:TextBox ID="txtEid" CssClass="form-control" runat="server" ReadOnly="true"></asp:TextBox>
</div>
<div class="col-sm-1">
<asp:Button ID="Button1" CssClass="btn btn-dark" runat="server" OnClick="btnReset_Click" Text="New Registration" />
</div>
</div>
<div class="row">
<div class="col-lg-3">
<asp:Label ID="Label1" runat="server" Text="Employee Name"></asp:Label>
</div>
<div class="divbox col-lg-3">
<asp:TextBox ID="txtEname" CssClass="form-control" runat="server"></asp:TextBox>
</div>
<div class="col-sm-3">
<asp:LinkButton ID="LinkButton1" CssClass="btn-light" PostBackUrl="~/EmployeeEdit.aspx" runat="server">Go To Edit Page</asp:LinkButton>
</div>
</div>
<div class="row">
<div class="col-lg-3">
<asp:Label ID="Label5" runat="server" Text="Department"></asp:Label>
</div>
<div class="divbox col-lg-3">
<asp:DropDownList ID="ddlDept" CssClass="form-control" runat="server"></asp:DropDownList>
</div>
</div>
<div class="row">
<div class="col-lg-3">
</div>
<div class="col-lg-3">
<asp:Button ID="btnSubmit" CssClass="btn btn-success" OnClick="btnSubmit_Click" runat="server" Text="Submit" />
<asp:Button ID="btnReset" runat="server" CssClass="btn btn-light " OnClick="btnReset_Click" Text="Reset" />
</div>
</div>
</div>
</div>
</form>
</body>
</html>
EmployeeInsert.aspx.cs
-----------------------------
using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
namespace DBExamples
{
public partial class EmployeeInsert : System.Web.UI.Page
{
SqlConnection con;
SqlDataReader dr;
SqlCommand cmd =new SqlCommand();
protected void Page_Load(object sender, EventArgs e)
{
con = new SqlConnection(ConfigurationManager.ConnectionStrings["constr"].ConnectionString);
if(!IsPostBack)
{
LoadDept();
}
}
private void LoadDept()
{
cmd.Connection = con;
cmd.CommandText = "usp_Department";
cmd.CommandType = CommandType.StoredProcedure;
con.Open();
dr = cmd.ExecuteReader();
ddlDept.DataValueField = "DId";
ddlDept.DataTextField = "DName";
ddlDept.DataSource = dr;
ddlDept.DataBind();
ddlDept.Items.Insert(0, "--select--");
con.Close();
}
protected void btnSubmit_Click(object sender, EventArgs e)
{
if(ddlDept.SelectedIndex>0)
{
cmd.Connection = con;
cmd.CommandText = "usp_Employee";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Name", txtEname.Text);
cmd.Parameters.AddWithValue("@DId", ddlDept.SelectedValue);
cmd.Parameters.AddWithValue("@type","in");
con.Open();
if(cmd.ExecuteNonQuery()>0)
{
SqlCommand cmd1 = new SqlCommand("usp_Employee", con);
cmd1.CommandType = CommandType.StoredProcedure;
cmd1.Parameters.AddWithValue("@type", "mx");
txtEid.Text= cmd1.ExecuteScalar().ToString();
Response.Write("<script>alert('Registration Successful')</script>");
con.Close();
}
else
{
Response.Write("<script>alert('Registration Failed')</script>");
}
}
else
{
Response.Write("<script>alert('Select a Department')</script>");
}
}
protected void btnReset_Click(object sender, EventArgs e)
{
ClaerData();
}
private void ClaerData()
{
txtEid.Text = txtEname.Text = "";
ddlDept.SelectedIndex = 0;
}
}
}
EmployeeEdit.aspx
--------------------------
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="EmployeeEdit.aspx.cs" Inherits="Example1.EmployeeEdit" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
<link href="Content/bootstrap.css" rel="stylesheet" />
<style>
.divbox{
margin:10px;
}
.box{
border:groove;
padding:20px 20px 20px 60px;
align-content:center;
margin:40px 100px;
}
</style>
</head>
<body>
<form id="form1" runat="server">
<div class="container">
<h3>Employee Edit</h3>
<div class="box">
<div class="row">
<div class="col-lg-3">
<asp:Label ID="Label3" runat="server" Text="Employee Id"></asp:Label>
</div>
<div class="divbox col-lg-3">
<asp:DropDownList ID="ddlEid" CssClass="form-control" OnSelectedIndexChanged="ddlEid_SelectedIndexChanged" AutoPostBack="true" runat="server"></asp:DropDownList>
</div>
<div class="col-sm-3">
<asp:LinkButton ID="LinkButton1" CssClass="btn-light" PostBackUrl="~/EmployeeInsert.aspx" runat="server">New Registration</asp:LinkButton>
</div>
</div>
<div class="row">
<div class="col-lg-3">
<asp:Label ID="Label1" runat="server" Text="Employee Name"></asp:Label>
</div>
<div class="divbox col-lg-3">
<asp:TextBox ID="txtEname" CssClass="form-control" runat="server"></asp:TextBox>
</div>
</div>
<div class="row">
<div class="col-lg-3">
<asp:Label ID="Label5" runat="server" Text="Department"></asp:Label>
</div>
<div class="divbox col-lg-3">
<asp:DropDownList ID="ddlDept" CssClass="form-control" runat="server"></asp:DropDownList>
</div>
</div>
<div class="row">
<div class="col-lg-3">
</div>
<div class="col-lg-3">
<asp:Button ID="btnUpdate" CssClass="btn btn-success" OnClick="btnUpdate_Click" runat="server" Text="Update" />
<asp:Button ID="btnDelete" runat="server" CssClass="btn btn-light" OnClick="btnDelete_Click" Text="Delete" />
</div>
</div>
</div>
</div>
</form>
</body>
</html>
EmployeeEdit.aspx.cs
----------------------------
using System;using System.Data;
using System.Data.SqlClient;
using System.Configuration;
namespace DBExamples
{
public partial class EmployeeEdit : System.Web.UI.Page
{
SqlConnection con;
SqlDataReader dr;
protected void Page_Load(object sender, EventArgs e)
{
con = new SqlConnection(ConfigurationManager.ConnectionStrings["constr"].ConnectionString);
if(!IsPostBack)
{
btnUpdate.Enabled = false;
btnDelete.Enabled = false;
loadEmp();
loadDept();
}
}
private void loadEmp()
{
SqlCommand cmd = new SqlCommand("usp_Employee", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@type","s");
con.Open();
dr = cmd.ExecuteReader();
ddlEid.DataValueField = "EId";
ddlEid.DataTextField = "EId";
ddlEid.DataSource = dr;
ddlEid.DataBind();
ddlEid.Items.Insert(0,"--select--");
con.Close();
}
private void loadDept()
{
SqlCommand cmd1 = new SqlCommand("usp_Department", con);
cmd1.CommandType = CommandType.StoredProcedure;
con.Open();
dr = cmd1.ExecuteReader();
ddlDept.DataValueField = "DId";
ddlDept.DataTextField = "DName";
ddlDept.DataSource = dr;
ddlDept.DataBind();
ddlDept.Items.Insert(0,"--select--");
con.Close();
}
protected void btnUpdate_Click(object sender, EventArgs e)
{
if(ddlEid.SelectedIndex>0)
{
SqlCommand cmd = new SqlCommand("usp_Employee",con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@EId", ddlEid.SelectedValue);
cmd.Parameters.AddWithValue("@Name",txtEname.Text);
cmd.Parameters.AddWithValue("@DId", ddlDept.SelectedValue);
cmd.Parameters.AddWithValue("@type", "up");
con.Open();
if (cmd.ExecuteNonQuery() > 0)
{
Response.Write("<script>alert('User Detail Updated')</script>");
}
else
{
Response.Write("<script>alert('Failed')</script>");
}
}
else
{
Response.Write("<script>alert('Select Employee Id')</script>");
}
con.Close();
loadEmp();
}
protected void btnDelete_Click(object sender, EventArgs e)
{
if (ddlEid.SelectedIndex > 0)
{
SqlCommand cmd = new SqlCommand("usp_Employee", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@EId", ddlEid.SelectedValue);
cmd.Parameters.AddWithValue("@type", "de");
con.Open();
if (cmd.ExecuteNonQuery() > 0)
{
Response.Write("<script>alert('User Deleted')</script>");
}
else
{
Response.Write("<script>alert('Failed')</script>");
}
}
else
{
Response.Write("<script>alert('Select Employee Id')</script>");
}
con.Close();
loadEmp();
txtEname.Text = "";
ddlDept.SelectedIndex = 0;
}
protected void ddlEid_SelectedIndexChanged(object sender, EventArgs e)
{
btnUpdate.Enabled = true;
btnDelete.Enabled = true;
SqlCommand cmd = new SqlCommand("usp_Employee", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@EId", ddlEid.SelectedValue);
cmd.Parameters.AddWithValue("@type", "se");
con.Open();
dr = cmd.ExecuteReader();
if (dr.Read())
{
txtEname.Text = dr["Name"].ToString();
ddlDept.SelectedValue = dr["DId"].ToString();
}
con.Close();
}
}
}
This is the right weblog for everyone who wishes to be familiar with this topic. You are aware of a great deal of its virtually tricky to argue to you (not too I personally would want…HaHa). You actually put a new spin using a topic thats been written about for many years. Wonderful stuff, just excellent! free course to learn Microservice
ReplyDeleteThanks for the wonderful as well as informative write-up We truly appreciate all the effort that proceeded to go in to the producing. best Angular JS wirter
ReplyDelete