Handling transactions for stored procedure calls

3 posts, 1 answers
  1. Marco
    Marco avatar
    6 posts
    Member since:
    Aug 2011

    Posted 15 Apr 2014 Link to this post

    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.
  2. Answer
    Ralph Waldenmaier
    Admin
    Ralph Waldenmaier avatar
    202 posts

    Posted 16 Apr 2014 Link to this post

    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
     
    OpenAccess ORM is now Telerik Data Access. For more information on the new names, please, check out the Telerik Product Map.
     
  3. DevCraft banner
  4. Marco
    Marco avatar
    6 posts
    Member since:
    Aug 2011

    Posted 16 Apr 2014 in reply to Ralph Waldenmaier Link to this post

    Thanks Ralph!
Back to Top