Values in Identity column

Values in Identity column

Old forum URL: forums.lhotka.net/forums/t/5088.aspx


gPlans posted on Saturday, July 12, 2008

When saving a business object that contains an identity column, the value is assigned by the DB automatically. Is there a way of assigning unique values to the identity column before the insert? i.e. before the business object is saved like ado.net's datatable or do I have to write such logic myself?

tetranz replied on Saturday, July 12, 2008

The usual way to do this is to assign negative numbers to the new objects. When they are saved, update it with the real id that you get back from the db. Keep a static variable in the class to remember the next temporary id and decrement when required.

gPlans:
When saving a business object that contains an identity column, the value is assigned by the DB automatically. Is there a way of assigning unique values to the identity column before the insert? i.e. before the business object is saved like ado.net's datatable or do I have to write such logic myself?

JonStonecash replied on Saturday, July 12, 2008

If you use an identity column, the only way to make that work is to allow the database to do its magic.  The identity column must be managed by the database to satisfy the requirements of serving multiple insert requests from from application instances.

One other choice is to use a GUID as the primary key.  You can generate the GUID value in the application and simply send the value to the database at the time of the insert.  A lot of people swear by this approach.  The downside is that the GUID is significantly harder to read, type, etc.  That makes debugging more difficult.  The GUID column also takes up 128 bits against 32 bits for the typical identity column. 

In my mind, the major (but not the only) discriminator between the two approaches is the "distance" between the application and the database.  If the application is "far away" in the sense of having to communicate with the database over a narrow (perhaps sometimes disconnected) pipe, the GUID makes a lot of sense.  I could build an application that runs on a disconnected laptop and creates GUID-keyed data on a local copy of the database, syncing up when it finally got a connection.  This would be a very workable solution.  On the other hand, if the application and the database were "close together" in the sense that there was a big pipe to the database and the application was designed on the premise that pipe was always available, the downsides of the identity column do not have much impact.

I suspect that there are other issues here, but this is my two cents.

Jon Stonecash

gPlans replied on Saturday, July 12, 2008

Hi,

 

Sorry guys, I left out some crucial information from my earlier post. Here it is:

  1. I am new to CSLA but not to .Net or to programming in general.
  2. My target database is an already existing and is being used by another application, so editing its schema is not an option. 

JonStonecash:

 

Using a GUID could have been the best option, if I could change the schema (my apologies for not supplying you with all the information)

 

tetranz:

 

Using negative numbers seem to be the only option since there is no possibility of having a conflict while updating to the database. I have done this before in ADO.NET datatables. My question is: since having objects with a unique key is an everyday requirement, and since Identity Columns are very common, is there a traditional way of doing this in CSLA build-in or otherwise? Note: I am talking about an object before it has been saved to the database since after saving, the assigned ID can be retrieved.

tetranz replied on Saturday, July 12, 2008

gPlans:

Using negative numbers seem to be the only option since there is no possibility of having a conflict while updating to the database. I have done this before in ADO.NET datatables. My question is: since having objects with a unique key is an everyday requirement, and since Identity Columns are very common, is there a traditional way of doing this in CSLA build-in or otherwise? Note: I am talking about an object before it has been saved to the database since after saving, the assigned ID can be retrieved.



There's no built in way of handling it. That's because Csla doesn't assume any type of data access technology.

The negative numbers work fine, involves very little code and ensures that the object always has a unique id which Csla requires if it's a child object in a collection. I don't have my standard code at my fingertips but it's something like:

private static int _tempId;  // (that's at the class level);

DataPortal_Create()
{
  _id = --_tempId;
}

I usually use stored procedures with SQL Server. My Id parameter is marked as OUTPUT in my insert sprocs. @Id is set to SCOPE_IDENTITY() after the insert.

DatePortal_Insert
{
  _id = Get new value of parameter after execution.  Sorry I don't have the exact line of code. I think it's just the Value property of SqlParameter.
}

Some of my WinForms use the id to control single instance. I need to update that after a save.

Cheers
Ross

gPlans replied on Saturday, July 12, 2008

tetranz:

There's no built in way of handling it. That's because Csla doesn't assume any type of data access technology.

Thanks so much, that is the confirmation I needed. By writting custom code, I know I will not be re-inventing the wheel.

Regards,
Joe

Copyright (c) Marimer LLC