sequential number

Posts   
 
    
braidiano
User
Posts: 40
Joined: 18-Nov-2006
# Posted on: 19-Dec-2007 19:40:18   

Hi,

LLBLBGen 2.5 DataAdapter

I have a InvoiceEntity with a SequentialNumber property (that rappresenting the same field in DB). This SequentialNumber is based on Year and InvoiceType. So I have a unique SequentialNumber for each year and for each InvoiceType. For example I have this record:

InvoiceID Year InvoiceType SequentialNumber 1 2005 Type1 1 2 2005 Type1 2

3 2006 Type1 1 4 2006 Type2 1 5 2006 Type2 2

6 2007 Type1 1 7 2007 Type1 2

I save my InvoiceEntity and other related entities by a UnitOfWork2, and actually before Commit()'s call, i get the next SequentialNumber by an Adapter.GetScalar() and a PredicateExpression based on InvoiceType and Year:

InvoiceEntity invoice;
UnitOfWork2 uow;

invoice.SequentialNumber = GetNextSequentialNumber(InvoiceType, Year);
uow.AddForSave(invoice,true);
uow.Commit(....);

... but I know that this is an unsafe way in a distributed scenario flushed

Are there any way in LLBL to execute the Adapter.GetScalar() (actually inside GetNextSequentialNumber method) call in the UnitOfWork2.Commit(), and so inside the InvoiceEntity saving transaction ?

regards Davide

jmeckley
User
Posts: 403
Joined: 05-Jul-2006
# Posted on: 19-Dec-2007 21:00:51   

there are 3 possibilities. 1. use a table trigger to update the field in the db instead of LLBL logic. Make SequentialNumber properties readonly on entity. 2. override the OnBeforeSaving function in DataAccessAdapter object. determine if the entity is an InvoiceEntity and if it IsNew. if so then set the sequence. not as safe as option 1. 3. place a UC on the Invoice table for the aggregated fields. catch this exception in your logic and attempt to resave the invoice.

I would place UC on the fields and insert trigger on the table to calculate the seq. no.

braidiano
User
Posts: 40
Joined: 18-Nov-2006
# Posted on: 19-Dec-2007 22:08:00   

thanks for your reply jmeckley, I prefer to do it via code (because it is an independent RDBMS way) , aren't there any other way except OnBeforeSaving overriding?

DvK
User
Posts: 318
Joined: 22-Mar-2006
# Posted on: 19-Dec-2007 22:34:55   

I would also use the OnBeforeSave event, but use something like a GetScalar in case the entity is not new to fetch the current sequence, OR use a scalarexpression in the update you're performing, so in one-go you fetch and set the sequence in the update.

braidiano
User
Posts: 40
Joined: 18-Nov-2006
# Posted on: 20-Dec-2007 09:38:10   

thanks for your replies.

In case of SelfServicing scenario, can I use the same way? (OnValidatingEntityBeforeSave overriding?)

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 20-Dec-2007 10:44:57   

Yes you can.

IMHO, that's the best way:

Dvk wrote:

OR use a scalarexpression in the update you're performing, so in one-go you fetch and set the sequence in the update.

to execute the following update statement:

UPDATE Invoice
SET SequentialNumber = (SELECT MAX(i2.SequentialNumber) + 1 FROM  Invoice i2 WHERE ...) 
braidiano
User
Posts: 40
Joined: 18-Nov-2006
# Posted on: 21-Dec-2007 16:58:59   

thanks to all simple_smile

Walaa wrote:

Yes you can.

IMHO, that's the best way:

Dvk wrote:

OR use a scalarexpression in the update you're performing, so in one-go you fetch and set the sequence in the update.

to execute the following update statement:

UPDATE Invoice
SET SequentialNumber = (SELECT MAX(i2.SequentialNumber) + 1 FROM  Invoice i2 WHERE ...) 

Can you provide me an example to build a scalarexpression in LLBL, please (SelfServicing and Adapter)?

thanks! Davide

DvK
User
Posts: 318
Joined: 22-Mar-2006
# Posted on: 21-Dec-2007 22:07:34   
        Dim account As New AccountEntity
        Dim bucket As New RelationPredicateBucket(AccountFields.AccountId = 1) 'set filter to e.g. accountId = 1

        'build expression to apply. Fetch MAX accountNumber from account table and add 1 and use this value as input for update entity
        Dim expr As New Expression(AccountFields.AccountNumber.SetAggregateFunction(AggregateFunction.Max), ExOp.Add, 1)
        account.Fields(AccountFieldIndex.AccountNumber).ExpressionToApply = expr

        Using adapter As New DatabaseSpecific.DataAccessAdapter
            adapter.UpdateEntitiesDirectly(account, bucket)
        End Using

This simple piece of code should fix it...