This blog is moved to
http://amalhashim.wordpress.com

Sunday, April 4, 2010

C# | 3 Tier Architecture

In this article I am going to explain how easily we can build up a 3 layered application using .net framework and c#. I have an Employee database. For demonstration I have cut the scope by talking only to one table, EmployeeDetails.

In 3 tier, we are dealing with

1. Data Access Layer

2. Business Layer

3. Presentation Layer

Here is the code of my Data Access 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();
}
}

Now comes the business 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
;
}
}

Finally my presentation
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);
}
}

For better demonstration, you can think the presentation layer as a windows form application. With a 3 textboxes and one button. You can enter Id, Name and Address and use the button event for adding the data.

No comments: