Handling transactions for stored procedure calls

[Marco]

Hi there!

Calling a stored procedure in a transaction always leads to a "transaction already active" exception when starting a new one after commit:

01.namespace SpTest

02.{

03.    class Program

04.    {

05.        static void Main(string[] args)

06.        {

07.            const string connectionId = "MyDbConnection";

08.            var connectionString = ConfigurationManager.ConnectionStrings[connectionId].ConnectionString;

09. 

10.            using (var db = Database.Get(connectionString, new BackendConfiguration() { Backend = "oracle" }, new MetadataContainer()))

11.            {

12.                using (var objectScope = db.GetObjectScope() as IExtendedObjectScope)

13.                {

14.                    var transaction = objectScope.Transaction;

15.                    transaction.Begin();

16. 

17.                    CallTestStoredProcedure(objectScope);

18. 

19.                    transaction.Commit();

20.                    transaction.Begin(); // this line fails!


21.                }

22.            }

23.        }

24. 

25.        private static void CallTestStoredProcedure(IExtendedObjectScope scope)

26.        {

27.            bool isNew;

28.            using (var command = scope.GetConnection(out isNew).CreateCommand())

29.            {

30.                command.CommandType = CommandType.StoredProcedure;

31.                command.CommandText = "SP_TEST";

32.                command.Parameters.Add(new[] { new OAParameter("ID", null) });

33.                command.ExecuteNonQuery();

34.            }

35.        }

36.    }

37.}

How can I get rid of this without checking for transaction.IsActive?

Thx.

[Ralph Waldenmaier]

Hello Marco,
Thank you for providing your code snippets.
I was able to reproduce the reported behaviour. The problem here is the usage of the connection in the CallTestStoredProcedure method. The connection is obtained from the scope which means that you are now responsible for this connection.
Please see the following example how this could look like:

private static void CallTestStoredProcedure(IExtendedObjectScope scope)
{
    bool isNew;
    using (var connection = scope.GetConnection(out isNew))
    {
        using (var command = connection.CreateCommand())
        {
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "SP_TEST";
            command.Parameters.Add(new[] { new OAParameter("ID", null) });
            command.ExecuteNonQuery();
        }
    }
}

Using this pattern I was no longer getting the reported exception.

I hope this information is helpful for you.
Do come back in case you need further assistance.

Regards,
Ralph Waldenmaier
Telerik

 

 

[Marco]

Thanks Ralph!