NSqlUnit is an NUnit plugin that enables the testing of database objects and data via NUnit. Using the NUnit 2.x metaphor of .Net attributes, NSqlUnit extends NUnit functionality into the database realm. The user (test-writer) is able to execute SQL script files, procedures, functions, and arbitrary SQL commands, optionally returning data back to the test for assertions or comparisons. The plugin uses ADO.Net 2.0's provider-independent programming model which (in theory) will allow the component to be used against and ADO.Net-compliant back end.

• .Net 2.0
• NUnit 2.4 (2.3.6293.0 or greater)

1. Install NUnit 2.4
2. Drop the NSqlUnit DLL into <NUnit 2.4 Install Directory>/bin/addins
3. Create your tests (see below)
4. Create more tests

NSqlUnit is an NUnit plugin consisting of the extension point itself (which registers the plugin with NUnit), a Fixture Builder (a.k.a. a Suite Builder) that creates the fixture, a set of Attributes that direct the fixture's behavoir, the Fixture and the Test Case. For those interested in the dirty details, the most important classes are NSqlUnit.SqlFixtureBuilder and NSqlUnit.SqlTestCase. You should probably start there.

For more complex examples, see the NSqlUnit.MockTests source code.

//The NSqlUnitTestFixture attribute indicates the specifice provider (argument #1) and the connection string (argument #2)
[NSqlUnitTestFixture(@"System.Data.SqlClient", @"Data Source=localhost\sqlexpress;Initial Catalog=pubs;Integrated Security=True")]
public class FooTest
{
	//Behaves normally, as do the other NUnit attributes
	[SetUp]
	public void SetUp()
	{
	}

	//Will be used to capture/retain query results
	private DataSet _results;
	public DataSet Results
	{
		get
		{
			return _results;
		}
		set
		{
			_results = value;
		}
	}

	[SqlTest] //Indicates a test case to be executed (the [Test] attribute would also be OK here)
	[SqlSchema("foo.xml")] //Pre-load the data from the XML file foo.xml into the database
	[SqlExecuteString("SELECT * FROM authors", ResultsPropertyName="Results")] //Execute SELECT * FROM authors and put the results into the Results property in this class
	public void TestSelectStarFromAuthors
	{
		//Check the Results DataSet that we got what we were looking for
		Assert.NotEqual(0, Results.Tables[0].Rows.Count);
	}
}
						

Attribute	Description
NSqlUnitTestFixture	Indicates that the suite should be handled by NSqlUnit. Has two required arguments. The first is a string indicating the namespace of the provider that should be used, according to ADO.Net 2.0s provider-independent programming model. For instance, the MS SQL Server native client provider would be indicated with the string "System.Data.SqlClient". The second is the connection string that will be used to connect to the databaase.
SqlTest	Indicates a test case. In most cases, interchangeable with the normal NUnit [Test] attribute. SqlTest has one optional parameter, used to indicate if the test case should be wrapped in a transaction. By default, this is true, meaning that all changes to the database will be backed out at the conclusion of the test case. Should the user wish the transaction to be committed, the [SqlTest(false)] or [SqlTest(Rollback=false)] should be used.
SqlSchema	Indicates data to be pre-loaded into the database prior to any other interactions. The one required parameter is a string value that should indicate an XML DataSet that contains the required data. Malformed data or structure will throw an Exception; there is no run-time validation by design. The plugin will attempt to find the XML file within the Assembly as a Resource, then on the filesystem, either in the same directory as the Assembly or as a fully-qualified path on the filesystem. An Exception will be thrown if the file cannot be found. Multiple SqlSchema attributes are allowed.
SqlExecuteString	Indicates that the plugin should execute an arbitrary SQL statement (the one required parameter) against the database. As above, there is no run-time validation, so any malformed statements will throw an Exception. Multiple SqlExecuteString attributes are allowed.
SqlExecuteScript	Indicates that the plugin should execute a script against the database. The one required argument is a string indicating the location of the script file (using the same rules as SqlSchema, above). Again, malformed SQL will throw an Exception. Multiple SqlExecuteScript attributes are allowed.
SqlExecuteProcedure	Indicates that the plugin should execute a database procedure/function. The first argument is the only required; it is the name of the procedure/function, following the syntax required by the provider used by the fixture. Also, a param array is an optional variable-length argument, and should contain values that will be required by the procedure/function. The plugin will use the database to determine the parameter type(s) for purposes of executing the procedure/function, but no validation on the data types arguments will be conducted. Multiple SqlExecuteProcedure attributes are allowed.