We help IT Professionals succeed at work.

Transactions in VB.NET

379 Views
Last Modified: 2012-05-08
Experts,

How would I go about creating a database transaction that I can roll back in Visual Basic.NET?  Is it possible to enclose a method in a transaction that gets called several times and then roll back if any of those calls to that method fail?
Comment
Watch Question

Shaun KlineLead Software Engineer
CERTIFIED EXPERT

Commented:
A transaction is created when you use connection.CreateTransaction(). Create the transaction before you start updating data, and then wrap the calls to your method in a Try Catch statement. If your method fails (assumption is a fail means an exception is thrown), your Catch block would perform the transaction.RollBack(). If all succeeds, just prior to the Catch block put a transaction.Commit. This works as long as all of your DB update statements use the same connection object. This may require you to pass the connection and transaction objects to your method.
CERTIFIED EXPERT

Commented:
Adding to Shaun here is the test code

http://www.devasp.net/net/articles/display/190.html

Author

Commented:
Can a transaction span databases?
Shaun KlineLead Software Engineer
CERTIFIED EXPERT

Commented:
An ASP.Net translation is tied to a connection object, which can only go to one database. So if you were calling stored procedures to perform your updates, and those stored procedures modified a different database, the transaction would not rollback changes to the second database.

If you are modifying multiple databases from code, you would need a transaction object for each connection object and handle the commit/rollback for all transactions together.

Author

Commented:
Bascially I'm writing a new Agent record (personnel) into a central database.  After that, I want to take that newly generated Agent ID and propagate it to several other databases.  If the ID already exists in the Agent table of another database, I'll get an error and will want to roll everything back.
It sounds like I'll just have to keep the AgentID handy and if there's a problem, go back through all the databases and delete the records I've already written to the other databases.
CERTIFIED EXPERT

Commented:
Here is the scenario,

Try
Create transaction

Add a new agent record and populate other databases

If agentId already exists Then

Throw ex

End If

Commit()

Catch ex as exception
rollback()

End Try

Author

Commented:
Right.  But my understanding is that I can't roll back across databases.  I think I'll have to keep track of the databases I've written to and just loop through and delete the agent ID from those tables one at a time.
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT

Commented:
Thats just an Idea.try it out. I didnt come across such scenario.
Shaun KlineLead Software Engineer
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
That is very helpful.  Where does the example roll anything back?  Can I roll back everything from the TransactionScope object?
CERTIFIED EXPERT

Commented:
I think it does automatically because they made a not as comment in the code

' The Complete method commits the transaction. If an exception has been thrown,        ' Complete is called and the transaction is rolled back.
ROMA CHAUHANProject Lead
CERTIFIED EXPERT

Commented:
Try this code
Dim trans As SqlTransaction
                Try
                    conn.open
                    Try
                         trans = dbConn.BeginTransaction()
                        myCommand.Connection = conn
                        myCommand.CommandText = SQL_str
                        myCommand.Transaction = trans
                        myCommand.ExecuteNonQuery()
                        conn.Commit()
                        conn.Close()
                     Catch ex as Exception
                          trans.Rollback()
                     Finally
                          'Close the connection here, if you so desire
                      End Try
                Catch ex as Exception
                          MsgBox 'Could not open connection'
                End Try


And Also these link may help you,
http://www.vb-helper.com/howto_net_db_transaction.html
http://www.java2s.com/Code/VB/Database-ADO.net/Transactionsavepointandrollback.htm
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.