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.

Sorry for the wasted post.

My BO is mapped to the Database Deployment Toolkit. Is there a table or primary key property in the DDT that sets the BO to PrimaryKeyIsAutoIncremented to true and PrimaryKeyIsUpdatable = false? The DDT field property does not have auto incrementing enabled and "Primary Key is Updatable" is false. The table properties "Automated Primary Key Index Creation on Structure Saves" is true.

Just wondering if my DDT is wrong as I would ideally like all my BOs to default to PrimaryKeyIsAutoIncremented to false and PrimaryKeyIsUpdatable = true.

Thanks

I won't be able to help you with the DDT thing.  I was wondering of you could enlighten me a bit, though.  It seems odd to me that anyone would want to make a primary key updateable (an exception would be one-time data migration, I suppose).  Could you provide a real-world example where this type of thing is the best solution for data storage and retrieval?  I use primary keys everywhere...not a one is updateable.

Thanks for helping my curiosity!
Bill

They added the PrimaryKeyUpdatable to remove the primekey from the update statement that is generated by the datalayer.

So with PrimaryKeyUpdatable set to false when a record is added and an insert is done it includes the pk field in the insert statement, however when that same record is later updated the pk field will not be included in the update statement.

Now if you edit a record and did not change the value of you pk field and PrimaryKeyUpdatable is set to TRUE then when you save the update statement includes the pk fld and it is updated with the same value so no harm really comes from it.

The SF guys added this property at my request I am using guid primekeys and merge replication. I have the prime key set as the rowguid for replication, if you try to update the pk field even with the same value it throws a sql exception which makes perfect sense.

I guess you can look at PrimaryKeyUpdatable = IncludePrimaryKeyFieldInUpdateStatement. It is probably not a bad idea to set it to false and turning it on only when you explicitly need to update a pk value.

I hope that helps am makes sense.

Paul

Hi Paul,

Yes it does at least to me   In my VFP application all my PK where GUID based since the creation of the GUID was very easy and light and it helped me when doing some remote synch with VFP tables.  I still have some projects which will be re-done in SF and the inclussion of this functionality will allow me to still use those GUID fields.  A couple of weeks ago, I did a small test of this functionality and it does work like a charm!

Guids as primekeys work fine with SF.

The issue I had was because I was using my guid primkeys as a merge replications rowguid column, This caused some issues that required the addition of the PrimaryKeyis Updatable property.

So If you are not using merge replication with guid primary keys and have set the merge replications rowguid field to the Prime key then the PrimaryKeyisupdateable setting wouldnt matter.

Here is some logic I have in my base class BO to assign the PK which may help you out if you haven't added it yet.

Paul answered the question as to why the field property exists in the DDT perfectly.  As for having a property that sets these properties, you really just need to create a base BO and inherit your BOs from the base BO with these properties set, this way you don't have to set them each time.

Note: You will need to be on 1.6.6 beta and load the attached business assembly into the GAC in order for this to work.  I had to add the Overridable tab to the PrimaryKeyIsAutoIncremented and PrimarykeyIsUpdatable properties.

Hi Paul,

Thanks for the code must appreciated.  Of course I did not have it since I am currently not using GUID for the current project, but yes, I will use it.

By taking this type of logic into account, we turned a query taking 4.5 seconds the other day on a very large database into 22 ms....that was a happy day   We made an even larger impact when dealing with dates.  Instead of storing these fields that we will be testing on with >= or <= as a date, we stored them as ticks in a BigInt field.  This was the largest change in performance that we had seen thus far.  Databases deal with integer data better than strings when it comes to performance and parameters.  You won't have as large a hit on a PK field, but you don't want to box yourself into a corner either.

I am very impressed with the way SF seems to give the best of both worlds in one sense - handling the fk thing with integer keys with no problem. 

Does the newsequentialid() function in SQL 2005 change any of the thinking about considering use of UIDs?  would it be difficult to have newsequentialid() used to create UIDs on the back end? 

I'm pretty sure I'll be using Int keys in SF for the most part, but just wondered about the practicality of sequential UIDs ?

