Indexing Query Results

A service is allowed to query a database table at most once, which may leave us with a heap of useful yet unclassified data rows.

For example, let us consider a GetClientOrders service, accepting a ClientID and returning this client's orders, detailed with the item names and quantities. This service could call the following SELECT stored procedures :

Order_SelectByClientID
OrderItem_SelectByClientID
Item_SelectByClientID

The GetClientOrders service then needs to build a list of orders, which begins by iterating over the Order data rows. We then need to get the order's items, which asks the following questions :

Which OrderItem data rows belong to the current order?
How can we get the details of each of these items?

We provide the answers to these questions by indexing the OrderItem and Item data rows in memory. This can be done using a Hashtable, or using the FindBy and Select methods of the ADO.NET DataTable :

For each Order: Get OrderItems[orderID]
For each OrderItem: Get Items[itemID]