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 Layerstatic 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:
Post a Comment