title: Mocking SqlConnection, SqlCommand and SqlReader in C# using MsTest
body: <p>I came across this <a href="https://stackoverflow.com/questions/34944462/moq-and-sqlconnection">answer</a> and I'm interested in implementing the second answer using Fake. Here's <a href="https://stackoverflow.com/questions/29976826/how-can-i-stub-idbconnection">another</a> one. </p>
  
  <p>I'm not really understanding all the concepts there and I'm still reading and understanding <a href="https://docs.microsoft.com/en-us/visualstudio/test/isolating-code-under-test-with-microsoft-fakes?view=vs-2015&amp;redirectedfrom=MSDN" rel="noreferrer">documentation</a>, can someone help using my code, where I'm trying to access list of customers on how to use Fake/Shim/Stub/Mock here?</p>
  
  <p>You may rewrite <code>FindAll</code> method too just in case if it's to be refactored to accept dependencies.</p>
  
  <p><em>Editing after discussion</em></p>
  
  <pre><code>public class Data
  {
      private Func&lt;IDbConnection&gt; Factory { get; }
  
      public Data(Func&lt;IDbConnection&gt; factory)
      {
          Factory = factory;
      }
  
      public IList&lt;Customer&gt; FindAll()
      {
          using (var connection = Factory.Invoke())
          {
              const string sql = "SELECT Id, Name FROM Customer";
              using (var command = new SqlCommand(sql, (SqlConnection) connection))
              {
                  command.Connection.Open();
                  using (var reader = command.ExecuteReader())
                  {
                      IList&lt;Customer&gt; rows = new List&lt;Customer&gt;();
                      while (reader.Read())
                      {
                          rows.Add(new Customer
                          {
                              Id = reader.GetInt32(reader.GetOrdinal("Id")),
                              Name = reader.GetString(reader.GetOrdinal("Name"))
                          });
                      }
                      return rows;
                  }
              }
          }
      }
  }
  </code></pre>
  
  <p><strong>Customer</strong></p>
  
  <pre><code>public class Customer
  {
      public int Id { get; set; }
      public string Name { get; set; }
  }
  </code></pre>
  
  <p><strong>UnitTest</strong></p>
  
  <pre><code>[TestMethod]
  public void TestDB()
  {
      var commandMock = new Mock&lt;IDbCommand&gt;();
  
      var readerMock = new Mock&lt;IDataReader&gt;();
      commandMock.Setup(m =&gt; m.ExecuteReader()).Returns(readerMock.Object).Verifiable();
  
      var parameterMock = new Mock&lt;IDbDataParameter&gt;();
      commandMock.Setup(m =&gt; m.CreateParameter()).Returns(parameterMock.Object);
      commandMock.Setup(m =&gt; m.Parameters.Add(It.IsAny&lt;IDbDataParameter&gt;())).Verifiable();
  
      var connectionMock = new Mock&lt;IDbConnection&gt;();
      connectionMock.Setup(m =&gt; m.CreateCommand()).Returns(commandMock.Object);
  
      var data = new Data(() =&gt; connectionMock.Object);
      var result = data.FindAll();
      Console.WriteLine(result);
  }
  </code></pre>
  
  <p><strong>Error</strong></p>
  
  <p>Had a hiccup with a dependency, added <code>System.Data.SqlClient</code>, another error follows.</p>
  
  <blockquote>
    <p>System.InvalidCastException: Unable to cast object of type
    'Castle.Proxies.IDbConnectionProxy' to type
    'System.Data.SqlClient.SqlConnection'.</p>
  </blockquote>
  
  <p>pointing to this line </p>
  
  <p><code>using (var command = new SqlCommand(sql, (SqlConnection) connection))</code></p>