Hi all,
I wish to navigate (next, previous, first, last) between data of an entity (customer for example) for a field (Id or Name or ..) but without to use a List (BLB, ERLB ..) or collection.
I want to display a BO (BusinessBase) and then, with navigation buttons , to be able to display the next (by Id or Name ..), the previous, the first or the last BO existing in the database for the same entity.
How do you do for that ? of course the most generic as possible.
I thought to use a CommandBase and keep the CurrentRow in MyBusinessBase (between the BO and BusinessBase). Is there another solution ?
What is the best : direct Sql or stored procedures ? I don't want to have too many stored proc (I want to use this navigation for all entities of the application).
Thanks for your best advices.
Dominique
So we must assume that you have some way to find the "previous" or "next" item based on having the id value of the "current" item. I'm not sure how you do that - it depends on your data model and expertise with SQL I'm guessing.
But if I assume you have some way to get the right data from the database, I'd just have an extra couple factories on the business class:
var obj = CustomerEdit.GetCustomer(5); // gets customer 5
var obj = CustomerEdit.GetPreviousCustomer(5); // gets the customer before 5
var obj = CustomerEdit.GetNextCustomer(5); // gets the customer after 5
That should make it pretty easy for the UI to do its thing, doesn't complicate the business model at all, and pushes the complexity into the data access layer (or database).
Thanks, Rocky, for your (very) quick answer.
I thought to your suggest code, but it adds 4 methods for each entity (GetNextCustomer, GetPreviousCustomer, GetFirstCustomer, GetLastCustomer).
I prefer to add these methods in MyBusinessBase (GetNext, GestFirst, GestPrevious, GetLast).
Do you think it is realist ?
It is sure, it is well (not only for performance) to push the complexity into the database .. but .. I am also the DBA and I am not a truly expert in SQL ;-)
You could do this too:
public enum RecordTypes
{
Current,
Previous,
Next
}
Then in your factories:
var obj = CustomerEdit.GetCustomer(5, RecordTypes.Current);
That doesn’t cause a lot more factories – just one
more parameter per factory.
Rocky
Yes, I think it is a good idea to use a second parameter for the criteria of the Get method of the BO.
Now I need to find good stored proc to do the work.
Thanks
I think I found good answers for the stored proc on :
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=82422
Next:
SELECT TOP 1 * FROM table_name WHERE Field_Name > Criteria ORDER BY SomeKey(s)
Previous:
SELECT TOP 1 * FROM table_name WHERE Field_Name < Criteria ORDER BY SomeKey(s) DESC
First / Last just leave out the WHERE but keep the ORDER BY
Its a bit more complicated if your CRITERIA is on multiple fields:
Next:
WHERE MyColumn1 > @Parameter1
OR (MyColumn1 = @Parameter1 AND MyColumn2 > @Parameter2)
Copyright (c) Marimer LLC