Sign in
Microsoft.com
 
Microsoft
 
 
 

locked
Foreign key not updated in synchronization RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi everyone,

     I am facing an issue that I don’t know how to get rid of.

    Here’s the context:

    I have two tables Order and OrderItem that have the following structure in SqlCe database

    Order

    OrderItem

    Id

    Id

     

    OrderId

     

    There is a relation defined for Order.Id and OrderItem.OrderId. And both Id fields are Identity ones.

    I have an order entered by the clerk with the Windows mobile app, so after the operation I have the following records inserted:

    Order

    OrderItem

    Id = 1

    Id = 1, OrderId = 1

     

    Id = 2, OrderId = 1

     

    On the server side I have the same tables with both Id fields are Identity ones, but without relation between Order.Id and OrderItem.OrderId.

     

    Now the problem:

    After synchronization I end with these records in the server database:

    Order

    OrderItem

    Id = 125

    Id = 132, OrderId = 1

     

    Id = 133, OrderId = 1

     

    As you can see, the OrderId field for both records doesn’t match the Order.Id field. Note that both table have their SyncDirection to UploadOnly.

    How can I make sure that both OrderItem.OrderId correspond to the Order.Id field ?

    Best regards,

    Bernard

    Sunday, September 26, 2010 8:59 PM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    Sync Fx will not handle that automatically. As i have mentioned, the sync is done per table. So Sync Fx will have no idea what "new" orderid was inserted in the order table and apply it to the OrderItem table. It simply knows there are rows to be inserted for OrderItem and the values come from the client copy of the OrderItem table.

    If you can include at least the original orderid from the client in the server order table, you can try doing a look up and substitution in an orderditem trigger.  

    basically, you would use the original orderid in the orderitem dataset from the client to look up the matching original orderid in the server order table to get the new server OrderId and use that to do the insert to the OrderItem.

    Tuesday, September 28, 2010 1:50 AM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    you can look up "Selecting an Appropriate Primary Key for a Distributed Environment" in the documentation on guidelines for choosing keys in Sync Fx.

    Sync Fx is doing syncs by table and will not pick up the OrderId from the Orders table automatically so it can use it in the OrderItem table

    in your scenario, do you want to carry over the OrderId from the client to the server? or are you intentionally generating a new OrderId in the server when you do the upload?

     

    Monday, September 27, 2010 3:17 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    HI JuneT,

    Thank's for the answer.

    I already went through the "Selecting an Appropriate Primary Key for a Distributed Environment" article, but the server database is widely spreaded so I cannot change it unfortunately.

    In our scenario, the primary key for the Order.Id field is generated automatically since it is an Identity field. The OrderItem.OrderId field is not carried out to the server, but could be passed if needed.

    If the Sync Framework doesn't handle this scenario automatically, it would be cool to have an example somewhere on how to handle it, because it's a common scenario.

    Best Regards,

    Bernard

    Monday, September 27, 2010 3:30 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    going back to my question, do you want to keep to keep the same orderid from the client or do you want a new orderid on the server?
    Monday, September 27, 2010 4:04 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    I need a new orderId on the server

    In fact, according to my example the needed results are:

    Order

    OrderItem

    Id = 125

    Id = 132, OrderId = 125

     

    Id = 133, OrderId = 125

    Monday, September 27, 2010 4:14 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Sync Fx will not handle that automatically. As i have mentioned, the sync is done per table. So Sync Fx will have no idea what "new" orderid was inserted in the order table and apply it to the OrderItem table. It simply knows there are rows to be inserted for OrderItem and the values come from the client copy of the OrderItem table.

    If you can include at least the original orderid from the client in the server order table, you can try doing a look up and substitution in an orderditem trigger.  

    basically, you would use the original orderid in the orderitem dataset from the client to look up the matching original orderid in the server order table to get the new server OrderId and use that to do the insert to the OrderItem.

    Tuesday, September 28, 2010 1:50 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Thank's JuneT,

    To solve my problem, I created a new ServerSyncProvider where I've added a new event (OnInsert) to handle a new insertion on the server and update the DataSet accordingly. I did so, because I also need to handle synchronization with MySql databases while by default, the sync framework supports MsSql and Oracle databases.

    Once again, many thanks for your help.

    Best Regards,

    Bernard

    Sunday, October 3, 2010 7:18 PM