0

I tried creating unit tests for some methods that has stored procedures. Below are the questions i have.

  • How to unit test a project that uses lot of stored procedures to get the data from database for validations?

    • If i have to call those methods from my unit test, i am actually making n no of database calls...Is it worth it?

    • If i have to use the mock data, how to get the copy of the data from database locally?

    • How to mimic the stored procedure calls to use the mock data?

user3123734
  • 45
  • 6
  • 1
    What part of calling stored procs you want to test? Are you writing your own SQL classes? (For regular app you'd just mock data persistence layer and never worry about DB calls in unit tests...) – Alexei Levenkov Feb 20 '14 at 22:46
  • To validate a field it makes couple of db calls and based on the data it returns it does couple of validations on those fields and returns true or false. Given the scenario, how will you mock? and what do you mean by mocking data persistence layer? Pls eloborate. Thanks much for your response! – user3123734 Feb 20 '14 at 22:50
  • Jeroen Vannevel have very detailed answer (+1). It indeed does not look like you need *unit tests* for that case. – Alexei Levenkov Feb 20 '14 at 23:02

2 Answers2

1

You're looking at integration tests, not unit tests. Integration tests have a dependency from an external system (datetime, database, filesystem, networkservice, etc) while unit tests their scope is confined to one unit (one or more methods in one or more classes).

The first thing to realize here is that in order to do an integration test, you will be relying on an external system. You never want to test against your production environment and you want to maintain control over the test data.

For this reason you should create a database that is exactly the same as your existing one but is stripped of all data and uses mock data instead. This makes sure that your tests will stay consistent throughout changes and thus can be relied upon.
You should also remember to keep your test database functionally equivalent to your live database to avoid falling behind and having outdated tests.

What's key here is this: abstract your data source layer. If you put all your database calls behind an interface, you can simply mock/stub this interface and provide test data from there.

Your unit of work should be to the point and test the one thing the method is supposed to do (Single Responsibility Principle).
If the objective of your method is to manipulate data from the database then you mock your data source and you unit test your manipulation.
If the objective of your method is to call a stored procedure in your database, then you use your integration test to feed the procedure input and assert against its output.

A simple example of this principle in action:

interface DataSource {
    List<String> GetData();
}

class Manipulator {
    private DataSource _source;

    public Manipulator() { }

    public Manipulator(DataSource d) { _source = d; }

    public void ManipulateData() {
        var data = _source.GetData();

        // Do something with the data
    }
}

This sample utilizes constructor injection (look into the concepts of dependency injection and IoC containers!).

Now to answer your questions to the point:

If i have to call those methods from my unit test, i am actually making n no of database calls...Is it worth it?

Yes, otherwise you don't have an integration test. Since you have a separate test database this will not interfere with your production environment.

If i have to use the mock data, how to get the copy of the data from database locally?

I'm not quite sure what you're asking here to be honest, but I think it is resolved with the idea of a testing database.

How to mimic the stored procedure calls to use the mock data?

You don't. You use the actual stored procedures by calling them with your test data and asserting the result on what you expect. If they work then that's the end of it, if they don't return the correct result then you know something went wrong.

Patrick
  • 1,717
  • 7
  • 21
  • 28
Jeroen Vannevel
  • 43,651
  • 22
  • 107
  • 170
  • Thank you so much for the detailed explanation! We have different copy of the same databases...say 100. the data might differ in some cases but same for some. What would be the better approach in this case? – user3123734 Feb 20 '14 at 23:30
  • Why do you have 100 databases? Are they the same database functionally with just different data or are the databases entirely different? – Jeroen Vannevel Feb 20 '14 at 23:35
  • Yes! It is all the same schema in most cases with different data...It is maintained seperately for data protection & other reasons. Thanks! – user3123734 Feb 21 '14 at 00:01
  • Then you only need one testing database. You want to test the functionality (stored procedures) in the database, not the data itself. So now you will have 101 databases: 100 with production data and 1 with test data where you perform your tests on. – Jeroen Vannevel Feb 21 '14 at 00:03
0

You need to define an interface that has all of your data access methods. You already have one class with those methods, so you can simply refactor it in Visual Studio by right clicking in an empty space in your data access class and select Refactor and then Extract Interface. So now you have an interface, you need to add a mock data access class that implements that interface.

In doing this, your mock data access class doesn't need to know anything about any stored procedures as long as it returns some data of the correct types specified by the interface. The last part is to pass an instance of the actual data access class when using the application and for testing, pass an instance of the mock data access class instead.

Sheridan
  • 68,826
  • 24
  • 143
  • 183