Hi,

I just recently started using LLBLGEN (Migrating our ERP product from Gupta SQLWindows to .NET) and so far, it has been very productive. We chose LLBLGEN after evaluating several DAL tools (at least 5) in order to have something similar to what we had developed in SQLWindows. Very well Frans!

Coming from a non Microsoft world, I still don't understand why MS still insists in using DataSets ( a la Borland)...but that's another story .

Now, I have a business scenario that I am not completely sure how to implement it:

• I have an Invoice, InvoiceDetails, Product, WarehouseStock and Stock Transaction entities. The problem I am having is deciding how to handle the following use case:

• The user enters an invoice which is handled by an InvoiceManager class.

• The InvoiceManager class has to save the invoice and details
• Foreach InvoiceDetail, the invoice manager will update the warehouse stock for the designated product and it will generate a StockTransaction.

At first sight it seems trivial but:

a. I am using the adapter model since our application must run with several databases. b. We don't use database triggers or SPs to mantain portability of our application c. The StockTransaction entity is numbered via an internal sequence table which must be uptated and retrieved at the time of insertion. We don´t use DBMS sequences due to portability issues. The reason to update then retrieve is to establish an instantaneous lock on the table in order to avoid collisions with other users. This method works very well in our proevious version of the software (built with Gupta SQLWindows).

Since there are no "lifetime events" (Before/After Insert, Delete, Update), and there is not, to my knowledge, a way of overriding the Save/SaveCollection methods, I have the following questions:

1. Do I need to traverse the InvoiceDetail collection in order to update the WarehouseStock entity? This has to be done at the time of Invoice creation, since other users might be updating the WarehouseStock as well...so the WarehouseStock entity cannot be retrieved before. Obviously, seems like I cannot save the InvoiceDetail Collection directly.
2. How to implement the sequence generation using a DB Table that contains tablenames and sequences? Remember that in order to be effective, the update and retrieval of sequences must be done at the time of insertion (not before).

Now that I wrote it, some ideas come to my mind. Nevertheless, I will appreciate any feedback...

Thanks

Alfredo Santizo

Coming from a non Microsoft world, I still don't understand why MS still insists in using DataSets ( a la Borland)...but that's another story Wink.

hmm.. The Dataset that Borland always had is not even close to the Abortion of a Dataset that MS implemented. The Borland dataset was actually a DataTable with the DataAdapter and command object combined. - Neater mesa thinks!

If it wasn't for OR Mappers we all would have spent all our days writing retrieval logic using the Connection, Command, DataAdapter and Dataset - Gee i don't even want to think about it.

Alfredo wrote:

1. Do I need to traverse the InvoiceDetail collection in order to update the WarehouseStock entity? This has to be done at the time of Invoice creation, since other users might be updating the WarehouseStock as well...so the WarehouseStock entity cannot be retrieved before. Obviously, seems like I cannot save the InvoiceDetail Collection directly.

The adapter pattern supports transactions, but I do not beleive that you can control the isolation level of the transaction like you could in COM+. If the orders / invoicing system was a seperate system from the inventory system, I would use COM+. However, you can still use an entity collection and all updates to the collection would be performed within the scope of the same transaction if one already existed (or a new one would be created.)

Here is an excerpt from the llblgen pro help file:

Updating entities in a collection in memory When you have loaded a set of entities in a collection and for example have bound this collection to a datagrid, the user probably has altered one or more objects' fields in the collection. You can also alter the fields yourself by looping through the objects inside the collection. When you want to save these changes to the persistent storage, you can use all save methods of the objects inside the collection, but you can also use the SaveEntityCollection() method of the DataAccessAdapter object which walks all objects inside the collection and, if the object is 'dirty', (which means, it's been changed and should be updated in the persistent storage) it is saved. This is all done in a transaction if no transaction is currently available. (See for more information about transactions the section Transactions).

If you implement IDataAccessAdapter OR Inherit from DataAccessAdapterBase you can override SaveEntity and SaveEntityCollection.

If each InvoiceDetail had a relation with a WarehouseStock entity, you should be able to traverse the InvoiceDetail collection and update each WarehouseStock entity. You might even be able to use a prefetch to fetch all WarehouseStock records for each InvoiceDetail record.

My question to you is this... is your question really about concurrency? Meaning that if there is no stock then you cannot create an invoice detail with the associated product? What should happen if Bob orders 5 widgets and Tim orders 6 widgets and there are only 10 widgets in stock? If you needed Tim's order to go through regardless, then you cannot have Bob's WarehouseStock update block Tim's invoice creation (enter COM+ and isolation level).

1. How to implement the sequence generation using a DB Table that contains tablenames and sequences? Remember that in order to be effective, the update and retrieval of sequences must be done at the time of insertion (not before).

Not sure that I follow this question.

Devildog74 wrote:

My question to you is this... is your question really about concurrency? Meaning that if there is no stock then you cannot create an invoice detail with the associated product? What should happen if Bob orders 5 widgets and Tim orders 6 widgets and there are only 10 widgets in stock? If you needed Tim's order to go through regardless, then you cannot have Bob's WarehouseStock update block Tim's invoice creation (enter COM+ and isolation level).

Not really. The problem I was facing was deciding how to handle the WarehouseStock updates. I cannot use prefetch paths because they will contain a snapshot of the stock records that could had been modified by another user or application. The solution to this issue would be to have the InvoiceManager invoke a "WarehouseManager" that will create or update the WarehouseStock table at the time of insertion/update/delete for each invoice detail.

1. How to implement the sequence generation using a DB Table that contains tablenames and sequences? Remember that in order to be effective, the update and retrieval of sequences must be done at the time of insertion (not before).

Not sure that I follow this question.

The issue in this case, that I am still trying to figure out is that as I explained, we don't use identity fields in our database. What we use is a table called SEQNOS which contains two fields: table_name, sequence.

So, in order to assign sequences (id's) to the invoices, we would do something like this (pseudo-code) at the time of Invoice insertion:

update seqnos set sequence = sequence + 1 where table_name = 'Invoice' select sequence into :nMySequence where table_name = 'Invoice' Invoice.InvoiceNo = nMySequence

And all this has to be done in the same transaction scope as the Invoice insertion.

Again, I think that using a "SequenceManager" embedded in the same insertion service of the InvoiceManager I can accomplish this...

I am becoming a Manager Pattern addict....I come from a pure Domain model pattern environment.

Thanks

Again, I think that using a "SequenceManager" embedded in the same insertion service of the InvoiceManager I can accomplish this...

Basically you would want the InvoiceManager to handle it's sequence. I had to do this with order-orderdetails on my app to support revisions. The OrderManager handles creating the new orderId with the correct revision tag. Why do seperate managers when you will only be using them combined, I think in that case you should do one manager.