http://www.fotia.co.uk/fotia/DY.19.NewSequentialId.aspx

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

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.

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.

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.

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.

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.

Thanks Trent.

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

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?

As I understand it is the presence of the network card's mac address (on the machine creating the GUID) in the GUID algorithm assures uniqueness.

Peter

While I like the idea of sequential GUIDs the problem with UuidCreateSequential is it still uses the mac address and it may only guarantee uniqueness on the local PC if the PC doesn't have a network adapter.

Hummm,  not NIC, not program to run

I have been sick over the weekend and my wife is trying to keep me in bed so I don't get worse...but she had to go to the store so I thought I would do a little catching up on the forum.

To answer your question, yes, the DDT will most definitely update a database structure for a SQL Server that uses replication.  When doing this, however, it is best to update the structures when there will be the least amount of activity on the databases (none if possible).  but in any case, you can actually write a program to deploy your structures using the DatbaseMigrator class.  There is a sample that comes with the framework that shows how to do this.  You can create a program that updates all servers on separate threads at the same time...reducing deployment time and making the process more streamlined.  It is the same thing that you would do otherwise, just updating multiple servers on different threads at the same time.

On a separate note here, there has been a lot of discussion on GUIDs and sequential GUIDs.  There is one problem wth sequential GUIDs that exists when you will not be talking to a server and you need to come back in and merge the data.  It is the same problem that exists when using integers.  If you come up with a mechanism to create sequential GUIDs, and you take a laptop, let's say, offsite and it creates records, and there are other laptops doing the same thing, then you can definitly have sync issues.  So this then turns into the situation where each laptop has its own pre-set range to work within, with ends up having the same issues as using a PK.

I recommend doing a little test.  I am of the mind that it is easier to deal with the one outlier that may (though it is extremely unlikely) produce a duplicate GUID.  If you create a program that creates new records non-stop using the System.Guid.NewGuid() for 24-48 hours (this will prouce some SERIOUS records!)  I believe that you will find that there will more than likely never be a duplicate.  If you del with disconnected data (pre-merge or replication) in thi maanner and perform a "pre-merge" query to ensure that there are no duplicates, then you don't get into the syncing issue of sequential GUIDs.  This is just some food for thought.

If you have an application that is going to replicate it would need a Nic to be able to do so , but I suppose it is possible that it could be an external type nic that is unplugged whilst the user is adding records. I went back and forth on the what method to use as well, below is some code that uses datetime ticks to sequence a guid.

As far as updating schema, with sql 2005 at least you can make some minor schema changes directly to the publisher. http://msdn2.microsoft.com/en-us/library/ms151870.aspx .

However it is easy to get A.F.U when doing anything major. I have not had to change much except a few alter table's to add a column or 2, but in my next major release I will probably be hating replication due to the number of schema changes I will be making, most likely I'll end up having to drop and re-add subsciptions.

In my case however I can restrict my users from using the software until the database and application has been updated. I create software for internal use only so I have more flexibilty than some when it comes to things like that.

So the DDT can handle replicated databases? That is brilliant!  SQL Server 2005 does allows some minor upgrades to a replicated database but it is not enough and on some occasions it has even rolled back a new field addition 24 hours after it was applied. Too many times the replication has to be dropped, the upgrade applied and then replication turned back on again. This in itself is not necessarily the problem but we could be dealing with 50G+ databases which are then pushed out to the subscribers.

While it is true most PCs will have an NIC, we are faced with the situation when a single site PC with no interface has been creating transactions for years which then has to be merged with another of our systems. Basically, in our application, we really need a globally unique ID and I can't see beyond the ugly Guid(). In our case the sequential ID isn't guaranteed to be globally unique and wouldn't be any different to the integer mechanism we currently use - which still has conflicts from time to time.

Aaron

The really nice thing that has been added in the 1.6.6 beta are pre and post deployment scripts.  This allows you to turn this off if you need to....while still using the DDT.  This has become a very handy implementation that we have started using ourselves (pre and post deployment scripts that is )

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.