[erburrell]

I am developing a class structure to access a database. I am trying to separate the data as much as I can from the ui. To do this, I am developing several layers of code.

1. A "DataFile" Layer that includes the actual database (Access) and the DataSets for them.
I am splitting the data sets up into Admin functions, Employee Data Access, and so on.
2. A second layer I am calling the "Data Access Layer" (DAL) which includes classes that actually pull data from the database through the datasets. For example, for the Employees.cs class, I have the following under the DAL,

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using BurrSOFTDesktop.DataDirectory.DataFiles.EmployeesTableAdapters;
using BurrSOFTDesktop.DataDirectory.DataFiles;

namespace BurrSOFTDesktop.DataDirectory.DAL
{
    [System.ComponentModel.DataObject]
    class EmployeeDataAccess
    {
        private EmployeesTableAdapter _employees = null;
        protected EmployeesTableAdapter Adapter
        {
            get
            {
                if (_employees == null)
                    _employees = new EmployeesTableAdapter();
                return _employees;
            }
        }

        [System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Select, true)]
        public Employees.EmployeesDataTable GetEmployees()
        {
            return Adapter.GetEmployees();
        }

        [System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Select, false)]
        public Employees.EmployeesDataTable GetEmployeesByEmployeeID(int employeeID)
        {
            return Adapter.GetEmployeesByEmployeeID(employeeID);
        }

        [System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Select, false)]
        public Employees.EmployeesDataTable GetEmployeesByPartialLastName(string partialName)
        {
            return Adapter.GetEmployeesByPartialLastName(partialName);
        }

        [System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Select, false)]
        public Employees.EmployeesDataTable GetEmployeesByLocation(int locationID)
        {
            return Adapter.GetEmployeesByLocationID(locationID);
        }

        [System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Select, false)]
        public Employees.EmployeesDataTable GetEmployeesByDepartment(int departmentID)
        {
            return Adapter.GetEmployeesByDepartment(departmentID);
        }

        [System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Select, false)]
        public Employees.EmployeesDataTable GetEmployeesBySuppperisor(int supervisorID)
        {
            return Adapter.GetEmployeesBySupervisorID(supervisorID);
        }

        [System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Select, false)]
        public Employees.EmployeesDataTable GetEmployeesByHireDate(DateTime startDate, DateTime? endDate)
        {
            if (endDate == null)
                endDate = DateTime.Now;

            return Adapter.GetEmployeesByHireDateRange(startDate, endDate);
        }

        /// <summary>
        /// This method will add a new employee to the Employees Data Table.
        /// It should be noted that there is NO verification in this method to ensure that an employee is not added twice.
        /// The Business Layer should check for a duplicate employee by verifying full name prior to calling this method.
        /// </summary>
        [System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Insert, true)]
        public bool AddEmployee(string firstName, string middleName, string lastName, string address1, string address2,
                                string city, string state, string zip, string phone1, string phone2, DateTime? hireDate,
                                int? locationID, string title, int? departmentID, int? supervisorID, decimal? payRate,
                                bool wRApprover, bool pRApprover, decimal? pRLimit, string user1, string user2, string user3)
        {
            Employees.EmployeesDataTable employees = new Employees.EmployeesDataTable();
            Employees.EmployeesRow employee = employees.NewEmployeesRow();

            if (firstName == string.Empty) employee.SetFirstNameNull();
                else employee.FirstName = firstName;
            if (middleName == string.Empty) employee.SetMiddleNameNull();
                else employee.MiddleName = middleName;
            if (lastName == string.Empty) employee.SetLastNameNull();
                else employee.LastName = lastName;
            if (address1 == string.Empty) employee.SetAddress1Null();
                else employee.Address1 = address1;
            if (address2 == string.Empty) employee.SetAddress2Null();
                else employee.Address2 = address2;
            if (city == string.Empty) employee.SetCityNull();
                else employee.City = city;
            if (state == string.Empty) employee.SetStateNull();
                else employee.State = state;
            if (zip == string.Empty) employee.SetZipNull();
                else employee.Zip = zip;
            if (phone1 == string.Empty) employee.SetPhone1Null();
                else employee.Phone1 = phone1;
            if (phone2 == string.Empty) employee.SetPhone2Null();
                else employee.Phone2 = phone2;
            if (hireDate == null) employee.SetHireDateNull();
                else employee.HireDate = (DateTime)hireDate;
            if (locationID == null) employee.SetLocationIDNull();
                else employee.LocationID = (int)locationID;
            if (title == string.Empty) employee.SetTitleNull();
                else employee.Title = title;
            if (departmentID == null) employee.SetDepartmentIDNull();
                else employee.DepartmentID = (int)departmentID;
            if (supervisorID == null) employee.SetSupervisorNull();
                else employee.Supervisor = (int)supervisorID;
            if (payRate == null) employee.SetPayRateNull();
                else employee.PayRate = (float)payRate;
            employee.WRApprover = wRApprover;
            employee.PRApprover = pRApprover;
            if (pRLimit == null) employee.SetPRLimitNull();
            else employee.PRLimit = (float)pRLimit;
            if (user1 == string.Empty) employee.SetUser1Null();
                else employee.User1 = user1;
            if (user2 == string.Empty) employee.SetUser2Null();
                else employee.User2 = user2;
            if (user3 == string.Empty) employee.SetUser3Null();
                else employee.User3 = user3;

            employees.AddEmployeesRow(employee);

            int rowsAffected = Adapter.Update(employee);

            // return true if one and only one row was affected.
            return rowsAffected == 1;
        }

        [System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Update, true)]
        public bool UpdateEmployee(int employeeID, string firstName, string middleName, string lastName, string address1, string address2,
                                string city, string state, string zip, string phone1, string phone2, DateTime? hireDate,
                                int? locationID, string title, int? departmentID, int? supervisorID, decimal? payRate,
                                bool? wRApprover, bool? pRApprover, decimal? pRLimit, string user1, string user2, string user3)
        {
            Employees.EmployeesDataTable employees = Adapter.GetEmployeesByEmployeeID(employeeID);
            if (employees.Count == 0)
                return false;

            Employees.EmployeesRow employee = employees[0];

            if (firstName != string.Empty) employee.FirstName = firstName;
            if (middleName != string.Empty) employee.MiddleName = middleName;
            if (lastName != string.Empty) employee.LastName = lastName;
            if (address1 != string.Empty) employee.Address1 = address1;
            if (address2 != string.Empty) employee.Address2 = address2;
            if (city != string.Empty) employee.City = city;
            if (state != string.Empty) employee.State = state;
            if (zip != string.Empty) employee.Zip = zip;
            if (phone1 != string.Empty) employee.Phone1 = phone1;
            if (phone2 != string.Empty) employee.Phone2 = phone2;
            if (hireDate != null) employee.HireDate = (DateTime)hireDate;
            if (locationID != null) employee.LocationID = (int)locationID;
            if (title != string.Empty) employee.Title = title;
            if (departmentID != null) employee.DepartmentID = (int)departmentID;
            if (supervisorID != null) employee.Supervisor = (int)supervisorID;
            if (payRate != null) employee.PayRate = (float)payRate;
            if (wRApprover != null) employee.WRApprover = (bool)wRApprover;
            if (pRApprover != null) employee.PRApprover = (bool)pRApprover;
            if (pRLimit != null) employee.PRLimit = (float)pRLimit;
            if (user1 != string.Empty) employee.User1 = user1;
            if (user2 != string.Empty) employee.User2 = user2;
            if (user3 != string.Empty) employee.User3 = user3;

            int rowsAffected = Adapter.Update(employee);

            return rowsAffected == 1;
        }

        [System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Delete, true)]
        public bool DeleteEmployee(int employeeID)
        {
            int rowsAffected = Adapter.DeleteEmployeeByEmplyeeID(employeeID);

            return rowsAffected == 1;
        }
    }
}

