SImple Injection Example files

FakePersonTests.cs

public class PersonServiceUnitTests
{
  [Test]
  public void ShouldUseFake()
  {
    var factory = new FakeSqlHelperFactory();
    var personService = new PersonServiceFourthPass(factory);
    var expectedFirst = "Foo";
    var expectedLast = "Bar";
    
    var person = personService.GetPerson(Guid.NewGuid());
    
    expectedFirst.ShouldEqual(person.FirstName);
    expectedLast.ShouldEqual(person.LastName);
  }
}

FakeSqlHelper.cs

public class FakeSqlHelper : ISqlHelper
{
  private readonly string _connectionString;
  
  public FakeSqlHelper(string connectionString)
  {
    _connectionString = connectionString;
  }
  
  public void Dispose()
  {
  }
  
  public void ExecuteNonQuery(string sqlQuery)
  {
  }
  
  public void AddParam<T>(string key, T value)
  {
  }
  
  public DataTable GetTable(string sSQL)
  {
    using (DataTable table = new DataTable())
    {
      // Two columns.
      table.Columns.Add("FirstName", typeof(string));
      table.Columns.Add("LastName", typeof(string));
      
      table.Rows.Add("Foo", "Bar");
      return table;
    }
  }
}

FakeSqlHelperFactory.cs

public class FakeSqlHelperFactory : ISqlHelperFactory
{
  public ISqlHelper GetSqlHelper()
  {
    return new FakeSqlHelper("");
  }
}

ISqlHelper.cs

public interface ISqlHelper : IDisposable
{
  void ExecuteNonQuery(string sqlQuery);
  void AddParam<T>(string key, T value);
  DataTable GetTable(string sSQL);
}
public interface ISqlHelperFactory
{
  ISqlHelper GetSqlHelper();
}

Person.cs

public class Person
{
  public Guid PersonId { get; set; }
  public string FirstName { get; set; }
  public string LastName { get; set; }
}

PersonServiceFirstPass.cs

public class PersonServiceFirstPass
{
  string _connectionString = "Data Source=(localdb)\\MSSQLLocalDB;" +
    "Initial Catalog=SimpleInjectionExample;" +
    "Integrated Security=True;Connect Timeout=30;" +
    "Encrypt=False;TrustServerCertificate=False;" +
    "ApplicationIntent=ReadWrite;MultiSubnetFailover=False";

  public Guid WritePerson(Person person)
  {
    if (person.PersonId != Guid.Empty)
    {
      throw new ArgumentException("Person Id is not empty.");
    }
    string sSQL = "INSERT INTO Person " +
      "(PersonId, FirstName, LastName)" +
      "VALUES" +
      "(@personId, @FirstName, @LastName);" +
      "SELECT SCOPE_IDENTITY()";
      Guid identity = Guid.NewGuid();
        using (SqlConnection connection = new SqlConnection(_connectionString))
        {
          connection.Open();
          SqlCommand command = new SqlCommand(sSQL, connection);
          command.Parameters.Add("@personId", SqlDbType.UniqueIdentifier).Value = identity;
          command.Parameters.Add("@FirstName", SqlDbType.VarChar).Value = person.FirstName;
          command.Parameters.Add("@LastName", SqlDbType.VarChar).Value = person.LastName;
          command.ExecuteNonQuery();
        }
        return identity;
    }

  public Person GetPerson(Guid personId)
  {
    string sSQL = "SELECT FirstName, LastName " +
        "FROM Person " +
        "WHERE PersonId = @PersonId";
      Person result;
      using (SqlConnection connection = new SqlConnection(_connectionString))
      {
        connection.Open();
        SqlCommand command = new SqlCommand(sSQL, connection);
        command.Parameters.Add("@PersonId", SqlDbType.UniqueIdentifier).Value = personId;
        var reader = command.ExecuteReader();
        var table = new DataTable();
        table.Load(reader);
        var firstName = table.Rows[0][0].ToString();
        var lastName = table.Rows[0][1].ToString();
        result = new Person {PersonId = personId, FirstName = firstName, LastName = lastName};
      }
      return result;
    }

    public void CleanUp()
    {
      string sSQL = "DELETE FROM Person";
      using (SqlConnection connection = new SqlConnection(_connectionString))
      {
        connection.Open();
        SqlCommand command = new SqlCommand(sSQL, connection);
        command.ExecuteNonQuery();
      }
    }
}

