C# Unit Testing SQL Server – Adding Unit Tests to a Large Project with Stored Procedures

centity-frameworksql serverstored-proceduresunit testing

We work on a fairly large casino/gaming/wallet/lottery platform. It's a turn-key application that is currently in use by 4 clients, and soon to be much more. I've made some bullet points regarding the architecture below:

  • ~130K LOC in just C# code
  • over 500 stored procs, in one of the 5 databases our system uses – because our code is used in regulated environments, the source code is "locked" for some clients until it is reviewed by a (very expensive) outside agency, so making changes via stored proc is a good balance of keeping both regulators and clients happy. The other databases are not near the complexity of the "main" database.
  • C# application code, spread out in Windows Forms, Xamarin Android, Windows services, web apps, SQL CLR library, class libraries and unit test projects
  • Uses EF6 for data layer, some ad-hoc, some stored procs

While we have some unit tests in place, it's mostly for infrastructure-related stuff. There aren't many tests that test the business features. The other issue is that a lot of the heavy lifting is done in stored procedures. It's come time to add some unit tests in before we start refactoring some of the code, to prevent regressions. What I am looking for is a list of the different ways to go about this. This is what I have come up with so far:

  • We have a "Test" SQL Server instance that we could set up to clear and repopulate data when starting each test.
    • Pros
      • The clear/reload data script can easily be modified when adding new tests
    • Cons
      • The clear/reload script could get huge
      • Testing could be slow because of clearing/reloading data on each test run
  • We could use Moq to mock the EF6 data context as I have read on SO
    • Pros
      • Testing would be fast as the data would come from C# code and be compiled
    • Cons
      • Adding test data in C# would take forever
  • Use mstest to test C# procs that don't use stored procedures, and use Redgate's SQL test product to test SQL procedures by itself
    • Pros
      • Unsure. While we own the developer suite of Redgate products, we haven't used SQL test before.
    • Cons
      • It's not a true test of the inner workings of the procs in my opinion
  • Make a new override of the EF data context that forcibly loads the databases on the QA server, creates a new SqlTransaction on the connection, and when disposed of, calls Rollback to dispose of any changes made
    • Pros
      • Doesn't require a lot of modifications to a lot of the internals of the existing code, or refactoring
    • Cons
      • Requires that we refactor a bunch of code to take in a database connection rather than allowing it to be created, e.g. make an IOC for getting the database connection

The plan I have is to get a couple of good tests written, and if a lot of refactoring is not in the cards, have our QA guy learn programming by writing tests based on the examples made while getting it all set up.

How should I handle adding unit testing to a large project? How should I handle testing procedures that call stored procedures in a database? What I would like to know is the best way to shim unit tests in without a lot of refactoring. Refactoring this code base to decouple the data layer, for some spots, would not be hard, but in other spots, would be tough to do, so the method with the least amount of friction would be best.

Best Answer

A stored procedure and the C# code that uses it are different units, so a single unit test can not test them both. You'll need separate unit tests:

  • Unit tests that mocks the stored procedure and verifies the C# code uses it correctly.
  • Unit tests that activate the stored procedure the same way the C# code would activate it(if you really want to you can say it mocks the C# code) and verifies the stored procedure does what it is supposed to do.

Unit tests in both bullets assume the interface between the C# code and the stored procedures remain the same. But if you plan on refactoring, there is a good chance you'll want to refactor that interface! Unit tests will not protect you then: if the interface changes both mocks are out-of-date and the tests will need to be rewritten!

I suggest that instead of focusing on unit tests you should focus more on integration tests. This means a single test can test both the C# code and the stored procedure it uses. This also means you don't need to mock the database (only the data), so writing the tests will be much easier.

See if you can use transactions around each test. BEGIN the transaction before you start the test, and after the test ROLLBACK the transaction. That way you won't have to repopulate the database for each test - every test in the session will use the same initial database and the framework will clean up after it.

Related Topic