Getting primary key following SQL INSERT

I have a Windows Application that I am designing an API for.

There are several database tables involved and the primary keys are surrogate keys and not identity.
Currently I hold all additions to these tables into a collection. Then when Save is cliced I run through the collection and execute the save operations to the database.

Each table is dependent upon a userID that I am getting when the user clicks "Create New User". Now I have an issue, should I get the surrogate key at the time I save to the collection or should I get when the user clicks Save.

I am not sure if there is a different reason to not do one versus the other.
Any help would be greatly appreciated.

Kim Denniston, Jun 03, 2011

Reply 1

Kim:

Can you explain what you mean when you have primary keys that are surrogate keys but not identity?
The question is who generates the surrogate key, that database or the code?

Basically when you save a record, you want to have the code generate the key (say a GUID), or the database generate the key (using Identity or autonumber). In the first case you have the primary key immediately and not special actions are required. In the second case you need to retrieve it immediately following INSERT by using SELECT @@IDENTITY or something similar (depending on your db and db version).

Hope this gets you going.

Trey Henlon, Jun 07, 2011

public abstract class ComputingMachine //or AbstractCalculator { } Then inherit all calculators from ComputingMachine.

Jan 19, 2012

Suppose I have to design different type of calculators, then what should I use as my base, an abstract class or an interface and why?

Feb 23, 2011

This is a good down to earth analogy.

Feb 16, 2011