How to unit test database depended behavior C#

I have a C# application which communicates w开发者_StackOverflowith 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!

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.

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

SQLite Nunit & Fluent Nhibernate - Test your data access layer

As others have said, you must NOT depend on a DB and it's structure in unit-testing. Hence I would create an Interface which hides the DB from the component which uses it (aka DB abstraction layer) and then implement an in-memory-DB for the sake of unit-testing.

Tables of this in-memory-DB can be implemented using a simple C# HashTable for each DB table.

This seperation also has the benefit of creating a separation in your code between the DB which may change (e.g. move from MySQL to Oracle, etc) from the components which use it.

You should use mockup programs ( plugins) like:

Rhino mocks or moq

those programs can simulate data source like database during testing.

Consider switching to MbUnit for your integration tests. It has very handy Rollback attribute exactly for your needs.

MbUnit has the same attributes for marking test classes and methods as NUnit has. So you'll only need to change the using directives and reference the MbUnit dll's.