unit testing a call to a stored procedure with sqlconnection

Question

I've created a method that I'd like to unit test with NUNIT:

public void dea(Guid pguid, string serialNumber)
{
    var connectionString = ConfigurationManager.ConnectionStrings["myconnection"].ConnectionString;
    if (!string.IsNullOrEmpty(connectionString))
    {
        using (var con = new SqlConnection(connectionString))
        {
            try
            {
                con.Open();
                var cmd = new SqlCommand("spdea", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@pguid", pguid);
                cmd.Parameters.AddWithValue("@serialnumber", serialNumber);
                var rowsaffected = cmd.ExecuteNonQuery();
            }
            finally
            {
                con.Close();
            }
        }
    }
    else
    {
        throw new ConfigurationErrorsException("Configuration string is not found.");
    }

}

I want to execute several units against this method; however, it looks like I've written it in a way that does not support unit testing. For example, the SqlConnection is local to my method, and perhaps should instead be more public?

How can I rewrite this method to be more testable?

Currently, I've got the following in my unit test:

public void ExecuteNonQuery_was_called()
{
    //Arrange
    var consumer = new DTConsumer();
    var dbCommandMock = new Mock<IDbCommand>();
    dbCommandMock.Setup(dbc => dbc.ExecuteNonQuery());

    var dbConnectionMock = new Mock<IDbConnection>();
    dbConnectionMock.Setup(x => x.CreateCommand()).Returns(dbCommandMock.Object);

    //Act
    consumer.dea(It.IsAny<Guid>(), It.IsAny<string>());

    //Assert
    dbCommandMock.VerifyAll();
}

However, I'm off track because I think I've implemented the method in a non-testable fashion.

How can I rewrite this method to be more testable?

Honestly, I am not even sure that you need Mock in this case. You can setup your class test and just have static method that executes your proc and returns an integer. In the test setup method you will get your connection string and use it and reuse it. This is not even a unit test - this is functional test. You would use mock in cases when you test objects without connecting to database, therefore mocking data instead of working real data. — T.S., Feb 17 '16 at 23:00
Something along these lines http://stackoverflow.com/questions/23374314/using-moq-and-mock-objects-my-list-count-is-always-0-when-it-should-not-be. Mocking should create something for you that you can use instead of DB. for example create mock of UserModel->fill some properties->mock IUserDataProvider->Setup this IUserDataProvider.Save->call save->Examine Result. For example that in result a call went to Idbommand.ExecuteNonQuery and examine parameters that should have values as in original UserModel mock — T.S., Feb 17 '16 at 23:14
See!! you asking right questions. This should be different test. To test your stored proc, you don't need to use unit test. Stored proc is a static code. You can test it right from DB management console. If you want to test your data integrity, ok, use unit test but no need to mock. Just call your proc and test if records good. But if you want to test your CODE, now you can use mocks — T.S., Feb 17 '16 at 23:19
Are you trying to test that it calls to an actual database (aka integration test)? — Nkosi, Apr 18 '16 at 12:55

unit testing a call to a stored procedure with sqlconnection

0 Answers0