Simplified Database Unit testing using Enterprise Services

My blog has moved.
You can view this post at the following address:
http://www.osherove.com/blog/2004/6/19/simplified-database-unit-testing-using-enterprise-services.html
Published Saturday, June 19, 2004 7:51 PM by RoyOsherove
Filed under:

Comments

Saturday, June 19, 2004 5:02 PM by Addy Santo

# re: Simplified Database Unit testing using Enterprise Services

Very nice!
Saturday, June 19, 2004 6:41 PM by Brian Noyes

# re: Simplified Database Unit testing using Enterprise Services

This is awesome Roy! Great use of ES, very insightful idea. I am a big fan of ES and we (IDesign) recommend its use to many of our customers. Now I have a great way to show them how it can help their TDD as well (with all due credit to the originator of the idea).

Scha-weeet.
Saturday, June 19, 2004 8:36 PM by Steve

# re: Simplified Database Unit testing using Enterprise Services

Nice!
Saturday, June 19, 2004 8:58 PM by James Newkirk

# re: Simplified Database Unit testing using Enterprise Services

Excellent. Alexei and I were troubled by the TransactionManager class in the book and we thought there must be a better way. Thanks for pointing this out and for the great article.
Saturday, June 19, 2004 10:01 PM by Dave Donaldson

# re: Simplified Database Unit testing using Enterprise Services

Nice article, Roy! I'm one of those that think Enterprise Services doesn't get enough attention, and hopefully this helps bring it a little more to the forefront.
Saturday, June 19, 2004 10:28 PM by TrackBack

# Simplified Database Unit testing using Enterprise Services (by Roy Osherove)

Sunday, June 20, 2004 2:20 AM by Roy Osherove

# re: Simplified Database Unit testing using Enterprise Services

Thanks folks. I'm flattered that you like it.
I'm still trying to find holes in my theory and "bugs" in it.
Please let me know if you find any of those. I'll update the article accordingly.

Thanks,
Roy
Sunday, June 20, 2004 2:57 AM by Ido Samuelson

# re: Simplified Database Unit testing using Enterprise Services

Simple and effective

This is a great solution Roy, I wonder if that can also be used against LDAP, i'll check it up...
Sunday, June 20, 2004 3:33 AM by TrackBack

# Unit Testing in .NET - NUnit - Automating Unit Tests - Testing a Database

Sunday, June 20, 2004 4:50 AM by Ido Tandy

# re: Simplified Database Unit testing using Enterprise Services

Very Nice Roy !!
(I was just wondering about the subject the other day...)
A few questions:
1. Can i use the same method for testing my BL / WF layers ?
or is there a better test method for those layers...
2. Does it matter if my DAL is ES or not ?
3. you mentioned that "every assembly that it references must have a strong name as well", does it means my DAL needs a strong name ?
(if its ES then of course it has, but what if its not ?...)
Thanks,
Ido.
Sunday, June 20, 2004 4:51 AM by Udi Dahan - The Software Simplist

# re: Simplified Database Unit testing using Enterprise Services

Very nice indeed. I think that it's a fantastic way to do DB testing.

One small aside, though - in your categoryManager code, your building sql strings dynamically which opens up the possibility of sql injection attacks. You should be using parameterized sql - either in sprocs or in your own code.

But still, EXCELLENT article.
Sunday, June 20, 2004 4:57 AM by Amir Tuval

# re: Simplified Database Unit testing using Enterprise Services

Hi, Roi.

Great article, very insightful.

It definitely gives a good solution to most cases, however, there are a few exceptions:

1. If your ADO.NET provider does not support distributed transactions (most popular RDBMSs support this but not all of them) - for example MSACCESS or an early version of IBM DB2.

2. If the component you are testing is using Enterprise services, but with inappropriate Transaction attribute ("Disabled", "Not Supported" or "Requires New").

3. If the component is performing an action that cannot participate in a distributed transaction (such as a DDL command, that commits automatically for most RDBMSs).