PersonServiceFourthPass.cs

public class PersonServiceFourthPass
{
  readonly ISqlHelperFactory _sqlHelperFactory;
  
  public PersonServiceFourthPass(ISqlHelperFactory factory)
  {
    _sqlHelperFactory = factory;
  }

  public Guid WritePerson(Person person)
  {
    if (person.PersonId != Guid.Empty)
    {
      throw new ArgumentException("Person Id is not empty.");
    }
    string sSQL = "INSERT INTO Person " +
        "(PersonId, FirstName, LastName)" +
        "VALUES" +
        "(@personId, @FirstName, @LastName);";
    Guid identity = Guid.NewGuid();
    using (var sqlHelper = _sqlHelperFactory.GetSqlHelper())
    {
      sqlHelper.AddParam("personId", identity);
      sqlHelper.AddParam("FirstName", person.FirstName);
      sqlHelper.AddParam("LastName", person.LastName);
      sqlHelper.ExecuteNonQuery(sSQL);
    }
    return identity;
  }
  public Person GetPerson(Guid personId)
  {
    string sSQL = "SELECT FirstName, LastName " +
        "FROM Person " +
        "WHERE PersonId = @personId";
    Person result;
    using (var sqlHelper = _sqlHelperFactory.GetSqlHelper())
    {
      sqlHelper.AddParam("personId", personId);
      var table = sqlHelper.GetTable(sSQL);
      var firstName = table.Rows[0][0].ToString();
      var lastName = table.Rows[0][1].ToString();
      result = new Person { PersonId = personId, FirstName = firstName, LastName = lastName };
    }
    return result;
  }

  public void CleanUp()
  {
    string sSQL = "DELETE FROM Person";
    using (var sqlHelper = _sqlHelperFactory.GetSqlHelper())
    {
      sqlHelper.ExecuteNonQuery(sSQL);
    }
  }
}

PersonServiceIntegrationTests.cs

public class PersonServiceIntegrationTests
{
  string _connectionString = "Data Source=(localdb)\\MSSQLLocalDB;" +
      "Initial Catalog=SimpleInjectionExample;" +
      "Integrated Security=True;Connect Timeout=30;" +
      "Encrypt=False;TrustServerCertificate=False;" +
      "ApplicationIntent=ReadWrite;MultiSubnetFailover=False";

  private ISqlHelperFactory factory;
  private PersonServiceFourthPass personService;
  private Person person;

  [Test]
  public void ShouldWritePerson()
  {
    factory = new SqlHelperThirdFactory();
    personService = new PersonServiceFourthPass(factory);
    person = new Person {FirstName = "First", LastName = "Last"};
    var newId = personService.WritePerson(person);

    // Yucky Sql Stuff to verify
    string sSQL = "SELECT FirstName, LastName " +
      "FROM Person " +
      "WHERE PersonId = @PersonId";
    Person result;
    using (SqlConnection connection = new SqlConnection(_connectionString))
    {
      connection.Open();
      SqlCommand command = new SqlCommand(sSQL, connection);
      command.Parameters.Add("@PersonId", SqlDbType.UniqueIdentifier).Value = newId;
      var reader = command.ExecuteReader();
      var table = new DataTable();
      table.Load(reader);
      var firstName = table.Rows[0][0].ToString();
      var lastName = table.Rows[0][1].ToString();
      result = new Person { PersonId = newId, FirstName = firstName, LastName = lastName };
    }

    person.FirstName.ShouldEqual(result.FirstName);
    person.LastName.ShouldEqual(result.LastName);
    newId.ShouldEqual(result.PersonId);
  }

  [Test]
  public void ShouldReadPerson()
  {
    factory = new SqlHelperThirdFactory();
    personService = new PersonServiceFourthPass(factory);
    person = new Person { FirstName = "First", LastName = "Last" };
    var newId = personService.WritePerson(person);
    var readPerson = personService.GetPerson(newId);
    readPerson.FirstName.ShouldEqual(person.FirstName);
    readPerson.LastName.ShouldEqual(person.LastName);
    readPerson.PersonId.ShouldEqual(newId);
  }

  [TearDown]
  public void Cleanup()
  {
    factory = new SqlHelperThirdFactory();
    personService = new PersonServiceFourthPass(factory);
    personService.CleanUp();
  }
}

PersonServiceSecondPass.cs

