Sql data access methods oracle

Question

I have a basic dll which contains common database methods such as getconnectionstring,executescalar, executenonquery,doselect etc. I am working with (vb.net or c#) and oracle. Now i need to write a method for a transaction where i may have 2-3 sqls and should commit only if all of them work. I am trying to see how to do it. I need sql1 to return some sort of unique field which would be used to insert into sql2 and/or sql3.

  private sub executeTransaction(byval sql1 as string,byval sql2 as string,byval sql3 as string)

       code to begin transaction
       execute sql1 returning unique id to a local field    'since this id may be different based on sql, how to handle this?
       execute sql2
       execute sql3 optional
       if exception rollback 
       commit on finally block and then close the connection object

  end sub

I am looking for suggestions/best practices to write the above method. Thanks in advance.

D Stanley · Accepted Answer · 2013-04-30 15:54:17Z

1

The pattern is:

Create a transaction from your connection
Assign that transaction to each command
commit/rollback when done

using(OracleConnection conn = new OracleConnection())
{
    using(DbTransaction transaction = conn.BeginTransaction())
    {
        try
        {
            // .... create a command 
            cmd1 = new OracleCommand({sql to get a value});
            cmd1.Transaction = transaction;
            cmd1.ExecuteScalar();

            // .... create another command 
            cmd1 = new OracleCommand({sql to update a value});
            cmd2.Transaction = transaction;
            cmd2.ExecuteNonQuery();

            transaction.Commit();  
        }
        catch (Exception err)
        {
            transaction.Rollback();
            throw err;   // or just throw; to preserve the deeper stack trace
        }
    }
}

answered Apr 30, 2013 at 15:54

D Stanley

153k12 gold badges190 silver badges257 bronze badges

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

2 Comments

cableload Over a year ago

Thank you.this will work. One quick question, how will i make executescalar return the id of the last inserted row? I was thinking of using executenonquery with the returning clause.

D Stanley Over a year ago

All ExecuteScalar does is return the value of the result's first row's first column, so you would have to do a SELECT at the end of your batch. ExecuteNonQuery with a return parameter may be cleaner.

Collectives™ on Stack Overflow

Sql data access methods oracle

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