I am working on an asp.net project with oracle as backend. Initialy I developed this application using three layer architecture with UI as aspx pages, BLL and DAL as class library projects. I used static classes and methods in both BLL and DAL. DAL only consisted of single class with static methods of Select, Execute and ExecuteScalar which accepted queries forwarded by BLL classes.
DAL
private static string connString = ""
private static OracleConnection conn;
public static OracleConnection OpenConn()
{
if (conn==null)
{
conn = new OracleConnection(connString);
}
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
return conn;
}
public static DataTable Select(string query)
{
DataTable dt = new DataTable();
OracleDataAdapter da = new OracleDataAdapter(query, OpenConn());
da.Fill(dt);
return dt;
}
public static void Execute(string query)
{
OracleCommand cmd = new OracleCommand(query, OpenConn());
cmd.ExecuteNonQuery();
}
public static int ExecuteScaler(string query)
{
OracleCommand cmd = new OracleCommand(query, OpenConn());
int id = Convert.ToInt32(cmd.ExecuteScalar());
return id;
}
The way this DAL is called by BLL classes is as below Employee BLL
public static DataTable GetEmployees(int facilityid)
{
DataTable dt = new DataTable();
string q = string.Format("SELECT * FROM ..");
dt = OraDAL.Select(q);
return dt;
}
public static DataTable AddEmployee(string name, , int departmentid , int employeeType)
{
DataTable dt = new DataTable();
string q = string.Format("INSERT INTO ...");
dt = OraDAL.Select(q);
return dt;
}
Now I am refactoring the application. Same three layer architecture with BLL and DAL as class library projects with an additional class library project named Domain to contain domain classes which is referenced by all other projects. I use these classes for data transfer between layers. This time keeping DAL classes as static and BLL as normal classes. I have moved most of the functionality (queries) in to DAL. Now DAL has classes like EmployeesDAL, DepartmentsDAL along with the generic class that contains Select, Execute and ExecuteScalar static methods.
Employee.cs
public class Employee
{
public int Id { get; set; }
public string Name { get; set; }
public Department department { get; set; }
public EmployeeType employeeType { get; set; }
}
EmployeesDAL
public static List<Employee> GetEmployees(int departmentid)
{
if (departmentid > 0)
{
string query = string.Format("SELECT * FROM EMPLOYEES WHERE department='{0}')", departmentid);
return GetCollection(OraDAL.Select(query));
}
return null;
}
public static Employee GetEmployee(int employeeid)
{
if (employeeid > 0)
{
string query = string.Format("SELECT * FROM PMS_EMPLOYEES WHERE Id='{0}'", employeeid);
return GetSingle(OraDAL.Select(query));
}
throw new Exception("Employee id not valid");
}
public static int AddEmployee(Employee employee)
{
if (employee != null)
{
string query = string.Format("INSERT INTO PMS_EMPLOYEES (name, department, employee_type) VALUES ('{0}','{1}','{2}')", employee.Name, employee.department.Id, employee.employeeType.Id);
return OraDAL.Execute(query);
}
throw new Exception("Values not valid");
}
private static List<Employee> GetCollection(DataTable table)
{
List<Employee> employees = null;
if (table != null)
{
if (table.Rows.Count > 0)
{
employees = new List<Employee>();
foreach (DataRow row in table.Rows)
{
employees.Add(ReadDataRow(row));
}
}
}
return employees;
}
private static Employee GetSingle(DataTable table)
{
if (table != null)
{
if (table.Rows.Count > 0)
{
DataRow row = table.Rows[0];
return ReadDataRow(row);
}
}
return null;
}
private static Employee ReadDataRow(DataRow row)
{
Employee employee = new Employee()
{
Id=int.Parse(row["ID"].ToString()),
Name=row["NAME"].ToString(),
employeeType=EmployeeTypesDAL.GetEmployeeType(int.Parse(row["EMPLOYEE_TYPE"].ToString())),
department=DepartmentsDAL.GetDepartment(int.Parse(row["DEPARTMENT"].ToString()))
};
return employee;
}
EmployeesBLL.cs
public class EmployeesBLL
{
public List<Employee> GetEmployees(int departmentid)
{
if (departmentid > 0)
{
return EmployeesDAL.GetEmployees(departmentid);
}
return null;
}
public int AddEmployee(Employee employee)
{
if (employee != null)
{
return EmployeesDAL.AddEmployee(employee);
}
throw new Exception("Employee cannot be null");
}
The post is getting longer but I want you to have a better idea of the situation. I am facing few issues with the new design and made me ask this question, is this the right approach to do things? Since the main goal is to avoid moving datatables back and forth among different layers. Though this new way of coding the logic is too time consuming but is it worth the effort. The business layer is getting too thin as it seems the only service they provide is to call similar methods from DAL, do I need BLL in first place? If yes what possible situations could be where a separate BLL is necessary.
Edit
One issues that I noted with the above design is the number of database calls. Too many calls since when an object is populated all child objects are also populated resulting in calls to database. For instance populating Employee object will result in populating department instance. In most cases I would only need department id rather than whole department information.