public class PersonServiceSecondPass
{
  string _connectionString = "Data Source=(localdb)\\MSSQLLocalDB;" +
    "Initial Catalog=SimpleInjectionExample;" +
    "Integrated Security=True;Connect Timeout=30;" +
    "Encrypt=False;TrustServerCertificate=False;" +
    "ApplicationIntent=ReadWrite;MultiSubnetFailover=False";

  private SqlDataReader _myReader;

  public Guid WritePerson(Person person)
  {
    if (person.PersonId != Guid.Empty)
    {
      throw new ArgumentException("Person Id is not empty.");
    }
    string sSQL = "INSERT INTO Person " +
      "(PersonId, FirstName, LastName)" +
      "VALUES" +
      "(@personId, @FirstName, @LastName);" +
      "SELECT SCOPE_IDENTITY()";
    Guid identity = Guid.NewGuid();
    using (var sqlHelper = new SqlHelper(_connectionString))
    {
      sqlHelper.SqlCommand.CommandText = sSQL;
      sqlHelper.SqlCommand.Parameters.Clear();
      sqlHelper.AddParam("personId", identity);
      sqlHelper.AddParam("FirstName", person.FirstName);
      sqlHelper.AddParam("LastName", person.LastName);
      sqlHelper.SqlCommand.ExecuteNonQuery();
    }
    return identity;
  }

  public Person GetPerson(Guid personId)
  {
    string sSQL = "SELECT FirstName, LastName " +
      "FROM Person " +
      "WHERE PersonId = @personId";
    Person result;
    using (var sqlHelper = new SqlHelper(_connectionString))
    {
      sqlHelper.SqlCommand.CommandText = sSQL;
      sqlHelper.SqlCommand.Parameters.Clear();
      sqlHelper.AddParam("personId", personId);
      var table = sqlHelper.GetTable();
      var firstName = table.Rows[0][0].ToString();
      var lastName = table.Rows[0][1].ToString();
      result = new Person { PersonId = personId, FirstName = firstName, LastName = lastName };
    }
    return result;
  }

  public void CleanUp()
  {
    string sSQL = "DELETE FROM Person";
    using (var sqlHelper = new SqlHelper(_connectionString))
    {
      sqlHelper.SqlCommand.CommandText = sSQL;
      sqlHelper.SqlCommand.ExecuteNonQuery();
    }
  }
}

PersonServiceThirdPass.cs

public class PersonServiceThirdPass
{
  string _connectionString = "Data Source=(localdb)\\MSSQLLocalDB;" +
    "Initial Catalog=SimpleInjectionExample;" +
    "Integrated Security=True;Connect Timeout=30;" +
    "Encrypt=False;TrustServerCertificate=False;" +
    "ApplicationIntent=ReadWrite;MultiSubnetFailover=False";

  private SqlDataReader _myReader;

  public Guid WritePerson(Person person)
  {
    if (person.PersonId != Guid.Empty)
    {
      throw new ArgumentException("Person Id is not empty.");
    }
    string sSQL = "INSERT INTO Person " +
      "(PersonId, FirstName, LastName)" +
      "VALUES" +
      "(@personId, @FirstName, @LastName);" +
      "SELECT SCOPE_IDENTITY()";
    Guid identity = Guid.NewGuid();
    using (var sqlHelper = new SqlHelperSecond(_connectionString))
    {
      sqlHelper.AddParam("personId", identity);
      sqlHelper.AddParam("FirstName", person.FirstName);
      sqlHelper.AddParam("LastName", person.LastName);
      sqlHelper.ExecuteNonQuery(sSQL);
    }
    return identity;
  }

  public Person GetPerson(Guid personId)
  {
    string sSQL = "SELECT FirstName, LastName " +
      "FROM Person " +
      "WHERE PersonId = @personId";
    Person result;
    using (var sqlHelper = new SqlHelperSecond(_connectionString))
    {
      sqlHelper.AddParam("personId", personId);
      var table = sqlHelper.GetTable(sSQL);
      var firstName = table.Rows[0][0].ToString();
      var lastName = table.Rows[0][1].ToString();
      result = new Person { PersonId = personId, FirstName = firstName, LastName = lastName };
    }
    return result;
  }

  public void CleanUp()
  {
    string sSQL = "DELETE FROM Person";
    using (var sqlHelper = new SqlHelperSecond(_connectionString))
    {
      sqlHelper.ExecuteNonQuery(sSQL);
    }
  }
}

