Local Database Cache and Identity Columns RRS feed

  • Question

  • Ok, I am trying to use the Sync Framework for the first time instead of writing my own framework for an occasionally connected application.  This application is connected to the Internet via a cellular wireless modem (slow) so I want to cache the database locally to improve performance. At one point I thought I had it all working but now I am having issues with adding new records to tables with Identity columns for primary keys.

    I am using Datasets and binding sources.  When a new record is added through the binding source, the primary key value is getting a 1.  When I update the dataset, I get a primary key violation because that primary key already exists and was brought down from the server during the synchronization.  I verified that the primary key on the datatable in the dataset was AutoIncrement with a seed of 1 and step of 1. It appears it wants to start at 1 even though I already have records brought down from the server.

    At one point this was working  but broke when I changed the schema on the server database, dropped my local database cache and added it back in using the sync wizard.  Since then, it has not worked.  I also tried adding a record to the table directly through Visual Studio by opening the .sdf and doing show table data on the table.  I fill out every field but the primary key and when I arrow off the record, it give me a duplicate primary key error as well.  This tells me the issue is with the ce database and not my dataset definition in the application.

    While trying to figure out what is going on, I began to think about how the sync framework handles different scenarios. I know by default if the same record is updated on the server and client (i assume same is based on the primary key), the server wins but this can be changed.  The real question is how does it handle inserted rows.  Assuming the primary key issue is resolved that is.  Let say on the server I have 5 rows in Table A with primary keys 1-5. When I initialize the local database cache on two clients - 1 and 2, the ce database is build and those 5 records a insert into the local database with keys 1-5 and both clients. If client 1 inserts a new row and gets primary key 6 and client 2 inserts a new row and gets primary key 6, what happens when the both sync to the server?  Or, lets say the server inserts a row after client 1 syncs and client 1 inserts a row.  The server and client each get primary key 6 for their new records but how is that handled in synchronization?

    Thanks for your help.


    Thursday, February 14, 2013 7:38 PM

All replies

  • Ok, I resolved the identity column issue when inserting into the local database.  It appears the database was corrupt.  I did a repair from within visual studio and it started working.  Not sure why this happened and if I will need to do that after each sync.

    I tested the scenario of starting my application, synchronizing the databases and then adding a new record into the local database and also the server database.  Each of the records were assigned a primary key value of 9 with different data for the other fields.  When I synchronize, nothing happens.  The local record does not get pushed to the server and the server does not get pushed to the local.

    How do you handle this scenario with the sync framework. Without the ability to handle simultaneous inserts on multiple clients or the server and clients the framework seems pretty useless.  I cannot believe that is the case and know I am just missing something fundametal.

    Any help would be appreciated.


    Thursday, February 14, 2013 8:57 PM
  • Using identity columns as a Primary Key in distributed environment or in synchronization is not advisable. As you've already find out, two different replicas can generate the same PK value thus resulting to a conflict.

    If two clients insert the same PK, what do you want to do with it? Even assuming without using Sync Framework, how would you record that in your central database? 

    I suggest you have a look at the documentation: Selecting an Appropriate Primary Key for a Distributed Environment

    You're not seeing the changes you're expecting because there's a conflict.  

    Have a look at How to: Handle Data Conflicts and Errors

    Better, have a look at the documentation so you get the fundamentals.

    Friday, February 15, 2013 12:02 AM