locked
Sync Services for ADO.NET, use with Master - Detail tables RRS feed

  • Question

  • We are currently hitting some serious problems when using Sync Services for ADO.NET with a fairly large database (100+ tables). Problem mostly arise from tables where there are Master - Detail relationship. For example:
    Imagine that there are two tables: Master, and Detail.
    Master has 2 columns: ID and Value
    Detail has 3 columns: ID, MasterID, and Value, and MasterID refers to the ID column of Master table.
    At the beginning, we have:
    Master:
    ID |    Value
    ---------------------------------------
    1  |    Master 1
    ---------------------
    Detail:
    ID  |    MasterID |    Value
    -------------------------------------
    1   |    1        |    Detail 1
    ---------------------

    Then, client deletes the master and detail records, server updates the master node from 'Master 1' to 'My Master 1'.
    At this point, we have an client delete server update conflict. If client win, then there is no problem.
    If server win, the Master record is retained but the Detail record is deleted, since there are no conflict on detail record, and the deletion made by client is freely propagate to the server. That is not what user want. In this situation, what the user want is: If the server win on that conflict, then all the related records from the server must override the corresponding records on the client, i.e. after sync, we must have master and detail records like this:
    Master:
    ID |    Value
    ---------------------------------------
    1  |    My Master 1
    ---------------------
    Detail:
    ID  |    MasterID |    Value
    -------------------------------------
    1   |    1        |    Detail 1
    ---------------------

    The previous example highlight a possible mismatch in the design of Sync Services for ADO.NET:
    In Sync Services, each table represents an object type.
    In reality, multiple tables combined together by the application to represent an object. So, one record in a table might be related to another record in another table, and sometimes cannot be separated.

    I have a question:
    - Can Sync Services handle the relationship between Master and Detail tables? Does the above mismatch exist?


    EDIT - Wrong forum. Can someone move this thread to the forum dedicated for Sync Service for ADO.NET?
    Monday, November 17, 2008 8:13 AM

Answers

  • after of 3 day of hard work, i think this will hep a bit
    this will solve ClientDelete(Master&Details) - ServerUpdate(Master) issue

    first, you need to add this code to ApplyChangeFailed event handler of server side:

    Code Snippet

    Private Sub _serverSyncProviderDong_ApplyChangeFailed(ByVal sender As Object, ByVal e As Microsoft.Synchronization.Data.ApplyChangeFailedEventArgs) Handles _serverSyncProviderDong.ApplyChangeFailed

    'declare a table that will hold data of details table to be keep

    Private _updateConflictTable As New DataTable("updateConflictTable")

    'In common situation we only need to keep the name of the details table, column name that use for store foreign key and column foreign key data to be keep.
    Dim tableName As DataColumn = New DataColumn("tableName", GetType(String))
    Dim columnName As DataColumn = New DataColumn("columnName", GetType(String))
    Dim guidData As DataColumn = New DataColumn("guidData", GetType(Guid))

    _updateConflictTable = New DataTable("updateConflictTable")
    _updateConflictTable.Columns.Add(tableName)
    _updateConflictTable.Columns.Add(columnName)
    _updateConflictTable.Columns.Add(guidData)


    'handle ClientDeleteServerUpdate case:

    If (e.Conflict.ConflictType = ConflictType.ClientDeleteServerUpdate) Then
    'Get the ID for the deleted row from the client data table,
    'and add it to a list of GUIDs. We keep rows at the server & client
    'based on this list.

    'Determine if current table has child relations with other table
    For Each relation As DataRelation In e.Conflict.ClientChange.ChildRelations
    'search for child rows that belong to rows of current table
    'those column in the details table that will be del if we don't handle them
    For Each row In e.Conflict.ClientChange.Rows
    For Each childRow As DataRow In row.GetChildRows(relation)
    'add to table that hold the affects data
    'in this situation I keep column:table name, key column name, column data
    'we will going to make them updated on the server, so the conflict will raise
    'on details table

    _updateConflictTable.Rows.Add(relation.ChildTable.TableName, relation.ChildKeyConstraint.Columns.FirstOrDefault().ColumnName, childRow(relation.ChildKeyConstraint.Columns.FirstOrDefault().ColumnName))
    Next
    Next
    Next

    'Now update row on details table at server, we do this for client to change at the
    download phrase

    For Each row As DataRow In _updateConflictTable.Rows
    Dim updateTable As New SqlCommand()
    updateTable.Connection = CType(e.Connection, SqlConnection)
    updateTable.Transaction = CType(e.Transaction, SqlTransaction)

    Dim strQuery = <string>
    UPDATE '<%= row(0) %>' SET '<%= row(1) %>' = '<%= row(2).ToString() %>' WHERE '<%= row(1) %>' ='<%= row(2).ToString() %>'
    </string>

    updateTable.CommandText = strQuery.Value
    updateTable.ExecuteNonQuery()
    Next

    'keep server untouched
    e.Action = ApplyAction.Continue

    End If


    End Sub


    Compile and run the code, you will notice that record at both server & client side deleted after update too!
    When in debugging, i check "e.Conflict.ClientChange.ChildRelations" is nothing in there, sync framework doesn't add it for us. So we must go to client side and add the relation manually to dataset.

    Go to ChangesSelected event handler of ClientProvider and paste this code:
    Code Snippet
    Private Shared Sub localDBProvider_ChangesSelected(ByVal sender As Object, ByVal e As Microsoft.Synchronization.Data.ChangesSelectedEventArgs) Handles localDBProvider.ChangesSelected
    Dim localDataSet As DataSet = New LocalCache()
    Dim changeDt = e.Context.DataSet

    'search for relation on client dataset
    For Each relation As DataRelation In localDataSet.Relations

    'Building new relation on Context.DataSet based on the relation of local dataset
    Dim changeRel As DataRelation
    Dim colRelParent As Array = Array.CreateInstance(GetType(DataColumn), _
    relation.ParentColumns.Count)
    Dim colRelChildren As Array = Array.CreateInstance(GetType(DataColumn), _
    relation.ChildColumns.Count)

    'search for primary columns of relationship
    For i As Int16 = 0 To relation.ParentColumns.Count - 1
    colRelParent(i) = _
    changeDt.Tables(relation.ParentTable.TableName).Columns(relation.ParentColumns(i).ColumnName)
    Next

    'search for foreign columns of relationship
    For i As Int16 = 0 To relation.ChildColumns.Count - 1
    colRelChildren(i) = _
    changeDt.Tables(relation.ChildTable.TableName).Columns(relation.ChildColumns(i).ColumnName)
    Next

    'construct new data relation
    changeRel = New DataRelation(relation.RelationName, colRelParent, colRelChildren, False)

    'now add to dataset, this dataset will be sent server at upload phrase
    e.Context.DataSet.Relations.Add(changeRel)

    Next

    End Sub

    Last things to notice: in your synchonize function, please add the master table before the details table in the SyncGroup before call Synchronize() method.

    Now recompile and everything should work!


    Hope this help.

    Monday, November 24, 2008 10:16 AM

