2.6 Final Adapter Template As part of my asp.net web testing, I want to generate some database rows in tables in the Page_Load event, and then rollback all database activity in the Page_Unload event.

Multithreading complicates this. Does anyone have any suggestions of a way to do this robustly using the LLBLGenPro Adapter?

My suggestion is: don't do this that way. I mean, having a pending transaction between the page lifecycle. You better:

A. Grab all the info on Page_Load, then on Page_Unload you can create and persist entities. You can use UnitOfWork objects to hold all actions and then commit such object. or B. Persist and commit on Page_Load, then delete or undo in some way such actions in Page_Unload.

What is the scenario? What are you trying to achieve?

daelmo wrote:

My suggestion is: don't do this that way. I mean, having a pending transaction between the page lifecycle. You better:

A. Grab all the info on Page_Load, then on Page_Unload you can create and persist entities. You can use UnitOfWork objects to hold all actions and then commit such object. or B. Persist and commit on Page_Load, then delete or undo in some way such actions in Page_Unload.

What is the scenario? What are you trying to achieve?

Thanks. I agree. having a long living transaction is a bad idea, esp. in a multithreaded environment, and I should have a teardown that clears the data somehow, not rely on Rollback.

We are trying to achieve a database fixture that is empty at the start of each Page_Load. It gets filled at page load time explicitly or with a test using something like WatiN to drive the browser.

The scenario is that we are testing a single control or group of related controls render properly and its events work how we want, not an application workflow at this point.

We already have the targetted database test fixture creation tool as we created it for TDD use when creating Linq to LLB for the control - it is used extensivley in TDD and has proven very effective - , so we can dynamically produce a very small database populated specifically targetted for a web page/control. We just need to consider the different ways to return the fixture to its empty state after the page has rendered. There are hundreds of controls in the entire set of applications we are building.

Currently, we backup the empty database once then restore it automatically before each test run.

I think creating a teardown method in the test fixture that sends truncate table commands would work fine too and might be easier for us, although it is an additional thing to create in the fixture that requires knowledge of what the data being created is. I didn't understand how using a UoW could help me do this.

Thanks for the comments.

If you want empty tables then simply delete all records in the teardown method.

Additionally, you can execute TRUNCATE <table> statements in a stored proc at teardown. (so no transaction, simply execute the tests as-is and in the tear-down call the proc). This is the fastest (delete from <table> logs every row in the transaction log, which can be slow). We use this method too: map the proc as an action procedure in the project, it's really 1 line of code

The proc of course has to have truncate <table> statements, one for each table, in the right order. (so start with the fk leaves, then work your way up the tree to the pk sides. )

Otis wrote:

Additionally, you can execute TRUNCATE <table> statements in a stored proc at teardown. (so no transaction, simply execute the tests as-is and in the tear-down call the proc). This is the fastest (delete from <table> logs every row in the transaction log, which can be slow). We use this method too: map the proc as an action procedure in the project, it's really 1 line of code

The proc of course has to have truncate <table> statements, one for each table, in the right order. (so start with the fk leaves, then work your way up the tree to the pk sides. )

This one is the best so far as it doesn't rely on long running transactions and is certain to get rid of all fixture created data plus any data that a test has produced.

It means creating explicit action procedures for each test fixture though, but I guess that is a small price to pay. (I wonder how fast 150 truncates on mainly empty tables would take, so I only need a global teardown procedure? I'll try it.)

To answer my own question:

EXEC sp_MSForEachTable 'TRUNCATE TABLE ?'

This is almost instantaneous and I don't need to create it specifically for each test fixture or whenever I add or delete tables from the schema.

Any db fixture will only use at most 10-20 tables if that, so this is an easy way to do this with low maintenance over a large number of test fixtures.

So I will just choose the most convenient way to do this - call the sql directly or create an action procedure to run it

Note that this skeleton database has no data and for most tests no relationships set up. I also don't have any views that reference the tables in a way that would prevent truncation.

Thanks to you all!

Good you figured it out Thanks for the feedback