3. A Business Logic Layer (BLL) that access the DAL only to perform all the required work. Here is where my question lies...\
4. The actual UI layer that will pull form the BLL.

Now, on the the real question...
In my business logic layer, when developing my classes for accessing the data, how fractured should I make the class files?
For example, take the Employees DAL I posted above. SHould I make one big class that tries to cover all the GetEmployeesBy... methods, or should I break them up into multiple classes? For example,

Should I make the following classes for the employee structures:
1. GetAllEmployees
2. GetAnEmployee
3. GetEmployeesBySupervisor
...
and so on.

The idea of my BLL is that I will aceess the DAL and get the information dumped into datatables, and then access each item in a data row through methods in the class. So if I called GetAnEmployee class, I could get the FirstName with GetAnEmployee.FirstName. For the classes with multiple employes in the table, I would use and index variable that can be set by the calling program.

Any thoughts?

Thanks,

Ed.

[PennyBoki]

I think you should go with it the way it is now. I haven't seen such code where the methods are implemented in classes. I know you want to make things easier for yourself, but I don't think that this is the way. Think about it what do you really get beside the small typing "improvement" in contrast to more work with classes, which means more files. Also think about scalability, as well as other "bad" consequences that this idea would imply.

[MentalFloss]

It is actually seriously time for you to consider an object relational mapper library (OR/M) such as NHibernate or LINQ.

