How to unit test database depended behavior C#

Question

I have a C# application which communicates with DB. I want to test some functions which depend on DB. So I want to be sure that DB has an initial state before each test run. I use NUnit to test my application. What means are available to restore an initial state of DB?
Thank you for your help!

Answer 1

You don't unit test interaction with the database. This type of testing is called integration testing. You can use your usual testing tools (NUnit, MSTest, etc) for this, but best is to separate integration tests in a separate project, use a dedicated test database, and run those tests within a transaction (for instance, use TransactionScope) that you rollback. This ensures the data doesn't change and behavior of your tests is predictable, which is very important.

If you can, try to design your application in such a way that it is easy to fake all external sources such as your database to allow you to run (usually faster) unit tests. This however isn't easy. Especially when dealing with an existing application that hasn't been designed for testability. In that scenario I've found integration testing a good way to start.

Answer 2

You may use in memory SQLite database for creating test db environment.

SQLite Nunit & Fluent Nhibernate - Test your data access layer