?
Solved

Transactional ODP.NET

Posted on 2005-05-15
11
Medium Priority
?
824 Views
Last Modified: 2010-05-18
Does ODP.NET has this transaction type commands(Transaction, Commit, End) like for completing a transaction thers a trasaction, begin, and end command...so that when i want to complete two tasks i know the program will only exit until those two tasks are complted and will not exit before then....



Moiz
0
Comment
Question by:Moizsaif123
  • 6
  • 5
11 Comments
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 14010370
Yes,

      Dim txn as OracleTransaction
...
      txn = con.BeginTransaction(IsolationLevel.ReadCommitted)
...
      txn.Rollback()
...
      txn.Commit()
...
0
 
LVL 4

Author Comment

by:Moizsaif123
ID: 14010421
i have three insert statements in my web app, which are used to insert image files as blobs using odp.net, then do i put those statements in between these commands...

txn = con.BeginTransaction(IsolationLevel.ReadCommitted)
...

' Insert Statements...

...
     txn.Commit()



Moiz
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 14010460
yes.  You will also need the rollback in the event of an error.   Typically this is done in a try catch code block.
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 4

Author Comment

by:Moizsaif123
ID: 14010550
so it might look something like this....

Dim myConnection As New OracleConnection("Data Source=x;User ID=x;Password=x;")
Dim cmd As New OracleCommand(Ssql, myConnection)
Dim txn As OracleTransaction
  Try
        txn = myConnection.BeginTransaction(IsolationLevel.ReadCommitted)


        Ssql = "INSERT INTO TESTPURPOSE(img_name,img_data,img_contenttype) " & _
        " VALUES(:img_name, :img_data, :img_contenttype)"
        With cmd
            .CommandText = Ssql
            .Connection = myConnection
        End With
        Try
            myConnection.Open()
            cmd.ExecuteNonQuery()
        Catch EX As OracleException
            TextBox1.Text = EX.Message
        End Try

   txn.Commit()
  Catch ex As Exception
        txn.Rollback()
  End Try


Moiz
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 14010865
I'm not sure what the inner try is doing but so far it looks pretty good.  I would also look into adding a finally clause to perform cleanup of objects like the connection and transaction.

something like:
...
finally
   txn.dispose()
...
0
 
LVL 4

Author Comment

by:Moizsaif123
ID: 14011021
yea i removed the inner try now, for the select query, theres no commit required? so only for the insert and update....

and what does this do..

txn.Dispose();
cmd.Dispose();
con.Close();
con.Dispose();


Dim myConnection As New OracleConnection("Data Source=x;User ID=x;Password=x;")
Dim cmd As New OracleCommand(Ssql, myConnection)
Dim txn As OracleTransaction
  Try
        txn = myConnection.BeginTransaction(IsolationLevel.ReadCommitted)


        Ssql = "INSERT INTO TESTPURPOSE(img_name,img_data,img_contenttype) " & _
        " VALUES(:img_name, :img_data, :img_contenttype)"
        With cmd
            .CommandText = Ssql
            .Connection = myConnection
        End With
            myConnection.Open()
            cmd.ExecuteNonQuery()
   txn.Commit()
  Catch ex As Exception
        txn.Rollback()
End Try


txn.Dispose();
cmd.Dispose();
con.Close();
con.Dispose();
0
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 400 total points
ID: 14013310
>>and what does this do..

Properly closes and cleans up the ODP objects.  It's a good habit to get into.  I've seen memory leaks in .Net apps using Oracle data providers and not cleaning up the objects.
0
 
LVL 4

Author Comment

by:Moizsaif123
ID: 14019509
one question:

Is MTS server work similar to the transaction thing we worked on?
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 14020298
I'm assuming you are talking about Microsoft Transaction Server not Oracles Multi Threaded Server/

No.  I'm not up on everything Microsoft MTS can do but it is not just to perform a simple transaction in a block of application code.  

Check out: http://www.microsoft.com/technet/archive/transsrv/mtxpg03.mspx
0
 
LVL 4

Author Comment

by:Moizsaif123
ID: 14021004
when you say

>>it is not just to perform a simple transaction in a block of application code.

means MTS does do some transactional processing?




Moiz
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 14021241
From what I know about it, it uses 'transaction' to mean a business rule from a sort-of application server type system.

Again, I really don't know anything about Microsoft MTS.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Suggested Courses
Course of the Month17 days, 3 hours left to enroll

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question