How to add unit of work pattern in my repository pattern code

Question

i have design a small repository pattern code but unit of work is missing there. so i am looking for guidance like how with minimum effort i can add unit of work code into my repository pattern code. looking for help and code sample. here is my full code.

Base repository

 public abstract class AdoRepository<T> where T : class
    {
        private SqlConnection _connection;
        public virtual void Status(bool IsError, string strErrMsg)
        {

        }

        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 reader = (SqlDataReader) null;
            var list = new List<T>();
            try
            {
                command.Connection = _connection;
                _connection.Open();
                reader = command.ExecuteReader();
                while (reader.Read())
                {
                    list.Add(PopulateRecord(reader));
                }

                reader.NextResult();
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        GetDataCount(Convert.ToInt32(reader["Count"].ToString()));
                    }
                }
                Status(false, "");
            }
            catch (Exception ex)
            {
                Status(true, ex.Message);
            }
            finally
            {
                // Always call Close when done reading.
                reader.Close();
                _connection.Close();
                _connection.Dispose();
            }

            return list;
        }

        protected T GetRecord(SqlCommand command)
        {
            var reader = (SqlDataReader)null;
            T record = null;

            try
            {
                command.Connection = _connection;
                _connection.Open();

                reader = command.ExecuteReader();
                while (reader.Read())
                {
                    record = PopulateRecord(reader);
                    Status(false, "");
                    break;
                }
            }
            catch (Exception ex)
            {
                Status(true, ex.Message);
            }
            finally
            {
                reader.Close();
                _connection.Close();
                _connection.Dispose();
            }
            return record;
        }

        protected IEnumerable<T> ExecuteStoredProc(SqlCommand command)
        {
            var reader = (SqlDataReader)null;
            var list = new List<T>();
            try
            {
                command.Connection = _connection;
                command.CommandType = CommandType.StoredProcedure;
                _connection.Open();
                reader = command.ExecuteReader();

                while (reader.Read())
                {
                    var record = PopulateRecord(reader);
                    if (record != null) list.Add(record);
                }
            }
            finally
            {
                // Always call Close when done reading.
                reader.Close();
                _connection.Close();
                _connection.Dispose();
            }
            return list;
        }
    }

StudentRepository which extend base AdoRepository
-----------------------------------------------

public class StudentRepository : AdoRepository<Student>
    {
        public int DataCounter { get; set; }
        public bool hasError { get; set; }
        public string ErrorMessage { 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"]),
                StateID = Convert.ToInt32(reader["StateID"].ToString()),
                StateName = reader["StateName"].ToString(),
                CityID = Convert.ToInt32(reader["CityID"].ToString()),
                CityName = reader["CityName"].ToString()
            };
        }

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

        public override void Status(bool IsError, string strErrMsg)
        {
            hasError = IsError;
            ErrorMessage = strErrMsg;
        }
}

calling StudentRepository from mvc controller like below way

 public class StudentController : Controller
    {
        private StudentRepository _data;

        public StudentController()
        {
            _data = new StudentRepository(System.Configuration.ConfigurationManager.ConnectionStrings["StudentDBContext"].ConnectionString);
        }

        // GET: Stuent
        public ActionResult List(StudentListViewModel oSVm)
        {

            StudentListViewModel SVm = new StudentListViewModel();
            SVm.SetUpParams(oSVm);
            SVm.Students = _data.GetStudents(SVm.StartIndex, SVm.EndIndex, SVm.sort, oSVm.sortdir).ToList();
            SVm.RowCount = _data.DataCounter;

            return View("ListStudents",SVm);
        }
    }

Answer 1

You can have a unit of work class that contains objects of the repositories that you have.

public class UnitOfWork : IUnitOfWork
{
    /// <summary>
    /// Database context.
    /// </summary>
    private readonly CoreDBContext context = new CoreDBContext();

    /// <summary>
    /// StudentRepository repository.
    /// </summary>
    public StudentRepository studentRepository 
    {
        get { return this.studentRepository ?? (this.studentRepository = new studentRepository (this.context)); }
    }

    public OtherRepository otherRepository
    {
        get { return this.otherRepository?? (this.otherRepository= new otherRepository(this.context)); }
    }

    /// <summary>
    /// Saves changes to database.
    /// </summary>
    public void Save()
    {
        this.context.SaveChanges();
    }
}

The above repository class contains the Database context, repository objects as properties and save method.

After including the reference of DAL layer you can invoke it by using the following code

using (IUnitOfWork unitOfWork = new UnitOfWork())
        {
         StudentListViewModel SVm = new StudentListViewModel();
        SVm.SetUpParams(oSVm);
        SVm.Students =  unitOfWork.studentRepository.GetStudents(SVm.StartIndex, SVm.EndIndex, SVm.sort, oSVm.sortdir).ToList();
        }

The repository class can be changed to take the DBContext object in as constructor object.