I was curious how to maintain my database at a consistent state while unit testing (technically integration testing already – but since it uses unit testing framework as they say the lines are terminologies are getting blurry) so I looked around for "database unit testing" write ups.
It turns out that [more]mbUnit has [Rollback] which makes use of COM born out of Roy Osherove's article on Simplified Database Unit testing using Enterprise Services (and a number of related articles).
But I'm using NUnit (at least for now until I have checked out mbUnit) and again although there are a number of articles on extending NUnit (using Roy's approaches among others), it's a little messy to extend, create new DLLs and all. So seeing that the approach made use of Enterprise Service, it made me wonder whether it's possible to use .NET 2.0 TransactionScope (and possibly other upcoming enhancements to System.Transactions).
I tried to come up with a simple test/project to see if it would work and it did the job so posting it. (also adding a link to compressed sample project at the bottom)
using System.Data.SqlClient;
using System.Transactions;
using NUnit.Framework;
namespace TransactionScopeNUnitTest
{
[TestFixture]
public class TestClass
{
private const string connString = @"Server=.sql2005;database=test;uid=<yourUID>;pwd=<yourPassword>";
private TransactionScope scope;
[TestFixtureSetUp]
public void Setup()
{
scope = new TransactionScope(TransactionScopeOption.RequiresNew);
}
[TestFixtureTearDown]
public void TearDown()
{
if (scope != null)
{
scope.Dispose();
}
}
[Test]
public void Delete()
{
// test runner could run the tests in any order
// so we insert the a known entry first
Insert();
using (SqlConnection conn = new SqlConnection(connString))
{
using (SqlCommand cmd = new SqlCommand(
"DELETE FROM Table1 WHERE Col1 = 1"))
{
cmd.Connection = conn;
conn.Open();
cmd.ExecuteNonQuery();
Verify();
}
}
}
[Test]
public void Insert()
{
using (SqlConnection conn = new SqlConnection(connString))
{
using (SqlCommand cmd = new SqlCommand(
"INSERT INTO Table1 (Col1, Col2) VALUES (2, '2')"))
{
cmd.Connection = conn;
conn.Open();
int retval = cmd.ExecuteNonQuery();
Assert.Greater(retval, 0);
}
}
}
[Test]
public void Verify()
{
using (SqlConnection conn = new SqlConnection(connString))
{
using (SqlCommand cmd = new SqlCommand(
"SELECT * FROM Table1 WHERE Col1 = 2"))
{
cmd.Connection = conn;
conn.Open();
SqlDataReader rdr = cmd.ExecuteReader();
Assert.IsTrue(rdr.HasRows, "Inserted Row Not Found");
}
}
}
}
}
Here's the simple SQL script for the test 'Table1' too
CREATE TABLE [dbo].[Table1](
[Col1] [int] NOT NULL,
[Col2] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
NOTE: When I ran this, despite using just one connection string, it required "Distributed Transaction Coordinator" service to be running on the machine (Start > Run > type "services.msc"). I used to think that it is only needed if the transaction is escalated to a distributed transaction but more on that next time.
Also, System.Transactions is not added by default to new projects (at least for class library template) so you would need to explicitly add a reference to it.
This is really not a discovery so to speak and I'm sure there are other articles out there on this as it's only brought about by the introduction of TransactionScope as an alternative to previous approaches but for those running on .NET 2.0 and want a simpler solution than extensions or more complicated stuff, I think this would suffice.
Download sample – TransopeNUnitTest.zip (59.95 kb)
Related Links:
TransactionScope's advantages over ADO.NET and Enterprise Services