It's going to get pretty painful quickly if you continue on this route. Now, you may want to just code it yourself and have something against using existing libraries. That's fine. However, the tools I mentioned are going to simplify things greatly. It will remove much of the required boilerplate code that goes into writing the data access layer.

[baavgai]

Why are you reinventing the wheel? You're wrapping lots of code, but I'm not seeing anything added. In particular, I'd avoid that Update. You go through all the effort to grab a record from the database, use it.

Creating and returning a DataTable is a convenience method; don't get carried away. Your adapter should Fill and Update. The rest of the work can be preformed on the DataSet directly and your DAL needn't get involved. A DataAdapter Update is a truly clever mechanism, applying inserts, updates, and deletes.

public class DataManager {
	public int Fill(Employees.EmployeesDataTable dt) { }
	
	// no need to change the name here, this is the primary key
	public int Fill(Employees.EmployeesDataTable dt, int employeeID) { }
	
	public int FillByPartialLastName(Employees.EmployeesDataTable dt, string partialName) { }
	public int FillByLocation(Employees.EmployeesDataTable dt, int locationID) { }
	public int FillByByDepartment(Employees.EmployeesDataTable dt, int departmentID) { }
	public int FillByBySupervisor(Employees.EmployeesDataTable dt, int supervisorID) { }
	
	// no need to use cute types
	public int FillByByHireDate(Employees.EmployeesDataTable dt, DateTime startDate, DateTime endDate) { }
	public int FillByByHireDate(Employees.EmployeesDataTable dt, DateTime startDate) {
		return FillByByHireDate(dt, startDate, DateTime.Now)
	}
	
	public int Update(Employees.EmployeesDataTable dt) { }
}

I use the above pattern a lot. Hiding all data manipulation behind the one public Manager. The rest of to program can freely manipulate the DataSets, not worrying about any data store details. I have had to move large projects to different databases and using this methodology saves a lot.

Hope this helps.

[erburrell]

Very good replies, and thanks for them.

Mental,

I have downloaded NHibernate, and am trying to work through how to set it up and use it.

I still Have a question regarding code in my interface. Now mind that I am by no means a professional programmer, and am just starting down the road...

Everthing I have read regarding programing with a database states that the data management and the UI need to be completely seperate. If I use a mapper such as NHibernate, I still need to enter all my Business Logic somewhere. From baavgai's comment, it sounds like he is saying to just place most of the data access functions in the UI, but that is contrary to what I've read.

Should I use a mapper, and then create the Business Layer between it and the UI?

Not trying to be stubborn, just wanting to layout my code to make it easier for myself and others later. If there is a book, or reference out there that covers this topic, I'd love to know about it.

Thanks,

Ed.

[MentalFloss]

You are going to want to read up and research on NHibernate for a little while first. Once you feel that you have a good grasp of it, create a very small test application - I'm talking one table, and one form that has data populated from that table. Get the application working so that it pulls existing information in your table and loads it to the form.

Then create a new form that allows insert of a new item.

Then have the form able to load a different record each time you click a button (such as Next/Previous).

Once that's done, do the same for LINQ. See which one works better for you.

After all that, create a prototype application that's nearly as complex for a particular feature and use the one you liked better.

From there, you'll have a really good idea how you want things to look and work.

Sorry that this is general but you're at a stage where it just has to be this way. The problems aren't easy to solve at this level of detail. So, we'll need specific problems to be of better help.

You have more than enough to get you started though. Good luck.