none
Transactions across OleDBConnection and TransactionScope RRS feed

  • Question

  • I am not sure if there are any article explaining how the two API works together.


    For example, a low level DB library uses OleDBTransactions, such as calling dbConnection.BeginTransaction(), and when application codes uses TransactionScope, which calls the DB library functions, would there be any issue or conflict?

    Monday, February 9, 2009 4:01 PM

All replies

  •  You can call BeginTransaction inside of TransactionScope, but in this case it would be local transaction for that connection. If you Commit that transaction inside of Transaction scope, it will work, but in a case if you need to roll everything back it will stay committed.
    Val Mazur (MVP) http://www.xporttools.net
    Tuesday, February 10, 2009 11:01 AM
    Moderator
  • VMazur said:

     You can call BeginTransaction inside of TransactionScope, but in this case it would be local transaction for that connection. If you Commit that transaction inside of Transaction scope, it will work, but in a case if you need to roll everything back it will stay committed.


    Val Mazur (MVP) http://www.xporttools.net



     I have done some simple prototyping and it seems that everything works fine, let's say for the following:

    using (TransactionScope transcope = new TransactionScope())
    {
                    using (SqlConnection Conn = new SqlConnection(ConnStr))
                    {

                        Conn.Open();

                        try
                        {
                            SqlCommand Cmd1b = new SqlCommand(sqlStr1b, Conn);
                            Cmd1b.ExecuteNonQuery();

                            SqlCommand Cmd2b = new SqlCommand(sqlStr2b, Conn);
                            Cmd2b.ExecuteNonQuery();

                            DoSqlTransaction(); //Rollback inside this can throw exception and by NOT calling the transcope.Complete() I can have everything "rollback"

                            transcope.Complete();
                        }
                        catch (Exception ex)
                        {
                           
                            lblMessage.Text = ex.Message;
                        }

                        Conn.Close();
                    }
     }


    Friday, February 13, 2009 10:22 AM
  • From the posted code it looks like you are using same database connection. In this case using local transaction is enough and there is no need to involve TransactionScope functionality as it could add additional overhead.
    Val Mazur (MVP) http://www.xporttools.net
    Friday, February 13, 2009 11:11 AM
    Moderator
  • I was trying to test how TransactionScope and SqlTransaction works together, can I use a local transaction to test?
    Monday, February 16, 2009 7:41 AM
  • Yes, you can use it.
    Val Mazur (MVP) http://www.xporttools.net
    Wednesday, February 18, 2009 11:10 AM
    Moderator