how can I unit test my class methods having database calls

Question

I am working on introducing unit testing to a class in my project which is responsible for verifying customers according to a specific criteria. For clarity, let's say one of the methods in my class is shown below:

public static MyCustomStatus IsCustomerVerified(int OrderId, int customerId)
{
  MyCustomStatus result = null;
  //invoke a stored procedure and use its result to initialize 'result' here
  using (var conn = new SqlConnection("con_string"))
  using (var cmd = new SqlCommand("my_stored_procedure", conn) { CommandType = CommandType.StoredProcedure, CommandTimeout = 120 })
  {
    cmd.Parameters.AddWithValue("@OrderId", OrderId);
    cmd.Parameters.AddWithValue("@customerId", customerId);
    conn.Open();
    SqlDataReader reader = cmd.ExecuteReader();
    while (reader.Read())
    {
        if (result == null)
        {
            result = new MyCustomStatus()
            {
                VerificationStatus = Convert.ToInt32(reader["VerificationStatus"]),
                StatusName = reader["StatusName"].ToString(),
                ErrorMessage = Convert.ToBoolean(reader["ErrorMessage"])
            };
        }                        
    }
  }
  return result;
}

and the MyCustomStatus class is something like this:

class MyCustomStatus
{
  public int VerificationStatus {get;set;}
  public string StatusName {get;set;}
  public string ErrorMessage {get;set;}
}

I know about the first rule of TDD is first create tests and then implement the methods but as I have already implemented the methods, (and some of them are implemented by other developers), I want to bring unit testing into picture but how? I have read multiple tutorials and seen videos and almost all of them take an example of a simple math operation for example SUM(1,1) should be 2 otherwise test fails. Do I need to revamp my code only then I can use unit tests for this class? If yes, what type of revamping?

Environment: C#, .NET framework, Visual Studio solution

Checkout Mocking, click the thread: https://stackoverflow.com/questions/2665812/what-is-mocking — Lakshya Srivastava, Aug 30 '19 at 02:56
Your question would be way more answerable if you would replace the comment in `IsCustomerVerified` by some more code details, so we could tell you how to modify that code to make the class more testable. — Doc Brown, Aug 30 '19 at 04:15
A similar question was asked and answered 11 years ago on Stackoverflow: [How to unit test an object with database queries](https://stackoverflow.com/questions/30710/how-to-unit-test-an-object-with-database-queries). You should be able to to transfer the solution to your case. — Doc Brown, Aug 30 '19 at 04:20
Possible duplicate of [Staying OO and Testable while working with a database](https://softwareengineering.stackexchange.com/questions/42792/staying-oo-and-testable-while-working-with-a-database) — gnat, Aug 30 '19 at 05:44

score 1 · Answer 1 · answered Aug 30 '19 at 03:59

Do I need to revamp my code only then I can use unit tests for this class?

A little bit.

The basic idea is that code that is coupled to the database is hard/expensive to test, and therefore we want to choose a design where very little code is coupled to the database, so that we can test everything else cheaply.

Furthermore, we choose a design such that we can easily replace the code coupled to the database with an in memory substitute. Thus, in the unit test, we'll construct a test subject that uses the substitute (and therefore is not coupled to the database), and then test that.

The bits that are coupled to the database? We'll test them some more expensive way (integration tests perhaps). Because that part of the code is going to be more expensive to test, we work really hard to make it as simple as possible.

All of the complicated bits want to be in the easy to test part.

You might have a function that looks something like

void InvokeStoredProcedure(int OrderID, int CustomerId, Listener listener) {
    using (SqlConnection conn = ... ) {
        SqlCommand cmd = command(conn)
        cmd.Parameters.Add( paramter(OrderID) )
        cmd.Parameters.Add( parameter(CustomerId) )

        r = cmd.ExecuteReader()
        while(r.Read()) {
            listener.onRow(r[0], r[1], r[2], ...)
        }
    }
}

and the substitute version could look like

void Substitute(int OrderID, int CustomerId, Listener listener) {
    listener.onRow("Correct", "Horse", "Battery", "Staple")
}

All of this is justified by the argument that we should prefer designs that are easy to test.

how can I unit test my class methods having database calls

1 Answers1