dotConnect for Oracle disable autocommitting

dotConnect for Oracle disable autocommitting

Postby 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

Postby 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();
            }
        }
Wernervdh
 
Posts: 2
Joined: Tue 12 Feb 2013 14:08

Re: dotConnect for Oracle disable autocommitting

Postby 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/docs/?Devart.Data.Oracle~Devart.Data.Oracle.OracleConnection~AutoCommit.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();
Pinturiccio
Devart Team
 
Posts: 1997
Joined: Wed 02 Nov 2011 09:44


Return to dotConnect for Oracle