3
\$\begingroup\$

I am attempting to update some legacy code that I work with, but my knowledge of data access is limited to a basic use of Entity Framework and simple Dapper. After some research, I have a loose attempt at implementing the repository and unit of work patterns in C# using Dapper.

In the actual implementation of this, there is little or no raw SQL, but all CRUD operations are done with stored procedures as some of the objects are not single table rows. I just used a really rudimentary example here so I could save time. The same goes for connections to the actual database, but I actually do not know the best way to share a connection between repositories without passing it as a parameter to the constructor. A downloadable repository can be found here.

This is the repository interface that is being implemented.

namespace Repository.Interfaces
{
    public interface IRepository<T>
    {
        void Insert(T entity);
        void Delete(T entity);
        void Update(T entity);
        T GetById(int id);
        void Commit();
    }
}

This is the generic base class.

using Repository.Interfaces;
using System;
using System.Data.SQLite;
using Dapper;

namespace Repository.Repositories
{
    public abstract class RepositoryBase<T> : IRepository<T>, IDisposable
    {
        /// <summary>
        /// Just using a SQLite database for the purposes of this demo.
        /// </summary>
        protected SQLiteConnection _connection = new SQLiteConnection("Data Source=:memory:");
        protected SQLiteTransaction _transaction;
        private bool _disposed;
        /// <summary>
        /// Opens a connection for each object created.
        /// </summary>
        protected internal RepositoryBase()
        {
            _connection.Open();
            _transaction = _connection.BeginTransaction();
        }
        public abstract void Delete(T entity);
        public abstract T GetById(int id);
        public abstract void Insert(T entity);
        public abstract void Update(T entity);
        /// <summary>
        /// Cleans up after a transaction is complete.
        /// </summary>
        public void Commit()
        {
            try
            {
                _transaction.Commit();
            }
            catch
            {
                _transaction.Rollback();
            }
            finally
            {
                _transaction.Dispose();
                _transaction = _connection.BeginTransaction();
            }
        }
        public void Dispose()
        {
            Disposable(true);
            _connection.Dispose();
            GC.SuppressFinalize(this);
        }

        private void Disposable(bool disposing)
        {
            if (!_disposed)
            {
                if (disposing)
                {
                    _transaction.Dispose();
                    _transaction = null;
                }

                _disposed = true;
            }
        }

        public void CreateDatabase()
        {
            const string sqlCmd = @"create table Dogs (Id int PRIMARY KEY, Name varchar(50), Breed varchar(50)); "
                            + "create table Cats (Id int PRIMARY KEY, Name varchar(50), ShortHair bit); "
                            + "insert into Dogs VALUES (1, 'Fido', 'Chocolate Lab');"
                            + "insert into Dogs VALUES (2, 'Spot', 'Dalmation');"
                            + "insert into Cats VALUES (1, 'Mittens', 1);"
                            + "insert into Cats VALUES (2, 'Mr. Floof', 0);";
            _connection.Query(sqlCmd);
        }

    }
}

This is my rudimentary implementation of a repository.

using Dapper;
using Repository.Models;
using System.Linq;
using System.Collections.Generic;

namespace Repository.Repositories
{
    public class DogRepository : RepositoryBase<Dog>
    {
        public override void Delete(Dog dog) 
        {
            var dogSql = @"delete from Dogs where Id = " + dog.Id +";";
            _connection.Query(dogSql, transaction: _transaction);
        }

        public override Dog GetById(int id)
        {
            return _connection.Query<Dog>("select * from Dogs where Id = " + id + ";", transaction: _transaction).FirstOrDefault();
        }

        public override void Insert(Dog dog)
        {
            var dogSql = @"insert into Dogs VALUES (" +
                            dog.Id + ", '" + dog.Name + "', '" + dog.Breed + "');";
            _connection.Query(dogSql, transaction: _transaction);
        }

        public override void Update(Dog dog)
        {
            var dogSql = @"update Dogs set Name = '" + dog.Name + "' and Breed = '" + dog.Breed + "' where Id = " + dog.Id + ";";
            _connection.Query(dogSql, transaction: _transaction);
        }

        public IEnumerable<Dog> GetAll()
        {
            return _connection.Query<Dog>("select * from Dogs", transaction: _transaction);
        }
    }
}

For a calling example, I have a really basic console application:

using Repository.Repositories;
using System;

namespace RepositoryConsole
{
    public class Program
    {
        public static void Main(string[] args)
        {
            var dogs = new DogRepository();

            dogs.CreateDatabase();

            dogs.Insert(new Repository.Models.Dog
            {
                Breed = @"German Shepherd",
                Id = 3,
                Name = @"Good boy"
            });

            var allDogs = dogs.GetAll();

            Console.WriteLine("Dogs: ");
            foreach (var dog in allDogs)
            {
                Console.WriteLine(dog);
            }
            dogs.Commit();
            Console.ReadKey();
        }


    }
}

Is this code structured correctly to implement a transaction featuring rollbacks?

As I am using Dapper, do I even need to implement managed transactions?

What changes could be made to make the code more resistant to errors on saves/deletes?

For reference, I will actually be using SQL Server rather than SQLite in the production code.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.