asp.net 2.0
llbl 2.0.0.61127
adapter 2.0
MS SQL 2K
Here's an overview of the logic.
- user can select a orders which currently have a status of 4.
- the status of the selected orders are then updated to either 0 or 1 .
this depends on whether the balance <> 0 or balance = 0
db schema
table: order_status | columns: order_number, status, updated_by, updated_on
view: order_balance | columns: order_number_balance
order_balance is a view because the acutal tables behind this view are located in a seperate MS SQL 2K database. This database is readonly from my applet.
I added the view as an entity within the DAL relating the table and view to each other on order_number.
options
* 2 updates with a UOW.AddUpdateEntitiesDirectlyCall(entity, filter). inner join status table to balance view
update order_status
set status = @NewStatus,
updated_by = @updated_by,
updated_on = @updated_on
from order_status s inner join order_balance b on s.order_number = b.order_number
where s.status = 4
and s.order_number in (@Orders)
and b.balance = 0
- 2 updates with a OUW.AddUpdateEntitiesDirectlyCall(entity, filter). subselect the order number from balance view
update order_status
set status = @NewStatus,
updated_by = @updated_by,
updated_on = @updated_on
where status = 4
and order_number in (
select order_number
from order_balance
where order_number in (@Orders)
and balance = 0
)
- 1 update statement with a db function call to return the correct status
update order_status
set status = dbo.is_balanced(order_number),
updated_by = @updated_by,
updated_on = @updated_on
where status = 4
and order_number in (@Orders)
option 1 would appear to be the most effecient and db egnostic.
option 2 would work too, seems more complex than option 1.
option 3 is not ideal as my business logic is now mixed into the db and may not be db egnostic.
