Is "Commit" necessary when updating Oracle from asp.net?

Question

I have this code

    protected void btnUpdateAddress_Click(object sender, EventArgs e)
    {
        sdsAddressComparison.Update();
    }

that I'm using to update an oracle database. When I run the update sql code in SQL Navigator I have to type "Commit" or hit the commit button.

Do I have to code in a "Commit" somewhere in ASP.NET? and if so how and where do i do it?

Answer 1

Normally, No you do not need the commit.

However you can write the code to use a transaction, and at the completion of the transaction you can call commit.

E.G. (Regular):

try {
        // Open connection
        dbConn.Open();
        //DB Update Code Here
    }
    catch (Exception ex) {
        throw;
    }
    finally {
        // Close database connection
        dbConn.Close();
    }

E.G. (As Transaction):

try {
        // Open connection & begin transaction
        dbConn.Open();
        dbTran = dbConn.BeginTransaction();
        
        //DB Update Code Here
        // Commit transaction
        dbTran.Commit();
    }
    catch (Exception ex) {
        // Rollback transaction
        dbTran.Rollback();
        throw;
    }
    finally {
        // Close database connection
        dbConn.Close();
    }

Answer 2

Just for clarification....I'm not talking about SQL Server transactions ....I'm talking about Oracle updates which usually require a commit command when I use either SQL navigator or SQL Plus

The reason i'm posting this is because I can update this data in SQL Navigator but it doesn't update when I use ASP.NET.

Answer 3

By default, your ASP.Net code, and most other client API's for databases (ODBC, OLE DB, JDBC, etc), run in auto-commit mode. That is, any time a statement is executed successfully, the result is committed. If you are running in that sort of a default mode, there is no need to explicitly commit your update.

On the other hand, there is generally a great deal to be said for putting your updates in explicit transactions-- if you ever have to issue multiple updates in order to make one logical business change, the default auto-commit mode is a very poor one. The classic example here is that if you update account A to withdraw $50 and then update account B to deposit $50 and you end up having two different transactions because of auto-commit being enabled, it is possible that the first transaction would succeed while the other transaction fails and the system loses track of $50.

So you generally want to write code similar to what Brian has demonstrated where you use transactions and issue the explicit commit. But by default, you don't have to and your updates will auto-commit.

Answer 4

How do I check if autocommit is turned on?

Answer 5

The behavior you're seeing in SQL Navigator is probably determined by an options setting.

I haven't used SQL Navigator, but I do use TOAD which is also by Quest Software. In the options dialog there, it's under View->Toad Options..., then the Oracle->Transactions node.

There's the following relevant settings:

• [ ] Commit after every statement (checkbox)

• When closing connections: (radio selection)

  • ( ) Commit
  • ( ) Rollback
  • ( ) Prompt for commit/rollback when changes detected, or detection is not possible due to lack of privileges on dmbs_transaction.

So you could change the setting so you don't need to hit the commit button (or type "commit"), but it's generally a bad practice since a commit is something that you should explicitly be doing (or explicitly rolling back).

Answer 6

Autocommit is also available in SQL*Plus.

SET AUTOCOMMIT ON
SET AUTOCOMMIT OFF

or

SET AUTOCOMMIT 100

Use SHOW AUTOCOMMIT to see the current setting.

But ... I hate this setting. You commit at the end of a meaningful unit of work, not part way through.