ProgramFakePass.cs

private static void FakePass()
{
  var factory = new FakeSqlHelperFactory();
  var personService = new PersonServiceFourthPass(factory);
  var person = new Person();
  person.FirstName = "Judy";
  person.LastName = "Reynolds";
  var newId = personService.WritePerson(person);

  var readPerson = personService.GetPerson(newId);
  Console.WriteLine($"New Person ID: {readPerson.PersonId} {Environment.NewLine} " +
                    $"New Name: {readPerson.FirstName} {readPerson.LastName} ");
  Console.WriteLine("Did it work?");
  Console.ReadKey();
  personService.CleanUp();
}

ProgramFirstPass.cs

static void FirstPass()
{
  var personService = new PersonServiceFirstPass();
  var person = new Person();
  person.FirstName = "Joe";
  person.LastName = "Reynolds";
  var newId = personService.WritePerson(person);

  var readPerson = personService.GetPerson(newId);
  Console.WriteLine($"New Person ID: {readPerson.PersonId} {Environment.NewLine} " +
                    $"New Name: {readPerson.FirstName} {readPerson.LastName} ");
  Console.WriteLine("Did it work?");
  Console.ReadKey();
  personService.CleanUp();
}

ProgramFourthPass.cs

private static void FourthPass()
{
  var factory = new SqlHelperThirdFactory();
  var personService = new PersonServiceFourthPass(factory);
  var person = new Person();
  person.FirstName = "Judy";
  person.LastName = "Reynolds";
  var newId = personService.WritePerson(person);

  var readPerson = personService.GetPerson(newId);
  Console.WriteLine($"New Person ID: {readPerson.PersonId} {Environment.NewLine} " +
                    $"New Name: {readPerson.FirstName} {readPerson.LastName} ");
  Console.WriteLine("Did it work?");
  Console.ReadKey();
  personService.CleanUp();
}

ProgramSecondPass.cs

private static void SecondPass()
{
  var firstPass = new PersonServiceSecondPass();
  var person = new Person();
  person.FirstName = "Mark";
  person.LastName = "Reynolds";
  var newId = firstPass.WritePerson(person);

  var readPerson = firstPass.GetPerson(newId);
  Console.WriteLine($"New Person ID: {readPerson.PersonId} {Environment.NewLine} " +
                    $"New Name: {readPerson.FirstName} {readPerson.LastName} ");
  Console.WriteLine("Did it work?");
  Console.ReadKey();
  firstPass.CleanUp();
}

ProgramThirdPass.cs

private static void ThirdPass()
{
  var firstPass = new PersonServiceThirdPass();
  var person = new Person();
  person.FirstName = "Shannon";
  person.LastName = "Reynolds";
  var newId = firstPass.WritePerson(person);
  
  var readPerson = firstPass.GetPerson(newId);
  Console.WriteLine($"New Person ID: {readPerson.PersonId} {Environment.NewLine} " +
                    $"New Name: {readPerson.FirstName} {readPerson.LastName} ");
  Console.WriteLine("Did it work?");
  Console.ReadKey();
  firstPass.CleanUp();
}

SqlHelperCleanupSample.cs

public void CleanUp()
{
  string sSQL = "DELETE FROM Person";
  using (var sqlHelper = new SqlHelper(_connectionString))
  {
    sqlHelper.SqlCommand.CommandText = sSQL;
    sqlHelper.SqlCommand.ExecuteNonQuery();
  }
}

SqlHelperFirst.cs

public class SqlHelper : IDisposable
{
  private readonly SqlCommand _sqlCommand;
  private readonly SqlConnection _sqlConnection;

  public SqlHelper(string connectionString)
  {
    _sqlConnection = new SqlConnection(connectionString);
    _sqlCommand = new SqlCommand { Connection = _sqlConnection };
    _sqlConnection.Open();
  }

  public void Close()
  {
  // close the connection
    if (_sqlConnection.State == ConnectionState.Open)
        _sqlConnection.Close();
  }

  public SqlCommand SqlCommand
  {
    get { return _sqlCommand; }
  }

  public DataSet GetDataSet()
  {
    var myAdpater = new SqlDataAdapter { SelectCommand = _sqlCommand };
    var myDataSet = new DataSet();
    
    myAdpater.Fill(myDataSet);
    return myDataSet;
  }

