Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles
(untagged)

Implementing Repository pattern and Dependency Injection in ADO.NET using Generics in C#

0.00/5 (No votes)
22 May 2016 6  
Repository pattern implementation in ADO.NET using Generics

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 do which was 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:

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<user>
{
    private DbContext _context;
    public UserRepository(DbContext context)
        : base(context)
    {
        _context = context;
    }

    public IList<user> 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();
        }
    }
}</user>

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.

First, create an interface namely IUserService:

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

    [OperationContract]
    User RegisterUser(User user);

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

    [OperationContract]
    bool UserNameExists(string username, string email);
}</user>

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.

 

Here is the implementation of DbContext:

public class DbContext
    {
    
        private readonly IDbConnection _connection;
        private readonly IConnectionFactory _connectionFactory;
        private readonly ReaderWriterLockSlim _rwLock = new ReaderWriterLockSlim();
        private readonly LinkedList<AdoNetUnitOfWork> _uows = new LinkedList<AdoNetUnitOfWork>();
 
        public DbContext(IConnectionFactory connectionFactory)
        {
            _connectionFactory = connectionFactory;
            _connection = _connectionFactory.Create();
        }
 
        public IUnitOfWork CreateUnitOfWork()
        {
            var transaction = _connection.BeginTransaction();
            var uow = new AdoNetUnitOfWork(transaction, RemoveTransaction, RemoveTransaction);
 
            _rwLock.EnterWriteLock();
            _uows.AddLast(uow);
            _rwLock.ExitWriteLock();
 
            return uow;
        }
 
        public IDbCommand CreateCommand()
        {
            var cmd = _connection.CreateCommand();
 
            _rwLock.EnterReadLock();
            if (_uows.Count > 0)
                cmd.Transaction = _uows.First.Value.Transaction;
            _rwLock.ExitReadLock();
 
            return cmd;
        }
 
        private void RemoveTransaction(AdoNetUnitOfWork obj)
        {
            _rwLock.EnterWriteLock();
            _uows.Remove(obj);
            _rwLock.ExitWriteLock();
        }
 
        public void Dispose()
        {
            _connection.Dispose();
        }
    }

 

The implementation of ConnectionFactory is :

public class DbConnectionFactory : IConnectionFactory
    {
 
        private readonly DbProviderFactory _provider;
        private readonly string _connectionString;
        private readonly string _name;
 
        public DbConnectionFactory(string connectionName)
        {
            if (connectionName == null) throw new ArgumentNullException("connectionName");
 
            var conStr = ConfigurationManager.ConnectionStrings[connectionName];
            if (conStr == null)
                throw new ConfigurationErrorsException(string.Format("Failed to find connection string named '{0}' in app/web.config.", connectionName));
 
            _name = conStr.ProviderName;
            _provider = DbProviderFactories.GetFactory(conStr.ProviderName);
            _connectionString = conStr.ConnectionString;
 
        }
 
        public IDbConnection Create()
        {
            var connection = _provider.CreateConnection();
            if (connection == null)
                throw new ConfigurationErrorsException(string.Format("Failed to create a connection using the connection string named '{0}' in app/web.config.", _name));
 
            connection.ConnectionString = _connectionString;
            connection.Open();
            return connection;
        }
    }

 

The implementation of UnitOfWork:

public class AdoNetUnitOfWork : IUnitOfWork
    {
        private IDbTransaction _transaction;
        private readonly Action<AdoNetUnitOfWork> _rolledBack;
        private readonly Action<AdoNetUnitOfWork> _committed;
 
        public AdoNetUnitOfWork(IDbTransaction transaction, Action<AdoNetUnitOfWork> rolledBack, Action<AdoNetUnitOfWork> committed)
        {
            Transaction = transaction;
            _transaction = transaction;
            _rolledBack = rolledBack;
            _committed = committed;
        }
 
        public IDbTransaction Transaction { get; private set; }
 
        public void Dispose()
        {
            if (_transaction == null)
                return;
 
            _transaction.Rollback();
            _transaction.Dispose();
            _rolledBack(this);
            _transaction = null;
        }
 
        public void SaveChanges()
        {
            if (_transaction == null)
                throw new InvalidOperationException("May not call save changes twice.");
 
            _transaction.Commit();
            _committed(this);
            _transaction = null;
        }
    }

 

The interfaces of IConnectionFacotry and IUnitOfWork looks like:

public interface IUnitOfWork
    {
        void Dispose();
 
        void SaveChanges();
    }
public interface IConnectionFactory
    {
        IDbConnection Create();
    }

 

 

Now in future, when I add another table in database, I would 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.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here