If you use DataProviderEnterprise as the provider class are you required to use System.Transactions.TransactionScope when setting up a transaction block? If you don't and you use esTransactionScope, EntitySpaces seems to ignore it.

We're on EntitySpaces 1.5.3 using Sql Server 2005 and Asp.Net 2.0.

We have a mix of legacy objects and functions that do not use EntitySpaces and some that do. When using them together, I was under the impression that we would need EntitySpaces to use the System.Transactions.TransactionScope class rather than the built in transaction scope class so that all of the objects would be saved under one transaction. I thought that by setting the providerClass in web.config to DataProviderEnterprise that EntitySpaces would use the TransactionScope class under the hood rather than the one that mimics the TransactionScope class. A test below shows that's not quite the case.

Test 1: providerClass="DataProviderEnterprise" - save is not rolled back

using (esTransactionScope scope = new esTransactionScope())

{

   ...

   esItem.Save(); // EntitySpace object

   // esItem save should be rolled back

   throw new Exception("Test Transaction Exception");

}

What happens here is that the esItem object is saved to the database. EntitySpaces seems to ignore the fact that there is a transaction scope. Is this a coding or configuration error? Should the Save function throw an exception itself because the wrong transaction scope type is used? If not, then merely setting the providerClass to "DataProviderEnterprise" and using esTransactionScope will have you thinking that you are saving data as part of a transaction when you actually aren't. I guess the same situation would apply if the providerClass was set to "DataProvider" and you used System.Transactions.TransactionScope. I apologize if this issue has been addressed in a later version.

I did some other tests and saw that the transaction was rolled back correctly if "DataProviderEnterprise" and TransactionScope are used together or "DataProvider" and esTransactionScope are used together.

Test 2: providerClass="DataProviderEnterprise" - save is rolled back

using (TransactionScope scope = new TransactionScope())

{

   ...

   esItem.Save(); // EntitySpace object

   // esItem save should be rolled back

   throw new Exception("Test Transaction Exception");

}

Test 3: providerClass="DataProvider" - save is rolled back

using (esTransactionScope scope = new esTransactionScope())

{

   ...

   esItem.Save(); // EntitySpace object

   // esItem save should be rolled back

   throw new Exception("Test Transaction Exception");

}

I think I have my answer based on the tests above but I'm not sure if Test 1 is a bug or whether it "works as designed". Perhaps an enhancement can be made to catch a mismatch of the providerClass and the transaction scope class.

Thanks for any advice or info you can give,

dadams

Well, first let me say that 1.5.3 (as you know) is quite old? I'm not sure why you never upgraded, there have been fixes in esTransactionScope, a work around due to a bug in the Microsoft SqlTransaction class and other fixes and enhancements. However, that is not the issue here, let's take a look at your method:

// Test 1: providerClass="DataProviderEnterprise" - save is not rolled back
using (esTransactionScope scope = new esTransactionScope())
{
   esItem.Save(); // EntitySpace object

   // esItem save should be rolled back
   throw new Exception("Test Transaction Exception");
}

 
Take a look at our Save() in the Enterprise version:

public static DataTable SaveStoredProc(esDataRequest request)
{
    bool needToInsert = false;
    bool needToUpdate = false;
    bool needToDelete = false;

    if (Shared.HasUpdates(request.Table.Rows, out needToInsert, out needToUpdate, out needToDelete))
    {
        SqlDataAdapter da = new SqlDataAdapter();

        if (needToInsert) da.InsertCommand = Shared.BuildStoredProcInsertCommand(request);
        if (needToUpdate) da.UpdateCommand = Shared.BuildStoredProcUpdateCommand(request);
        if (needToDelete) da.DeleteCommand = Shared.BuildStoredProcDeleteCommand(request);

        using (TransactionScope scope = new TransactionScope())
        {
            using (SqlConnection conn = new SqlConnection(request.ConnectionString))
            {
                if (needToInsert) da.InsertCommand.Connection = conn;
                if (needToUpdate) da.UpdateCommand.Connection = conn;
                if (needToDelete) da.DeleteCommand.Connection = conn;

                da.Update(request.Table);
            }
            scope.Complete();
        }
    }
} 

When you call esItem.Save() the save method is going to use the Microsoft TransactionScope since you have declared "DataProviderEnterprise" in your config file. So, you are trying to govern a Microsoft TransactionScope with our esTransactionScope, this is not possible. The Microsoft TransactionScope operates at a higher level than does our esTransactionScope. Our esTransactionScope is ADO.NET connection based whereas the Microsoft Transaction scope sits up high on it's thrown watching everything that goes on. If you were to access two different servers the TransactionScope mechanism would enlist the DTC and promote your lightweight transaction to a fully distributed transaction. All that is to say that there's just no way for an ADO.NET SqlTransaction (which is what esTransactionScope uses) to govern the higher level Microsoft TransactionScope.

I hope this helps, feel free to follow up.

Hi Mike,

That’s interesting that the esItem still used the TransactionScope class even though it was wrapped by the esTransactionScope using clause. At the time, I didn’t have the DTC service up and running. When I switched to using the TransactionScope class, I got an exception about the MSDTC not being available on the server and I also had to update my web.config to include the System.Transactions dll. As soon as I made those changes and started the service, it worked fine. It wasn’t a distributed transaction so I wonder what esItem used to save the data before I started the service and made the web.config change. I'm more curious than anything else.

Thanks for the explanation about what goes on beneath the covers. I wonder if there is a way to detect this situation (throw an exception) since all it takes is a configuration change (using DataProviderEnterprise) and if you still have code that is using esTransactionScope, you’ve lost the ability to save the data as part of a transaction. Just something to think about to keep clueless people like me from getting into trouble! :)

You may also want to consider adding to the example in the esTransactionScope class help topic under the EntitySpaces.Interfaces Namespace section. Adding an example showing the code and config settings for using TransactionScope may make things clearer. I originally thought all I had to do was change the configuration setting, use esTransactionScope as I had before, and I'd be good to go. The help topic does say to use the DataProviderEnterprise setting with the TransactionScope but an example may help to drive that point home.

It certainly is in our plans to upgrade. We just haven't had the time and things keep getting added to the top of the list. I'm sure you know how that goes. Hopefully we can someday.

Thanks for your help,

dadams