Hi,

the application will work with number ranges for person, company … entities, which are managed in a separate, central database table. So with each new instance of these entities, the primary key has to be determined via that table.

a SQL-script would look like:

UPDATE PrmiaryKeyTable SET LastId= LastId+ 1 WHERE IdType= “Person”;

SELECT NewPrimariKey = LastId FROM PrmiaryKeyTable WHERE IdType = “Person”;

INSERT INTO Person (PersonId, …) VALUES (:NewPrimaryKey, …)

What is the best and most general way to implement that within LLBLGen using selfservicing with VB.NET, .NET 2.0 and SQL Server

I think the best thing to do is to map the PrmiaryKeyTable into an LLBLGen Entity. (you can map tables from different databases/catalogs)

Then you can perform the above statements by LLBLGen objects/code (better in one transaction).

Firstly thanks for the answer so far.

Now I have some additional questions.

Because it could happen, that within the application there are several places from which new persons (for example) have to be inserted. On one hand I would like to place the update/select statement on the PrimaryKeyTable for the type "Person" at a central place immediately prior to the insert-command of the person. On the other hand there is just one and the same method ”Save” for insert, update and delete. And I don’t want to put the additional commands in the generated Code. My questions now are:

Where is the right place to put the “pre-insert” statements? What is to do to have no overhead in case of delete and update? How does the best practise solution look like?

Yet another help would be great.

thanks,

Ursula

And I don’t want to put the additional commands in the generated Code. My questions now are: Where is the right place to put the “pre-insert” statements? What is to do to have no overhead in case of delete and update? How does the best practise solution look like?

Better to have a BL method that performs the Insert of a new Person, and there you should have the pre-insert code. And you should call this method from the Presentation Layer whenever you want to create a new Person.

What is to do to have no overhead in case of delete and update?

What do you mean by overhead in this context? Please elaborate more.

Let me see whether I get it right what you said

Did you suggest

to overwrite the Save method in the BL to do the update/select just in case of insert (to have no overhead in case of delete/update) and than call the original Save method?

I’m not sure to know how to do that; could you give me a short VB scenario?

What is to do to have no overhead in case of delete and update?

If you are going to override methods or directly add the overhead code to the generated code: Deletion is performed by calling a Delete method, which has nothing to do with the saving. To differentiate between the Insert and Update, check for the Entity property IsNew. False means an Update, and True means an Insert.

Did you suggest to overwrite the Save method in the BL

I did not mean that, I meant you can have a BL method ("InsertNewPerson" for example) that handles the task of creating a new Person, and where you will have the overhead code to get the PK value from the other table.

You should not call this method when Updating or Deleting.

Ok, thank you! Now I understand how I should do it, when I want to insert a single new instance.

But I think that I can't perform it in that way for situations, where I "normally" would use a SaveMulti. How can I solve this?

Hi,

what dbms do you use? Did you make sure you couldn't get the expected behaviour with identity/sequence like native features?

I still think if you have to handle that pks table yourself, a specific trigger should be more robust than entities fetches/updates.

Now if you have to do it through llblgen, updating your pks table should be faster using an expression update query (look for "Expressions in entity updates" in the manual).

For SaveMulti, there is no trivial solution. Going with triggers would prevent that kind of difficulties

Hi,

"what dbms do you use?"

SQL Server, most probably 2005

"Did you make sure you couldn't get the expected behaviour with identity/sequence like native features?"

The customers of the application made the requirement to work with number ranges in a couple of predefined contexts. For Example, they must have them with there products, because different areas are responsible for different, closed product groups and they want to recognise that within the product keys. Same for the customer (entity) and… I know this is not normalized, not good Design…, but the customer insists on it

"I still think if you have to handle that pks table yourself, a specific trigger should be more robust than entities fetches/updates. " "Now if you have to do it through llblgen, updating your pks table should be faster using an expression update query (look for "Expressions in entity updates" in the manual)"

My Problem is not the update of the pks table, that really could be done with an after insert trigger very well or with a expression update or …

My problem is to determine the pk value which should be used with the insert.

As I know I can’t have something like a return Parameter from a before insert trigger which can be included within the insert statement. I can’t insert the pk during a before insert trigger, because afterwards I would have to do an update and not the posted insert And I can’t leave the pk empty and post an after insert trigger to store the correct value, because of pk constrain violation during the insert itself

So for the moment, I don’t know any DB solution for that problem, but perhaps there is something I don’t know yet. For the moment it is in my mind, that I have to solve it in bl. But perhaps you have another good idea for me; I would appreciate it very, very much, if you have another tip for a method to solve the problem

thanks, Ursula

ursula wrote:

As I know I can’t have something like a return Parameter from a before insert trigger which can be included within the insert statement. I can’t insert the pk during a before insert trigger, because afterwards I would have to do an update and not the posted insert And I can’t leave the pk empty and post an after insert trigger to store the correct value, because of pk constrain violation during the insert itself

You should look for the keywords "instead of insert". That will do what you need with insert triggers

I didn't know the "instead of insert" trigger. I try it and it works very well

Thank you very much!!!