VS Unit test on database procedures inMemory that can be integrated with continuous delivery pipeline

user_308553 0
Viewed 67

So Im trying to figure out a way to properly unit test database procedures in visual studio so I can integrate it into our deployment pipeline.

what I am doing right now is create a database project and link it to our database. Then generate unit test with visual studio on the procedures. The problem is that unit testing the procedures this way will make changes to data in the actual database (which I don't want). Another problem is when someone make change to the database without updating that database project

I can either:

  1. do a rollback after every unit test (but there are some procedures that contain commit, so even if I rollback in c#, some part will still get committed)

  2. create a testing database (not an option in my situation)

  3. create a in-memory database (I prefer this option. I checked out SQLite. However it doesn't seem to support procedures)

Any advises or suggestions are appreciated

stored-procedures unit-testing tsql sql-server sql
1 Answers
  1. create a in-memory database (I prefer this option. I checked out SQLite. However it doesn't seem to support procedures)

SQL Server do not support in-memory option

  1. create a testing database (not an option in my situation)

This is usually a best option for tests against an actual database.
Tests should be executed in isolation of each other to produce consistent and trusted result.
Nobody will break your testing database, because you will run a script to create it and fill with the required data before tests.

  1. do a rollback after every unit test (but there are some procedures that contain commit, so even if I rollback in c#, some part will still get committed)

Try to use TransactionScope Class which should rollback internal transactions committed during test.
But if you are using some database which used by others, your tests can possibly fail because somebody made changes.

user_15655250
Related