StrataFrame Forum » .NET Forums » General .NET Discussion » GUID Primekeys

GUID Primekeys

Keith Chisarik — Tue, 01 May 2007 13:02:00 GMT

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.

RE: GUID Primekeys

Trent L. Taylor — Tue, 01 May 2007 13:02:00 GMT

We will add it to the list, Keith. ;)

RE: GUID Primekeys

Keith Chisarik — Tue, 01 May 2007 11:04:25 GMT

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 :D

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.

RE: GUID Primekeys

Keith Chisarik — Thu, 26 Apr 2007 09:14:55 GMT

OK thanks Ben.

RE: GUID Primekeys

StrataFrame Team — Thu, 26 Apr 2007 08:48:57 GMT

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.

RE: GUID Primekeys

Keith Chisarik — Thu, 26 Apr 2007 08:26:36 GMT

/shameless bump

RE: GUID Primekeys

Keith Chisarik — Wed, 25 Apr 2007 11:38:28 GMT

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.

RE: GUID Primekeys

Paul Chase — Wed, 25 Apr 2007 10:48:10 GMT

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

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

[codesnippet]

Protected Overrides Sub OnSetDefaultValues()

MyBase.OnSetDefaultValues()

'Set Value for GUID PK

'Get Reference To Primary Key Field Property

Dim LoPkField As Reflection.PropertyInfo = Me.GetType.GetProperty(Me.PrimaryKeyField)

'If the PK is a Guid Generate a Sequential GUID to prevent Index fragmentation

If LoPkField.PropertyType Is GetType(System.Guid) Then

LoPkField.SetValue(Me, BoCommon.NewSeqGuid, Nothing)

End If

'Set Value for Location Code

If Me.PopulateLocationCode Then

Dim LoLocation As Reflection.PropertyInfo = Me.GetType.GetProperty("LOCNCODE")

LoLocation.SetValue(Me,Payroll.Common.LocationCode, Nothing)

End If

[/codesnippet]

RE: GUID Primekeys

StrataFrame Team — Wed, 25 Apr 2007 09:25:25 GMT

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 ;))