VB.Net, use linq transaction to run s/p that contains transaction

Posted on 2011-10-13
Last Modified: 2012-05-12
I have some VB.Net code that runs several SQL stored procedures in sequence.  Table updates within each of the sprocs are wrapped in a transaction.  For example, sp1 starts a transaction, updates 5 tables, commits or rollsback.  It returns a success code to the calling program.  If successful, sp2 starts a transaction, updates 3 tables, commits or rollsback.  It returns a success code to the calling program.  If successful ... and so on.

I am using linq so I have:

If success = true then
    dc.sp2(parameters, success)
    if success = true then
    end if
end if

and so on

What I want to do is start a transaction for dc and if success is false for ANY of the sprocs, roll everything back.

In other words, there will be transactions within the "dc transaction".  If the dc transaction is rolled back, will the nested transactions also be rolled back?
Question by:g_johnson
    1 Comment
    LVL 83

    Accepted Solution

    Committing inner transactions is ignored by the SQL Server Database Engine. The transaction is either committed or rolled back based on the action taken at the end of the outermost transaction.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Suggested Solutions

    Article by: Kraeven
    Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
    If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

    754 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

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now