Quick Start Guide

#NDbUnit Quick-start examples and walk-through#

##High-Level Overview of integrating NDbUnit into your Unit Tests##

1. Download the NDbUnit.Core.dll assembly from this site or check out the latest code and compile the solution for yourself
2. Create a Visual Studio project and reference the NDbUnit.Core.dll assembly into the project
3. Reference the support assembly that corresponds to your desired database target into the project (e.g., NDbUnit.SqlClient.dll = Microsoft SQL Server, NDbUnit.MySql.dll = MySQL support, etc.)
4. If using a database target OTHER than MS SQL or MS OleDB, reference the corresponding ADO.NET client library into the project (e.g., System.Data.MySQL.dll = MySQL support, System.Data.SQLite.dll = SQLite support, etc.)
5. Create a .NET dataset schema definition file (xsd) for the table(s) in your database that you want NDbUnit to operate on
6. Create an XML file containing the data that you want NDbUnit to load into your database during the test-run
7. Use NDbUnit methods to control the content (state) of your database during unit tests

##Step-By-Step Tutorial##

For this tutorial, let's assume that you have a (very simple) SQL Server test database as follows...

Table: Customer

field CustomerId: int, auto-increment PK

field Firstname: varchar[50], nullable

field Lastname: varchar[50], nullable

You can use the following sql script to create this table:

CREATE TABLE [dbo].[Customer](
	[CustomerId] [int] IDENTITY(1,1) NOT NULL,
	[Firstname] [varchar](50) NULL,
	[Lastname] [varchar](50) NULL,
 CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED 
(
	[CustomerId] ASC
)WITH (PAD_INDEX  = OFF,
  STATISTICS_NORECOMPUTE  = OFF,
  IGNORE_DUP_KEY = OFF,
  ALLOW_ROW_LOCKS  = ON,
  ALLOW_PAGE_LOCKS  = ON)
    ON [PRIMARY]
) ON [PRIMARY]

GO

###Step 1: Create a .NET Dataset containing the table###

NDbUnit relies on a .NET Dataset XSD file to control its interaction with your database when you invoke its commands. If your dataset contains every table in your database, then NDbUnit commands will affect your entire database. If your dataset contains a subset of the tables that are in your database, then NDbUnit commands will operate on just the subset of tables defined in your dataset XSD schema file.

• Add a new .NET Dataset to your project, let's call it MyDataset.xsd

• Browse to the test database from the Server Explorer in Visual Studio

• Drag the Customer table from the Server Explorer into the DataSet designer surface to add it to the DataSet definition

• You need not consider defining any DataAdapter queries or otherwise (and in fact can delete the default DataAdapter that Visual Studio automatically adds to the DataTable in the DataSet designer -- its not needed and so can be safely left in place or simply removed from the DataTable as desired)

• Save the DataSet file as MyDataset.xsd

• Note that you can also now choose to delete or retain the 'dataset designer support files' MyDataset.xsc, MyDataset.xss, and MyDataset.designer.cs as they are also not needed for NDbUnit and will be recreated by Visual Studio if you reopen the MyDataset.xsd file anyway

You should now have a file called MyDataset.xsd created by the Visual Studio Dataset Designer. This DataSet schema definition is used by NDbUnit to determine the tables within the database to operate upon and so you can limit the 'scope' of your database that NDbUnit will manipulate by carefully crafting .NET DataSets as needed to limit its interaction with your database to only a subset of your tables. In this simple example, there is only a single table in the database and so we add just this single table to our .NET dataset definition.

###Step 2: Create an XML file for the test data###

The next step is to create sample data for NDbUnit to load into the database to support your testing. The format for this sample data is an XML file that adheres to the XSD constraints defined by the dataset XSD schema definition file. In essence, its just a .NET dataset serialized to XML.

There are several methods for creating this sample data. For complex and large test data, we recommend that you consider programmatically filling the .NET Dataset defined in Step 1 above and then simply invoking the dataset's .WriteXML(...) method to output the dataset contents as XML. NDbUnit even provides a convenience method (GetDatasetFromDB(...)) that makes this trivially simple (more on that at the end of this tutorial).

For simpler test data (as is the case here), it can often be easiest to merely enter the data by hand. Either way, you should be able to create the following XML file and add it to your project:

<?xml version="1.0" encoding="utf-8" ?>
<MyDataset xmlns="http://tempuri.org/MyDataset.xsd">
  <Customer>
    <CustomerId>1</CustomerId>
    <Firstname>John</Firstname>
    <Lastname>Doe</Lastname>
  </Customer>
  <Customer>
    <CustomerId>2</CustomerId>
    <Firstname>Sam</Firstname>
    <Lastname>Smith</Lastname>
  </Customer>
