locked
INSERTS without unique identifier RRS feed

  • Question

  • Hello,

    I am trying to convert an existing mobile solution into the .NET Compact Framework with the MS Sync Framework.
    Because I have to leave the existing data table structure untouched for downgrade-compatibility,
    it is hard for me to change my primary key from int to an unique identifier.

    But if I understand right I have to use a unique identifier to manage the possible INSERT-conflicts while inserting data
    on several mobile clients.

    My question is now, if I  REALLY have to use the unique identifier column or if it is possible
    to let the SQL Server (on the server side) create the column ids.

    I believe I can do this if I modify the Sync-Insert Statements which where generated with MS Visual Studio 2008
    and delete the ID-Columns out of the Insert statements.

    E.g.:

    INSERT INTO tableA ("DATA_ID", "DATAVALUE1", "DATAVALUE2")...
    becomes:
    INSERT INTO tableA ("DATAVALUE1", "DATAVALUE2")...

    But this won´t work because the sync process does not retrieve the server side generated IDs to update the
    client sql server ce database after the sync process.


    I hope this is enough to make my problem clear.
    Please let me know if you need any further informations.


    Regards,
    Martin

    • Moved by Hengzhe Li Friday, April 22, 2011 5:07 AM (From:SyncFx - Microsoft Sync Framework Database Providers [ReadOnly])
    Thursday, February 26, 2009 5:35 PM

Answers

  • I think you could use INTEGER typed primary key for the sync identity use.  A special attention is needed that Primary Key update is not supported by the Sync Services for ADO.NET.  If you have to update a Primary Key, please do

    1. Delete the row using the old key value
    2. Add a row back with the new key value

    In order to prevent the PK collision, please try to assign different integer range as the PK value range on different devices and the Server.  If the PK collision happens, please define your conflict resolution policy and handle it in the ApplyChangeFailed event (server or client or both).

    As for PK INSERT/INSERT conflicts, the unique identifier column won't help much if the PK column is still on the table schema.

    Thanks.
    Leo Zhou ------ This posting is provided "AS IS" with no warranties, and confers no rights.
    Thursday, February 26, 2009 7:00 PM
    Answerer

All replies

  • I think you could use INTEGER typed primary key for the sync identity use.  A special attention is needed that Primary Key update is not supported by the Sync Services for ADO.NET.  If you have to update a Primary Key, please do

    1. Delete the row using the old key value
    2. Add a row back with the new key value

    In order to prevent the PK collision, please try to assign different integer range as the PK value range on different devices and the Server.  If the PK collision happens, please define your conflict resolution policy and handle it in the ApplyChangeFailed event (server or client or both).

    As for PK INSERT/INSERT conflicts, the unique identifier column won't help much if the PK column is still on the table schema.

    Thanks.
    Leo Zhou ------ This posting is provided "AS IS" with no warranties, and confers no rights.
    Thursday, February 26, 2009 7:00 PM
    Answerer
  • Hello Leo,

     

    thank you very much for your answer. Using an Integery primery key sounds a bit more difficult than using an unique identifier.

    I will have a look at it.

     

    As a proposal for all the guys which write helpful tutorials...

    It would be nice if you all also use unique identifiers, e.g. in the " Synchronization Services for ADO.NET (Devices)" tutorial

    or even mention the problem that it´s better to use unique identifiers and that the Sync Framework does not handle PK-Conflicts.

     

    Regards,

    Martin



    Friday, February 27, 2009 8:54 AM
  • Hello Leo,

    I am still working on my problem. Can you maybe give me an example what you mean with:

    If you have to update a Primary Key, please do

    1. Delete the row using the old key value
    2. Add a row back with the new key value



    On which event (in which class) do I have to handle this?
    I am not very familar until now with writing much of the Sync-Code by myself. I always used the Sync-Generator tool for local databases in VS2008.


    Because I am using a Sync-WebService to synchronize my mobile devices, I had the idea to code a "PK Generator WebService".

    If I have a new row on my mobile devices I want to ask the PK Generator WS to do an empty entry in the Server Database and give the created ID back to the client.
    The Client deletes its row and creates a new one with the given ID and then starts the Sync-Process, which means that the client will always have only Updates and deletes, but never a new Insert.

    Could this work?


    Another question.....
    I think at the moment the SyncF ramework works with the same SQL-Queries for Client and Server. Is that true?
    Can I specify another Insert-Command for the Client which is not the same like on the server?




    Thank you in advance,

    Martin

    Friday, March 6, 2009 9:44 AM
  • Hi
    So as and when u want to insert a new row, you must have the connection to talk to WS?

    But i think for mobile case it does not guarantee that u will  always be connected.
    I m interested in this topic too and currently doing reasearch on it.
    Thats  why.

    :)

    Regards
    Bo Bo
    Tuesday, March 24, 2009 1:19 AM
  • Hi Bo Bo,

    sure if you want to sync, you need an established connection to the WS.
    But this is a general problem and does not depend on the WS. You always need a connection to any device you want to sync with.

    For this reason I am using a local replicated MS SQL CE database on the mobile device.
    I sync with the WS when I have a connection so I have a replicated database on my mobile device and can work with it.


    Regards,
    Martin
    Tuesday, March 24, 2009 11:31 AM