  public DataTable GetTable()
  {
    var ds = GetDataSet();
    var dt = ds.Tables[0];
    return dt;
  }

  public void AddParam<T>(string key, T value)
  {
    if (key.StartsWith("@")) key = key.Substring(1);
    _sqlCommand.Parameters.AddWithValue($"@{key}", value);
  }

  public void Dispose()
  {
    Close();
  }

  ~SqlHelper()
  {
    if (_sqlConnection != null && _sqlConnection.State != ConnectionState.Closed)
        _sqlConnection.Close();
  }

}

SqlHelperSecond.cs

public class SqlHelperSecond : IDisposable
{
  private readonly SqlCommand _sqlCommand;
  private readonly SqlConnection _sqlConnection;
  
  public SqlHelperSecond(string connectionString)
  {
    _sqlConnection = new SqlConnection(connectionString);
    _sqlCommand = new SqlCommand { Connection = _sqlConnection };
    _sqlConnection.Open();
    ClearParameters();
  }

  public void ExecuteNonQuery(string sqlQuery)
  {
    _sqlCommand.CommandText = sqlQuery;
    _sqlCommand.ExecuteNonQuery();
  }
  public void AddParam<T>(string key, T value)
  {
    if (key.StartsWith("@")) key = key.Substring(1);
    _sqlCommand.Parameters.AddWithValue($"@{key}", value);
  }

  public void ClearParameters()
  {
    _sqlCommand.Parameters.Clear();
  }

  public DataTable GetTable(string sSQL)
  {
    _sqlCommand.CommandText = sSQL;
    var myAdpater = new SqlDataAdapter { SelectCommand = _sqlCommand };
    DataTable dt=new DataTable();
    myAdpater.Fill(dt);
    return dt;
  }
  
  public void Dispose()
  {
    if (_sqlConnection.State == ConnectionState.Open)
      _sqlConnection.Close();
  }
}

SqlHelperThird.cs

public class SqlHelperThirdFactory : ISqlHelperFactory
{
  string _connectionString = 
    "Data Source=(localdb)\\MSSQLLocalDB;" +
    "Initial Catalog=SimpleInjectionExample;" +
    "Integrated Security=True;Connect Timeout=30;" +
    "Encrypt=False;TrustServerCertificate=False;" +
    "ApplicationIntent=ReadWrite;MultiSubnetFailover=False";

  public ISqlHelper GetSqlHelper()
  {
      return new SqlHelperThird(_connectionString);
  }
}
public class SqlHelperThird : ISqlHelper
{
    private readonly SqlCommand _sqlCommand;
    private readonly SqlConnection _sqlConnection;

    public SqlHelperThird(string connectionString)
    {
        _sqlConnection = new SqlConnection(connectionString);
        _sqlCommand = new SqlCommand { Connection = _sqlConnection };
        _sqlConnection.Open();
        ClearParameters();
    }

    public void ExecuteNonQuery(string sqlQuery)
    {
        _sqlCommand.CommandText = sqlQuery;
        _sqlCommand.ExecuteNonQuery();
    }
    public void AddParam<T>(string key, T value)
    {
        if (key.StartsWith("@")) key = key.Substring(1);
        _sqlCommand.Parameters.AddWithValue($"@{key}", value);
    }

    public void ClearParameters()
    {
        _sqlCommand.Parameters.Clear();
    }

    public DataTable GetTable(string sSQL)
    {
        _sqlCommand.CommandText = sSQL;
        var myAdpater = new SqlDataAdapter { SelectCommand = _sqlCommand };
        DataTable dt = new DataTable();
        myAdpater.Fill(dt);
        return dt;
    }

    public void Dispose()
    {
        if (_sqlConnection.State == ConnectionState.Open)
            _sqlConnection.Close();
    }

}

SqlHelperThirdFactory.cs

public class SqlHelperThirdFactory : ISqlHelperFactory
{
  private readonly string _connectionString = 
    "Data Source=(localdb)\\MSSQLLocalDB;" +
    "Initial Catalog=SimpleInjectionExample;" +
    "Integrated Security=True;Connect Timeout=30;" +
    "Encrypt=False;TrustServerCertificate=False;" +
    "ApplicationIntent=ReadWrite;MultiSubnetFailover=False";

  public ISqlHelper GetSqlHelper()
  {
    return new SqlHelperThird(_connectionString);
  }
}