Click here to Skip to main content
15,868,014 members
Articles / Programming Languages / C# 4.0

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

Rate me:
Please Sign up or sign in to vote.
4.96/5 (14 votes)
22 May 2016CPOL2 min read 97.2K   3.8K   36   30
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:

C#
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:

C#
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:

C#
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></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:

C#
[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:

C#
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 == nullthrow 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 { getprivate 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, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer
Pakistan Pakistan
Ehsan Sajjad is a Microsoft Certified Professional, Microsoft Certified C# Specialist and he is also among the top users on StackOverflow from Pakistan with 50k+ reputation at time of writing this and counting.

He is a passionate software developer with around 5 years of professional experience in Microsoft Technologies both web and desktop applications and always open to learn new things and platforms especially in mobile application development and game development.

Some Achievements :

  • 5th Top Contributor from Pakistan on Stackoverflow.com
  • Top User in ASP.NET MVC from Pakistan on Stackoverflow.com
  • 21st June 2017 - Article of the Day - ASP.NET Community (Beginners Guide on AJAX CRUD Operations in Grid using JQuery DataTables in ASP.NET MVC 5)
  • 19th April 2017 - Article of the Day - ASP.NET Community (ASP.NET MVC Async File Uploading using JQuery)
  • March 2017 - Visual C# Technical Guru Silver Medal on Microsoft Tech Net Wiki Article Competition
  • 20 January 2017 - Article of the Day - ASP.NET Community (Async File Uploading in ASP.NET MVC)
  • 22nd September 2016 - Article of the Day - ASP.NET Community (GridView with Server Side Filtering, Sorting and Paging in ASP.NET MVC 5)
  • 22nd August 2016 - Article of the Day - ASP.NET Community (Beginners Guide for Creating GridView in ASP.NET MVC 5)
  • December 2015 - C-SharpCorner Monthly Winner

Comments and Discussions

 
QuestionPerformance Pin
Akuma_kyd12-Nov-19 16:31
Akuma_kyd12-Nov-19 16:31 
QuestionConverting the pattern to Web API Pin
crcklssp22-Jul-19 21:49
crcklssp22-Jul-19 21:49 
QuestionOther db support Pin
Cool Smith22-Mar-19 3:16
Cool Smith22-Mar-19 3:16 
QuestionHow you can incorporate SP calling from repository Pin
Tridip Bhattacharjee12-Feb-17 22:07
professionalTridip Bhattacharjee12-Feb-17 22:07 
AnswerRe: How you can incorporate SP calling from repository Pin
Ehsan Sajjad12-Feb-17 23:40
professionalEhsan Sajjad12-Feb-17 23:40 
QuestionMore complex queries Pin
InbarBarkai22-May-16 20:53
InbarBarkai22-May-16 20:53 
AnswerRe: More complex queries Pin
Ehsan Sajjad23-May-16 1:01
professionalEhsan Sajjad23-May-16 1:01 
GeneralRe: More complex queries Pin
InbarBarkai24-May-16 18:44
InbarBarkai24-May-16 18:44 
GeneralRe: More complex queries Pin
Ehsan Sajjad24-May-16 23:33
professionalEhsan Sajjad24-May-16 23:33 
GeneralRe: More complex queries Pin
InbarBarkai25-May-16 1:24
InbarBarkai25-May-16 1:24 
GeneralRe: More complex queries Pin
Ehsan Sajjad25-May-16 1:26
professionalEhsan Sajjad25-May-16 1:26 
GeneralRe: More complex queries Pin
InbarBarkai25-May-16 3:28
InbarBarkai25-May-16 3:28 
GeneralRe: More complex queries Pin
Ehsan Sajjad25-May-16 22:19
professionalEhsan Sajjad25-May-16 22:19 
GeneralRe: More complex queries Pin
InbarBarkai26-May-16 0:15
InbarBarkai26-May-16 0:15 
GeneralRe: More complex queries Pin
Ehsan Sajjad27-May-16 0:24
professionalEhsan Sajjad27-May-16 0:24 
QuestionDiscuss all advantages point wise of Repository pattern Pin
Tridip Bhattacharjee13-Oct-15 22:11
professionalTridip Bhattacharjee13-Oct-15 22:11 
AnswerRe: Discuss all advantages point wise of Repository pattern Pin
Ehsan Sajjad13-Oct-15 23:22
professionalEhsan Sajjad13-Oct-15 23:22 
SuggestionUse a container on next step Pin
LOG.ETI12-Oct-15 3:12
LOG.ETI12-Oct-15 3:12 
GeneralRe: Use a container on next step Pin
Ehsan Sajjad12-Oct-15 3:31
professionalEhsan Sajjad12-Oct-15 3:31 
GeneralRe: Use a container on next step Pin
Ehsan Sajjad12-Oct-15 3:38
professionalEhsan Sajjad12-Oct-15 3:38 
GeneralRe: Use a container on next step Pin
LOG.ETI12-Oct-15 21:21
LOG.ETI12-Oct-15 21:21 
GeneralRe: Use a container on next step Pin
Ehsan Sajjad13-Oct-15 1:47
professionalEhsan Sajjad13-Oct-15 1:47 
GeneralRe: Use a container on next step Pin
LOG.ETI13-Oct-15 1:56
LOG.ETI13-Oct-15 1:56 
GeneralRe: Use a container on next step Pin
Ehsan Sajjad13-Oct-15 3:41
professionalEhsan Sajjad13-Oct-15 3:41 
GeneralRe: Use a container on next step Pin
LOG.ETI13-Oct-15 3:59
LOG.ETI13-Oct-15 3:59 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.