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.

Oops! Just noticed that PrimaryKeyIsAutoIncremented was set to true. Making it false and PrimaryKeyIsUpdatable to true works.

Sorry for the wasted post.

It's me again

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

Hi Aaron!

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

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!

Edhy Rijo

Hi edhy,

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.

Edhy Rijo

One word of caution, if you are not planning on using replication you should not use GUID primary keys!  They create a lot of additional overhead and dramatically slow down queries on the SQL side versus an integer value.  So if you are planning on your databases getting very large, then I would highly recommend against using a GUID.  There is definintely a time and place for GUIDs and Paul's application is one of those, but if you are creating GUIDs just to create a GUIDs PK, then I would strongly recommnend against doing this.  We have been spending a lot of time in T-SQL lately and working on very complex queries and tracing execution paths...when you have a GUID as a primary key versus an integer, especially on a large database, the query can execute as much as 100 times slower (yes times, not percent).  A query that took 300 ms went to 3 ms.  So as the database becomes much larger and this same query took 1 second it would go from being 1 second to 10 ms.  This is a huge difference!

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.