none
Identity Crisis workaround issues RRS feed

  • Question

  • Hi All,

    OK I have an identity crisis - well my software does anyway.  I bet everyone makes that ____ joke eh?

    Keeping the information simple I have a table with 3 fields - ID, GUID and TITLE.  The ID is INT, the primary key and is set to auto increment.  The GUID field is set to NEWSEQUENTIALID.

    When inserting data into the server all works fine as you'd expect.  I can type something into the title column and the other two columns get created automatically. No suprise there.

    To allow me to carry on using the ID column in my web app what I want to do is allow the sync client to work with the GUID and ignore the ID column completely.

    To that end I tried to exclude the ID column from the fields in the sync.  That failed owing to it being the PK. 

    Then I tried to include the field but on the client I set the ID column to be a normal NULLABLE INT field and set the GUID column to be the table's PK.    This worked and allowed me to have a record with a NULL ID, GUID GUID value and the title that I set.

    On syncing back to the server I was expecting the upsync to put the data into the table and then on the downsync the ID would be changed to the value that you’d expect SQL to generate (as if the record was being inserted in SQL). 

    Nothing, nada, null.  The record stayed on the client.  Test records that I put straight into the server came back down to the client but not this client side record. If I manually entered a record with a value for the ID column on the client that went to the server but not this one with ID = NULL.

    So, after all that what I want to do is completely drop the ID field when syncing and only look at the GUID. BUT the ID field needs to stay as the primary key on the server.  In my DbServerSyncProvider part of the WCF service is there a way to change the PK of the table to be that of the GUID rather than the ID?  But retain the ID as PK for normal SQL functionality.

    Thanks in advance for any assistance.

    Regards,

    Jon

    Wednesday, September 29, 2010 3:04 PM

Answers

All replies

  • Hey Jon,

    Do you want to filter out the PK column during sync session? This is not-supported scenario. You could manually modify the Command/SP/Trigger to remove the PK column and might run into issues since this isn’t tested.

    Thanks,


    Ann Tang
    Wednesday, September 29, 2010 8:57 PM
  • not sure if it will work, but try defining the columns using DbSyncColumnDescription and manually adding them to the table description columns collection.

    e.g.,

    DbSyncScopeDescription myScopeDesc = new DbSyncScopeDescription("myScope");
    DbSyncTableDescription myTable = new DbSyncTableDescription("myTable");
    DbSyncColumnDescription myColumn1 = new DbSyncColumnDescription();
    myColumn1.UnquotedName ="myGuid";
    myColumn1.Type="uniqueidentifier";
    myColumn1.IsPrimaryKey=true;
    myTable.Columns.Add(myColumn1);
    myScopeDesc.Tables.Add(myTable);

     

    Thursday, September 30, 2010 12:03 AM
    Moderator
  • Thanks Ann and JuneT,  I'll look into both of these options.

    JuneT I've only used SyncGroups so far as I'm working with mobile devices - I was under the impression that in this case the scopes are not used?  More research I guess.

    Regards,

    Jon

    Thursday, September 30, 2010 7:28 AM
  • hi jon,

    sorry, didnt notice you were using the offline provider.

    for the offline provider, you can manually specify the structure of the table using SyncSchema or you can listen to the CreatingSchema or SchemaCreated on the SqlCeClientSyncProvider. you can look up How to: Initialize the Client Database and Work with Table Schema in the docs.

    Thursday, September 30, 2010 3:47 PM
    Moderator
  • Hi JuneT,

    No worries.

    Yeah that's what I did but because of the fact that the identity column was the PK, if on the client I left it set to null then when pushing up to the server the row didn't appear.  It stayed on the client just never appeared on the server.  I used the events that you mentioned to intercept the ID col and disable it when creating the schema.  Would I have been best removing it completely on the client or would that have caused issues higher up?

    To get around this I have set the PK to the GUID column.  I suppose that now the best bet is to put a clustered index on the identity column. 

    Regards,

    Jon

    Friday, October 1, 2010 7:37 AM
  • i think you can safely remove it on the client.
    Friday, October 1, 2010 10:36 AM
    Moderator