Using oracle transactions in vb.net

[Diego F.]

Hello. I'm writing some code to connect and execute queries using oracle
namespace in VB.NET.

My question is about the use of transactions. To use oracle transactions, I
must do something like:

Dim con As New OracleConnection(_conString)
Dim cmd As New OracleCommand(query, con)
cmd.CommandType = CommandType.Text
con.Open()
Dim trans As OracleTransaction = con.BeginTransaction()

But I'm used to do con.Open inside a try catch statement. If I do that, and
put the BeginTransaction inside the try, I get a null reference warning in
the catch, where I do trans.rollback.

Now I have that:

Dim con As New OracleConnection(_conString)
Dim cmd As New OracleCommand(query, con)
cmd.CommandType = CommandType.Text

con.Open()
Dim trans As OracleTransaction = con.BeginTransaction()

Try

cmd.ExecuteNonQuery()
trans.Commit()

Catch e As Exception

trans.Rollback()

End Try

But I don't like the con.Open before the try block. What is the best way to
do that?

 

[Guest]

Diego F.,

This isn't really a problem with using oracle transactions. You are getting
a general warning that you might be trying to use a variable before it has
been assigned a value.

Of course, this is only a warning and if you are handling the transaction
variable correctly in the catch or finally block you can just ignore the
warning.

In the catch or finally block you should first test to see if the variable
has been assigned a value before trying to use it. Something like:

If trans IsNot Nothing Then ...

If you just don't like having the warning message show up, you can turn that
particular warning message off in the project's properties. On the Compile
tab, change the notification for use of variable prior to assignment from
warning to none.

Kerry Moorman

 

[diAb0Lo]

Hello. I'm writing some code to connect and execute queries using oracle
namespace in VB.NET.

My question is about the use of transactions. To use oracle transactions, I
must do something like:

Dim con As New OracleConnection(_conString)
Dim cmd As New OracleCommand(query, con)
cmd.CommandType = CommandType.Text
con.Open()
Dim trans As OracleTransaction = con.BeginTransaction()

But I'm used to do con.Open inside a try catch statement. If I do that, and
put the BeginTransaction inside the try, I get a null reference warning in
the catch, where I do trans.rollback.

Now I have that:

Dim con As New OracleConnection(_conString)
Dim cmd As New OracleCommand(query, con)
cmd.CommandType = CommandType.Text

con.Open()
Dim trans As OracleTransaction = con.BeginTransaction()

Try

cmd.ExecuteNonQuery()
trans.Commit()

Catch e As Exception

trans.Rollback()

End Try

But I don't like the con.Open before the try block. What is the best way to
do that?

--

Regards,

Diego F.

Hi,

I think put all inside try block is the best way. Any error occured
inside (fail to connect, failing executing query or transaction will
be catched and transaction will be reversed).

Any other opinion?