How to test a method that makes calls to a database

Refresh

April 2019

Views

100 time

1

I have several methods that make connections to a database and I can't figure out how to write unit tests for them. Here is a sample of what I'm talking about.

public static User GetByPendingUserId(int a_PendingUserId)
{
    User l_User = new User();
    SqlConnection l_conn = DbHelp.CreateSqlConnection();
    SqlCommand l_cmd = DbHelp.CreateCommand(l_conn, "User_Get");
    l_cmd.Parameters.AddWithValue("@a_PendingUserId", a_PendingUserId);
    DataTable l_result = new DataTable();
    SqlDataAdapter l_adapter = new SqlDataAdapter(l_cmd);
    l_conn.Open();
    l_adapter.Fill(l_result);
    l_conn.Close();
    l_adapter.Dispose();
    l_cmd.Dispose();
    if (l_result.Rows.Count > 0)
    {
        User.SetMembers(l_result.Rows[0], l_User);
    }
    return l_User;
}

Since I am writing a unit test and not an integration test, I want to test the logic of the method without connecting to an actual database. How can I do this?

1 answers

3

Assuming you want to test the code that relies on these methods one way you could achieve this is by using the Repository pattern. You encapsulate data access for a given section of you system into an abstraction called a repository. So in your case you may have a user repository which would be comprised of the following interface for instance and a corresponding implementation.

public interface IUserRepository
{
    User GetByPendingUserId(int a_PendingUserId)
}

Note that GetByPendingUserId can't be static anymore.

You can now implement this interface. So your actual production implementation might look like this:

public class UserRepository : IUserRepository
{
    public User GetByPendingUserId(int a_PendingUserId)
    {
        User l_User = new User();
        SqlConnection l_conn = DbHelp.CreateSqlConnection();
        SqlCommand l_cmd = DbHelp.CreateCommand(l_conn, "User_Get");
        l_cmd.Parameters.AddWithValue("@a_PendingUserId", a_PendingUserId);
        DataTable l_result = new DataTable();
        SqlDataAdapter l_adapter = new SqlDataAdapter(l_cmd);
        l_conn.Open();
        l_adapter.Fill(l_result);
        l_conn.Close();
        l_adapter.Dispose();
        l_cmd.Dispose();
        if (l_result.Rows.Count > 0)
        {
            User.SetMembers(l_result.Rows[0], l_User);
        }
        return l_User;
    }
}

For testing purposes you can now implement a another version of this that returns whatever data you like to fit the goal of your test. Maybe it just returns a new User. It's up to you. The point is you no longer have a dependency on an actual database and you can focus on testing the logic of your application in your unit tests.

public class TestUserRepository : IUserRepository
{
    public User GetByPendingUserId(int a_PendingUserId)
    {
        return new User()
    }
}

Once you have more interfaces like these in place you can then look at using a mocking framework which gives you more flexibility around testing specific scenarios.