NUint測試數據庫操作的testcase時，一個問題是測試結束後如何恢復數據庫數據，網上看到的一個方法是利用事務回滾
以下轉載
In this article I’ll show what problems we encounter today when we perform unit tests against database related component, and the various ways used to solve these problems. I’ll also show a new method to solve these problems, which is far easIEr and simpler than any of the others. At the end of this article you will have the tools necessary to do simple and easy unit tests against database related components without the need to write messy cleanup code after your tests.
Update:
You will find more information on issues and solutions regarding this approach in the following article: Issues and solutions for database unit testing with enterprise services. You may also find XTUnit helpful in this regard.
Test Driven Development makes life easier. Unit tests are the lifeblood of a good Test Driven Development approach (and thus, are also the lifeblood of most of the agile methodologIEs out there such as XP, Scrum and others). Therefore it stands to reason that one should strive to create unit tests with the least amount of work and maintenance, which will in turn allow the developer to focus on the business problem at hand and the various design and algorithms needed to create a working product/component.
For the past couple of years I’ve been struggling with Unit tests, That is, I’ve used them in major production projects and have seen the good and the bad that can come from a fully test Driven approach. I’ll let the “good” part be taken care of by the numerous other articles out there that talk about the benefits you get from Unit Tests and TDD, and will focus on one of the most problematic parts of Unit tests in a real world project – testing against the database.
Testing Data Access layer components (DALs for short) is problematic in a number of ways. The number one reason f is this:
Testing the various CRUD(Create, RetrIEve, Update, Delete) Operations on a single class can result in a massive amount of “garbage” data residing on the test database (You are using a test database, aren’t you?). This creates several problems:
1. Problem: Garbage Data
Your database will eventually be full of junk data which you’ll need to get rid of sooner or later>
2. Problem: Affecting other tests
Worst, you may break one of the golden rules of unit testing: No test should be dependent on another test to perform correctly. That is, you should be able to run your tests in any order you choose or maybe just one of them at a time, and they should all still work. That means that every test needs to start with a known state on which it will act. For DALs this usually means a known state of records in the database.
But guess what? When you test CRUD Operations you’re actually changing the state of the database, so the next test that will be run that might depend on a specific record being there might fail because you just deleted that record in your current test. Anything can happen.
3. Problem: starting tests from a known state
This is the other side of the previous problem: My test needs to have something in the database in order to perform correctly. For example – it needs a specific “category” entity in the Category tables in order to test inserting a child “Product” into the Products table. I need to make sure I insert these values into the database before I perform the test, but I also want to make sure that data is no longer there after I finish my test.
So how do you deal with all this garbage data that’s making your database and tests unstable? Obviously, you need to make sure that before and after each test you leave the database in the same state that you got it in. That is – you need to “undo” your CRUD Operations after testing them. Here are some of the most prevalent methods used to achIEve this:
This is the most obvious and in simple cases one of the simplest to achIEve. To do this, after each test (at the end of it) you execute the opposite actions than the ones you took in the test. For example, if you inserted a record in the test – you delete it at the end.
Some problems and questions that arise from this approach:
o If I inserted the record into the Database using my objects’ Insert() method which I was testing – should I remove it from the database using the object’s Delete() method (which might have not been tested yet or may not even exist yet!) or should I do it against the database using direct calling of stored procedures or direct ADO.Net classes?
o If I choose to remove the records I inserted directly from the DB – this involves some serious amounts of extra code, residing in my test! Andthat code can have bugs as well.
o If I choose to use my object to delete and Insert as well - what do I test first? (Chicken and the egg kind of question..)
o I can also choose to remove all the garbage data at the end of all the tests run (TestFixureTearDown method). But then I’d need lots of data and custom code and …. It’s just complicated.
o I can ignore all my garbage data and restore my database from backup before running all the unit tests. This might solve the problem partially but may still leave a few unit tests that mess with other test’s data. A big no-no.
In his very interesting and insightful book, “Test Driven Development with Microsoft.Net”, James Newkirk addresses this same problem with a seemingly simply solution: Execute all your actions inside a transaction and rollback that transaction when you’re done with each test. This is a great solution and can definitely be implemented, but also has its problems:
The previous two approaches are the most used, and if you’ve used either of them you’d know that they are still hard to achIEve with massive amount of custom work just to make the database data more reliable.
When I was reading Jame’s book, in the chapter that discusses implementing transactions, I came across this little sentence (abbreviated):
“We want to achIEve the same functionality as “RequiresTransaction” for each tested component, just like in COM+”
This sentence triggered an interesting thought : why work so hard? Why not let COM+(Or, “Enterprise Services” in .Net) do the work of coordinating the transactions for us? Thus – I came up with another way of implementing database rollback functionality :
What we want is to be able to achIEve the transaction functionality without all the overhead of writing our own custom Transaction manager class as prescribed in James’ book. How do we do that? With COM+ Enterprise services. Here is the technical gist of the method:
Our simple efforts start with this simple base class
using System;
using NUnit.Framework;
using System.EnterpriseServices;
namespace TransactionTesting
{
[TestFixture]
[Transaction(TransactionOption.Required)]
public class DatabaseFixture:ServicedComponent
{
[TearDown]
public void TransactionTearDown()
{
if(ContextUtil.IsInTransaction)
{
ContextUtil.SetAbort();
}
}
}
}
Here are some important pointers about this class:
for that end :
The second attribute specifIEs that whenever this class is instantiated or invoked, COM+ will automatically create a transaction context for it to run under, or enlist it into an existing transaction context.
1. Before each test a transaction context is opened (or enlisted into an existing transaction)
2. The test is performed
3. TearDown is called and the transaction is rolled back thus
We get the following benefits from this approach:
1. it is dead simple and easy to implement
2. You need no code whatsoever to roll back any database changes your components have made throughout the test. They will be rolled back automatically.
3. You do not need to manage transaction by yourself.
We’re still not finished but we’re awfully close to the finish line.
All serviced components need to have strong names. This one is no exception.
[assembly: AssemblyKeyFile(@"..\..\..\test.snk")]
Notice that I’ve put here a relative file path. This is the file I generated from sn.exe and it is located in the same directory as my .sln file. That way I can put this same attribute on all the other projects that need it as well.
This step is important because by default when the attribute is at its default state Visual Stuido.Net will automatically increment the assembly version with each build. That’s a bad practice to have on any project, but when you’re dealing with a COM+ component (A ServicedComponent is just that) you need to realize that for every separate version of the test assembly that you build, you will have created a different COM+ component in the COM+ catalogue of the testing Machine. SO – after you’ve run the tests 50 times a day your COM+ catalog might turn out to look pretty scary and we don’t want that. Setting the version to a fixed version saves us this little headache.
That’s basically it. You’re done.
Here is a simple example of a test fixture that derives from this class to achIEve a perfectly transparent data rollback after each test. This class tests a simple class that does category insert and delete against the Northwind database:
using System;
using NUnit.Framework;
using System.Data;
using System.Data.SqlClIEnt;
using Microsoft.ApplicationBlocks.Data;
using NorthwindDAL;
namespace TransactionTesting
{
public class CategoryTests:DatabaseFixture
{
string CONN = @"Server=localhost;Database=Northwind;Trusted_Connection=True";
[Test]
public void Insert()
{
CategoryManager mgr = new CategoryManager();
int newID = mgr.Insert("Test category");
VerifyRowExists(CONN, newID,true); }
[Test]
public void Delete()
{
CategoryManager mgr = new CategoryManager();
int newID = mgr.Insert("Test category");
bool result = mgr.Delete(newID);
Assert.IsTrue(result);
VerifyRowExists(CONN, newID,false);
}
private void VerifyRowExists(string connectionString, int existingRowID,bool shouldExist)
{
string SELECT = "Select * from CategorIEs where categoryID=" + existingRowID.ToString();
SqlDataReader dr = SqlHelper.ExecuteReader(connectionString,CommandType.Text,SELECT);
Assert.AreEqual(shouldExist,dr.HasRows);
dr.Close();
}
}
}
As you can see there is no clean up code anywhere. That’s how simple this technique really is.
Just so you can see there are no tricks up my sleeve – here is the CategoryManager tested class (in VB.Net – just for the heck of it):
Public Class CategoryManager
Private CONN As String = "Server=localhost;Database=Northwind;Trusted_Connection=True"
Public Function Insert(ByVal CategoryName As String) As Integer
Dim sql As String = "Insert into categorIEs (categoryName) values('" + CategoryName + "');Select scope_identity()"
Dim result As Integer = NorthwindDAL.Data.SqlHelper.ExecuteScalar(CONN, CommandType.Text, sql)
Return result
End Function
Public Function Delete(ByVal id As Integer) As Boolean
Dim sql As String = "delete from categorIEs where categoryID=" + id.ToString()
Dim result As Integer = _
NorthwindDAL.Data.SqlHelper.ExecuteNonQuery(CONN, CommandType.Text, sql)
Return (result = 1)
End Function
End Class
No tricks, no hidden cards. The database actually gets updated, only the updates are rolled back.
In this article I’ve show a way to define simpler, more maintainable unit tests against the database Access layer components using the powerful and easy to use services of COM+. Hopefully using this technique database unit testing can become a much less painful experIEnce, and thus allow more developers do more tests on their database code – and find those bugs as soon as possible.