This Is One of Best Example For 3 Tier Architecture in this Example DataAccess Layer Common For All the Business Objects
----------------------------------------------------------------------------------------------------------------------
Stored procedures
For Get All Records:- SelectAllStudent
Insert SP:- InsertStudent
Update:- UpdateStudent
Delete Student Procedure:- DeleteStudent
Stored procedures For EMployee
For Get All Records:- SelectAll
Employee Insert SP:- InsertEmployee
Update:- UpdateEmployee
Delete Student Procedure:- DeleteEmployee
SQL
Code For 3 Tier ArchiTecture
Create table StudentTbl(Id int primary key,Name varchar(50),Gender Varchar(20),City varchar(20))
--Stored Procedre OF get all records
create procedure SelectAllStudent
as
begin
select*from StudentTbl
End
Create table EmployeeTbl(Id int primary key,Name varchar(50),Gender Varchar(20),Salary money)
--stored
Procedured for Insert
create procedure InsertStudent(@Id int,@name varchar(50),@Gender varchar(20),@City varchar(20))
as
begin
insert into StudentTbl values(@Id,@Name,@Gender,@City)
end
InsertStudent 1,'Ram','Gender','City'
--stored
Procedure For Update Student
create procedure UpdateStudent(@id int,@Name varchar(20),@Gender varchar(20),@City varchar(20))
as
begin
update StudentTbl Set Name=@Name,Gender=@Gender,City=@City where Id=@id
end
UpdateStudent @Name='Rama Rao',@Gender='Male',@City='Durgi',@id=1
--Delete
Student
create procedure DeleteStudent @Id int
as
begin
delete from StudentTbl where Id=@id
end
DeleteStudent 8
Create Employee Table
Create table EmployeeTbl(Id int primary key,Name varchar(50),Gender Varchar(20),Salary Money)
--Stored
Procedure For Get All Records From Employee
create procedure SelectAll
as
begin
select*from EmployeeTbl
End
--Stored Procedure For Insert Data
create procedure InsertEmployee(@id int,@Ename varchar(50),@Gender varchar(20),@Salary money)
as
begin
insert into EmployeeTbl values(@id,@Ename,@Gender,@Salary)
end
InsertEmployee 3,'Ram','Gender',7000
--stored Procedure For Update Employee
create procedure UpdateEmployee(@id int,@Ename varchar(20),@Gender varchar(20),@Salary money)
as
begin
update EmployeeTbl Set Name=@Ename,Gender=@Gender,Salary=@Salary where Id=@id
end
UpdateEmployee @EName='Srinivas Rao',@Gender='Male',@salary=15000,@id=1
--stored procedure for Delete
create procedure DeleteEmployee @Id int
as
begin
delete from EmployeeTbl where Id=@id
end
DeleteEmployee 1
Data Access layer
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
namespace MultiTableEntity._3TierArchiTecture
{
public class DataAccessLayer
{
//Note:This
Data Access layer Will Any Business logic Layer Which Should Pass The required
Parameter
//This Layer Accepts only Stored procedure and
SqlCommand Cmd Object as Parameter
//Connection
String
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString);
//Get All Records From Table
public DataSet AllBind(string Query)
{
SqlDataAdapter Da = new SqlDataAdapter(Query, con);
DataSet Ds = new DataSet();
Da.Fill(Ds);
return Ds;
}
//Inserting
Record In Table using SqlCommand Object
and Stored Procedure
public int InsertTbl(SqlCommand cmd,string Query)
{
con.Open();
cmd.Connection = con;
cmd.CommandText = Query;
cmd.CommandType = CommandType.StoredProcedure;
int i=cmd.ExecuteNonQuery();
return i;
}
//Udate
Record in Table Using SqlCommand Object and Stored Procedure
public int UpDate_Record(SqlCommand cmd, string Query)
{
con.Open();
cmd.Connection = con;
cmd.CommandText = Query;
cmd.CommandType = CommandType.StoredProcedure;
int i = cmd.ExecuteNonQuery();
return i;
}
//Deleting Record in Table Using SqlCommand Object and Stored Procedure
public int DeletRecord(SqlCommand cmd,string Query)
{
con.Open();
cmd.Connection = con;
cmd.CommandText = Query;
cmd.CommandType = CommandType.StoredProcedure;
int I = cmd.ExecuteNonQuery();
con.Close();
return I;
}
}
}
BusinessLogicLayer For Employee
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
namespace MultiTableEntity._3TierArchiTecture
{
public class BusinessLogicLayer
{
DataAccessLayer Dal = new DataAccessLayer();
Employee obj = new Employee();
public DataSet GetAllRecords()
{
string Commandtext = "SelectAll";
DataSet ds = Dal.AllBind(Commandtext);
return ds;
}
public string EmployeeInsert(Employee obj)
{
SqlCommand cmd = new SqlCommand();
cmd.Parameters.AddWithValue("@id",
obj.EmployeeId);
cmd.Parameters.AddWithValue("@Ename",
obj.EmployeeName);
cmd.Parameters.AddWithValue("@Gender",
obj.Gender);
cmd.Parameters.AddWithValue("@Salary",
obj.Salary);
string sp = "InsertEmployee";
int i=Dal.InsertTbl(cmd, sp);
if(i==1)
{ return "successfully
Inserted"; }
else
{ return "Failed to
Insert"; }
}
public string Delete_Employee(int empId)
{
SqlCommand cmd = new SqlCommand();
cmd.Parameters.AddWithValue("@Id",
empId);
string Query = "DeleteEmployee";
int result = Dal.DeletRecord(cmd, Query);
if(result==1){
return "Sucess Full
Deleted";
}
else
{
return "Failed to
Delete";
}
}
public string Employee_Update(Employee obj)
{
SqlCommand cmd = new SqlCommand();
cmd.Parameters.AddWithValue("@id",
obj.EmployeeId);
cmd.Parameters.AddWithValue("@Ename",
obj.EmployeeName);
cmd.Parameters.AddWithValue("@Gender",
obj.Gender);
cmd.Parameters.AddWithValue("@Salary",
obj.Salary);
string sp = "UpdateEmployee";
int i = Dal.UpDate_Record(cmd, sp);
if (i == 1)
{ return "successfully
Updated"; }
else
{ return "Failed to
Update"; }
}
}
}
Business Logic layer for Student
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
namespace MultiTableEntity._3TierArchiTecture
{
public class BusinessLogicLayer
{
DataAccessLayer Dal = new DataAccessLayer();
Employee obj = new Employee();
public DataSet GetAllRecords()
{
string Commandtext = "SelectAll";
DataSet ds = Dal.AllBind(Commandtext);
return ds;
}
public string EmployeeInsert(Employee obj)
{
SqlCommand cmd = new SqlCommand();
cmd.Parameters.AddWithValue("@id",
obj.EmployeeId);
cmd.Parameters.AddWithValue("@Ename",
obj.EmployeeName);
cmd.Parameters.AddWithValue("@Gender",
obj.Gender);
cmd.Parameters.AddWithValue("@Salary",
obj.Salary);
string sp = "InsertEmployee";
int i=Dal.InsertTbl(cmd, sp);
if(i==1)
{ return "successfully
Inserted"; }
else
{ return "Failed to
Insert"; }
}
public string Delete_Employee(int empId)
{
SqlCommand cmd = new SqlCommand();
cmd.Parameters.AddWithValue("@Id",
empId);
string Query = "DeleteEmployee";
int result = Dal.DeletRecord(cmd, Query);
if(result==1){
return "Sucess Full
Deleted";
}
else
{
return "Failed to
Delete";
}
}
public string Employee_Update(Employee obj)
{
SqlCommand cmd = new SqlCommand();
cmd.Parameters.AddWithValue("@id",
obj.EmployeeId);
cmd.Parameters.AddWithValue("@Ename",
obj.EmployeeName);
cmd.Parameters.AddWithValue("@Gender",
obj.Gender);
cmd.Parameters.AddWithValue("@Salary",
obj.Salary);
string sp = "UpdateEmployee";
int i = Dal.UpDate_Record(cmd, sp);
if (i == 1)
{ return "successfully
Updated"; }
else
{ return "Failed to
Update"; }
}
}
}
No comments:
Post a Comment