I'm trying to implement something here that seems like it aught to work, but 
is not.  I've done some extensive searching around the web, and have not 
found anything directly relavant.  I am trying to solve two basic issues here:

1 - I want to use System.Transactions.TransactionScope to manage single and 
distributed Transactions;

2 - Transactions may be nested, and some nested Transactions may (or may not 
be) using the same SQL Connection.

The idea here is to support a "Dynamic connection string" solution, where 
the data for a given object lies on an undetermined server.  This works by 
having the DAO function of my object get its SQL Connection from a static 
"Connection Manager", that takes a type name and looks it up in a database 
table, and dtermines the Connection String to use to create the connection to 
that object's data, like this:

public class MyClass
{
   public static void Insert( MyClass obj)
   {
      // Get an IDbConnection
      IDbConnection cn = ConnectionManager.GetSqlConnection(obj.GetType());
      ... // Do the DAO work
   }

So it seems then I could do something like this:

using(TransactionScope ts = new TransactionScope())
{
   MyClass obj = new MyClass();
   ... // Populate data members

   MyClass.Insert( obj);
   ts.Complete();
}

Nothing apparently wrong here, but I've run into some problems, the most 
distressing of which is the fact that if you comment out the ts.Complete() 
line, the transaction still commits!  Under the covers, here is what goes on:

1 - ConnectionManager gets a request for an IDbConnection for a specific 
Data Type.  It will look this up in a dataase we'll call DevSql2.  So, it 
creates a connection to DevSql2 to get that connection string.  It does this 
inside of a USING block with a new TransactionScope with it's creation option 
set to "Suppressed", so that the DB call to look up the Connection String is 
not involved in a Transaction.

When the Conenction String is retreived, a new IDbConnection is created for 
that connection string, and returend to the caller (The MyClass::Insert() 
finction, in this case), where it's used.

Now, it just so happens that in this particular case, the Connection Strings 
table and the table I'm inserting into both live on the same database, and 
therefore have the same Conenction String.  Either TransactionScope or the 
DTC doesn't seem to like this, as it throws up an exception when I try to 
open the 2nd connection to DevSql2 (throwing back an error saying that the 
Transaction has already been eithe rimplicitly or explicitly committed or 
aborted.)  I worked around this issue by recycling the connections.  That is, 
my Connection Manager doesn't actually instantiate a new Connection if one is 
already open for that Connection String.

The code runs with no errors, but if I try to make an Insert not commit (by 
failing to call Complete() inisde the TransactionScope), the thing still 
commits, and I don't understand why.  Here's an oversimplified example of 
what I'm doing:

void main()
{
   // Create a root TransactionScope
   using( TransactionScope tr1 = new TransactionScope())
   {
      // Get an existing record
      MyClass obj = MyClass.GetById( 1);
      Console.WriteLine( obj.ToString());

      // Create a nested Transaction
      using( TransactionScope ts2 = new TransactionScope())
      {
         // Create and save a new object
         MyClass obj2 = new MyClass();
         int id = MyClass.Insert(obj2);
         Console.WriteLine( "Inserted new record {0}", id);

         // Should be able to retrieve the uncommitted record within the 
Transaction
         obj = MyClass.GetById( id);
         Console.WriteLine( obj.ToString());

         // Don't call Complete() - Implicit failure
      }

      // Should NOT be able to retrieve the object now...
      obj = MyClass.GetById( obj.ID);
      Console.WriteLine( obj.ToString());  // Sure enough, it's there!?!?!

      ts.Complete();  // Throws an exception indicating that the Transaction 
was aborted.
   }
}

The implicit transaction (tr2) should have been rolled back when it's USING 
block ended.  It apparently was, as the Complete() call on ts fails.  Isn't 
there a way to inspect ts prior to calling Complete() to see if it will 
complete?  It seems kind of dirty to only find out when an exception gets 
thrown.

When I query the database after runnign this, sure enough, that reocrd 
created in ts2 is there.  It seems like it should not be.  How do I get that 
to roll back?

Any help on this would be greatly appreciated.  FYI, I'm trying to 
incorporate busienss objects with DAO into the CSLA framework.  There are 
some articles otu there on CSLA and some issues with MSDTC on SQL Server 
2000, but those are stricly related to performance.  Godo performance is 
nice, but at the moment, I'd just be glad to ahve the thing WORK, period.  
Thanks!

Jim