How to write unit test code for my ADO.Net based r

Question

i am quite new in unit test area. so please guide me how to write unit test for ADO.Net based repository and action method inside my controller?

i will be using VS own unit test framework.

so please see the code and tell me what are the classes and functions need to go through unit test?

it will be really helpful if some one tell me which area i need to unit test and which area not required?

if possible please write few unit test code which show me how to write unit test for action ,student repository and student viewmodel.

do i need to use mock or vs own unit test is capable enough to test my repository functions ?

This is my base repository

namespace DataLayer.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, string CountColName="TotalCount")
        {
            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);
                }

                reader.NextResult();
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        GetDataCount(Convert.ToInt32(reader[CountColName].ToString()));
                    }
                }

            }
            finally
            {
                // Always call Close when done reading.
                reader.Close();
                _connection.Close();
                _connection.Dispose();
            }
            return list;
        }
    }
}

This is my student reporsitory

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> SaveXML(string strXML, int PageNo,int PageSize,string SortCol,string SortOrder)
        {
            if (PageNo <= 0) PageNo = 1;

            using (var command = new SqlCommand("USP_SaveStudent"))
            {
                command.Parameters.Add("@Data", SqlDbType.VarChar,-1).Value = strXML;
                command.Parameters.Add("@PageNbr", SqlDbType.Int).Value = PageNo;
                command.Parameters.Add("@PageSize", SqlDbType.Int).Value = PageSize;
                command.Parameters.Add("@SortColumn", SqlDbType.VarChar, 20).Value = SortCol;
                command.Parameters.Add("@SortOrder", SqlDbType.VarChar, 4).Value = SortOrder;

                return ExecuteStoredProc(command);
            }
        }

        public IEnumerable<Student> Delete(int id, int PageNo, int PageSize, string SortCol, string SortOrder)
        {
            if (PageNo <= 0) PageNo = 1;

            using (var command = new SqlCommand("USP_DeleteStudent"))
            {
                command.Parameters.Add("@ID", SqlDbType.VarChar, -1).Value = id;
                command.Parameters.Add("@PageNbr", SqlDbType.Int).Value = PageNo;
                command.Parameters.Add("@PageSize", SqlDbType.Int).Value = PageSize;
                command.Parameters.Add("@SortColumn", SqlDbType.VarChar, 20).Value = SortCol;
                command.Parameters.Add("@SortOrder", SqlDbType.VarChar, 4).Value = SortOrder;

                return ExecuteStoredProc(command);
            }
        }

        public IEnumerable<Student> GetStudents(int PageNo, int PageSize, 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);
            if (SortOrder == "Ascending")
                SortOrder = "ASC";
            else if (SortOrder == "Descending")
                SortOrder = "DESC";

                if (PageNo <= 0) PageNo = 1;

            using (var command = new SqlCommand("USP_GetStudentData"))
            {
                command.Parameters.Add("@PageNbr", SqlDbType.Int).Value = PageNo;
                command.Parameters.Add("@PageSize",SqlDbType.Int).Value=  PageSize;
                command.Parameters.Add("@SortColumn", SqlDbType.VarChar, 20).Value = SortCol;
                command.Parameters.Add("@SortOrder", SqlDbType.VarChar, 4).Value = SortOrder;
                return ExecuteStoredProc(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;
        }
    }

Student Model
---------------
public class Student
    {
        public int ID { get; set; }
        [Required(ErrorMessage = "First Name Required")]
        public string FirstName { get; set; }
        [Required(ErrorMessage = "Last Name Required")]
        public string LastName { get; set; }
        public bool IsActive { get; set; }

        public int StateID { get; set; }
        public string StateName { get; set; }

        public int CityID { get; set; }
        public string CityName { get; set; }
}

Student View Model
------------------
public class StudentListViewModel
        {
            public int StartIndex { get; set; }
            public int EndIndex { get; set; }
            public int page { get; set; }
            public int RowCount { get; set; }
            public int PageSize { get; set; }
            public int CurrentPage { get; set; }

            public string sort { get; set; }
            public string sortdir { get; set; }

            public IList<Student> Students { get; set; }

            [Required(ErrorMessage = "State Required")]
            public int SelectedStateId { set; get; }
            public IList<State> States { get; set; }

            [Required(ErrorMessage = "City Required")]
            public int SelectedCityId { set; get; }
            public IList<City> Cities { get; set; }

            public StudentListViewModel()
            {
                PageSize = 5;
                sort = "ID";
                sortdir = "ASC";
                CurrentPage = 1;
            }

            public void SetUpParams(StudentListViewModel oSVm)
            {
                if (oSVm.page == 0)
                    oSVm.page = 1;

                StartIndex = ((oSVm.page * oSVm.PageSize) - oSVm.PageSize) + 1;
                EndIndex = (oSVm.page * oSVm.PageSize);
                CurrentPage = (StartIndex - 1) / oSVm.PageSize;

                if (string.IsNullOrEmpty(oSVm.sort))
                    oSVm.sort = "ID";

                if (string.IsNullOrEmpty(oSVm.sortdir))
                    oSVm.sortdir = "ASC";
            }
}

