C # unit testing, mocking stored procedure

Question

In the past, when I implemented unit test, I struggled to set up “decent” unit tests for data access levels, as they often have a database as an external dependency. In an ideal world, I ridiculed calls to a stored procedure so that the external dependency is removed.

However, I was not able to figure out how to do this with falsification of MOQ or to find any other infrastructure that supports this. Instead, I returned to creating a test test database with known data (so that I can always get the output that I expect), but this is slightly different from Mocking at this level.

Can anyone suggest how to debug this part of the data access layer [I know for the Entity Framework that https://effort.codeplex.com/ exists ??

---

<B> Detail For example, if I have the following method

public object RunStoredProc() { //Some Setup using (SqlConnection conn = new SqlConnection(CONNNECTION_STRING)) { using (SqlCommand comm = new SqlCommand("storedProcName", conn)) { conn.Open(); comm.CommandType = CommandType.StoredProcedure; using (SqlDataReader reader = comm.ExecuteReader()) { while (reader.Read()) { //Logic } } } } //Return object based on logic } 

then how can I make fun of the output of a stored procedure so that SQLDataReader contains the specified data. At a higher level, I could make fun of the RunStoredProc() method, but that will not help me verify the logic in this method. Alternatively, I could remove SQLReader in another method

 public object RunStoredProc() { //Some Setup List<object> data = GetData(); //Logic //Return object based on logic } private List<object> GetData() { using (SqlConnection conn = new SqlConnection(CONNNECTION_STRING)) { using (SqlCommand comm = new SqlCommand("storedProcName", conn)) { conn.Open(); comm.CommandType = CommandType.StoredProcedure; using (SqlDataReader reader = comm.ExecuteReader()) { while (reader.Read()) { //place into return object } } } } } 

but since the GetData method must be private (not part of the published interface), then I could not mock it, and therefore the problem remains.

Answer 1

I think that we have all the interfaces ( IDbConnection , IDbTransaction , IDbCommand , IDataReader ) and borrowing ideas from EF ( IDbConnectionFactory ) to abstract everything we need so that they can be mocked and used with dependency injection. I think SqlConnection and the rest are more implementation details and can be abstracted.

Following the idea of ​​the Entity Framework, you can create a factory connection

 public interface IDbConnectionFactory { /// <summary> /// Creates a connection based on the given database name or connection string. IDbConnection CreateConnection(string nameOrConnectionString); } 

And you can then reorganize your sample method to use only abstractions.

 public class MyDataAccessClass { private IDbConnectionFactory dbConnectionFactory; private string CONNNECTION_STRING = "Connection string here"; public MyDataAccessClass(IDbConnectionFactory dbConnectionFactory) { this.dbConnectionFactory = dbConnectionFactory; } public object RunStoredProc() { //Some Setup List<object> result = new List<object>(); using (IDbConnection conn = dbConnectionFactory.CreateConnection(CONNNECTION_STRING)) { using (IDbCommand comm = conn.CreateCommand()) { comm.CommandText = "storedProcName"; conn.Open(); comm.CommandType = CommandType.StoredProcedure; using (IDataReader reader = comm.ExecuteReader()) { while (reader.Read()) { //...Logic to populate result } } } } //Return object based on logic return result; } } 

From there, you mock interfaces using your mocking selection structure or create your own fakes to introduce and test your method.

 [TestClass] public class StoredProcedureUnitTest { [TestMethod] public void TestRunStoredProc() { //Arrange var connectionFactory = new Mock<IDbConnectionFactory>(); //..Setup... var sut = new MyDataAccessClass(connectionFactory.Object); //Act var actual = sut.RunStoredProc(); //Assert //... } }