none
Dataset PK parent-child propogation issue RRS feed

  • Question

  • I am having a problem with the propogation of the primary key to the FK's in the child tables once the parent table is created.  I am using VS 2005 Team Suite connecting to a SQL 2005 database. 

     

    I have attached the methods and classes I am using for this procedure for your review.  The data relation is configured as follows:

     

    "Both Relation and Foreign Key constraint"

    UpdateRule = Cascade

    DeleteRule = Cascade

    Accept/RejectRule = None

     

    I would appreciate any help I can get on this issue.  Thanks in advance.

     

    Private Class ChildEntity

    Sub New()

    End Sub

    Friend childTable As DataTable

    Friend childDataAdapter As SqlDataAdapter

    End Class

     

    Private Sub UpdateDataset(ByVal parentTable As DataTable, ByVal parentDataAdapter As SqlDataAdapter, _

    ByVal childEntities As List(Of ChildEntity))

    Try

    If Me.CertSqlConnection.State = ConnectionState.Closed Then

    Me.CertSqlConnection.Open()

    End If

    Dim transaction As SqlTransaction = Me.CertSqlConnection.BeginTransaction

    'Adds the parent table to the transaction

    parentDataAdapter.DeleteCommand.Transaction = transaction

    parentDataAdapter.InsertCommand.Transaction = transaction

    parentDataAdapter.UpdateCommand.Transaction = transaction

    'Adds the child table to the transaction

    For Each childEntity As ChildEntity In childEntities

    childEntity.childDataAdapter.DeleteCommand.Transaction = transaction

    childEntity.childDataAdapter.InsertCommand.Transaction = transaction

    If childEntity.childDataAdapter.UpdateCommand IsNot Nothing Then

    childEntity.childDataAdapter.UpdateCommand.Transaction = transaction

    End If

    Next childEntity

    Try

    parentDataAdapter.Update(parentTable.Select("", "", DataViewRowState.ModifiedCurrent))

    parentDataAdapter.Update(parentTable.Select("", "", DataViewRowState.Added))

    For Each childEntity As ChildEntity In childEntities

    childEntity.childDataAdapter.Update(childEntity.childTable.Select("", "", DataViewRowState.ModifiedCurrent))

    childEntity.childDataAdapter.Update(childEntity.childTable.Select("", "", DataViewRowState.Added))

    childEntity.childDataAdapter.Update(childEntity.childTable.Select("", "", DataViewRowState.Deleted))

    Next childEntity

    parentDataAdapter.Update(parentTable.Select("", "", DataViewRowState.Deleted))

    transaction.Commit()

    Catch ex As Exception

    transaction.Rollback()

    Throw

    End Try

    Finally

    End Try

    End Sub

     

    Private Sub SaveButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SaveButton.Click

     

    If Not Me.ValidateChildren() Then

    MsgBox("Some items have errors. Please correct the items and resubmit")

    End If

    Me.CertificationsBindingSource.EndEdit()

    Me.CertificationPhotosBindingSource.EndEdit()

    Me.CertificationFeaturesBindingSource.EndEdit()

    Me.CertificationMarketBindingSource.EndEdit()

    Me.CertificationModelTypeBindingSource.EndEdit()

    Me.CertificationRegionBindingSource.EndEdit()

    Me.CertificationNotesBindingSource.EndEdit()

    If Me.ProductCertificationsDataSet.HasChanges Then

    Dim childEntities As New List(Of ChildEntity)

    'load the child tables into the collections

    If Me.ProductCertificationsDataSet.CertificationFeatures.GetChanges IsNot Nothing Then

    Dim entity As New ChildEntity

    entity.childTable = Me.ProductCertificationsDataSet.CertificationFeatures

    entity.childDataAdapter = Me.CertificationFeaturesSqlDataAdapter

    childEntities.Add(entity)

    End If

    If Me.ProductCertificationsDataSet.CertificationMarkets.GetChanges IsNot Nothing Then

    Dim entity As New ChildEntity

    entity.childTable = Me.ProductCertificationsDataSet.CertificationMarkets

    entity.childDataAdapter = Me.CertificationMarketsSqlDataAdapter

    childEntities.Add(entity)

    End If

    If Me.ProductCertificationsDataSet.CertificationModelTypes.GetChanges IsNot Nothing Then

    Dim entity As New ChildEntity

    entity.childTable = Me.ProductCertificationsDataSet.CertificationModelTypes

    entity.childDataAdapter = Me.CertificationModelTypesSqlDataAdapter

    childEntities.Add(entity)

    End If

    If Me.ProductCertificationsDataSet.CertificationNotes.GetChanges IsNot Nothing Then

    Dim entity As New ChildEntity

    entity.childTable = Me.ProductCertificationsDataSet.CertificationNotes

    entity.childDataAdapter = Me.CertificationNotesSqlDataAdapter

    childEntities.Add(entity)

    End If

    If Me.ProductCertificationsDataSet.CertificationPhotos.GetChanges IsNot Nothing Then

    Dim entity As New ChildEntity

    entity.childTable = Me.ProductCertificationsDataSet.CertificationPhotos

    entity.childDataAdapter = Me.CertificationPhotosSqlDataAdapter

    childEntities.Add(entity)

    End If

    If Me.ProductCertificationsDataSet.CertificationRegions.GetChanges IsNot Nothing Then

    Dim entity As New ChildEntity

    entity.childTable = Me.ProductCertificationsDataSet.CertificationRegions

    entity.childDataAdapter = Me.CertificationRegionsSqlDataAdapter

    childEntities.Add(entity)

    End If

    UpdateDataset(Me.ProductCertificationsDataSet.Certifications, Me.CertificationsSqlDataAdapter, _

    childEntities)

    MsgBox("Changes saved successfully")

    End If

    End Sub

    Monday, November 19, 2007 9:24 PM