For these exceptions, I don't see any other way but to resort to other methods. There's no quick fix for using Enterprise Services (unless you're willing to change your tested component).

Another thought: How about using MS DTS? You can easily create a package that generates your initial database state on a new schema, and than run the package before the beginning of the test.

Best Regards,
Amir.
Sunday, June 20, 2004 5:14 AM by Roy Osherove

# re: Simplified Database Unit testing using Enterprise Services

Ido:
1. yes
2.No, unless the DAL has explicit "Don't use transactions" attributes.
3.yes

Udi:
It was for the same of example. I wanted to show there is no behind the scenes goo going on. Obviously that measn not production ready code.

Amir: Thanks for the insightful comments.
1. agreed.
2. agreed.
you could solve that by doing #ifdefs at the component level for DEBUG and RELEASE modes I would guess.

3.Didn't consider that, good point.

The main thing is that this method solves the pain problem for *most* cases out there. to me, solving 80% of the cases is much better than nothing. Obviously this isn't a silver bullett. Nothing ever is.
Sunday, June 20, 2004 6:14 AM by senkwe

# re: Simplified Database Unit testing using Enterprise Services

Perfect timing as I'm just beginning my foray into ES :-) Two birds with one stone, good stuff Roy!
Sunday, June 20, 2004 11:51 AM by Andres Aguiar

# re: Simplified Database Unit testing using Enterprise Services

It's not exactly true that the state of the database is the same if you rollback the transaction. If you have IDENTITY fields, then, even if you did not commit the transaction, the values you got are gone, and the next time you'll get different values.

So, you need to be very careful with foreign keys that map to autonumbered primary keys, as you cannot rely in the values you get. If you need a CategoryId in a Customer table, then you need to first insert a Category, get the identity value, and use it in the Customer table.

When the database is complex (e.g., to insert a record in 'Customer' you first need to insert a record in 15 tables), this can make your unit tests very complex.

Creating the database from scratch is a better solution. In that case you can assume that the first CustomerId you'll get is 1, and hard code that '1' in your tests. Is less elegant but easier.

Sunday, June 20, 2004 1:00 PM by TrackBack

# Database

Sunday, June 20, 2004 1:01 PM by TrackBack

# Database

Sunday, June 20, 2004 1:24 PM by Roy Osherove

# re: Simplified Database Unit testing using Enterprise Services

Andres: You have a point, btu I'd still rather do those inserts rather than recreate the DB from scratch before every test, which I think would big performance consequesnces.
Sunday, June 20, 2004 7:37 PM by Andres Aguiar

# re: Simplified Database Unit testing using Enterprise Services

Yes, that's also true.
Monday, June 21, 2004 5:02 AM by TrackBack

# Enterprise Services and TDD

Monday, June 21, 2004 10:50 AM by Justin Pitts

# re: Simplified Database Unit testing using Enterprise Services

Pretty. Simple.
Thanks!


Monday, June 21, 2004 11:44 AM by Robert Hurlbut

# re: Simplified Database Unit testing using Enterprise Services

Roy -- interesting article.

We were doing this 8 months ago on our own team (though, now I wished we had written about it then! ;) ).

We found issues, though, when testing with Server Application components. Also, we found issues with the various Oracle providers (both from Microsoft and Oracle) not playing well with NUnit started and ended transactions. With Server Applications, in particular, if you need the client to call Dispose explicitly, and in this case, the client is NUnit, which is also not derived from ServicedComponent.

I will write more about these issues and problems to watch out for on my blog later on today.
Monday, June 21, 2004 11:54 AM by Roy Osherove

# re: Simplified Database Unit testing using Enterprise Services

Robert - Excellent!
Monday, June 21, 2004 12:41 PM by Dave Hallett

# re: Simplified Database Unit testing using Enterprise Services

Great article Roy. I just managed to set up something akin to James's approach in my app, and now I'll be changing it again...

As an aside, you replied to the question about the DAL saying it would have to be signed. Am I missing something here, or could you avoid this by putting the Serviced Component test base class in a strong-named assembly, and referencing it from your main test assembly/ies? It doesn't appear to me that your ServicedComponent should have to know about your DAL. Then again, I may be all wet here!

Dave
Monday, June 21, 2004 12:48 PM by TrackBack

# Enterprise Services and TDD

Monday, June 21, 2004 4:39 PM by Roy Osherove

# re: Simplified Database Unit testing using Enterprise Services

Dave: no, that would not work (even if it sounds like it).
Because your derived text fixutres inherit from a ServicedComponent, they themselves are servicedcomponents and thus the assembly they reside in should also have a strong name. Which leads down the same path outlined in the article.
But thanks for asking. I actually tried before answering because I wan't sure myself.
Monday, June 21, 2004 6:32 PM by Dave Hallett

