Record Locking & Transactions in Strongly Typed DS

Kia · June 19th, 2007, 08:39 PM

Hi,

I have a form with 4 tables displayed in different tabs, all coming from the same dataset. Here's part of the Form.Load event so you know what I mean:

Code:

Me.taPaymentsApplied.Fill(Me.DsPaymentProcessing.PAYMENT_APPLIED)
Me.taNewPayment.Fill(Me.DsPaymentProcessing.PAYMENT, Me.txtCardID.Text)
Me.taExistingPayment.Fill(Me.DsPaymentProcessing.EXISTING_PAYMENT, Me.txtCardID.Text)
Me.taInvoices.Fill(Me.DsPaymentProcessing.INVOICE, Me.txtCardID.Text)

What I need to do is lock all the records that are opened in the latter 3 lines and begin a transaction. Then allow the user with the lock to add records to the first table and modify contents of the next 3. I'd prefer that other users simply not be allowed to even view these locked records.

Saving needs to be in a transaction, meaning either save ALL 4 tables or fail on all.

I can do this when I do everything programmatically, but I'm a bit lost when it comes to using objects from my Datasource window.

I'd appreciate any help.
Cheers

Kia

ashu_from_india · June 29th, 2007, 06:34 AM

dataset is all client side...so u cannot lock database rows using dataset...
place an explicit lock using transaction before u fetch the data...and commit / rollback it after modification

ashu_from_india · June 29th, 2007, 06:34 AM

and use IsolationLevel according to ur requirement

Kia · July 2nd, 2007, 09:32 PM

I keep seeing a little bit of code to extend the TableAdapter namespace and add a transaction sub to it. This is the code I saw on someone's blog

Namespace MyDataSetTableAdapters
    Partial Class MyTableAdapter
        Public Sub SetTransaction(ByVal trn As SqlClient.SqlTransaction)
            Me.Adapter.InsertCommand.Transaction = trn
            Me.Adapter.UpdateCommand.Transaction = trn
            Me.Adapter.DeleteCommand.Transaction = trn
            Me.Adapter.SelectCommand.Transaction = trn
        End Sub
    End Class
End Namespace

What I don't get is, what do you do with this? Where to put it? the only place I can figure out is the dataset's designed code, but it would get overwritten I thought. Enywhere else, I get an issue with the "Adapter" class not defined.

ashu_from_india · July 23rd, 2007, 06:23 AM

i hv not worked on TableAdapters and Update Method
i always use Command object & SQL statements for Insert, Update & Delete tasks