How to add Transactions with a DataSet created using the Add Connection Wizard?

Question

I have a DataSet that I have added to my project where I can Insert and Add records using the Add Query function in Visual Studio 2010, however I want to add transactions to this, I have found a few examples but cannot seem to find one that works with these.

I know I need to use the SQLClient.SQLTransaction Class somehow. I used the Add New Data Source Wizard and added the Tables/View/Functions I need, I just need an example using this process such as How to get the DataConnection my DataSet has used. Assuming all options have been set in the wizard and I am only using the pre-defined adapters and options asked for in this wizard, how to I add the Transaction logic to my Database.

For example I have a DataSet called ProductDataSet with the XSD created for this, I have then added my Stock table as a Datasource and Added an AddStock method with a wizard, this also if a new item calls an AddItem method, if either of these fails I want to rollback the AddItem and AddStock in this case.

Answer 1

In this example, I have a dataset called "dsMain" and a few direct queries in a "QueriesTableAdapter". I extend the partial class for the TableAdapter with a function that will create a transaction based on the first (0) connection and then apply it to every connection in the table adapter.

Namespace dsMainTableAdapters
    Partial Public Class QueriesTableAdapter
        Public Function CreateTransaction() As Data.IDbTransaction   
            Dim oConnection = Me.CommandCollection(0).Connection
            oConnection.Open()

            Dim oTrans = oConnection.BeginTransaction()

            For Each cmd In Me.CommandCollection
                cmd.Connection = oConnection
                cmd.Transaction = oTrans
            Next

            Return oTrans
        End Function
    End Class
End Namespace

You begin the transaction by calling the new function

Dim qa As New dsMainTableAdapters.QueriesTableAdapter
Dim oTrans = qa.CreateTransaction()

Then you can call TableAdapter queries within your transaction

qa.Query1
qa.Query2

When you are done with your queries you commit the transaction

oTrans.Commit()

You can do the same thing for any TableAdapter that was created for your datasets. If you have multiple TableAdapters that need to use the same transaction, then in addition to a "CreateTransaction" you should make a "SetTransaction" and have the Transaction be a parameter.

Answer 2

Hello first of all thanks for your answer carter, it helped me very much!

but iam not able to handle the part with the parameters

You can do the same thing for any TableAdapter that was created for your datasets. If you have multiple TableAdapters that need to use the same transaction, then in addition to a "CreateTransaction" you should make a "SetTransaction" and have the Transaction be a parameter.

so iam able to handle 1 transactions with 1 tableadapter, but not 1 transaction with 2 tableadapters:

iam doing this for a school project, and i really need your help!! here is the code to add a new material and a historical price to it(a changing price, like by fuel; iam saving it in an related table to material in the database):

Namespace DataSetTableAdapters
Partial Public Class MaterialPriceTableAdapter
Public Function SetTransaction() As Data.IDbTransaction

        Dim oConnection = Me.CommandCollection(0).Connection
        oConnection.Open()

        Dim oTrans = oConnection.BeginTransaction()

        For Each cmd In Me.CommandCollection
            cmd.Connection = oConnection
            cmd.Transaction = oTrans
        Next

        Return oTrans
    End Function
End Class

Partial Public Class MaterialTableAdapter

    Public Function CreateTransaction(ByVal MaterialPrice As System.Data.Odbc.OdbcTransaction) As Data.IDbTransaction

        Dim oConnection = Me.CommandCollection(0).Connection
        oConnection.Open()

        Dim oTrans = oConnection.BeginTransaction()

        For Each cmd In Me.CommandCollection
            cmd.Connection = oConnection
            cmd.Transaction = oTrans
        Next

        Return oTrans
    End Function
End Namspace

`

and now the code in the form the form:

Public Class AddMaterial
Dim material As New DataSetBATableAdapters.MaterialTableAdapter
Dim materialprice As New DataSetBATableAdapters.MaterialPriceTableAdapter
Dim oTrans = material.CreateTransaction(materialprice.SetTransaction())

Private Sub Save_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Save.Click
    Try
        material.InsertQuery(NameTextBox.Text, UnitComboBox.SelectedValue)
        materialprice.InsertQuery(Date_BeginDateTimePicker.Value, PriceTextBox.Text, Date_EndDateTimePicker.Value, Me.LkwTableAdapter.ScalarQuery())
        oTrans.Commit()
    Catch ex As Exception
        oTrans.Rollback()
        MsgBox("Error by Insert")
    End Try
    Me.Close
End Sub
End Class

if i save a new record the materialprice.insertquery isnt commited by otrans.commit. what am i doing wrong? if you have an idea what it is, please tell me

thanks, Xeras

Answer 3

This is untested, but this is how I imaging the CreateTransaction/SetTransaction combo should be written (with your OdbcTransaction object).

Public Function CreateTransaction() As System.Data.Odbc.OdbcTransaction
    Dim oConnection = Me.CommandCollection(0).Connection
    oConnection.Open()

    Dim oTrans = oConnection.BeginTransaction()

    SetTransaction(oTrans)

    Return oTrans
End Function


Public Sub SetTransaction(ByVal oTrans As System.Data.Odbc.OdbcTransaction)
    For Each cmd In Me.CommandCollection
        cmd.Connection = oTrans.Connection
        cmd.Transaction = oTrans
    Next
End Sub