Hi,

I am considering LLBLGEN as an O/R mapper for a project that uses an existing SQL Server 2000 database. The database, however, doesn't use identity fields for database tables.

Instead, there is one special table which contains the 'identity' values for all tables (the table name serves as a key), and when an entry is inserted in a table, its 'identity' value is incremented and retrieved from the special table. Don't ask me whe, it's just the way it is, and it cannot be changed because other applications depend on this database.

A possible solution for this problem could be to encapsulate this bahavior in a stored procedures that first determines the key and then inserts the entry in the table. Is there a way to attach such a stored procedure to an insert action in LLBLGEN? Or perhaps there is a different solution which I haven't considered yet?

Ron

ronp wrote:

A possible solution for this problem could be to encapsulate this bahavior in a stored procedures that first determines the key and then inserts the entry in the table. Is there a way to attach such a stored procedure to an insert action in LLBLGEN? Or perhaps there is a different solution which I haven't considered yet?

Ron

It's better to work with triggers in this case. You can use a trigger (before insert) and set the sequence name for the column to @@IDENTITY. Check these threads: http://llblgen.com/tinyforum/Messages.aspx?ThreadID=7255 http://llblgen.com/tinyforum/Messages.aspx?ThreadID=4725

daelmo wrote:

It's better to work with triggers in this case. You can use a trigger (before insert) and set the sequence name for the column to @@IDENTITY.

Thank you for your answer, but I am not sure if I understand what you mean. Are you talking about a database trigger on the table to which I want to insert a new record, let's call it T_SomeTable? Wouldn't this mean that other applications which use the old meachnism of first calculating and retrieving the next primary key value from the special tables T_Sequence and second use this key as a primary key in the new record, are also influenced by this mechanism? And which @@IDENTITY value should I use, because the tables in our database do not have identity fields?

In my opinion any solution should increment the sequence value in the T_Sequence table, retrieve it and use it as a primary key for the inserted record in T_SomeTable. Preferably during 1 database call, hence my suggestion of using a stored procedure.

Ofcourse I can also retrieve the new primary key with a separate stored procedure call, and then set the class property, but since I am building a client-server application, in that case I would like to do this on the server side. I guess this means the database specific part?

Ron

I'm assuming you don't want to make significant changes to the database as it is used by other applications...?

I can't see an easy method to do this in one sql proc/transaction - it is just too far away from the way LLBLgen is designed to work.

One thing you could do is Override the Entity.OnBeforeEntitySave method - this would provide you with an entry point where each entity could retrieve the required identity value from the table before being saved to the database.

Have a look at the documentation

http://www.llblgen.com/documentation/2.6/Using%20the%20generated%20code/gencode_tapinroutinesevents.htm

Another way this could work is to have a global function that caches a batch of ids (say 100) for each entity (table) and hands them out as required. This would mean that you could fill them as you created the entities. It does mean that you may end up with gaps in your id sequences, but this is rarely a problem unless they are being used for things like Invoice numbers.

Matt

MTrinder wrote:

I'm assuming you don't want to make significant changes to the database as it is used by other applications...?

That's correct, although adding stored procedures wouldn't have been a problem, ofcourse!

MTrinder wrote:

I can't see an easy method to do this in one sql proc/transaction - it is just too far away from the way LLBLgen is designed to work.

I was hoping there was a similar way in LLBLgen like in the Entity Framework where you can attach stored procedures to insert methods. But this Entity.OnBeforeEntitySave method you were talking about, is it possible to call a stored procedure in there for retrieving the sequence number? And is it on the server side? Also your suggestion of caching ids can be a nice performance improvement.

Ron

You can do whatever you need to in the function (calling stored procs etc...)

It runs in code wherever the entity happens to be when you save it

Matt