Answers

  • It is not my intention to confuse you but Microsoft usually does not explain these complicated things in simple English in the Docs.  But in Management Studio right click on your table and click on modify and right click on  the key go to relationship and you will see INSERT and UPDATE specifications expand you will see Cascade options.  The best place to see the options is in the Create Table and ALTER Table statements.  It is best you use alternative solution because your code as is will not run in SQL Server.


    http://msdn2.microsoft.com/en-us/library/ms177465.aspx


    http://msdn2.microsoft.com/en-us/library/ms174979.aspx

     

     

    Wednesday, November 28, 2007 12:08 AM

All replies

  • DRI(declarative referential integrity) also known as Cascade On Delete, Cascade On Update, Cascade SET Default and Cascade SET NULL in SQL Server in English means if A references B B must exist which means Primary Key A becomes Foreign Key B. You use A to Delete B, use A to Update B, use A to SET B to Default and use A to SET B to NULL. This in SQL Server is a two table operation and everything will work but if you choose to use A to reference more than B and SQL Server gets confused because the base Algebra 26 pages long does not support it. So go clean up your DRI and your code will run.  Now I am not saying you cannot reference more than one table but for that in SQL Server you need a DRI Trigger not the built in version you are now using.

    Wednesday, November 21, 2007 9:13 PM
  • I'm not sure I understand what you are saying here. 

    Wednesday, November 21, 2007 9:20 PM
  • ("Both Relation and Foreign Key constraint"

    UpdateRule = Cascade

    DeleteRule = Cascade

    Accept/RejectRule = None)

     

    Parent -child propagation is limited to two tables in SQL Server. I am assuming you have setup your propagation in ADO.NET but your tables have to accept what you setup.

    Wednesday, November 21, 2007 10:23 PM
  • I have a datarelation configured with the config listed above for each parent/child relation in the db.  Are you telling me that SQL has a limitation in Cascading relationships that will only allow a parent to update a single child table? 

     

    In my situation, I have parent, and I need it to propogate to ChildA, ChildB, ChildC, etc.

     

    If that is the case, I'm going to have to rethink my whole dev concept.

    Wednesday, November 21, 2007 11:01 PM
  • (In my situation, I have parent, and I need it to propogate to ChildA, ChildB, ChildC, etc.)

     


    That is Access not RDBMS, and I am not telling you one of the most important Algebra of the twentieth century comes with a limitation because I have told you the alternative which is a DRI trigger. In SQL Server and most RDBMS if A references B B must exist means you can have foreign keys but only one qualify for the built in DRI feature.

    Tuesday, November 27, 2007 6:49 PM
  • Again, I have a hard time understanding your point.  Some sample code would help greatly.  I have decided to scrap the approach altogether for now as I couldn't wait for a definitive answer. 

     

    Tuesday, November 27, 2007 9:09 PM
  • It is not my intention to confuse you but Microsoft usually does not explain these complicated things in simple English in the Docs.  But in Management Studio right click on your table and click on modify and right click on  the key go to relationship and you will see INSERT and UPDATE specifications expand you will see Cascade options.  The best place to see the options is in the Create Table and ALTER Table statements.  It is best you use alternative solution because your code as is will not run in SQL Server.


    http://msdn2.microsoft.com/en-us/library/ms177465.aspx


    http://msdn2.microsoft.com/en-us/library/ms174979.aspx

     

     

    Wednesday, November 28, 2007 12:08 AM
  •  

    I was able to get it to work using a different approach, but I think I understand what you are saying.  If I am reading your point correctly, I would need to set cascade updates on both the dataset relationships and on the foreign keys of the database. 

     

    Thank you very much for bearing with me.

    Wednesday, November 28, 2007 12:13 AM