</MyDataset>

In this XML file, we are defining two customer records (CustomerId=1 and CustomerId=2) and values for their Firstname and Lastname fields.

Note that the xmlns reference to the dataset schema file in Step 1 above (MyDataset.xsd) will allow Visual Studio to provide you with intellisense as you construct your test data, making it trivial to create simple test data by hand. Often with more complex test data, it can be simpler to add the records to the actual database directly and then programmatically fill the dataset and write the dataset out to XML as mentioned above.

###Step 3: Create an Instance of the NDbUnit database test class###

NOTE: These examples will assume that your unit test framework is NUnit. You may need to modify these examples (slightly) if you are using another test framework. Note that there is no direct dependency between NDbUnit and any specific unit test framework...in fact you could potentially use NDbUnit without any unit test framework at all if you choose.

Now we're (finally) ready to write some code. Create a test fixture, define a standard ADO.NET connection string (as appropriate for your own database, credentials, etc.), and pass it to the constructor for the SqlDBUnitTest class as below. This new object, the variable mySqlDatabase, will represent NDbUnit's interaction with your actual database and is the only NDbUnit-provided class that you need to instantiate to work with NDbUnit...

[TestFixture]
public class Tests
{
    [Test]
    public void Test()
    {
        string connectionString = "server=localhost;user=dbuser;password=dbpassword;initial catalog=MyDatabase;";
        NDbUnit.Core.INDbUnitTest mySqlDatabase = new NDbUnit.Core.SqlClient.SqlDbUnitTest(connectionString);
    }

}

###Step 4: Tell the NDbUnit test class what schema and data files to use###

We next need to assign the NDbUnit test class the dataset XSD file and the test data XML files that we created in Steps 1 and 2 as follows...

[TestFixture]
public class Tests
{
    [Test]
    public void Test()
    {
        string connectionString = "server=localhost;user=dbuser;password=dbpassword;initial catalog=MyDatabase;";
        NDbUnit.Core.INDbUnitTest mySqlDatabase = new NDbUnit.Core.SqlClient.SqlDbUnitTest(connectionString);

        mySqlDatabase.ReadXmlSchema(@"..\..\MyDataset.xsd");
        mySqlDatabase.ReadXml(@"..\..\MyTestdata.xml");
    }

    }

Note that the path to these files given to NDbUnit makes use of relative paths and assumes:

• you placed the XSD and XML files in the root of your project in Visual Studio
• you are compiling your project to the default output location of Debug\Bin or Release\Bin; if you have changed any of these defaults, then you may need to adjust the paths you provide NDbUnit to your XSD and XML files accordingly

###Step 5: Delete all existing data in the database and load test data###

The first step to managing the state of the database is (usually!) to remove all records from the DB in preparation to load the test data and then insert the test data into the database. We perform this operation by asking the NDbUnit test class to perform the CleanInsertIdentity DBOperration. As its name suggests, this performs a Clean operation (which deletes all data in the tables in the database) along with an InsertIdentity operation (which inserts data along with its identity values from the test data XML file) as so...

[TestFixture]
public class Tests
{
    [Test]
    public void Test()
    {
        string connectionString = "server=localhost;user=dbuser;password=dbpassword;initial catalog=MyDatabase;";
        NDbUnit.Core.INDbUnitTest mySqlDatabase = new NDbUnit.Core.SqlClient.SqlDbUnitTest(connectionString);

        mySqlDatabase.ReadXmlSchema(@"..\..\MyDataset.xsd");
        mySqlDatabase.ReadXml(@"..\..\MyTestdata.xml");

        mySqlDatabase.PerformDbOperation(NDbUnit.Core.DbOperationFlag.CleanInsertIdentity);

    }

}

This completely deletes all records from the existing database and inserts our test data along with its identity values in preparation to run our tests. Note that this step may or may not be needed with your own test database -- its demonstrated here because our subsequent unit test Assert(...) makes the assumption that the only records in the database are those loaded by NDbUnit.

###Step 6: Run the database dependent tests###

Now that the database has been emptied out and the test data inserted into it, the next step is to run the tests of your data access layer that are dependent upon the data being in the database. In this example, we are invoking a hypothetical GetAllCustomers(...) method that returns the complete list of customers in the database...