# re: Simplified Database Unit testing using Enterprise Services

Thanks Roy. It sounded plausible, but I had a feeling it would be too easy...
Monday, June 21, 2004 7:50 PM by Robert Hurlbut

# re: Simplified Database Unit testing using Enterprise Services

Roy -- I ran a test today with some simple code against an Oracle database using your method. I tested against a Server Application component. Follow the link above to see my results.
Monday, June 21, 2004 10:34 PM by TrackBack

# Enterprise Services and TDD part 2

Monday, June 21, 2004 11:05 PM by TrackBack

# Enterprise Services and TDD part 2

Tuesday, June 22, 2004 10:03 PM by Derek Simon

# re: Simplified Database Unit testing using Enterprise Services

"Am I missing something here, or could you avoid this by putting the Serviced Component test base class in a strong-named assembly, and referencing it from your main test assembly/ies?"

Unless I'm incorrectly interpreting your question, the application of the AllowPartiallyTrustedCallersAttribute class would more than handle a situation like that.
Wednesday, June 23, 2004 3:47 AM by Omer Shwartz

# re: Simplified Database Unit testing using Enterprise Services

Hi Roy,

It realy looks like a simple & effective approach that can help for 80% of the cases.
I thought on some problem that maybe related not just to this article but to the whole TDD approach (I admit that i'm not very familiar with this approach):

The tests results are based on the ability to verify automatically the results of the actions done during the tests (you used the "VerifyRowExists" method in your example). I support the need for automatic verification of the tests and in your case there is no other way because the transaction is automatically rolled back at the end of the test, but when you are writting code to verify test results some bugs in the verification process are eventually inevitable.

In your example the verification process is quite simple and you can write a generic code that do the "VerifyRowExists" logic and test it once seperately and by that you are solving most of the problem, but sometimes verification code is much more complex (e.g. when the dal operation is not running a simple CRUD function but rather handles multiple records by running a stored procedure or by triggers invoked automatically by the database). For instance, you might design your system in a way that when you delete a category that contains some items, all of the items should not be deleted, but rather shipped to a default category.

The big problem might occur when the bug in the verification does not fail the test but rather approves a test that has a bug.

Regards,
Omer.
Wednesday, June 23, 2004 7:26 AM by Rui Rosa Mendes

# re: Simplified Database Unit testing using Enterprise Services

Hi!. Very nice solution.
I have exact database context decribed here in this post. I use this technique, it works well on nunit-console, but it didn't work on nunit-gui. Nunit-gui constantly crash and the tests do not run. I’m trying to see what is happening.


Regards,
Rui
Wednesday, June 23, 2004 11:39 AM by Philip Nelson

# re: Simplified Database Unit testing using Enterprise Services

We have tried the transaction approach for over a year, though not with this very clever technique. We are abandoning the approach for several reasons that have cropped up over time. First of all, when we have code in dependent classes that manage their own transactions, or don't participate in an existing transaction, you can introduce testing bugs that aren't coding bugs when one class can't see the work that another has done. Not good. Second, transactions slow down the tests. This didn't seem like a big deal until we had hundreds of tests and running the full test suite started taking 15-20 minutes to run. Even worse if you share a test database with other developers and locking gets added to the mix. Finally, with your technique, is there any issue with strong names on assemblies the test assemply depends on? Very nice idea though.
Wednesday, June 23, 2004 5:06 PM by Christian Romney

# re: Simplified Database Unit testing using Enterprise Services

Roy,
Good article. I experimented with ES and NUnit a while back, but not in the way you describe here. The pain I ran into was with DAL classes that had parameterized constructors, which ES doesn't like. This approach is definitely easy and solid for most code that writes to a DB that supports distrbuted transactions though I would mention somewhere that the network config has to support it. In firewalled scenarios, the dev machine's DTC may not be able to talk to the DTC on the DB server.

Cheers,
Christian
Thursday, June 24, 2004 10:01 AM by Roy Osherove

# re: Simplified Database Unit testing using Enterprise Services

Thanks for all the comments.
There's definitely going to be an "appendix" post for this article with all the newfound edge cases and special cases and problems.
That's what community is all about.
I haven't had a lot of time to continue learning this issue (crazy week!) but I'll get on it as soo as I can.
Keep that info coming.

Roy
Friday, June 25, 2004 9:41 AM by lechonsazo@hotmail.com

# re: Simplified Database Unit testing using Enterprise Services

Roy,
Have you tested this with NUnit 2.2? As soon as I inherit frmo Serviced Component, NUnit says I have an invalid fixture!

Friday, June 25, 2004 9:55 AM by Roy Osherove

# re: Simplified Database Unit testing using Enterprise Services

No, I tested with Nunit 2.1. Thanks for the info.
Tuesday, June 29, 2004 10:56 AM by Roy Osherove

# re: Simplified Database Unit testing using Enterprise Services

A quick fix:
In Nunit-Gui - open General options and uncheck the "Reload before each test run" checkbox.
this should fix most if not all the "Object disposed" errors you see.
BTW: it works great on NUnit 2,2 (same option)
Thursday, July 01, 2004 5:28 AM by Udi Dahan - The Software Simplist

# re: Simplified Database Unit testing using Enterprise Services

Roy,

Have you thought about using services without components instead? It makes the tests a little lighter weight, which is important when you have a lot of tests to run.
Thursday, July 01, 2004 12:21 PM by Roy Osherove

# re: Simplified Database Unit testing using Enterprise Services

Udi:
Yes. I'm planning an article on this issue real soon. :)
Tuesday, July 06, 2004 11:43 AM by Johnny Hall

# re: Simplified Database Unit testing using Enterprise Services

My initial testing indicates that the base class simply becomes:

[TestFixture]
public class TransactionalFixture
{
[SetUp]
public void Setup()
{
ServiceConfig config = new ServiceConfig();
config.Transaction = TransactionOption.Required;

ServiceDomain.Enter(config);
}

[TearDown]
public void TearDown()
{
ServiceDomain.Leave();
}
}

Seems ok to me, without any extended testing. Remember you'll need XP SP2...
Tuesday, July 06, 2004 6:24 PM by Johnny Hall

# re: Simplified Database Unit testing using Enterprise Services

Don't think I was thinking too clearly this afternoon! Forgive me...

Doesn't work... code should read...


[TearDown]
public void TearDown()
{
ContextUtil.SetAbort();
ServiceDomain.Leave();
}

... I think. But I get an error. I'll investigate further...
Tuesday, July 06, 2004 7:04 PM by Johnny Hall

# re: Simplified Database Unit testing using Enterprise Services

Mint. Got it working a treat. My subclass had a Setup routine which was causing the TransactionalFixture.Setup to not be called. A quick call to base.Setup(); did the trick.

Works great.

Which is good, since I keep getting Object Disposed errors with the original technique, even when I clear the checkbox in options. Mmmm.
Wednesday, July 07, 2004 7:44 AM by Dejan Grujic

# re: Simplified Database Unit testing using Enterprise Services

I'm having problems with both NUnit gui and Nunit Add-in when test class is extended from ServicedComponent. They run ok first time but silently fail afterwards until next build. However Johnny's solution with

ServiceConfig config = new ServiceConfig();
config.Transaction = TransactionOption.Required;

works perfectly so far.
Thursday, July 08, 2004 5:31 AM by Claus Jakobsen

# re: Simplified Database Unit testing using Enterprise Services

Does anybody know of a solution similar to Johnny's that works on Windows 2000?

The problem with the original solution is (as far as I can tell) that when SetAbort() is called the COM+ object (and there by also the .NET object) is destroyed. This will give the 'object disposed' error when running test number 2 in the same class.
Thursday, July 08, 2004 12:05 PM by Ilene Le

# re: Simplified Database Unit testing using Enterprise Services

I keep getting the following error when loading the assembly into the NUnit:
Error: System.Runtime.InteropServices.COMException(0x80040154): Class not registered|| at System.Runtime.InteropServices.Marshal. ThrowExceptionForHR(Int32 errorCode,IntPtr errorInfo)|| at System.EnterpriseServices.Thunk.Proxy.CoCreateObject(Type serverT...

Do you have any idea as to what could be wrong?

Thanks
Ilene
Thursday, July 08, 2004 5:55 PM by Gary Labb

# re: Simplified Database Unit testing using Enterprise Services

I get this error Unhandled exception at 0x7c4a218a in nunit-gui.exe: 0xC0000005: Access violation writing location 0x7c4c4a68. When running the test in NUnit 2.1.

Tuesday, July 13, 2004 8:29 AM by Rui Rosa Mendes

# re: Simplified Database Unit testing using Enterprise Services

Sorry about the redundancy, but after a tough fight with nunit-gui, I feel like to say - It works great using service without components!!!

Thanks, Rui

Monday, August 23, 2004 10:29 PM by TrackBack

# TDD rant and raves

TDD rant and raves
Sunday, October 10, 2004 4:47 PM by TrackBack

# Unit Testing SqlServerCe database components

Wednesday, February 23, 2005 3:57 PM by TrackBack

# NUint的使用二 [原创]

Ping Back来自：blog.csdn.net
Tuesday, June 20, 2006 3:13 PM by Yale

# re: Simplified Database Unit testing using Enterprise Services

in my code i have transactions, my test code gives me error saying: "OracleConnection does not support parallel transactions."
Wednesday, June 21, 2006 11:33 AM by Yale

# re: Simplified Database Unit testing using Enterprise Services

if i have local transactions in the code, the test gives me error: System.InvalidOperationException: OracleConnection does not support parallel transactions
Monday, July 24, 2006 12:59 PM by Colin Jack

# re: Simplified Database Unit testing using Enterprise Services

I've tried using this, specifically using the DataRollbackAttribute, but keep getting wierd MSDTC Transaction Errors: http://groups.google.co.uk/group/microsoft.public.dotnet.framework.component_services/browse_thread/thread/ae13b44beda812e5/29b5c9dfc68003fb?lnk=st&q=&rnum=1&hl=en#29b5c9dfc68003fb The only thing I didn't include in that post was that some of the errors have the code XACT_E_NOTRANSACTION in them. Anyway I found one other post on the newsgroups from someone who was trying to use the ES approach and had the same issue so I wondered if anyone else has seen it and/or solved it?
Monday, July 31, 2006 11:40 AM by Ian Blackburn

# re: Simplified Database Unit testing using Enterprise Services

Roy, There is an even simpler way of doing this using .Net 2 and TransactionScope - see http://www.bbits.co.uk/blog/archive/2006/07/31/12741.aspx for details. Cheers Ian
Wednesday, September 27, 2006 2:32 PM by Joe

# re: Simplified Database Unit testing using Enterprise Services

I like your solution. I have a knowlege hole though, when you said "you ARE using a test database...". I don't see how, in the .net environment, to get all the connections that exist in all the forms changed back & forth from the test to the production database. Is there a book or website that describes this architecture and process? Thanks
Friday, October 13, 2006 3:12 PM by Mike Brockey

# re: Simplified Database Unit testing using Enterprise Services

I have yet another solution to this very common problem. We have been able to isolate the steps for preparing a database snapshot, tracking the database modifications and then refreshing the modified tables back to their original state all using built in sql statements. We call it dataFresh and it is available now from our website as part of our TDD Developer's Toolkit, all free of charge. http://blogs.ent0.com/blogs/datafresh/default.aspx
Friday, October 13, 2006 7:43 PM by ISerializable - Roy Osherove's Blog

# DataFresh - Database rollback library for unit tests

A while ago I wrote an article about database unit testing, and the various ways you could "rollback"

Monday, January 15, 2007 8:02 AM by Fariba Ahvaz

# re: Simplified Database Unit testing using Enterprise Services

Hi,

I have a problem in using com+ for transaction.

I used to use vb.net 2003. My program's architecture is 5 layers.

The Business objects and dataobjects are com+ objects.I use tcp channel for remoting and the atributes for com+ that I use are :

<Assembly: ApplicationName("App Name")>

<Assembly: ApplicationAccessControl(True)>

<Assembly: ApplicationActivation(ActivationOption.Server)>

<ComponentAccessControl(True), _

SecurityRole("MyRole", True), _

Transaction(TransactionOption.Required)>

and for all of my functions I use try/catch .at the end of try I use ContextUtil.SetComplete and In catch I use Contextutil.SetAbort. also I Inherit a class from Exception for my own exception.

but In one function I call 3 other functions.when the error of remoting has raised or when I throw my own exception, SetAbort does not work.

I will be glad If someone help.

Thanks

Thursday, March 08, 2007 2:24 AM by Oscar Land

# re: Simplified Database Unit testing using Enterprise Services

Dear Roy,

Could you pleased upload the source code sample of this article?

I couldn't make test with this. The error :

TestFixture failed: Invalid ServicedComponent-derived classes were found in the assembly.

Thanks. Best regards.