Here is few action which i need to test
---------------------------------------
public ActionResult List(StudentListViewModel oSVm)
        {
            if (Request.IsAjaxRequest())
                System.Threading.Thread.Sleep(1000); // just simulate delay of one second

            StudentListViewModel SVm = new StudentListViewModel();
            SVm.SetUpParams(oSVm);
            SVm.Students = _Studentdata.GetStudents(oSVm.page, oSVm.PageSize, oSVm.sort, oSVm.sortdir).ToList();
            SVm.States = _Statedata.GetAll().ToList();
            SVm.Cities = _Citydata.GetAll().ToList();
            SVm.RowCount = _Studentdata.DataCounter;
            return View("ListStudents",SVm);
        }

        [HttpPost]
        public ActionResult UpdateStudents(StudentListViewModel oSVm, string Action)
        {
            if (Request.IsAjaxRequest())
                System.Threading.Thread.Sleep(1000); // just simulate delay of one second

            StudentListViewModel SVm = new StudentListViewModel();
            SVm.SetUpParams(oSVm);
            if (Action == "UPDATE")
            {
                SVm.Students = _Studentdata.SaveXML(new List<Student>(oSVm.Students).ToXml("Students"),
                    oSVm.page, oSVm.PageSize, oSVm.sort, oSVm.sortdir).ToList();
            }
            else if (Action == "DELETE")
            {
                SVm.Students = _Studentdata.Delete(oSVm.Students[0].ID,
                    oSVm.page, oSVm.PageSize, oSVm.sort, oSVm.sortdir).ToList();

            }

            SVm.States = _Statedata.GetAll().ToList();
            SVm.Cities = _Citydata.GetAll().ToList();
            SVm.RowCount = _Studentdata.DataCounter;
            return PartialView("_StudentGrid", SVm);
        }

        [HttpPost]
        public ActionResult RefreshStudents(StudentListViewModel oSVm)
        {
            if (Request.IsAjaxRequest())
                System.Threading.Thread.Sleep(1000); // just simulate delay of one second

            StudentListViewModel SVm = new StudentListViewModel();
            SVm.SetUpParams(oSVm);
            SVm.Students = _Studentdata.GetStudents(oSVm.page, oSVm.PageSize, oSVm.sort, oSVm.sortdir).ToList();
            SVm.States = _Statedata.GetAll().ToList();
            SVm.Cities = _Citydata.GetAll().ToList();
            SVm.RowCount = _Studentdata.DataCounter;
            return PartialView("_StudentGrid", SVm);
        }

        [HttpGet]
        public JsonResult GetCityName(int StateID)
        {
            if (Request.IsAjaxRequest())
                System.Threading.Thread.Sleep(1000); // just simulate delay of one second

            return Json(new {CityList =_Citydata.GetCityByStateId(StateID)} , JsonRequestBehavior.AllowGet);
    }

thanks

Answer 1

[TestMethod]
public void List_StudentListViewModelIsValid_ViewModelIsPopulated()
{
    // Arrange
    var students = Builder<Student>().CreateListOfSize(10).Build();
    var repositoryMock = new Mock<StudentRepository>();

    repositoryMock.Setup(r => r.GetAll()).Returns(students);

    var testInstance = new StudentListViewModel(repositoryMock.Object); // DIP

    // Act
    testInstance.List()

    // ASsert
    // Check your values etc
}

The important thing to notice here is what I am doing with the repository. Instead of using NEW inside the List method, I inject the repository in the viewModel constructor. You should NEVER use "new" inside functions like this. Instead, pass those objects through the constructor of the class using them. This is known as The dependency inversion principle and is one of the SOLID principles of object oriented programming that makes your code testable.

So you would have to rewrite your viewmodel to take the repository as a constructor argument. this way, when you unit test, you can give the viewmodel a mock as the repository instead of having to use new.

And again, your abstract repository class can be mocked exactly as any interface.

EDIT: The Builder bit of code is from a nuget-package named NBuilder that I typically use whenever I just need some data from a mock. It lets me construct lists of all sizes when I need them in test-code :)

Answer 2

A general guideline:

If you unit-test methods that go outside of your memory block (for example, connecting to database, or writing to disk) you do NOT want to unit test them.

And this makes sense. for example, if you're testing that you can save yet another Person object in your people table, and the test fails, what went wrong? Was i the database connection? Is the database full? Maybe it's stopped for some weird reason? Or is there a bug in your code? - problem is, you can't know for sure, and a unit test is for you to be sure of what went wrong.

SO: Strategy 1: STOP writing code that does any form of IF-test at the same time that it is writing to disk or database. These classes should use only contracts, so that you can MOCK out the database layer entirely. For example:

public class PeopleManager{

  private IDataWriter<Person> _personWriter;

  public PeopleManager(IDataWriter<Person> personWriter){
    _personWriter = personWriter;
  }

  public void ImportantMethodToTest(Person person){
    if(..important conditionsTested...){
      _personWriter.Update(person);
    }
  }
}

The class above is unit-testable, because it does not depend on anything other than a contract to IDataWriter which you can implement in a class that does your writing to database, and ZERO logic. It simply writes.

Testing this is now super-fast and super-easy:

[TestMethod]
public void ImportantMethod_PersonIsNull_NothingIsWrittenToDatabase(){

  // Arrange
  var writerMock = new Mock<IDataWriter<Person>>();
  var peopleManager = new PeopleManager(writerMock.Object);

  // Act
  peopleManager.ImportantMethodToTest(null);

  // Assert
  writerMock.Verify( writer => writer.Update(It.IsAny<Person>), Times.Never());


}

I hope you see the picture. Things to search for to learn more are the SOLID principles, and what I used here is in part the Single Reponsability Principle and also the Dependency Inversion Principles. These principles make your code possible to unit-test