This blog contains only for students who want to practice the Dot Net Framework CRUD Operation in Connected Architecture.
What is Connection-Oriented Architecture?
Connection-Oriented Architecture in .Net Framework means when we are working with the Data Source we have to keep the connection continuously.
In this scenario, we load data from Data Source to DataReader through the Command Class.
This page is our insert page. Where we are taking the input of Name and Department from the user and provide them their generated Id.
For this, we have a Department table. from the Department, we are bringing all the departments to the dropdown menu.
Department Table
----------------------------
DId int IDENTITY(10,10) NOT NULL,
DName varchar(50) NULL,
Loaction varchar(50) NULL,
Stored Procedure for Department
------------------------------------------------
(
@DId int=null,
@DName varchar(50)=null
)
as
begin
Select DId, DName from Department
end
go
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.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();
}
}
}
--------------- The End ---------------
