DataSet Question

[Aspnot]

I have a DataSet with 2 related tables. When I save a new record to Table1 using DataAdapter.Update(Table1), I have my stored procedure return back to the DataSet a select of the row added. This does not update the values in the DataTable until I call AcceptChanges on the DataTable. (I need these values updated so that the correct Identity values are set for Table2 before I try to insert the records.) Since this whole process is in the middle of a transaction, I do not want to call AcceptChanges at this time as the transaction might get rolled back.

I must be missing something here. Can someone shed some light on this process for me?

Thanks!!

[Aspnot]

Any thoughts?

[campster]

Since this whole process is in the middle of a transaction, I do not want to call AcceptChanges at this time as the transaction might get rolled back.

Correct me if I'm wrong but once you call Update you will not be able roll back this part of the transaction because Update actually performs AcceptChanges.

[Aspnot]

After a row updates successfully, the changes to that row are accepted.

I just found this on the MSDN site. You were correct, campster.

OK. How can I do what I am trying to do?

Here is some of the code I am working on:

If ds.HasChanges Then
            Dim oDA As New PRDAL.DataAccess(AppInfo.ConnectionString, True)  ' The True parameter tells the DAL to use a Transaction
            Dim iPatient_ID As Integer
 
                ' Check for changes to Patients
                If Not ds.Patients.GetChanges(DataRowState.Added) Is Nothing Then
                    iPatient_ID = oDA.Inserts.InsertPatient(ds.Patients.GetChanges(DataRowState.Added))
                    If iPatient_ID = 0 Then
                        GoTo RollBackAndExit
                    End If
                End If
 
                If Not ds.Patients.GetChanges(DataRowState.Modified) Is Nothing Then
                    If oDA.Updates.UpdatePatient(ds.Patients.GetChanges(DataRowState.Modified)) Then
                        iPatient_ID = ds.Patients(0)("Patient_ID")
                    Else
                        GoTo RollBackAndExit
                    End If
                End If
 
                ' There is no insert or update
                If iPatient_ID = 0 Then
                    iPatient_ID = ds.Patients(0)("Patient_ID")
                End If
 
                If Not ProcessContacts(ds.Contacts, iPatient_ID, oDA) Then GoTo RollBackAndExit
                If Not ProcessContactTelephones(ds.Telephones, oDA) Then GoTo RollBackAndExit
 
                oDA.Transaction.Commit()
                Return True
 
RollBackAndExit:
                oDA.Transaction.Rollback()
                Return False

Private Function ProcessContacts(ByRef inDataTable As DataTable, ByVal inPatient_ID As Integer, ByRef oDA As PRDAL.DataAccess) As Boolean
 
        If Not CheckForContactChanges(inDataTable.GetChanges(DataRowState.Modified), oDA) Then Return False
 
        If Not CheckForContactAdditions(inDataTable.GetChanges(DataRowState.Added), inPatient_ID, oDA) Then  Return False
 
        Return True
    End Function

Each call to my DAL uses a SQLDataAdapter and calls the update method for each Insert or Update separately. It would seem to me that this means that I truly cannot rollback my transaction at a later point due to the changes being accepted by the DataSet.

I am going to have to do a little testing.

[campster]

GoTo RollBackAndExit

Yikes... I thought that it was against the law to use GoTo and labels.

[campster]

I have a DataSet with 2 related tables.

Are these related because they are similar or are you defineg a relationship using something like:
ds.relations.add(....)?

[Aspnot]

Quote:

Quote:
After a row updates successfully, the changes to that row are accepted.

I just found this on the MSDN site. You were correct, campster.

Hmmm. I am using VB.NET 2005 and upon testing the above statement, it doesn't appear to be true. Here is the scenario I used:

Prior to calling the DataAdapter.Update(), I checked the ds.Patients.GetChanges(DataRowState.Modified) value and it returned a DataTable.

After calling the DataAdapter.Update(), I again checked the ds.Patients.GetChanges(DataRowState.Modified) value and it still returned a DataTable.

Only after calling AcceptChanges() did it properly show Nothing.


There is a property of the DataAdapter object named AcceptChangesDuringUpdate that is set to False by default.

Maybe I misread Microsoft's statement quoted above. http://msdn2.microsoft.com/en-us/lib...er.update.aspx

[Aspnot]

Hehe. The label seemed to be the best statement for the situation.

I have the relationship defined at design-time for the tables.

[Aspnot]

I seem to have found the error of my ways, but that leads to another issue.

The problem is the way I was calling the updates. By passing in the results of the GetChanges method, that was creating a NEW DataTable and the Select that was coming back from SQL Server was being placed into that DataTable. I found this out by the following code:

If ds.HasChanges Then
            Dim oDA As New PRDAL.DataAccess(AppInfo.ConnectionString)
            Dim iPatient_ID As Integer
            Dim tempDT As DataTable
 
                ' Check for changes to Patients
                If Not ds.Patients.GetChanges(DataRowState.Added) Is Nothing Then
                    tempDT = ds.Patients.GetChanges(DataRowState.Added)
                    If Not oDA.Inserts.InsertPatient(tempDT) Then
                        GoTo RollBackAndExit
                    End If
                End If

When I add a new Patient, the tempDT DataTable shows the new Identity of the newly added record. The question now is how do I get that back into my original DataTable? I can Remove the old DataRow and then add in tempDT, but I assume that there is a more proper way to do this.

[campster]

This came from the link that you provided. Maybe things have changed in 2005?

When using Update, the order of execution is as follows:

1. The values in the DataRow are moved to the parameter values.
2. The OnRowUpdating event is raised.
3. The command executes.
4. If the command is set to FirstReturnedRecord, then the first returned result is placed in the DataRow.
5. If there are output parameters, they are placed in the DataRow.
6. The OnRowUpdated event is raised.
7. AcceptChanges is called.

[Aspnot]

That link is from MSDN2 which is supposed to be directly related to VS 2005 and SQL 2005.

I was out of the office today. I will continue to look into this situation tomorrow.