Do you need to "commit" between multiple oracle sql executions in a c# app?

Question

 var oraConnectionString = new OracleConnectionStringBuilder(_connectionString);
            using (var oraConnection = new OracleConnection(oraConnectionString.ConnectionString))
            {
                oraConnection.Open();
                try
                {
                    using (var command = new OracleCommand(sqlText, oraConnection))
                    {
                        command.Parameters.Clear();
                        command.BindByName = true;
                        command.Parameters.Add("node", _currentNode);
                        command.Parameters.Add("carrierId", CarrierId);
                        command.Connection.BeginTransaction();
                        command.ExecuteNonQuery();
                        command.CommandText = UtilityMethods.LoadTemplate("Resources/CreateCarrier.sql", "Carriers");
                        command.Parameters.Clear();
                        command.Parameters.Add("carrierName", CarrierName);
                        command.Parameters.Add("line1", Line1);
                        command.Parameters.Add("line2", Line2);
                        command.Parameters.Add("line3", Line3);
                        command.Parameters.Add("line4", Line4);
                        command.ExecuteNonQuery();
                        command.CommandText = UtilityMethods.LoadTemplate("Resources/GetCarrierId.sql", "Carriers");
                        CarrierId = Convert.ToString(command.ExecuteScalar());
                        if (CarrierId == null)
                        {
                            //TODO
                        }

                        command.Parameters.Clear();
                        command.CommandText = command.CommandText = UtilityMethods.LoadTemplate("Resources/AddCarrierToNode.sql", "Carriers");
                        command.Parameters.Add("node", _currentNode);
                        command.Parameters.Add("rank", Convert.ToInt32(Rank));
                        command.Parameters.Add("carrierId", Convert.ToInt32(CarrierId));
                        command.ExecuteNonQuery();
                        command.Transaction.Commit();
                    }
                }

For some reason CarrierId is returning null. The only thing that I can think of is that the changes from the previous sql commands have not been committed. Can anyone please tell me if you need to commit between each execution?

Since we cannot see what you SQL commands are, how do you know that is not the issue in there? — OldProgrammer
– OldProgrammer, Commented Jan 4, 2017 at 15:36
Within an Oracle session, one commit is enough to save DML operation done prior to that commit. — XING
– XING, Commented Jan 4, 2017 at 15:39
When to commit should be dictated exclusively by the business logic. — mustaccio
– mustaccio, Commented Jan 4, 2017 at 15:55

Oleg Borisov · Accepted Answer · 2017-01-04 16:01:26Z

1

It depends...

If you use the ODBC connection, you should check the settings for this link. By default, ODBC connection doesn't request autocommit for every operation.
I recommend you to call "Commit" after your operation anytime. You should implement your business logic in program and don't wait the potential configuration issue in ODBC configuration or other environment.

answered Jan 4, 2017 at 16:01

Oleg Borisov

263 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

OldProgrammer Over a year ago

Nope - "By default, ODBC transactions are in auto-commit mode" -msdn.microsoft.com/en-us/library/ms714549(v=vs.85).aspx

Oleg Borisov Over a year ago

Sorry, I mixed the sqlplus environment and ODBC settings. However, user / administrator can change it and I recommend to call "Commit" after all critical operation in the code.

Collectives™ on Stack Overflow

Do you need to "commit" between multiple oracle sql executions in a c# app?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related