How to unit test a repository and mock db with moq

Question

I am onboarding alone on an existing project that do not have any unit test. My first goal before any refactoring is to cover 100% of the code. I would like to avoid any regression.

I have read how do I mock sqlconnection or should I refactor the code? but my case as you can see below is quite different cause I need to do more than stub simply the sqlConnection. Basically, I need to mock the db. I would like to know the best approach to achieve it.

(By the way, I do not want to use any ORM such as Entity). Below the code of the repository :

public class HotelRepository : IHotelRepository
{
    private readonly IDbConnection _dbConnection;
    private readonly ILogService _loggerService;

    public HotelRepository(IDbConnection dbConnection, ILogService loggerService)
    {
        _dbConnection = dbConnection;
        _loggerService = loggerService;
    }

    public HotelDo GetByRid(string rid)
    {
        return Find(rid).FirstOrDefault();
    }

    public List<HotelDo> Find(string text)
    {
        try
        {
            _dbConnection.Open();

            var items = new List<HotelDo>();
            using (var command = _dbConnection.CreateCommand())
            {
                command.CommandType = CommandType.StoredProcedure;
                command.CommandText = "dbo.HotelSearchByRidOrName";

                command.Parameters.Add(new SqlParameter("@Text", text));

                using (var reader = command.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    while (reader.Read())
                    {
                        items.Add(new HotelDo()
                        {
                            Name = SqlExtension.ReaderToStringConverter(reader["Name"]),
                            Id = SqlExtension.ReaderToIntConverter(reader["Id"]),
                            Rid = SqlExtension.ReaderToStringConverter(reader["RIDHotel"]),
                            IdPms = SqlExtension.ReaderToNullableIntConverter(reader["IdPms"]),
                            LinkResaWeb = SqlExtension.ReaderToStringConverter(reader["LinkResaWeb"]),
                            LinkPms = SqlExtension.ReaderToStringConverter(reader["LinkPms"]),
                            IdBrand = SqlExtension.ReaderToNullableIntConverter(reader["IdBrand"]) ?? 0,
                            IsOnline = SqlExtension.ReaderToBoolConverter(reader["IsOnline"]) ?? false,
                            CodeCountry = SqlExtension.ReaderToStringConverter(reader["CodeCountry"])
                        });
                    }
                }
            }
            return items;
        }
        catch (Exception e)
        {
            var errorMessage = $"HotelRepository Find, text {text} ";
            _loggerService.Trace(LogSeverity.Error, errorMessage, e);

            throw new DalException() { Source = errorMessage, };
        }
        finally
        {
            _dbConnection.Close();
        }
    }

    public List<HotelDo> GetAll()
    {
        try
        {
            _dbConnection.Open();

            var items = new List<HotelDo>();
            using (var command = _dbConnection.CreateCommand())
            {
                command.CommandType = CommandType.StoredProcedure;
                command.CommandText = "dbo.HotelGetAll";

                using (var reader = command.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    while (reader.Read())
                    {
                        bool.TryParse(reader["IsOnline"].ToString(), out var isOnline);

                        items.Add(new HotelDo()
                        {
                            Id = SqlExtension.ReaderToIntConverter(reader["Id"]),
                            Rid = SqlExtension.ReaderToStringConverter(reader["RIDHotel"]),
                            Name = SqlExtension.ReaderToStringConverter(reader["Name"]),
                            CodeCountry = SqlExtension.ReaderToStringConverter(reader["CodeCountry"]),
                            LinkPms = SqlExtension.ReaderToStringConverter(reader["LinkPms"]),
                            IdPms = SqlExtension.ReaderToNullableIntConverter(reader["IdPms"]),
                            IdBrand = SqlExtension.ReaderToNullableIntConverter(reader["IdBrand"]) ?? 0,
                            LinkResaWeb = SqlExtension.ReaderToStringConverter(reader["LinkResaWeb"]),
                            IsOnline = isOnline
                        });
                    }
                }
            }
            return items;
        }
        catch (Exception e)
        {
            var errorMessage = $"HotelRepository GetAllHotels";
            _loggerService.Trace(LogSeverity.Error, errorMessage, e);

            throw new DalException() { Source = errorMessage, };
        }
        finally
        {
            _dbConnection.Close();
        }
    }
}

Thank you for your help

Answer 1

So based on what you've got I've set up a test frame for you to follow. I've removed less important components for breviety.

Before you you jump in I just want to give my 2 cents, if you don't know how to do this sort of thing you seem to be more or less in a junior position or less experiance with C# and also found your self in a more or less mature company that doesn't care about the development deparment, since an uncovered project just get's thrown your way says you don't have alot of resources to go about to imrpove the code base.

1. Test only the things that have business value (can you put a price on the piece of logic if it brakes)
2. Delivering stuff faster will make you look better as a programmer (noone in the business gives a damn about test covarage)
3. Study hard, get your experiance, good reputation and don't be afraid to get the hell out of there as soon as you start getting bored.

Main

void Main()
{
    var idIndex = 0;
    var ids = new string[] { "1", "2" };

    var mockDataReader = new Mock<IDataReader>();
    mockDataReader.SetupSequence(x => x.Read()).Returns(true).Returns(true).Returns(false);
    mockDataReader.SetupGet(x => x["Id"]).Returns(() => ids[idIndex]).Callback(() => idIndex++);

    var mockParameters = new Mock<IDataParameterCollection>();

    var mockCommand = new Mock<IDbCommand>();
    mockCommand.SetupGet(x => x.Parameters).Returns(mockParameters.Object);
    mockCommand.Setup(x => x.ExecuteReader(CommandBehavior.CloseConnection)).Returns(mockDataReader.Object);

    var mockConnection = new Mock<IDbConnection>();
    mockConnection.Setup(x => x.CreateCommand()).Returns(mockCommand.Object);

    var repo = new HotelRepository(mockConnection.Object);

    var result = repo.Find("search");

    Assert.Equal("1", result[0].Id);
    Assert.Equal("2", result[1].Id);
}

Repository

public class HotelRepository
{
    private readonly IDbConnection _dbConnection;

    public HotelRepository(IDbConnection dbConnection)
    {
        _dbConnection = dbConnection;
    }

    public List<Pony> Find(string text)
    {
        _dbConnection.Open();

        var items = new List<Pony>();
        using (var command = _dbConnection.CreateCommand())
        {
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "dbo.HotelSearchByRidOrName";

            command.Parameters.Add(new SqlParameter("@Text", text));

            using (var reader = command.ExecuteReader(CommandBehavior.CloseConnection))
            {
                while (reader.Read())
                {
                    items.Add(new Pony
                    {
                        Id = reader["Id"].ToString()
                    });
                }
            }
        }
        return items;
    }
}

Just a dumb o'l pony

public class Pony {
    public string Id { get; set; }
}