[Test]
public void Test()
{
    string connectionString = "server=localhost;user=dbuser;password=dbpassword;initial catalog=MyDatabase;";
    NDbUnit.Core.INDbUnitTest mySqlDatabase = new NDbUnit.Core.SqlClient.SqlDbUnitTest(connectionString);

    mySqlDatabase.ReadXmlSchema(@"..\..\MyDataset.xsd");
    mySqlDatabase.ReadXml(@"..\..\MyTestdata.xml");

    mySqlDatabase.PerformDbOperation(NDbUnit.Core.DbOperationFlag.CleanInsertIdentity);

    CustomerRepository repository = new CustomerRepository();

    Assert.AreEqual(2, repository.GetAllCustomers().Count);

}

By using NDbUnit to first delete all records that might exist in the database and then inserting the test data before the test is run, we are ensured of the validity of the subsequent Assert(...) statement in the unit test because our test data contains exactly two Customer records. This test will always pass because we are in complete control of the content of the database when the test is run.

###Consider Refactoring the Unit Test###

To improve the structure of the unit test fixture in the above example, consider refactoring it to leverage your unit test framework's support of fixture and test setup and teardown methods as so...

[TestFixture]
public class Tests
{
    private string _connectionString;

    private NDbUnit.Core.INDbUnitTest _mySqlDatabase;

    [SetUp]
    public void _Setup()
    {
        //before each test, we ensure that *only* our expected test data is loaded
        _mySqlDatabase.PerformDbOperation(NDbUnit.Core.DbOperationFlag.CleanInsertIdentity);
    }

    [FixtureSetUp]
    public void _TestFixtureSetup()
    {
        //when the fixture is setup, we configure our NDbUnit class instance and save it in the fixture
        _connectionString = "server=localhost;user=dbuser;password=dbpassword;initial catalog=MyDatabase;";
        _mySqlDatabase = new NDbUnit.Core.SqlClient.SqlDbUnitTest(_connectionString);

        _mySqlDatabase.ReadXmlSchema(@"..\..\MyDataset.xsd");
        _mySqlDatabase.ReadXml(@"..\..\MyTestdata.xml");

    }

    [FixtureTearDown]
    public void _TestFixtureTearDown()
    {
        //when all of the tests are complete, we delete all records from the database
        // to clean up after ourselves
        _mySqlDatabase.PerformDbOperation(NDbUnit.Core.DbOperationFlag.DeleteAll);
    }

    [Test]
    public void Test()
    {

        //invoke the test; with the setup moved out of here, the test is simpler and cleaner!

        CustomerRepository repository = new CustomerRepository();

        Assert.AreEqual(2, repository.GetAllCustomers().Count);
    }

}

###Using NDbUnit convenience methods to create the test data###

As mentioned above, NDbUnit provides a convenience method for filling a .NET dataset with the contents of your database. This can be valuable in situations where your test data is large, complex, or for whatever reason already exists in the test database and you need to get it loaded into the XML file programmatically for use in later unit tests. This eliminates the need to create a separate DataAdapter for the sake of retrieving data from the database to create an XML data file in support of further unit tests. The following snippet of code demonstrates the use of this convenience method, GetDatasSetFromDb(...)...

_connectionString = "server=localhost;user=dbuser;password=dbpassword;initial catalog=MyDatabase;";

_mySqlDatabase = new NDbUnit.Core.SqlClient.SqlDbUnitTest(_connectionString);

_mySqlDatabase.ReadXmlSchema(@"..\..\MyDataset.xsd");

System.Data.DataSet ds = _mySqlDatabase.GetDataSetFromDb();
ds.WriteXml("TestData.xml");

This will result in a new Testdata.xml file being created that can then be used to load this very same data back into the database as part of the unit tests that depend upon database consistency.

###Seem like a lot of effort? Consider the Proteus Project###

If you see in this a lot of danger of repetitive code and infrastructure just to get your test fixture up and running, then consider also investigating NDbUnit's sister project, Proteus, which provides for (among other things) a base class from which you can inherit your NDbUnit-dependent fixtures that provides a series of convenience methods that greatly simplify interacting with the NDbUnit library.

Using the Proteus Library, your test fixtures can look as simple as this...

[TestFixture]
public class Tests : Proteus.Utility.UnitTest.DatabaseUnitTestBase
{
    [SetUp]
    public void _Setup()
    {
        DatabaseSetUp();
        //rest of your setup logic here
    }

    [TearDown]
    public void _TearDown()
    {
        DatabaseTearDown();
        //rest of your teardown logic here
    }

    [TestFixtureSetUp]
    public void _TestFixtureSetup()
    {
        DatabaseFixtureSetUp();
        //rest of your fixture setup logic here
    }

    [TestFixtureTearDown]
    public void _TestFixtureTearDown()
    {
        DatabaseFixtureTearDown();
        //rest of your fixture setup logic here
    }

    [Test]
    public void Test()
    {

        //your test here

    }

}

...and the base class handles the NDbUnit setup 'goo' for you!