Instead of just saying “Because it’s a unit test”, lets talk about why we might want to have a test that doesn’t depend on a database.

Isolated Failures

We want a failing unit test to quickly identify where an issue is. The narrowest unit test failure already has two possible sources, both the unit under test and the test itself. Every dependency is another possible error source.

Assuming you do not have custom database data for every test, you run the risk that someone modifies the data for another test, but breaks yours. If you do have data for every test, then managing it all just became a pain. Schema change? Go modify every unit test.

Fast Failures

Unit tests should be fast. How fast? Fast enough that developers do not resist running them regularly, and do not waste time when doing so. Unit tests quickly lose a lot of value if people never run them because they spend 45 minutes setting up and tearing down the database 1000+ times.

Testing with the Database

That being said, our app does use a database. If we never test with the database, we don’t really know if the app works. Many projects do this testing when testing the integrated whole, and usually get run less often than unit tests. For example, one of my projects involved several applications with unit tests that could be run in less than 30 seconds a piece, but the integrated system tests lasted 4+ hours on a compute grid.

Despite that many developers prefer the term “integrationtests” instead of “unittests” when external systems like databases are involved
automated tests with databases are often painfull and fragile:

From Unit Testing with an In-Memory Database for a Spring/Hibernate Project

Testing DB code is often painful with any significant number of people. Often you 
either need every developer to have their own database, with it's own uniquely 
inconsistent data and ways of failing, or to use a shared database in which case 
people often manage to produce tests that don't work if more than one person runs 
the tests concurrently. Also testing on a "real" DB is slow and subject to failure 
for reasons not related to your code. 

With fragile i mean “does the test fail because there is a logical error in the code or is it because someone deleted a datarow that is required by the test”.

In order for database related tests to work you need to have a predefined set of databasecontent wich must be maintained. We have projects where there is a automated-testing-ony database where the developpers are not allowed to modify any data.

The other alternative is that the automated test generates the required test-data itself and works with an in-memory database.

It’s because a unit test shouldn’t have any dependencies. It must be self contained. Unit tests exists to test a “unit” of logic in your code by treating it as a black box. The specific source of data (database or other) should have no impact on the logic.

Anything else, even if called via a unit testing framework isn’t classed as a unit test. It would be a different type of test such as a functional test.

It’s worth looking up some of Kent Beck ‘s material on TDD.

MSTest?

The other answers say why you need to test in isolation, but you then test the DB stored procedures as well – call them with a certain input to a table filled with pre-defined data, and see what result is returned. Each test sets up data, calls the test code, then rollsback so the DB is back to its original state.

This is easy, use it in conjunction with your client-side tests. (its just a shame few people know enough to realise that unit testing applies to SQL as well as C# code, if only they would split the sql code into stored procedures they’d find DB testing was as easy. Few people write proper DB unit tests, which is surprising given how important the DB aspect is to most programs).

Now there is a separate issue with integration testing, and for that you’ll need to set up a DB with sample data and then call your client (using Selenium or similar) so that you exercise the whole thing end-to-end. This isn’t unit testing per-se. It is still very useful, and you really should be doing this as well – unit tests only verify the tiny detail of little bits of code and ignore the ‘bigger picture’ of how they work together. Integration tests will verify that your entire app works when all those little units are plugged together.

This is not horribly straightforward but not impossible. The biggest hurdle is picking a tool and method to automatically stand up the database in the state you want that will work silently from the command line. Personally I prefer roundhouse for this task but there are certainly other options. Insofar as getting it integrated with visual studio, the most straightforward way is to create a custom configuration and hack the call to the executable to build the database into the custom build settings.

The next question is “what kind of database to build and how to test against it?”

The approach that I think makes the most sense is to deploy an empty copy of the schema and then have each test responsible for creating the test data it needs and well as cleaning up after itself. This will take a bit longer per test to run through but it keeps you out of cross-cutting concerns between tests.

Finally, as others have indicated this is going to be slow so if you could separate this out into it’s own project that could be worth it.

Database testing is not done in Unit test but in Integrated test.

The definition of unit test is a test that tests single unit of behavior in isolation. A test that tests integration with the database is testing neither a single unit nor in isolation, therefore it is by definition not a unit test.

Asking why there are no unit tests that integrate with the database is like asking why there are no odd numbers divisible by two: it’s simply because of the definition of the word “odd number”.