Almost always when presenting data you will have to supply two essential
elements to your user interface: header search and detail presentation.
Supply criteria to the user to search through all those invoice headers,
like date, open/closed status, vendor, etc. (I typically supply a default
condition of showing only the top 300 rows if no search condition is
provided, sorting by create date descending when applicable). When the user
clicks on a row, have another section of your form load the invoice details
for that particular header.
If you still want to show invoice details based on a search (and not an
individual header), the query should look something like this:
SELECT TOP 300 d.*
FROM InvoiceHeader h
INNER JOIN InvoiceDetail d
ON d.InvoiceID = h.InvoiceID
WHERE
h.InvoiceDate = <your date variable>
AND <any other search conditions>
ORDER BY h.InvoiceID, d.InvoiceLineNumber
I put in the TOP 300 because users don't want to page through a large result
set - that's what a search feature is for. You can tell your users in a note
on the form that the result set has been truncated when necessary ("only the
top 300 search hits shown"). If a user really does want to see all 200,000
rows, this is a report, something to be printed; not a form. As such I never
user a datareader, because my queries never return large result sets.
As a side note, I don't recommend having any SQL at all in compiled code,
use stored procedures if you can; they supply a useful layer of abstraction,
boost performance, and allow you to make DB changes without having to
recompile your deployment. Also, take some time to buy a book or two on SQL
and really learn it. It will really make a big difference.
HTH,
Bob
