Re: Business objects, subset of collection

On 18 Jan, 21:19, jimbal...@xxxxxxxxx wrote:
The app is for scheduling payments of invoices and has three grids
(regular DataGridViews):
A Vendor grid showing a summary view of each vendor (with total Due,
etc.)
An Invoice grid listing all invoices for the selected vendor
(including a total amount of payments scheduled for each invoice)
A Payment grid listing all payments scheduled for the selected
Invoice.

Any payments added / removed or modifed in the payment grid (or mass-
updated via code) needs to be reflected instantly in the other two
grids.

Note that the vendor grid (parent) is based on the same DB table as
the invoice grid (the children), but the vendor grid is a summary view
and the invoice grid is a detail view.

The DB tables are:
Invoice (VendNo, InvNo, DueDate, Amount)
Payment (VendNo, InvNo, SeqNo, PayDate, PayAmount)

The Vendor Grid should contain the equivalent of this simplified SQL
statement:
SELECT Invoice.VendNo, SUM(Invoice.BalDue) as TotalDue,
SUM(Payment.PayAmount) as TotalPay
FROM Invoice LEFT OUTER JOIN Payment ON
 Invoice.VendNo = Payment.VendNo AND
 Invoice.InvNo = Payment.InvNo
GROUP BY Invoice.VendNo

I could of course do this as a view on the DB server and use that for
the vendor grid and use the Invoice table for the Invoice grid, but
how do you keep them in sync when payments are added / deleted or
modified from the invoices?  Would I have to persist the data to the
DB and rebind the grids? (too slow)

After adding a payment, you obviously need to refresh the grids. How
many millis does this take? How many millis does it take to execute
the SQL? What is your time constraint?

There are several issues with this.  There are thousands of invoices
and the vendor grid is bound to an aggregate SQL statement of all
these invoices.  Anytime the user changes a payment amount or date,
something like this would have to happen:

How long time does it take to execute your aggregate SQL?

1) The payment record is persisted to the DB
2) The invoice grid (which is also bound to an aggregate SQL statement
or view [since it contains payment totals] would have to be rebound
and the SQL statement thus re-executed)
3) The rebind above will cause the grid to lose its current row, so
you have to reposition using Find or similar.

I don't think you should have a grid showing all invoices for one
vendor. It is not practical in the GUI.

4) The vendor grid (which is bound to an aggregate SQL statement /
view has to be rebound and the SQL thus re-executed)
5) The vendor grid loses it place and have to be repositioned

If the numbers of vendors isn't too high, I think this is acceptable.
Otherwise you should not show all vendors in one grid.

6) The invoice grid will lose it place again due to the vendor grid's
current record changing

The above are not the exact steps and there is probably a better
sequence, but I think you get the point.

I have a working application already using business objects.  It was a
bit tricky and a bit messy with the filter related methods, but any
change to a payment is instantly reflected in all the other grids
without the need to persist & reload from DB, etc. (using the standard
databinding events of the .NET framework).

What about payments made from other client computers? Are they
reflected instantly too?

I have not dealt much with DataSets and I got the apparently errenous
impression that they would be a better tool for the job, but it seems
OOA/D is still the best way to go.

Unless you tell the performance metrics for the SQL queries, it is
hard to tell.

//frebe
.

Relevant Pages

  • Re: Business objects, subset of collection
    ... A Vendor grid showing a summary view of each vendor (with total Due, ... An Invoice grid listing all invoices for the selected vendor ... A Payment grid listing all payments scheduled for the selected ...
    (comp.object)
  • Re: Business objects, subset of collection
    ... The app is for scheduling payments of invoices and has three grids ... A Vendor grid showing a summary view of each vendor (with total Due, ... An Invoice grid listing all invoices for the selected vendor ...
    (comp.object)
  • Re: Business objects, subset of collection
    ... The app is for scheduling payments of invoices and has three grids ... A Vendor grid showing a summary view of each vendor (with total Due, ... An Invoice grid listing all invoices for the selected vendor ...
    (comp.object)
  • Re: Sorting dataset
    ... The CF implementation is the same as the full framework. ... I created a DS and bound it to a Grid Control. ... What are you using to print the invoice? ... Dim DVinvoice As DataView ...
    (microsoft.public.dotnet.framework.compactframework)