.NET begin/rollback/commit transaction with dataset-multiple tables

MS SQL, VB.NET - How do I use a begin/rollback/commit transaction with dataset with multiple tables?

In this case, I have one parent/child database, for which I've defined a .NET dataset.  My code basically looks like the attached snippet.

I want to wrap a transaction around the entire thing, but I can't figure out how to do that.  It seems that the real value of datasets hinges on the ability to commit or rollback the entire logical update.

Thank you.
Dim FamilyConn As New SqlConnection(SIS1ConnString)
        Dim ChildConn As New SqlConnection(SIS1ConnString)
        Dim CmdConn As New SqlConnection(SIS1ConnString)
        Dim FamilyDS As New MIS_SIS1DataSet()
        Dim FamilyDA As New MIS_SIS1DataSetTableAdapters.TableAdapterManager
        Dim ParDA As New MIS_SIS1DataSetTableAdapters.Test_ParentTableAdapter
        Dim ParTable As New MIS_SIS1DataSet.Test_ParentDataTable
        Dim ChildDA As New MIS_SIS1DataSetTableAdapters.Test_ChildTableAdapter
        Dim ChildTable As New MIS_SIS1DataSet.Test_ChildDataTable
        Dim ChildCmd As New SqlCommand("", CmdConn)

        Dim thisID As String = InputBox("CollID")

        FamilyConn.Open()
        ParDA.Fill(ParTable, thisID)
        ChildDA.Fill(ChildTable, thisID)

        MsgBox(ParTable.Count & " Parents")
        MsgBox(ChildTable.Count & " Children")

        Dim NewID As String = InputBox("New CollID")
        For Each thisParent As DataRow In ParTable.Rows
            thisParent.BeginEdit()
            thisParent.Item("CollID") = NewID
            thisParent.EndEdit()
        Next

        For Each thisChild As DataRow In ChildTable.Rows
            thisChild.BeginEdit()
            thisChild.Item("CollID") = NewID
            thisChild.EndEdit()
        Next

        CmdConn.Open()
        ChildCmd.CommandText = "alter table Test_Child nocheck constraint FK_Test_Child_Test_Parent"
        ChildCmd.ExecuteNonQuery()
        ParDA.Update(ParTable)
        ChildDA.Update(ChildTable)
        ChildCmd.CommandText = "alter table Test_Child check constraint FK_Test_Child_Test_Parent"
        ChildCmd.ExecuteNonQuery()
        ChildCmd.CommandText = "dbcc CHECKCONSTRAINTS ('Test_Child')"
        Dim ConstraintRdr As SqlDataReader = ChildCmd.ExecuteReader
        If (ConstraintRdr.Read) Then Throw New Exception("At least one foreign key is out of synch")

Open in new window

WJoeMooreAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

BirkyCommented:
Hi,

I don't think you can do what you are looking at doing. My understanding is that Transactions only work in the scope of a connection. Since you have multiple connections then you cannot roll each of them back. The code attached is how I have transactions working.

You might be able to rig your code to handle multiple connections and to roll back them all if one fails but I havent done this.

Hope this helps
Dim PTrans as SQLTransaction
Dim SQLCMD as New SqlCommand
Dim SQLCMD2 as New SqlCommand
Dim Conn as New SQLConnection ("Connection String")

SQLCMD = New SqlCommand("Select String", Conn)
SQLCMD2 = New SqlCommand("Select String 2", Conn)
Conn.Open
PTrans = Conn.BeginTransaction()

Try
    SQLCMD.ExecuteNonQuery()
    SQLCMD2.ExecuteNonQuery()
    PTrans.Commit()
Catch ex as Exception
    PTrans.Rollback()
Finally
    Conn.Close()
End Try

Open in new window

0
WJoeMooreAuthor Commented:
I'm afraid from what I've seen is 'you can't do that' and you are correct.  If so, you get the points.  I'll give it a day.  Thanks.
0
WJoeMooreAuthor Commented:
I'm afraid from what I've seen is 'you can't do that' and you are correct.  If so, you get the points.  I'll give it a day.  Thanks.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

b_levittCommented:
Use System.Transactions.  I applogize, I'm a little rusty with vb, but in c# it would be:

using(System.Transactions.TransactionScope trans= new System.Transaction.TransactionScope())
{
   //your code here
   trans.Complete
}

Keep in mind that once you open your second connection, the transaction will be 'promoted' to a distributed transaction so you'll need to make sure the distributed transaction coordinator service is running.

B
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
WJoeMooreAuthor Commented:
Thanks!!  In our case, the network transaction scope has been disabled--and I don't have any control over that, so I will need to do my rollback or commit on a table by table basis after doing as much checking as possible and simply put the application in an indeterminate state (as I define it) if I run into a 'partial commit'.
0
b_levittCommented:
I ran into the same problem when deploying a project hosted by a 3rd party.  I used a modified version of this solution:

http://www.codeproject.com/KB/database/TsFix.aspx

By managing the connection internally, it keeps the transaction from being promoted to the DTC.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.