在三层架构中适当布局功能

我正在一个asp.net项目与oracle作为后端工作。最初，我使用三层架构开发这个应用程序，其中UI作为aspx页面，BLL和DAL作为类库项目。我在BLL和DAL中都使用了静态类和方法。DAL仅由单个类组成，具有静态方法Select, Execute和ExecuteScalar，这些方法接受BLL类转发的查询。

木豆

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;
}

这个DAL被BLL类调用的方式如下所示员工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;
    }

现在我正在重构应用程序。与BLL和DAL相同的三层体系结构作为类库项目，另外还有一个名为Domain的类库项目，用于包含所有其他项目引用的域类。我使用这些类在层之间传输数据。这一次将DAL类保持为静态，将BLL保持为正常类。我已经将大部分功能(查询)移到了DAL中。现在DAL有了EmployeesDAL、DepartmentsDAL等类，以及包含Select、Execute和ExecuteScalar静态方法的泛型类。

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");
    }

这篇文章越来越长，但我希望你对情况有一个更好的了解。我在新设计中遇到了一些问题，这让我问了一个问题，这是正确的做事方法吗?因为主要目标是避免在不同层之间来回移动数据表。虽然这种新的逻辑编码方式太耗时，但值得付出努力。业务层变得太薄了，因为它们提供的唯一服务似乎就是从DAL调用类似的方法，我首先需要BLL吗?如果有，在什么情况下需要单独的清单?

编辑

我在上述设计中注意到的一个问题是数据库调用的数量。由于在填充对象时也填充了所有子对象，因此调用太多，导致调用数据库。例如，填充Employee对象将导致填充department实例。在大多数情况下，我只需要部门id，而不是整个部门的信息。