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.

  • 2
    Those 500 stored procedures, they don't require a user interface, I guess? And they are all programmaticably callable, I guess? And they contain a lot of business logic? So that looks like a good place to start with automatic tests (unit tests or not). Start without any tools or frameworks and see what kind of tests you want to implement, then pick a framework to make the process a little bit more smooth, not the other way round.
    – Doc Brown
    Commented Nov 3, 2016 at 19:04
  • 1
    How should I handle adding unit testing to a large project? Watch this presentation. Very relevant, do not make the mistake of dismissing it because it takes place at a Rails conference.
    – radarbob
    Commented Nov 4, 2016 at 22:15

1 Answer 1


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.

  • I can do this using a dbEntitiesContext.Database[.Connection].BeginTransaction() and RollbackTransaction() when calling stored procs? I can find out for myself if you don't know the answer. I would only have to refactor some code to take in a database connection if that's the case, and then have the specialized data connection call Rollback on dispose. Commented Nov 4, 2016 at 20:35
  • Yup. If you set your test framework to stop on the first test failure you might want to figure a way to prevent the rollback if the test failed(maybe by using the opposite of this). This will allow you to examine the database, which can be crucial to figuring out why the test failed. Also, it's worth mentioning again that these are no longer unit tests - they are now integration tests.
    – Idan Arye
    Commented Nov 5, 2016 at 2:31
  • Good answer. Integration testing is generally considered more productive, though at the cost of losing some specificity as to where failures are occurring. Managing data state & interdependence by minimizing visible change wherever possible (rollback), separating data elsewhere (ie, creating separate users/ accounts per-test for any which might need to commit), and strictly avoiding committing changes which could impact other tests can make this quite realistically achievable. PS: Design can be elegant to keep transaction coordination one layer above the DB code.
    – Thomas W
    Commented Feb 10, 2022 at 22:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.