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.