namespace UnitTestDemo
{
    using System.Collections.Generic;
    using System.Data;
    using System.Data.SqlClient;

    // We need a way to inject our mocked connection into the class under test.
    // For that I created this interface and during test time I will override IConnectionFactory with Unity
    public interface IConnectionFactory
    {
        IDbConnection GetConnection(string connectionString);
    }

    public class SqlServerConnectionFactory : IConnectionFactory
    {
        public IDbConnection GetConnection(string connectionString)
        {
            return new SqlConnection(connectionString);
        }
    }

    public interface IDataProvider
    {
        List<Model> RetreiveSomeData(int envId);
    }

    internal class DataProvider : IDataProvider
    {
        private readonly IConnectionFactory connectionFactory;

        // Injecting factory. In Unit test we can inject mocked factory that will return mock connection
        public DataProvider(IConnectionFactory factory)
        {
            this.connectionFactory = factory;
        }

        public List<Model> RetreiveSomeData(int envId)
        {
            const string ConnectionString = "Data Source=.;Initial Catalog=Test;Integrated Security=SSPI;";
            using (var connection = connectionFactory.GetConnection(ConnectionString))
            {
                connection.Open();

                using (var command = connection.CreateCommand())
                {
                    command.CommandText = "SELECT [EnvId],[ConStr] FROM [dbo].[ConnectionStrings] Where EnvId=@EnvId";
                    command.CommandType = CommandType.Text;

                    var param = command.CreateParameter();
                    param.ParameterName = "@EnvId";
                    param.Value = envId;
                    param.DbType = DbType.Int32;

                    command.Parameters.Add(param);

                    using (var reader = command.ExecuteReader())
                    {
                        var result = new List<Model>();
                        while (reader.Read())
                        {
                            var rEnvId = reader.GetInt32(reader.GetOrdinal("EnvId"));
                            var rConStr = reader.GetString(reader.GetOrdinal("ConStr"));
                            var model = new Model(rEnvId, rConStr);
                            result.Add(model);
                        }
                        return result;
                    }
                }
            }
        }
    }

    public class Model
    {
        public readonly string Host;

        public readonly int LocationId;

        public Model(int locationId, string host)
        {
            LocationId = locationId;
            Host = host;
        }

        public override string ToString()
        {
            return string.Format("Host: {0}, LocationId: {1}", Host, LocationId);
        }
    }
}