3 Tier architecture have 3 layers
1.Presentation Layer
2.Buisness Layer
3.Data access Layer
//Data Layer
static class DAL
{
const string ConnectionString = "server=.;initial catalog=Employee;user id=sa;password=sa123";
public static int ExecuteNonQuery(string commandText, SqlParameter[] parameters)
{
try
{
using (SqlConnection conn = new SqlConnection(ConnectionString))
{
conn.Open();
using (SqlCommand cmd = new SqlCommand(commandText, conn))
{
foreach (SqlParameter param in parameters)
cmd.Parameters.Add(param);
int rowsAffected = cmd.ExecuteNonQuery();
return rowsAffected;
}
}
}
catch
{
throw;
}
}
public static SqlDataReader GetReader(string commandText, SqlParameter[] parameters)
{
try
{
using (SqlConnection conn = new SqlConnection(ConnectionString))
{
conn.Open();
using (SqlCommand cmd = new SqlCommand(commandText, conn))
{
foreach (SqlParameter param in parameters)
cmd.Parameters.Add(param);
SqlDataReader reader = cmd.ExecuteReader();
return reader;
}
}
}
catch
{
throw;
}
}
}
To resemble the table EmployeeDetails I have created the following entity class
class EmployeeEntity
{
public int Id { get; set; }
public string Name { get; set; }
public string Address { get; set; }
public override string ToString()
{
StringBuilder sb = new StringBuilder();
sb.Append("Employee Name = ").Append(this.Name);
sb.Append(" Have Id = ").Append(this.Id);
sb.Append(" Lives In = ").Append(this.Address);
return sb.ToString();
}
}
//Buisness Layer
static class Employee
{
public static int AddEmployee(EmployeeEntity e)
{
SqlParameter[] param = new SqlParameter[3];
param[0] = new SqlParameter("@Id", e.Id);
param[1] = new SqlParameter("@Name", e.Name);
param[2] = new SqlParameter("@Address", e.Address);
return DAL.ExecuteNonQuery("INSERT INTO EmployeeDetails VALUES(@Id, @Name, @Address)", param);
}
public static EmployeeEntity GetEmployee(int id)
{
SqlParameter[] param = new SqlParameter[1];
param[0] = new SqlParameter("@Id", id);
SqlDataReader reader = DAL.GetReader("SELECT * FROM EmployeeDetails WHERE Id = @Id", param);
if (reader.HasRows)
{
EmployeeEntity emp = new EmployeeEntity();
while (reader.Read())
{
int temp = 0;
int.TryParse(reader["Id"].ToString(), out temp);
emp.Id = temp;
emp.Name = reader["Name"].ToString();
emp.Address = reader["Address"].ToString();
break;
}
return emp;
}
else
return null;
}
}
//presentation Layer
class Program
{
static void Main(string[] args)
{
Employee.AddEmployee(new EmployeeEntity() { Id = 1, Name = "Amal", Address = "MyAddress" });
Employee.AddEmployee(new EmployeeEntity() { Id = 2, Name = "Hashim", Address = "Hashim Address" });
Employee.AddEmployee(new EmployeeEntity() { Id = 3, Name = "Rooney", Address = "Manchester" });
EmployeeEntity emp = Employee.GetEmployee(1);
Console.WriteLine(emp);
}
}
Mesh_2010, if this helps please login to Mark As Answer. | Alert Moderator