Solved

Transaction Rollback not working using TableAdapters

Posted on 2008-09-29
2
1,851 Views
Last Modified: 2013-12-17
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
Comment
Question by:peerlesslv
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
2 Comments
 

Author Comment

by:peerlesslv
ID: 22601939
Is there anybody out there?
0
 

Accepted Solution

by:
peerlesslv earned 0 total points
ID: 22664689
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

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article shows how to use the open source plupload control to upload multiple images. The images are resized on the client side before uploading and the upload is done in chunks. Background I had to provide a way for user…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question