Problem in DAL

[koraykazgan]

Hi there,

I am trying to write a data access layer for my programm. In my programm I have 3 classes:

Entity - my empty, base class for all objects in the programm

Person - this class is derived from entity and has ID, LastName and FirstName properties

User - this class is derived from Person, because all users are persons. this class aditionally has password and dateOfRegisteration properties.


For this structure I have written a DAL with 3 classes:

EntityDAL - this class is the abstract base DAL class. It implements getByID, find and save methods. Besides it has 3 abstract functions named getGetStoredProcedureName, getFindStoredProcedureName and getSaveStoredProcedureName. Whenever an instance of a derived class is created and the find method is called, the find method in this class is called. This function then gets the find stored procedure name of the derived class, and executes this procedure. This class also has one more abstract method called rowToObject, which gets a DataRow object as parameter and returns an entity object.

PersonDAL - this class is derived from EntityDAL. It is implementing 3 abstract function from the base class named getGetStoredProcedureName, getFindStoredProcedureName and getSaveStoredProcedureName. This three methods are simply returning the names of the related stored procedures. Additionally this class implements the abstract method rowToObject. In this method I get a DataRow which is filled with related data to a person object. I simplye create a new Person and fill its properties with the row objects values. Because Person "is a" entity, I can return this person object, even if the method's return type was entity.

The find method in EntityDAL executes the find SP, and retrieves more than one DataRow. For each DataRow, the method rowToObject is executed, and each entity get by this method is stored in an entityCollection. After all rows are looped, the collection is returned. This scenario is working perfectly. I am pasting a cutted down verison of my classes:

public abstract class EntityDAL
{
protected abstract string getFindStoredProcedureName { get; }
protected abstract Entity rowToObject(DataRow row);

public EntityCollection find()
{
SqlCommand command = new SqlCommand();
command.CommandText = getFindStoredProcedureName;
command.CommandType = CommandType.StoredProcedure;
command.Connection = connection;

DataSet ds = new DataSet();
SqlDataAdapter adapter = new SqlDataAdapter(command);
adapter.Fill(ds);

EntityCollection entities = new EntityCollection();
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
Entity entity = rowToObject(ds.Tables[0].Rows);
entities.Add(entity);
}

return entities;
}
}

public class PersonDAL : EntityDAL
{
protected override string getFindStoredProcedureName { get { return "PersonsFind"; } }

protected override Entity rowToObject(DataRow row)
{
Person person = new Person();
person.ID = Convert.ToInt32(row["ID"]);
person.LastName = row["LastName"].ToString();
person.FirstName = row["FirstName"].ToString();
return person;
}
}

So far my code is running fine. But now I have to implement UserDAL. This class should be derived from PersonDAL, because User derives from Person. My problem is that I can't just create an instance of UserDAL and execute the finde method, because it would only get the users properties. UsersFind will only retrieve password and dateOfRegisteration fields. FirstName and LastName are not business of UsersFind. Again I am pasting the code for UserDAL:

public class UserDAL : PersonDAL
{
protected override string getFindStoredProcedureName { get { return "UsersFind"; } }
protected override Entity rowToObject(DataRow row)
{
User user = new User();
user.ID = Convert.ToInt32(row["ID"].ToString());
user.Password = row["Password"].ToString();
user.DateOfRegisteration = Convert.ToDateTime(row["DateOfRegisteration"]);
return user;
}
}

The stored procedure UsersFind is only retrieving data from the users table. I could join users with persons and retrieve all required data, but I am not sure if this would be a good design. Everytime the person table changes, I have to change the PersonsFind stored proc as well as UsersFind.
Even if I had joined these two tables in UsersFind, how can I put all the data togehter? Maybe I can call the rowToObject method of the base class (PersonDAL) in UserDAL.rowToObject. It would return a Person object, but I need a User object. So I have to downcast the person object to user, and then fill the fields of user, but I can not downcast person to user.

So my questions are:

1) Is there any better way rather than joining users and persons in UsersFind?
2) Can I downcast person to user in anyway?
3) Is this model correct, or do I have to change the model?

Important for me is that I don't do things twice. For example I could create a new user object and set FirstName and LastName in UserDAL.rowToObject. But then I have written this more than once.
I know, this was a long post, but I had to explain it detailed.

Thanks for help,
Bye.

 

[B00gyeMan]

There is a question you should answer first:

* What is the difference between a person and an user? Can any person be "promoted" to the rank of user? Or they are completely different? (I.e. you have a database of persons (and their additional details) and you must also have a table for the users of the application? In this case, I suggest you should completely separate the "Person" from "User" since their only common attribute is that they are people in which case you should not implement this hierarchy of classes just to take advantage of polymorphism).

Separating users data from persons data could be a good thing because you can separately store an user's name (i.e. this could be encrypted) and also other details like password expiration, max. number of mispelled password retries, etc...

P.S.: Try to drop the "DAL" suffix from the name of the classes. It is unnecessary and redundant since I suppose the name of the namespace which contains them also ends in "DAL".

 

[koraykazgan]

Hi again,

I am planning to write a football manager game. In the system there should be a person entity. Also there should be many positions like User, Player, Coach, Doctor, Staff, etc.

Each of this people share common properties like name and id, etc. Therefore I have designed a class names Person, which stores this common properties. All other entities derive from this class.

Also in the database I have tables like Persons, Users, Players, Doctors, etc. (i.e. in tables Users or Players, there are no FirstName and LastName fields. These are stored in Persons table. The Unique ID of the Persons table for any Player or user, is then stored in the specific table. So when I look in the Users table, the ID in this table is the same like in the Persons table. With this in mind, I can join both tables by the ID field of both tables.

You are right that the DAL classes are in a different namespace, but I want to leave this suffix on the dal classes, because when I use this classes I create an instance of the dal class. Then I create an instance of the entity. I fill the entity with information and pass the entity to the dal instance's save method. When I remove the DAL suffix, I got two classes with the same name. Therefore I want to leave this naming convention the same.

So can you help me with this??