I've a collection of Transaction objects built from an import process that converts a tab deliminted text file to a Transactions collection and then imports that collection into a database table if the objects in the collection are all valid.
One thing I'd like to check for is duplicates. I want to make sure that if a Transaction has the same invoice number as another Transaction object within the Transactions collection that both are flagged as Invalid.
Additionally, I'd like to flag a record as Invalid if the Transaction already exists within the database.
Regarding the checks upon import:
1: Solving the check for duplicates within the database is pretty simple. Every time the Invoice Number is changed I could do a quick database check to see if a duplicate exists. I"ll have to pool a connection so that I'm not opening and closing the connection 5,000 times.
2: Solving the check for duplicates within the collection may be a bit of a bear - with 5,000 items I'm thinking it may be kind of slow. Especially when the collection is first built and the rules are checked for the very first time.
Thoughts on a way to make this efficient?
I take a different approach with regard to importing data.
1. Read the file into memory (usually a Dataset.)
2. Sort the dataset in invoice number order (and invoice line sequence.)
3. Start at the top of the dataset and begin working your way through it one row at a time.
Using a CSLA Invoice BO:
4. Load the Header information from row 1.
5. Load the Line information from row 1.
6. While the header is the same continue loading lines for that invoice.
7. Check if it is valid.
8. If so, save the invoice to the DB and make a note in a log that it was successfully processed.
9. If not valid, then save the invoice to the DB in Rejected status. Log the reason.
10. If the Save fails (due to a DB violation) then make a note in the log that that particular invoice could not be processed at all and state the reason. e.g. Duplicate Invoice number.
11. So I am only dealing with 1 invoice at a time and it is farily simple to handle it.
Not sure if that helps. But...
Joe
Joe,
You've got a perfectly workable solution there - I may fall back to that if I cannot get this to work. In fact, I did something similar to that in an older version of this product. What I'm hoping to do is open the collection in a datagrid so the person can see and correct errors before ever going to the database.
If I cannot do that, I'll go with something similar to what you've suggested.
Thanks.
As I see it, you need to fully define the behavior you are looking for. There is a difference if you want the datagrid to contain ALL of the items versus only the invalid items, for instance.
In either case, you could still follow Joe's method for loading the business objects but you are trusting that the database query does get changed. Also, does it matter if you import a transaction that has the same invoice number as one already stored in the database? If so, then things get more complicated.
For the validation part, I would typically create a simply hashtable that is keyed on the invoice number. As you parse each record, you'll create the BO, populate it with the values from the text file and validate it. Your validation method will perform a lookup using the hashtable. If no match is found, it passes; otherwise, you now have a reference to the original item as well as the new one so you can mark BOTH invalid.
If you only want to display invalid items, you could save valid records as they are created and add invalid items to a collection that is bound to the datagrid for editting. Because the BO's are already wired-up to the validation method, it will take care of re-evaluating the objects when the data is changed. However, because the hashtable is keyed on the invoice number, you'll have to add logic to remove the original entry and replace it with the updated key when the user changes the value.
Finally, you can save the list of invalid transactions by calling Save() on the collection itself from your UI.
Like I said, though, if you can only have one transaction per invoice number in the database, this gets more complicated because you'll need to be able to get those from the database or run a query each time a value is changed which is going to affect performance.
HTH
Copyright (c) Marimer LLC