[Transactional(TransactionalTypes.TransactionScope)]

DazedAndConfused posted on Monday, December 01, 2008

We have been struggling to get this working: Should the below code work?

The first call

myTestA.Save() should work

myTestB.Save causes a truncation error on the table, and my thoughts are that A should roll back.

This is a SQL 2005 back end and the table and proc are:

Create TABLE [dbo].[TestTran](

[TestTranID] [int] IDENTITY(1,1) NOT NULL,

[Name] [varchar](5) COLLATE Latin1_General_CI_AS NOT NULL

)

 

Create Proc dbo.InsertTestTran @myvalue int

as

if @myvalue = 1

begin

Insert [TestTran] (Name)

values( 'Test1111111111111111')

END

else

Insert [TestTran] (Name)

values( 'Test1')

This is the c# code:

using System;

using System.Collections.Generic;

using System.Text;

using Csla;

using Csla.Data;

using System.Data.SqlClient;

using System.Data;

namespace ADFTest

{

class Program

{

private static SqlConnection _Connection;

static void Main(string[] args)

{

try

{

string mystring = getDBConnection

SqlConnection myConn = new SqlConnection(mystring);

myConn.Open();

_Connection = myConn;

TestTransaction();

}

catch

{ }

}

 

 

[Transactional(TransactionalTypes.TransactionScope)]

static void TestTransaction()

{

ClassA myTestA = new ClassA();

ClassB myTestB = new ClassB();

myTestA.Save();

myTestB.Save();

}

public static SqlConnection GetDBConnection()

{

return _Connection;

}

}

 

 

class ClassA : BusinessBase<ClassA>

{

public static ClassA NewObject()

{

return DataPortal.Create<ClassA>();

}

public override object GetIdValue()

{

return 1;

}

[Transactional(TransactionalTypes.TransactionScope)]

protected override void DataPortal_Insert()

{

SqlCommand sqlCmd2 = new SqlCommand("dbo.InsertTestTran", Program.GetDBConnection());

sqlCmd2.CommandType = CommandType.StoredProcedure;

SqlParameter myparam2 = new SqlParameter("@myvalue", '0');

sqlCmd2.Parameters.Add(myparam2);

sqlCmd2.ExecuteNonQuery();

}

}

class ClassB : BusinessBase<ClassB>

{

public static ClassB NewObject()

{

return DataPortal.Create<ClassB>();

}

public override object GetIdValue()

{

return 1;

}

[Transactional(TransactionalTypes.TransactionScope)]

protected override void DataPortal_Insert()

{

// This will cause a Truncation error in the database:

SqlCommand sqlCmd2 = new SqlCommand("dbo.InsertTestTran", Program.GetDBConnection());

sqlCmd2.CommandType = CommandType.StoredProcedure;

SqlParameter myparam2 = new SqlParameter("@myvalue", '1');

sqlCmd2.Parameters.Add(myparam2);

sqlCmd2.ExecuteNonQuery();

}

}

}

 

 

RockfordLhotka replied on Monday, December 01, 2008

The Transactional attribute is only useful when applied to a DataPortal_XYZ method of a root object. It has absolutely no meaning on any other method.

So you are using it on your static factory method, where it is entirely ignored. And you use it on both DP_XYZ methods - but they are treated as separate data portal calls and so run in separate transactional contexts.

You need to have ONE root object that is invoked through the data portal, and then that object does the insert/update of the other (child) object. Put the Transactional attribute on the DP_XYZ methods of that one root object and you should get the desired result.

whelzer replied on Monday, December 01, 2008

Apologies for the hijack..seems like a good place to ask:

What happens when using (Transactional(TransactionalTypes.TransactionScope) from say myBO_DP_Insert and its calls myBO2_DP_Insert with also has the Transactional attribute.

Is there only one transaction?  Is the "second" transaction nested?  Is there a way to use the old MTS style RequiresTransaction etc.

We're using DTC and the above methods - all was going very well until we came across a use case where one BO needs to create, populate and save a different BO.  99.99% of the time these objects are created from the UI.  Is there a standard way of doing this?

Any info or links much appreciated.

RockfordLhotka replied on Monday, December 01, 2008