internal class ImportAcquisitionDataDAO : IImportAcquisitionDataDAO
{
    private static Logger log = LogManager.GetCurrentClassLogger();

    private readonly IDbConnection connection;

    internal ImportAcquisitionDataDAO(IDbConnection connection)
    {
        this.connection = connection;
    }

    internal List<DefinitionEntry> GetDefinitions()
    {
        log.Debug("Getting definitions from database.");

        var definitions = new List<DefinitionEntry>();

        using (connection)
        {
            connection.Open();
            log.Trace("Database connection opened");

            IDbCommand command = connection.CreateCommand();
            command.CommandText = @"SELECT *
                                    FROM MAPPING";

            IDataReader reader = command.ExecuteReader();
            log.Trace("Command executed:n{0}", command.CommandText);

            definitions = GetMeterEntries(reader);
        }

        log.Debug("Obtained {0} definitions.", definitions.Count);
        return definitions;
    }

    private List<DefinitionEntry> GetMeterEntries(IDataReader reader)
    {
        log.Trace("Parsing definitions from response");

        var result = new List<DefinitionEntry>();

        while (reader.Read())
        {
            var definition = new DefinitionEntry(
                reader.GetString(0),
                reader.GetString(1),
                reader.GetString(2),
                reader.IsDBNull(3) ? null : reader.GetString(3),
                reader.IsDBNull(4) ? null : reader.GetString(4),
                reader.IsDBNull(5) ? null : reader.GetString(5),
                reader.IsDBNull(6) ? null : reader.GetString(6)
                );

            log.Trace(definition.ToString());
            result.Add(definition);
        }

        return result;
    }
}

public class DefinitionEntry
{
    public string MeterSN { get; private set; }
    public string MATNR { get; private set; }
    public string IpAddress { get; private set; }
    public string SIM { get; private set; }
    public string ModulID { get; private set; }
    public string SIMUser { get; private set; }
    public string SIMPassword { get; private set; }

    public DefinitionEntry(string meterSN, string matnr, string ipAddress, string sim, string modulId, string simUser, string simPassword)
    {
        MeterSN = meterSN;
        MATNR = matnr;
        IpAddress = ipAddress;
        SIM = sim;
        ModulID = modulId;
        SIMUser = simUser;
        SIMPassword = simPassword;
    }
}

[TestFixture]
public class ImportAcquisitionDataDAOTests
{
    private IDbConnection mockConnection;
    private IDbCommand mockCommand;
    private IDataReader mockReader;
    private ImportAcquisitionDataDAO dao;

    [SetUp]
    public void SetUp()
    {
        mockConnection = Substitute.For<IDbConnection>();
        mockCommand = Substitute.For<IDbCommand>();
        mockReader = Substitute.For<IDataReader>();
        dao = new ImportAcquisitionDataDAO(mockConnection);

        mockConnection.CreateCommand().Returns(mockCommand);
        mockCommand.ExecuteReader().Returns(mockReader);
    }

    [Test]
    public void TestGetDefinitions()
    {
        // mock
        var databaseDefinitionFirst = new DefinitionEntry("11111", "AS3000-5/100-400-P", "10.42.42.26", "SIM-001", "12345lkj", "alibaba", "abrakadabra");
        var databaseDefinitionSecond = new DefinitionEntry("22222", "AS3000-5/100-400-Q", "10.42.42.158", null, null, null, null);

        mockReader.Read().Returns(true, true, false);
        mockReader.GetString(Arg.Is<int>(0)).Returns(databaseDefinitionFirst.MeterSN, databaseDefinitionSecond.MeterSN);
        mockReader.GetString(Arg.Is<int>(1)).Returns(databaseDefinitionFirst.MATNR, databaseDefinitionSecond.MATNR);
        mockReader.GetString(Arg.Is<int>(2)).Returns(databaseDefinitionFirst.IpAddress, databaseDefinitionSecond.IpAddress);
        mockReader.IsDBNull(Arg.Is<int>(3)).Returns(false, true);
        mockReader.GetString(Arg.Is<int>(3)).Returns(databaseDefinitionFirst.SIM);
        mockReader.IsDBNull(Arg.Is<int>(4)).Returns(false, true);
        mockReader.GetString(Arg.Is<int>(4)).Returns(databaseDefinitionFirst.ModulID);
        mockReader.IsDBNull(Arg.Is<int>(5)).Returns(false, true);
        mockReader.GetString(Arg.Is<int>(5)).Returns(databaseDefinitionFirst.SIMUser);
        mockReader.IsDBNull(Arg.Is<int>(6)).Returns(false, true);
        mockReader.GetString(Arg.Is<int>(6)).Returns(databaseDefinitionFirst.SIMPassword);

        // use
        List<DefinitionEntry> tested = dao.GetDefinitions();

        // verify
        Assert.AreEqual(2, tested.Count);
        AssertDefinitionEntry(databaseDefinitionFirst, tested.First());
        AssertDefinitionEntry(databaseDefinitionSecond, tested.Last());
    }

    private void AssertDefinitionEntry(DefinitionEntry expected, DefinitionEntry tested)
    {
        Assert.AreEqual(expected.MeterSN, tested.MeterSN);
        Assert.AreEqual(expected.MATNR, tested.MATNR);
        Assert.AreEqual(expected.IpAddress, tested.IpAddress);
        Assert.AreEqual(expected.SIM, tested.SIM);
        Assert.AreEqual(expected.ModulID, tested.ModulID);
        Assert.AreEqual(expected.SIMUser, tested.SIMUser);
        Assert.AreEqual(expected.SIMPassword, tested.SIMPassword);
    }
}