I have an app developing that will include several wireless handheld barcode scanners running SQL Mobile, operating simultaneously alongside a winforms component. I am using SQL merge replication and RDA and like the concepts and implementation so far.



Now this works best with GUID primekeys so they are guaranteed to be unique across replicated copies of the tables on the handhelds.



So the question is this, any tricks or "things to know" when using GUID primekeys versus integers (as I have always used) in the framework? Do you recommend the default value of newid() ?



As always thanks for the insight.






Keith Chisarik

There are a few caveats for sure

First, if you want to use NewID() within SQL, you will have to create stored procedures for the INSERTs and configure the business objects to use them.  The reason is that there is no method like SCOPE_IDENTITY() within SQL Server that will return the last created NewID().  So, at the top of the sproc, you need to call NewID() and place it into the output parameter specified for the PK.  Then, you need to insert the record.

There is another option for GUIDs, and that is to use System.Guid.NewGuid() to create your Guids.  You can put the call within the SetDefaultValues() of the business object.  While you could technically get a duplication by letting each client create its own PKs, the MAC address of the computer is figured into creating the GUIDs (to help keep them unique since MAC addresses are unique) and you have a better chance of being struck by lightning, stung by a bee, and attached by a shark at the same time than you do of getting a duplicate GUID. 

So, either use sprocs for your INSERTs for the BOs or use the System.Guid.NewGuid().  Either way, there's not really any downside to using GUIDs for PKs (other than if you look at the database, an integer is easier to read than 32digit number with dashes )

I am using Guids as for my PK as well. I used the approach of overriding the set Default values in my subclassed Bas Biz object as below. I also call out to a function that returns a sequential guid that is similiar the NewSequentialId() function in SQL 2005 but you can replace "BoCommon.NewSeqGuid" with "Guid.NewGuid()"l.. Here is a link to an very informative artice about guids as pk's and the benefit of using sequential ID's 

http://www.informit.com/articles/article.asp?p=25862&rl=1

 

SQL merge replication creates the GUID primekeys for me when merging in new rows to the "publisher" database so no worries there.



One follow up question, I got this all working but had to specify the GUID primekey column as ROWGUID = YES on the field definition, I had to set it manually via SQL Management console because I couldnt find that setting in DDT, is there an advance button I am missing somewhere or another way to set "advance field properties"?



Ideally (in my brain at least) everything should be defined in the DDT and not require any "tweaking" upon deployment.



Thanks.

Keith Chisarik

/shameless bump

Keith Chisarik

Actually, the DDT does not have the functionality for you to set the RowGuid option on a column.  It's a pending feature request, but it has not been implemented.

OK thanks Ben.

Keith Chisarik

I wrote in BIG RED LETTERING on my deployment notes to remember to set the ROWGUID property on my deployment server at the customers site. Well as you can guess, I forgot so nothing worked for a while and I looked silly



There are other "advanced" field properties related to SQL merge and RDA replication that cannot be set via the DDT, if they could in the future that would be great.



Thank you.


Keith Chisarik

We will add it to the list, Keith.