Roy Oshorove has written a very good introductory article on using Enterprise Services to do Test Driven Development (using NUnit) against database classes.
Our team was doing this 8 months ago when we were in the thick of ES development (see some of my blog posts from that time).
In our case, we had great success with this, but at times, we also had inconsistencies and problems. In particular, if you are testing Server Applications, you may have objects that don't get released because the client, in this case NUnit, needs to call Dispose. Also, since NUnit itself is not referencing EnterpriseServices, you may have other inconsistencies in your transactions that are not cleaned up properly.
We also had problems with Oracle (both providers from Microsoft and Oracle) in using Enterprise Services in combination with NUnit. At times we would see a test succeed individually, but it would pass or fail inconsistently when running all tests, based on the inconsistent state of the transactions mentioned above.
I like Roy's approach, though, and I will be interested in determining if this has helped solve some of the problems we were seeing with our results.
Update: In answer to Roy's question on this post regarding how I approached this:
Today, I coded a sample in the format proposed by Roy's article. The only real difference between his code and my previous code was I placed the RollBack call in all the test classes, and not in a base class.
Testing with Oracle, I get the green light and everything works correctly, for the first time. The second time, I get the dreaded Oracle "inconsistent state" error: "ORA-03113: end-of-file on communication channel". On the third try, I get a green light again. And, it repeats the error the fourth time, and so on. Back and forth.
Update 2: A little more information. The actual exception is this: System.Runtime.InteropServices.COMException : You made a method call on a COMPlus component that has a transaction that has already aborted or in the process of aborting.
One item I noticed in Roy's code is that if you are testing a Server Application, and don't explicitly call Dispose in the test code, you will see objects instantiating in the Components Services view, but not releasing.
A slight improvement to Roy's code would be to wrap the inner code with either a “using” statement or explicitly write the try/catch/finally in order to catch exceptions and call Dispose on the object.
It still doesn't solve my Oracle problem, but just some further ideas.
