I want to set my own value for primary keys but the BO.Save() method doesn't write the primary key back to the database.

For example, I have an Int32 primary key called "CustomerID" and I set the value in SetDefaultValues() and the value is unique. I call BO.Add() which sets the value of CustomerID. I check the value of BO.CustomerID and it is correctly set to a valid value. However, when I do BO.Save() the CustomerID field is not written back to the SQL database (I can see this in SQL Profiler).

I have tried setting PrimaryKeyIsUpdatable to true but I guess I haven't done it correctly as Save() never writes the primary key.

Can anyone help?

Thanks in advance.

This isn't the issue.  The issue is not when creating the PK, but rather when performing queries on the data.  In VFP we used to use Integer PKs which was extremely frustrating because they did not have the AutoInc stuff when we started (and their AutoInc is nowhere in the same universe as SQL Server).  However, since we used Integer PKs life did get much better once we got on SQL Server and did not have to convert from GUIDs to Integer PKs.

But back to the original point, the issue is not when creating the PK.  If you want to use a GUID PK, don't let the server assign it, just call the System.Guid.NewGuid() method in the SetDefaultValues of the business object versus relying on the server.  It is basically impossible to get two GUIDs of the same uniqueness anyway.

When using identiy fields in SQL Server, the BO has the ability to automatically retrieve the PK once a new record is created, which I am sure that you have already discovered.  So the issue isn't the whole NextId() thing that you faced in VFP, but rather when you start to query the data to retrieve records within the DB.

Hope that makes sense

Very interesting performance information about GUIDs which is a concern as we were looking to migrate to GUIDs from int PKs.

We have a legacy system that involves many distributed databases per customer. All databases must be updatable even if the WAN connection is down. As a result, our int PKs must be globally unique which is why we have to update the PKs from the application. We had planned on migrating to a new database model that used GUIDs but these performance figures are worrying.

Unfortunately, our PKs must be globally unique across all databases and other than the performance problem, GUIDs looked the easiest option for an automatic solution rather than our manual int solution.

In your case I would probably go with GUIDs so that you do not have to engineer the global PKs.  When weighing the costs between the two the ease of the GUIDs would win out in my eyes as we have tried to engineer the other behavior in more than one instance and it always ends up out of sync at some point which causes issues.  This would fall under the replication category that Paul was talking about earlier.

I think you are right Trent. Whatever way we have tried to engineer it, it still results in conflicts somewhere down the line. Of course, that leaves us with a real headache as we have a large no of customer databases that need to be converted from the old legacy database running with int PKs to a revised database with GUIDs PK.

Hi Aaron,

I have a VFP application installed in 17 branches which synchronize to the main office.  My solution was to used GUID PK and a BranchID field, the GUID works just find and in the main office where all the repository is there has not been performance issues because all the fields are properly indexed and the generation of reports is normal. 

I should note that the synch process is one way only from branches to the main office and that may not be your case.  So if I would have to do this project in SF I would need to re-think the logic since instead of synchronizing I would have all branches connected to the main office database using SF Enterprise server, but still using the GUID PK.

Edhy Rijo

Hi Edhy,

That is good to know. We have tested with GUIDs to a limited degree and it looked okay but our big problem will be converting legacy databases to a new format. Some of the databases are literally in use 24x7x365. But I guess that problem is for another day.

 Guys,

If you want to use GUID's then you definately will want to create the guid sequentially. using the Guid.NewGuid function will create a truly random unsequential guid which well cause performance to suffer. By creating a sequential guid you are ensuring that records are inserted at the end thus keeping page splits and index fragmentation to a minimum.This is primarily what causes perfomance problem's when people use guid's as primary keys.Using this approach you will approach insert and select perfomance that is much closer to using ints. You still will have the usual drawbacks of using guids they are ugly and big etc. There are several articles that detail this in more depth just google sequential guids.

I have 17 offices that I converted\converting from Foxpro 2.6. I had a very elaborate data merge process for consolidating data at corporate office that ran all night for several hours. I now use Sql merge replication and guids to great effect. One thing that is a business requirment for me is the abilitly for an office to function without an UP network connection to corporate thus a client server type of environment would not work.

I have not had any perfomance issues so far, Like anything else it always comes down to making a decision based on your unique business requirements.

Below is a code snippet to generate a SEQUENTIAL Guid and to add it in the default values of you base BO.

Wow Paul, thanks for sharing this info with us.

My next VFP conversion project will definately make use of this code since the use of a GUID PK is a must.

About the above comment, would you mind explaining a bit more about how to use SQL merge replication I also have those needs for my next conversion project.

Edhy Rijo

Edhy,

Here is a step-by-step on setting up Merge Replication.  Just serach on Google for about 1000 more articles.

http://www.databasejournal.com/features/mssql/article.php/1438231 

And Paul is right on the sequential GUIDs...that is definitely the safest route to go.

Great!

Thanks Trent.

Edhy Rijo

Like Trent said there are a ton of articles explaining the different types of replication written by people that know it and can explain it way better than I could.

Just like the decision to use integer or guid primary keys it really depends' on your business requirements as to what replication scenario would work best for you. Also do not forget that StrataFrame has the Enterprise Server which may be a better option again depending on what your requirements are.

Paul

One consequence of using any form of replication is future changes to the database schema. Most database schema comparison/updating tools have problems upgrading a database currently in use for replication. In an ideal world you would be able to change your database schema which would then be replicated across to other databases. SQL Server 2005 is better at allowing some updating but most upgrading tools will fail on a replicated database which could force you to remove replication, upgrade and then setup replication again.

While we are on the subject, does the Database Deployment Toolkit support upgrading of replicated databases?

I like the idea of sequential GUIDs but does this still guarantee they will be globally unique?

Today I started to implement GUID PK for a new SF project, and this thread showed to be an invaluable resource of information.

The code posted by Paul and Trent lead me to create my first Business Object base class and after creating 3 forms, you'll notice how useful is to have of a base BO class to handle things for you .

So I just want to say THANKS! to everybody who participated in this thread, (including me ) because it is worthy.

Edhy Rijo