SQL, Setting Primary Key ID value BEFORE INSERT

Mar 31, 2010 at 5:50 PM

Hi,

Our SQL server database does not use auto increment fields for the primary keys, rather it uses a sequence table much like Oracle and Postgres.  On insert I need to insert and query a sequence table before to retrieve the primary key before insert.

Example:

New Customer,
Set Name = "TEST";

Insert Customer using EntitySession

Before the insert I need to retrieve the Customer Id, Example:
INSERT INTO SEQ_CUSTOMER (comm) VALUES ('IQToolKit');
SELECT SCOPE_IDENTITY();

I've created an Column Attribute called SequenceTable which specifies a sequence table for any given column.

I'm unable to figure out where to slide in the insert statement.  I've read the blog posts and have set a considerable amount of breakpoint and event ran a code coverage test. 

Please help!
Thanks

Apr 8, 2010 at 10:48 PM

I was able to offer this solution by extending some some of the IQ Toolkit classes and overriding the methods.  If you would like details, please send a message as I don't have everything documented in one place.

The abstract is:

Created my XmlMapping which inherits from Data.Mapping.XmlMapping
Created a custom SqlProvider which inherits from Data.SqlClient.SqlQueryProvider
Created my own entity table which inherits from Data.EntityProvider.EntityTable<T>