How to Test Database-Related Code with Nunit

How do I test database-related code with NUnit?

NUnit now has a [Rollback] attribute, but I prefer to do it a different way. I use the TransactionScope class. There are a couple of ways to use it.

[Test]
public void YourTest() 
{
    using (TransactionScope scope = new TransactionScope())
    {
        // your test code here
    }
}

Since you didn't tell the TransactionScope to commit it will rollback automatically. It works even if an assertion fails or some other exception is thrown.

The other way is to use the [SetUp] to create the TransactionScope and [TearDown] to call Dispose on it. It cuts out some code duplication, but accomplishes the same thing.

[TestFixture]
public class YourFixture
{
    private TransactionScope scope;

    [SetUp]
    public void SetUp()
    {
        scope = new TransactionScope();
    }

    [TearDown]
    public void TearDown()
    {
        scope.Dispose();
    }

    [Test]
    public void YourTest() 
    {
        // your test code here
    }
}

This is as safe as the using statement in an individual test because NUnit will guarantee that TearDown is called.

Having said all that I do think that tests that hit the database are not really unit tests. I still write them, but I think of them as integration tests. I still see them as providing value. One place I use them often is in testing LINQ to SQL code. I don't use the designer. I hand write the DTO's and attributes. I've been known to get it wrong. The integration tests help catch my mistake.

nunit test case for db connection

Calling an assert function NUnit.Framework.Assert.That(con.State == ConnectionState) for con.Open() and con.close()

namespace TestProject
{
    [TestClass] //Test Framework
    public class UnitTest1
    {
        Connection con = new Connection();

        [TestMethod] //Test Case 1
        public void TestOpenConnection()
        {
            con.ConnectionString = "Server = ab1-vm.app.test.com ;Port = 6080;Database = MY_DB;Schema = SCHM;User id = admin;PWD = *****;level=3;logname=C:\\my_logs";
            con.Open();
            NUnit.Framework.Assert.That(con.State == ConnectionState.Open);
        }

    
        [TestMethod] //Test Case 2
        public void TestCloseConnection()
        {
            con.ConnectionString = "Server = ab1-vm.app.test.com ;Port = 6080;Database = MY_DB;Schema = SCHM;User id = admin;PWD = *****;level=3;logname=C:\\my_logs";
            con.Open();  //open the connection
            con.Close(); //close to test if the connection closes
            NUnit.Framework.Assert.That(con.State == ConnectionState.Closed);
        }
    }
}

How to write NUNit test cases when data is read from some database?

An excellent way of doing this is by inserting data (do it in the negative index range (assuming the negatives are not in use for production data)) run your tests with that dataset, and then roll back your transaction once you are done.

Another option that would help you test your datalayer better down the road, it to make a wrapping interface for your database layer. You can then mock that interface when you are running tests that need it.

Normally you might have your IDataReader object and call IdataReader.GetMeData and it would return the result set from the DB. In this case you would mock it (I use Rhino Mocks) and tell it to return your set of test data when GetMeData is called.
p.s. Don't forget to use dependency injection to pass in the mock database access object.

How to do Data independent unit testing using NUnit

If you are really hitting the database this not a unit test but integration test.

Basically you have two options which one with it's pros and cons:

1. Keep with the idea of integration tests but ensure somehow that the data you are using is as you expected.
   This can be achieved using stored procedure in your testing database that recreate your data while calling it, you can call this procedure in your tests initialization and then do all of your testing.
   The main disadvantage here is that the test will take more time than unit test and will cost more resources.
   
   The main advantage is that you can be sure you're code integrates well with your database.

2. Choosing to use a real unit tests, in this option you will not going to use the database at all but instead create in-memory objects that represents the data from your database.
   
   
   Because you will create this objects in the arrange part of your unit test you can know extacly what data they are holding.
   
   The main disadvantage here is that you can't be sure you're code integrates well with your database.
   
   The main advantage is that the test will take less time than integeration test and will cost less resources, moreover your test can be run even if your testing database is down.

If you want you can actually choose to use both options, this is useful because each test is testing your code from a different perspective.

More about unit tests vs integeration tests can be found here.

Unit test code that interacts with database without creating data in database

I think what you're looking for is mocking. This allows you to simulate your data access code in order to test your business logic.

There are lots of good frameworks out there including moq and rhino mocks.

If you want to actually populate your database in order to test your data access layer then you're looking for more of an integration test. I've written quite a thorough answer covering my approach to db integration tests here.

NUnit testing the application, not the environment or database

The solution to this problem is well-known: mocking. Refactor your code to interfaces, then develop fake classes to implement those interfaces or mock them with a mocking framework, such as RhinoMocks, easyMock, Moq, et. al. Using fake or mock classes allow you to define what the interface returns for your test without having to actually interact with the external entity, such as a database.

For more info on mocking via SO, try this Google search: http://www.google.com/search?q=mock+site:stackoverflow.com. You may also be interesting in the definitions at: What's the difference between faking, mocking, and stubbing?

Additionally, good development practices, such as dependency injection (as @Patrik suggests), which allows the decoupling of your classes from its dependencies, and the avoidance of static objects, which makes unit testing harder, will facilitate your testing. Using TDD practices -- where the tests are developed first -- will help you to naturally develop applications that incorporate these design principles.

---

Related Topics