C#: How to Wrap my Unit of Work code in my repository classes

Question

See my repository code

public abstract class AdoRepository<T> where T : class
    {
        private static SqlConnection _connection;

        public AdoRepository(string connectionString)
        {
            _connection = new SqlConnection(connectionString);
        }

        public virtual T PopulateRecord(SqlDataReader reader)
        {
            return null;
        }

        public virtual void GetDataCount(int count)
        {

        }

        protected IEnumerable<T> GetRecords(SqlCommand command)
        {
            var list = new List<T>();
            command.Connection = _connection;
            _connection.Open();
            try
            {
                var reader = command.ExecuteReader();
                try
                {
                    while (reader.Read())
                    {
                        list.Add(PopulateRecord(reader));
                    }

                    reader.NextResult();
                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            GetDataCount(Convert.ToInt32(reader["Count"].ToString()));
                        }
                    }
                }
                finally
                {
                    // Always call Close when done reading.
                    reader.Close();
                }
            }
            catch(Exception ex)
            {
                string Msg = ex.Message;
            }
            finally
            {
                _connection.Close();
            }
            return list;
        }

        protected T GetRecord(SqlCommand command)
        {
            T record = null;
            command.Connection = _connection;
            _connection.Open();
            try
            {
                var reader = command.ExecuteReader();
                try
                {
                    while (reader.Read())
                    {
                        record = PopulateRecord(reader);
                        break;
                    }
                }
                finally
                {
                    // Always call Close when done reading.
                    reader.Close();
                }
            }
            finally
            {
                _connection.Close();
            }
            return record;
        }

        protected IEnumerable<T> ExecuteStoredProc(SqlCommand command)
        {
            var list = new List<T>();
            command.Connection = _connection;
            command.CommandType = CommandType.StoredProcedure;
            _connection.Open();
            try
            {
                var reader = command.ExecuteReader();
                try
                {
                    while (reader.Read())
                    {
                        var record = PopulateRecord(reader);
                        if (record != null) list.Add(record);
                    }
                }
                finally
                {
                    // Always call Close when done reading.
                    reader.Close();
                }
            }
            finally
            {
                _connection.Close();
            }
            return list;
        }
    }

   public class StudentRepository : AdoRepository<Student>
    {
        public int DataCounter { get; set; }

        public StudentRepository(string connectionString)
            : base(connectionString)
        {
        }
        public IEnumerable<Student> GetAll()
        {
            // DBAs across the country are having strokes 
            //  over this next command!
            using (var command = new SqlCommand("SELECT ID, FirstName,LastName,IsActive,StateName,CityName FROM vwListStudents"))
            {
                return GetRecords(command);
            }
        }
        public Student GetById(string id)
        {
            // PARAMETERIZED QUERIES!
            using (var command = new SqlCommand("SELECT ID, FirstName,LastName,IsActive,StateName,CityName FROM vwListStudents WHERE Id = @id"))
            {
                command.Parameters.Add(new ObjectParameter("id", id));
                return GetRecord(command);
            }
        }

        public IEnumerable<Student> GetStudents(int StartIndex, int EndIndex, string sortCol, string sortOrder)
        {
            string strSQL = "SELECT * FROM vwListStudents WHERE ID >=" + StartIndex + " AND ID <=" + EndIndex;
            strSQL += " ORDER BY " + sortCol + " " + sortOrder;
            strSQL += ";SELECT COUNT(*) AS Count FROM vwListStudents";
            var command = new SqlCommand(strSQL);
            return GetRecords(command);
        }

        public override Student PopulateRecord(SqlDataReader reader)
        {
            return new Student
            {
                ID = Convert.ToInt32(reader["ID"].ToString()),
                FirstName = reader["FirstName"].ToString(),
                LastName = reader["LastName"].ToString(),
                IsActive = Convert.ToBoolean(reader["IsActive"]),
                StateName = reader["StateName"].ToString(),
                CityName = reader["CityName"].ToString()
            };
        }

        public override void GetDataCount(int count)
        {
            DataCounter = count;
        }
    }

this way unit of work code added

public class UnitOfWorkFactory
{
    public static IUnitOfWork Create()
    {
        var connection = new SqlConnection(ConfigurationManager.ConnectionStrings("MyDb").ConnectionString);
        connection.Open();

        return new AdoNetUnitOfWork(connection, true);
    }
}

public class AdoNetUnitOfWork : IUnitOfWork
{
    public AdoNetUnitOfWork(IDbConnection connection, bool ownsConnection)
    {
        _connection = connection;
        _ownsConnection=ownsConnection;
        _transaction = connection.BeginTransaction();
    }

    public IDbCommand CreateCommand()
    {
        var command = _connection.CreateCommand();
        command.Transaction = _transaction;
        return command;
    }

    public void SaveChanges()
    {
        if (_transaction == null)
            throw new InvalidOperationException("Transaction have already been commited. Check your transaction handling.");

        _transaction.Commit();
        _transaction = null;
    }

    public void Dispose()
    {
        if (_transaction != null)
        {
            _transaction.Rollback();
            _transaction = null;
        }

        if (_connection != null && _ownsConnection)
        {
            _connection.Close();
            _connection = null;
        }
    }
}

using (var uow = UnitOfWorkFactory.Create())
{
    var repos = new UserRepository(uow);

    uow.SaveChanges();
}

public class UserRepository
{
    private AdoNetUnitOfWork _unitOfWork;

    public UserRepository(IUnitOfWork uow)
    {
        if (uow == null)
            throw new ArgumentNullException("uow");

        _unitOfWork = uow as AdoNetUnitOfWork;
        if (_unitOfWork == null)
            throw new NotSupportedException("Ohh my, change that UnitOfWorkFactory, will you?");
    }

    public User Get(Guid id)
    {
        using (var cmd = _unitOfWork.CreateCommand())
        {
            cmd.CommandText = "SELECT * FROM Users WHERE Id = @id");
            cmd.AddParameter("id", id);

            // uses an extension method which I will demonstrate in a 
            // blog post in a couple of days
            return cmd.FirstOrDefault<User>();
        }
    }
}

but i want to add unit of work code in my repository class. may be in AdoRepository class or StudentRepository class as a result i do not have to write the below code again and again.

using (var uow = UnitOfWorkFactory.Create())
{
    var repos = new UserRepository(uow);

    uow.SaveChanges();
}

so when i will be working with any repository like UserRepository then i have to repeat again and again this lines of code UnitOfWorkFactory.Create() which i do not want rather i am looking for a way to embed unit of work code in some where centralize as a result i could reduce the repeated code.

so looking for idea and suggestion. if possible give me modified version of code.

thanks

Answer 1

I will be straightforward about this:

A UoW that contains repositories is an anti pattern at least if DDD is involved. If it isn't, then you're dealing with a very simple app so make you life easier and use a ORM which implements what you want.

As a thumb rule, a repository may contain a UoW as an implementation detail and you should be certain that you really need a repository. A repository inside a UoW is a very specific case which is valid ONLY with CRUD apps and you'll just be reinventing a very small part of an ORM.

Using ado.net directly is wasting time (and money). Either use an ORM or a data mapper aka micro-ORM. With the latter option the UoW is the Db transaction.

You don't gain anything significant using Ado.Net,the performance gain is so small it's not even funny (I know that because I've benchmarked it). The only thing you'll get is longer devel time and human errors.

Actually, if I take a better look at your code, you're basically building a micro ORM, without knowing it. It's still not a repository though, because the abstraction doesn't have business semantics and it's too tightly coupled to an implementation detail.