Implementing Repository Pattern And Dependency Injection In ADO.NET Using Generics In C#

Nowadays, I am trying to learn different design patterns in object oriented paradigm that are pretty useful to implement generic solutions for different scenarios. Few weeks ago for a job hunt, I got an assignment to develop a web application that would interact with database, so I took it up as a challenge and decided to make it loosely coupled using design patterns which were applicable in that scenario.

One of them which is implemented in my assignment is repository pattern using generics and with that Dependency Injection using which I injected dependencies of Repository class via constructor.

I made a generic class which would be inherited by other types against the different tables in the application. In this class, I have used different framework features like Reflection and Generics.

My generic class is an abstract class, so it needs to be inherited for making use of it. You will see next how we will use it.

Here is the Repository class:

public abstract class Repository<tentity> where TEntity : new()
{
    DbContext _context;

    public Repository(DbContext context)
    {
        _context = context;
    }

    protected DbContext Context
    {
        get
        {
          return this._context;
        }
    }

    protected IEnumerable<tentity> ToList(IDbCommand command)
    {
        using (var record = command.ExecuteReader())
        {
            List<tentity> items = new List<tentity>();
            while (record.Read())
            {

                items.Add(Map<tentity>(record));
            }
            return items;
        }
    }

    protected TEntity Map<tentity>(IDataRecord record)
    {
        var objT = Activator.CreateInstance<tentity>();
        foreach (var property in typeof(TEntity).GetProperties())
        {
            if (record.HasColumn(property.Name) && !record.IsDBNull(record.GetOrdinal(property.Name)))
                property.SetValue(objT, record[property.Name]);
        }
        return objT;
    }
}

Now, I have table in database User whose schema is:

CREATE TABLE [dbo].[tblUser] (
    [UserID]    INT           IDENTITY (1, 1) NOT NULL,
    [FirstName] NVARCHAR (25) NULL,
    [LastName]  NVARCHAR (25) NULL,
    [UserName]  NVARCHAR (25) NULL,
    [Password]  NVARCHAR (25) NULL,
    [IsActive]  BIT           NULL,
    [IsDeleted] BIT           NULL,
    [CreatedBy] INT           NULL,
    [CreatedAt] DATETIME      NULL,
    [UpdatedBy] INT           NULL,
    [UpdatedAt] DATETIME      NULL,
    [Email]     NVARCHAR (50) NULL,
    PRIMARY KEY CLUSTERED ([UserID] ASC)
);

Against this table, I have Model class for mapping from table to that type which looks like the following,

public class User
{
    public int UserID { get; set; }

    public string FirstName { get; set; }

    public string LastName { get; set; }

    public string UserName { get; set; }

    public string Password { get; set; }

    public bool IsActive { get; set; }

    public bool IsDeleted { get; set; }

    public DateTime CreatedAt { get; set; }

    public int CreatedBy { get; set; }

    public DateTime UpdatedAt { get; set; }

    public int UpdatedBy { get; set; }

    public string Email { get; set; }
}

We want to fetch data from User table for which we will create a Repository class for User type and then we will write implementation to fetch records from User table from database. All our methods that need to get data, insert data, update data or delete data from User table will reside in the UserRepository class.

Here is the implementation of User Repository class:

public class UserRepository : Repository
{
    private DbContext _context;
    public UserRepository(DbContext context)
        : base(context)
    {
        _context = context;
    }

    public IList GetUsers()
    {
        using (var command = _context.CreateCommand())
        {
            command.CommandText = "exec [dbo].[uspGetUsers]";

            return this.ToList(command).ToList();
        }
    }

    public User CreateUser(User user)
    {
        using (var command = _context.CreateCommand())
        {
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "uspSignUp";

            command.Parameters.Add(command.CreateParameter("@pFirstName", user.FirstName));
            command.Parameters.Add(command.CreateParameter("@pLastName", user.LastName));
            command.Parameters.Add(command.CreateParameter("@pUserName", user.UserName));
            command.Parameters.Add(command.CreateParameter("@pPassword", user.Password));
            command.Parameters.Add(command.CreateParameter("@pEmail", user.Email));

            return this.ToList(command).FirstOrDefault();
        }
    }

    public User LoginUser(string id, string password)
    {
        using (var command = _context.CreateCommand())
        {
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "uspSignIn";

            command.Parameters.Add(command.CreateParameter("@pId", id));
            command.Parameters.Add(command.CreateParameter("@pPassword", password));

            return this.ToList(command).FirstOrDefault();
        }
    }

    public User GetUserByUsernameOrEmail(string username, string email)
    {
        using (var command = _context.CreateCommand())
        {
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "uspGetUserByUsernameOrEmail";

            command.Parameters.Add(command.CreateParameter("@pUsername", username));
            command.Parameters.Add(command.CreateParameter("@pEmail", email));

            return this.ToList(command).FirstOrDefault();
        }
    }
}

We are done for the UserRepository for now, I have added methods and wrote a Stored Procedure to complete the assignment. Now, I will tell how to make use of it in the Service Layer or in Business Rule to do operations.

Firstly, create an interface named IUserService:

[ServiceContract]
public interface IUserService
{
    [OperationContract]
    IList GetUsers();

    [OperationContract]
    User RegisterUser(User user);

    [OperationContract]
    User Login(string id, string password);

    [OperationContract]
    bool UserNameExists(string username, string email);
}

Here is my WCF Service for user that calls the UserRepository for doing operations,

public class UserService : IUserService
{
    private IConnectionFactory connectionFactory;

    public IList<user> GetUsers()
    {
        connectionFactory = ConnectionHelper.GetConnection();

        var context = new DbContext(connectionFactory);

        var userRep = new UserRepository(context);

        return userRep.GetUsers();
    }

    public User RegisterUser(User user)
    {
        connectionFactory = ConnectionHelper.GetConnection();

        var context = new DbContext(connectionFactory);

        var userRep = new UserRepository(context);

        return userRep.CreateUser(user);
    }

    public User Login(string id, string password)
    {
        connectionFactory = ConnectionHelper.GetConnection();

        var context = new DbContext(connectionFactory);

        var userRep = new UserRepository(context);

        return userRep.LoginUser(id, password);
    }

    public bool UserNameExists(string username, string email)
    {
        connectionFactory = ConnectionHelper.GetConnection();

        var context = new DbContext(connectionFactory);

        var userRep = new UserRepository(context);

        var user = userRep.GetUserByUsernameOrEmail(username, email);
        return !(user != null && user.UserID > 0);

    }
}

You can see that when creating instance of UserRepository, I am injecting database context via constructor and then I am calling different methods from userRepository according to need.

Now in future, when I add another table in database, I will create another Repository type and implement its Data Access logic and will call it in the same way, so applying Dependency Injection and Repository pattern, we are following DRY principle to some extent, but I am sure we can make it better than this.