Repository pattern usage

Question

I need some advice regarding repository pattern. Let's assume i have UserBal which stands for business logic which uses UserRepositoryDal where all database logic for that entity stands (queries to table T_Users because my repositories uses stored procedures.) In my database i have table T_Users and T_UserTypes. In T_User table there is relation to T_UserTypes. It means each user has own user type.

In my application i've created:

• UserBal - for business logic and uses UserRepositoryDal

• UserRepositoryDal - all database logic to get data for 'T_Users'

Repository have common functionality like: GetAll(), Insert etc..

My entites are:

public class User
{
     public int Id { get; set; }
     public string Name{ get; set; }
     public int FK_UserType { get; set; }
}

public class UserType
{
     public int Id { get; set; }
     public string Name{ get; set; }
}

I am not sure at this point whether should i create also repository for table T_UserTypes like: UserTypeRepositoryDal and for business logic UserTypeBal which would use UserTypeRepositoryDal ?

or should i just modify my User class instead of:

public int FK_UserType { get; set; } 

change to:

public UserType UserType { get; set; }

so there would not be UserTypeBal and UserTypeRepositoryDal at all

Nevertheless when in application i would like to get list of UserType for instance for combo box control or whatever i would not have UserTypeRepositoryDal with GetAll() so where should i put logic for UserTypes GetAll(). Should it be in UserRepositoryDal?

Answer 1

You do not need one repository per table. A repository can encapsulate all data access required for a given entity. The record in the users table is associated with a record in the user types table by way of a foreign key. You can model this in C# as well by replacing the User.FK_UserType property with an actual UserType object. The UserRepositoryDal (which "UserRepository" is descriptive enough that you can drop the "Dal" suffix") can query the database appropriately using a JOIN:

SELECT users.a, users.b, userTypes.a, userTypes.b
FROM t_users users
    JOIN t_user_types ON ...

When you need to reuse database mappings from tables to objects, create a new class in C# just for the data mapping.

No need for a separate repository.

---

Ok - let's say now i want to populate combobox with all UserTypes names and values as id to be taken later on when user selects something from that control. Where GetAll for UserType should be defined? in UserRepository as well?

This is honestly a good question, and one I have as well. Laiv mentioned adding a "lookup" which could be its own "repository" or data access class that specializes in generating the data for things like combo boxes. You can also add a GetUserTypes() method or a UserTypes property to the UserRepository that returns a collection of UserType objects, which you can use later on to create a new collection of combo box items.

I've used a property on the repository for lookup entities. Transforming them into combo box items is usually pretty easy:

var comboBoxItems = from userType in userRepository.UserTypes
                    select new SelectListItem()
                    {
                        Text = userType.Name,
                        Value = userType.Id.ToString()
                    };

Answer 2

First of all, I hope adding Bal(Business access layer) and Dal(Data Access Layer) to name of classes are just for sample. Because it is really bad naming convention I think. Your layer name is enough to understand it.

And for your question, exactly what you need to do is creating UserTypeRepositoryDal and UserTypeBal. As you think, if you do not that, you can not get user type list directly or you would add another class and this would be against Single Responsibility Principle(SRP). Also, every development about UserType won't be placed on appropriate class.

Beyond this, you can add public UserType UserType { get; set; } on User entity even there is FK_UserType. This is good way to manage 1-1 or 1-n relations. It looks like :

public class User
{
     public int Id { get; set; }
     public string Name{ get; set; }
     public int FK_UserType { get; set; }
     public UserType UserType { get; set; }
}

You can load UserType in User object by using FK_UserType. If you use ORM like Entity Framework, it can do this for you.

This way is proper to manage 1-1 and/or 1-n relations. If you have n-n relation, then you need relation table and its Bal and Dal classes.

Also, designing your repository and your entity class should not depend on whether you use Store Procedures or not.

And, to avoid creating too much repository classes, I recommend Generic Repository Pattern.