All replies

  •  

    Great question, I also have the same problem.

     

    Regards

    DX

    Tuesday, November 18, 2008 4:31 AM
  • Sync Services doesn't have the notion of a conflict unit that goes beyond a row.  This is something that we are considering how to address in the future.

     

    The best thing for the time being may be to try to update both the master and the details tables at the same time so that the conflict would be generated for both.  Then your app would need to make sure that the conflict is resolved consistently in both cases.

     

    Cheers,

    Michael Clark

     

     

    Tuesday, November 18, 2008 6:01 PM
    Moderator
  • Hi, your answer is not the solution for our same problem. In our project, we face the same problem of MASTER-DETAIL relationship. I will point out here all situation from our issues list. The question is: is there any suggest for each of the below issues?

    Let’s assume that we have 02 tables with the master-detail relationship. The master table named: MASTER, and the detail table named DETAIL.
     
    MASTER   DETAIL     

    M_ID        D_ID      

    Value        M_ID      
                   Value     


    + Issue 1: Client Insert Master and Detail, Server Insert Master and Detail, conflict in Master ID

     
    CLIENT SITE (SOURCE)                                      SERVER SITE (DESTINATION)      
                                                          
    MASTER                DETAIL                                   MASTER            DETAIL              

    M_ID    VALUE     D_ID       M_ID    VALUE          M_ID    VALUE     D_ID    M_ID    VALUE      
    m1        v1            d1           m1        dv1               m1         v1            d1        1           1      
    m2        v2            d2           m2        dv2               m2         v2            d2        2           2      
    m3        v3            d3           m3        dv3               m3         v3'           d4        m3        dv3'     

    Steps:
        +     SERVER: Insert Master(m3, v3’) va Detail(d4, m3, dv3’)
        +     CLIENT: Insert Master(m3, v3) va Detail(d3, m3, dv3)
        +     When running Sync, the conflict ClientInsertServerInsert will occurs
        +     If we choose CLIENT-WIN, the result will become:
     

    CLIENT SITE (SOURCE)                               SERVER SITE (DESTINATION)      

    MASTER                DETAIL                            MASTER            DETAIL                   

    M_ID    VALUE     D_ID    M_ID    VALUE      M_ID    VALUE   D_ID    M_ID    VALUE           
    m1        v1            d1        m1        dv1            m1        v1          d1        1        1           
    m2        v2            d2        m2        dv2            m2        v2          d2        2        2           
    m3        v3            d3        m3        dv3            m3        v3          d3        m3        dv3           
                     d4        m3        dv3'                             d4        m3        dv3'           
                                                                     
        +     If we choose SERVER-WIN, the result will become:
     
    CLIENT SITE (SOURCE)                                SERVER SITE (DESTINATION)      

    MASTER               DETAIL                             MASTER             DETAIL              

    M_ID    VALUE     D_ID    M_ID    VALUE       M_ID    VALUE   D_ID    M_ID    VALUE      
    m1        v1            d1        m1        dv1            m1        v1          d1         1          1      
    m2        v2            d2        m2        dv2            m2        v2          d2         2          2      
    m3        v3'           d3        m3        dv3            m3        v3'         d3         m3        dv3      
              d4      m3        dv3'                             d4         m3       dv3'     

    So, in both cases, the record of DETAIL table ((d3, m3, dv3), (d3, m3, dv3’)) might be wrong in biz logic. The new record might not belong to the newly updated master row.


    + Issue 2: Client Update Detail, Server Delete Master and Detail

     
    CLIENT SITE (SOURCE)                                 SERVER SITE (DESTINATION)        

    MASTER                  DETAIL                           MASTER        DETAIL    
        
    M_ID    VALUE        D_ID    M_ID    VALUE    M_ID    VALUE    D_ID    M_ID    VALUE        
    m1        v1               d1         m1         dv1        m1        v1          d1         1           1        
    m2        v2               d2         m2         dv2        m2        v2          d2         2           2        
    m3        v3               d3         m3         dv3’       m3        v3          d3         m3         dv3    

    Steps:
        +     SERVER: Delete Master(m3, v3) and Detail(d4, m3, dv3)
        +     CLIENT: Update Detail(d3, m3, dv3’)
        +     When running Sync, the conflict ClientUpdateServerDelete will occurs
        +     In this situation, there will be only option to let SERVER-WIN, otherwise the data will be inconsistent and exception will occurs.


    + Issue 3: Client Delete Master and Detail, Server Update Detail
     
    CLIENT SITE (SOURCE)                                 SERVER SITE (DESTINATION)      

    MASTER                 DETAIL                             MASTER            DETAIL              

    M_ID      VALUE     D_ID    M_ID    VALUE      M_ID    VALUE  D_ID    M_ID    VALUE      
    m1          v1            d1        m1        dv1            m1        v1        d1         1          1      
    m2          v2            d2        m2        dv2            m2        v2        d2         2          2      
    m3          v3            d3        m3        dv3            m3        v3        d3         m3       dv3'     

    Steps:
        +     SERVER: Update Detail(d3, m3, dv3’)
        +     CLIENT: Delete Master(m3, v3) and Detail(d3, m3, dv3)
        +     When running Sync, the conflict ClientDeleteServerUpdate will occurs
        +     In this situation, there will be only option to let CLIENT-WIN, otherwise the data will be inconsistent and exception will occurs.


    + Issue 4: Client Insert Detail, Server Delete Master
     
    CLIENT SITE (SOURCE)                                SERVER SITE (DESTINATION)      

    MASTER                DETAIL                            MASTER               DETAIL                   

    M_ID    VALUE     D_ID    M_ID    VALUE      M_ID    VALUE     D_ID    M_ID    VALUE           
    m1        v1            d1        m1        dv1            m1        v1            d1        1        1           
    m2        v2            d2        m2        dv2            m2        v2            d2        2        2           
    m3        v3            d3        m3        dv3            m3        v3                           
                                                                     

    Steps:
        +     SERVER: Delete Master(m3, v2)
        +     CLIENT: Insert Detail(d3, m3, dv3)
        +     When running Sync, there is no conflict, but this case will raise SqlException when adding Detail(d3, m3, dv3) to Server but there is no Master record for it.
    Wednesday, November 19, 2008 2:13 AM
  • Does anyone have solution? This make me crazy!
    Wednesday, November 19, 2008 3:38 AM
  • Sorry, but as stated in an earlier reply, there is no support currently in the sync framework for dealing with this

    kind of conflict problem.

     

    We hope to have better support in a future release.

     

    Friday, November 21, 2008 9:10 PM
  • after of 3 day of hard work, i think this will hep a bit
    this will solve ClientDelete(Master&Details) - ServerUpdate(Master) issue

    first, you need to add this code to ApplyChangeFailed event handler of server side:

    Code Snippet

    Private Sub _serverSyncProviderDong_ApplyChangeFailed(ByVal sender As Object, ByVal e As Microsoft.Synchronization.Data.ApplyChangeFailedEventArgs) Handles _serverSyncProviderDong.ApplyChangeFailed

    'declare a table that will hold data of details table to be keep

    Private _updateConflictTable As New DataTable("updateConflictTable")

    'In common situation we only need to keep the name of the details table, column name that use for store foreign key and column foreign key data to be keep.
    Dim tableName As DataColumn = New DataColumn("tableName", GetType(String))
    Dim columnName As DataColumn = New DataColumn("columnName", GetType(String))
    Dim guidData As DataColumn = New DataColumn("guidData", GetType(Guid))

    _updateConflictTable = New DataTable("updateConflictTable")
    _updateConflictTable.Columns.Add(tableName)
    _updateConflictTable.Columns.Add(columnName)
    _updateConflictTable.Columns.Add(guidData)


    'handle ClientDeleteServerUpdate case:

    If (e.Conflict.ConflictType = ConflictType.ClientDeleteServerUpdate) Then
    'Get the ID for the deleted row from the client data table,
    'and add it to a list of GUIDs. We keep rows at the server & client
    'based on this list.

    'Determine if current table has child relations with other table
    For Each relation As DataRelation In e.Conflict.ClientChange.ChildRelations
    'search for child rows that belong to rows of current table
    'those column in the details table that will be del if we don't handle them
    For Each row In e.Conflict.ClientChange.Rows
    For Each childRow As DataRow In row.GetChildRows(relation)
    'add to table that hold the affects data
    'in this situation I keep column:table name, key column name, column data
    'we will going to make them updated on the server, so the conflict will raise
    'on details table

    _updateConflictTable.Rows.Add(relation.ChildTable.TableName, relation.ChildKeyConstraint.Columns.FirstOrDefault().ColumnName, childRow(relation.ChildKeyConstraint.Columns.FirstOrDefault().ColumnName))
    Next
    Next
    Next

    'Now update row on details table at server, we do this for client to change at the
    download phrase

    For Each row As DataRow In _updateConflictTable.Rows
    Dim updateTable As New SqlCommand()
    updateTable.Connection = CType(e.Connection, SqlConnection)
    updateTable.Transaction = CType(e.Transaction, SqlTransaction)

    Dim strQuery = <string>
    UPDATE '<%= row(0) %>' SET '<%= row(1) %>' = '<%= row(2).ToString() %>' WHERE '<%= row(1) %>' ='<%= row(2).ToString() %>'
    </string>

    updateTable.CommandText = strQuery.Value
    updateTable.ExecuteNonQuery()
    Next

    'keep server untouched
    e.Action = ApplyAction.Continue

    End If


    End Sub


    Compile and run the code, you will notice that record at both server & client side deleted after update too!
    When in debugging, i check "e.Conflict.ClientChange.ChildRelations" is nothing in there, sync framework doesn't add it for us. So we must go to client side and add the relation manually to dataset.

    Go to ChangesSelected event handler of ClientProvider and paste this code:
    Code Snippet
    Private Shared Sub localDBProvider_ChangesSelected(ByVal sender As Object, ByVal e As Microsoft.Synchronization.Data.ChangesSelectedEventArgs) Handles localDBProvider.ChangesSelected
    Dim localDataSet As DataSet = New LocalCache()
    Dim changeDt = e.Context.DataSet

    'search for relation on client dataset
    For Each relation As DataRelation In localDataSet.Relations

    'Building new relation on Context.DataSet based on the relation of local dataset
    Dim changeRel As DataRelation
    Dim colRelParent As Array = Array.CreateInstance(GetType(DataColumn), _
    relation.ParentColumns.Count)
    Dim colRelChildren As Array = Array.CreateInstance(GetType(DataColumn), _
    relation.ChildColumns.Count)

    'search for primary columns of relationship
    For i As Int16 = 0 To relation.ParentColumns.Count - 1
    colRelParent(i) = _
    changeDt.Tables(relation.ParentTable.TableName).Columns(relation.ParentColumns(i).ColumnName)
    Next

    'search for foreign columns of relationship
    For i As Int16 = 0 To relation.ChildColumns.Count - 1
    colRelChildren(i) = _
    changeDt.Tables(relation.ChildTable.TableName).Columns(relation.ChildColumns(i).ColumnName)
    Next

    'construct new data relation
    changeRel = New DataRelation(relation.RelationName, colRelParent, colRelChildren, False)

    'now add to dataset, this dataset will be sent server at upload phrase
    e.Context.DataSet.Relations.Add(changeRel)

    Next

    End Sub

    Last things to notice: in your synchonize function, please add the master table before the details table in the SyncGroup before call Synchronize() method.

    Now recompile and everything should work!


    Hope this help.

    Monday, November 24, 2008 10:16 AM