dotConnect for Oracle disable autocommitting

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
Wernervdh
Posts: 2
Joined: Tue 12 Feb 2013 14:08

dotConnect for Oracle disable autocommitting

Post by Wernervdh » Tue 12 Feb 2013 14:16

Hi,

I just learned that dotConnect for Oracle autocommits. How can I disable it?
This is how I'm using OracleTransaction. Not sure how to disable autocomitting though. Please help.

Code: Select all

private void hhrcv_update_dc_grs_carton()
        {
            OracleCommand cmd = new OracleCommand();
            cmd.Connection = conn;
            conn.Open();
            OracleTransaction trans = conn.BeginTransaction();
            cmd.CommandTimeout = 0;
            cmd.CommandText = "dc.hhrcv_update_dc_grs_carton";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("pn_dc_grs_no", OracleDbType.Number).Value =  txtDcGRSNo.Text;
            cmd.Parameters.Add("pn_pallet_id_no", OracleDbType.Number).Value = txtPalletId.Text;
            cmd.Parameters.Add("pn_carton_code", OracleDbType.VarChar).Value = txtCartonCode.Text;
            cmd.Parameters.Add("pn_company_id_no", OracleDbType.Number).Value = Companyid;
            cmd.Parameters.Add("pn_order_no", OracleDbType.Number).Value = txtOrderno.Text;
            cmd.Parameters.Add("pn_emp_id_no", OracleDbType.Number).Value = empid;
            cmd.Parameters.Add(new OracleParameter("pv_error", OracleDbType.VarChar));
            cmd.Parameters["pv_error"].Direction = ParameterDirection.Output;
            string pv_error;
            cmd.ExecuteNonQuery();
            pv_error = cmd.Parameters["pv_error"].Value.ToString();

            if (pv_error.ToString() == "")
            {
                trans.Commit();
                
            }
            else
            {
                trans.Rollback();
                MessageBox.Show("" + pv_error, "Error");
                frmReturns r = new frmReturns();
                r.Show();
                this.Hide();
            }
        }

Wernervdh
Posts: 2
Joined: Tue 12 Feb 2013 14:08

Re: dotConnect for Oracle disable autocommitting

Post by Wernervdh » Wed 13 Feb 2013 06:28

Hi,

I have tried this as well. Still no luck. Can someone please help :cry:

Code: Select all

private void hhrcv_update_dc_grs_carton()
        {
            conn.Open();
            OracleCommand cmd = new OracleCommand();
            OracleTransaction trans;
            trans = conn.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
            cmd.Transaction = trans;
            cmd.Connection = conn;
            //conn.AutoCommit = false;
            cmd.CommandTimeout = 0;
            cmd.CommandText = "dc.FAILhhrcv_update_dc_grs_carton";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("pn_dc_grs_no", OracleDbType.Number).Value = txtDcGRSNo.Text;
            cmd.Parameters.Add("pn_pallet_id_no", OracleDbType.Number).Value = txtPalletId.Text;
            cmd.Parameters.Add("pn_carton_code", OracleDbType.VarChar).Value = txtCartonCode.Text;
            cmd.Parameters.Add("pn_company_id_no", OracleDbType.Number).Value = Companyid;
            cmd.Parameters.Add("pn_order_no", OracleDbType.Number).Value = txtOrderno.Text;
            cmd.Parameters.Add("pn_emp_id_no", OracleDbType.Number).Value = empid;
            cmd.Parameters.Add(new OracleParameter("pv_error", OracleDbType.VarChar));
            cmd.Parameters["pv_error"].Direction = ParameterDirection.Output;
            string pv_error;
            cmd.ExecuteNonQuery();
            pv_error = cmd.Parameters["pv_error"].Value.ToString();

            if (pv_error.ToString() == "")
            {
                trans.Commit();
            }
            else
            {
                trans.Rollback();
                MessageBox.Show("" + pv_error, "Error");
                frmReturns r = new frmReturns();
                r.Show();
                this.Hide();
            }
        }

Pinturiccio
Devart Team
Posts: 2420
Joined: Wed 02 Nov 2011 09:44

Re: dotConnect for Oracle disable autocommitting

Post by Pinturiccio » Wed 13 Feb 2013 14:01

Each SQL statement must be executed in a transaction in oracle. If the transaction is not started explicitly, Oracle creates an implicit transaction for the single statement being executed.

If AutoCommit = false, then, if you don't call conn.Commit();, the transaction will be rolled back.
If AutoCommit = true (default value), then the OCI_COMMIT_ON_SUCCESS flag is set. It will commit every single statement on success and will, if an exception is raised, likely roll back only the statement that failed.

If a transaction was started explicitly by calling BeginTransaction method the value of AutoCommit property is ignored.
For more information, please refer to http://www.devart.com/dotconnect/oracle ... ommit.html

Since you start a transaction explicitly in both of your example, the AutoCommit property value is ignored. Both examples persist data to the database not after stored procedure execution, but after execution of the line trans.Commit();

Post Reply