• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1908
  • Last Modified:

Transaction Rollback not working using TableAdapters

I'm attempting to use Transactions while using TableAdapters.  I created partial classes for each of my table adapters from some code I found online.  I'd rather not use Transaction Scopes since I read it's inefficient. My code snippets contain the partial classes I created as well as my code to implement the transactions.

I'm noticing that when an exception occurs, insert statements are still committing.

Any thoughts?
****** PARTIAL CLASSES ********
    partial class PASSWORDSTableAdapter
    {
        private OracleTransaction _transaction;
        public OracleTransaction Transaction
        {
            get { return _transaction; }
            set
            {
                _transaction = value;
                _connection = _transaction.Connection;
 
                if (_adapter == null)
                    this.InitAdapter();
 
                foreach (OracleCommand command in this.CommandCollection)
                {
                    command.Transaction = _transaction;
                }
 
                this.Adapter.InsertCommand.Transaction = _transaction;
                this.Adapter.UpdateCommand.Transaction = _transaction;
                this.Adapter.DeleteCommand.Transaction = _transaction;
            }
        }
    }
 
    partial class ACCOUNT_PASSWORDSTableAdapter
    {
        private OracleTransaction _transaction;
        public OracleTransaction Transaction
        {
            get { return _transaction; }
            set
            {
                _transaction = value;
                _connection = _transaction.Connection;
 
                if (_adapter == null)
                    this.InitAdapter();
 
                foreach (OracleCommand command in this.CommandCollection)
                {
                    command.Transaction = _transaction;
                }
 
                this.Adapter.InsertCommand.Transaction = _transaction;
 
            }
        }
    }
 
****** TRANSACTION CODE ********
        PASSWORDSTableAdapter ta1 = new PASSWORDSTableAdapter();
        ACCOUNT_PASSWORDSTableAdapter ta2 = new ACCOUNT_PASSWORDSTableAdapter();
        OracleTransaction dbTransaction = null;
        OracleConnection taConn = new OracleConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
 
        try
        {
            taConn.Open();
            dbTransaction = taConn.BeginTransaction();
            ta1.Transaction = dbTransaction;
            ta2.Transaction = dbTransaction;
 
            // insert password
            ta1.Insert(0, txtPassword.Text.ToString(), ddGlobal.SelectedValue.ToString());
 
            // encrypt/store password password
            using (OracleCommand cmd2 = new OracleCommand("security.encrypt_password",
                dbTransaction.Connection, dbTransaction))
            {
                cmd2.CommandType = CommandType.StoredProcedure;
                cmd2.Parameters.Add("key_in", OracleType.VarChar).Value = txtEncryptionKey.Text.ToString();
                cmd2.Parameters.Add("p_password_id", OracleType.Number).Value = myNewPasswordID;
                cmd2.ExecuteNonQuery();
            }
 
            // store system id, account id and password id in related table
            ta2.Insert(decimal.Parse(ddSystems.SelectedValue.ToString()), decimal.Parse(ddAccounts.SelectedValue.ToString()), myNewPasswordID);
 
            dbTransaction.Commit();
        }
 
        catch (Exception ex)
        {
            dbTransaction.Rollback();
            lblExceptionDetails.Visible = true;
            lblExceptionDetails.Text = "There was a problem inserting the password. ";
        }
        finally
        {
            if (dbTransaction != null) dbTransaction.Dispose();
            ta1.Dispose();
            ta2.Dispose();
        }

Open in new window

0
peerlesslv
Asked:
peerlesslv
  • 2
1 Solution
 
peerlesslvAuthor Commented:
Is there anybody out there?
0
 
peerlesslvAuthor Commented:
It turns out it's the Stored Procedure that I call that wasn't being Rolled Back.  There was a commit statement in the Stored Procedure which obviously made it impossible to Roll it Back.  
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now