Object Relational Mapping

Some one asked me on my mail recently:

Hi hope you are fine my question is

I can make a realationship between a query of a particular table to a particular object..
For example I have two table Customer and Order
SAy For Customer table i can make a Customer class.(select * from cutomer)
And can have one to one relationship between table(datareader) fields and object properties.
SAy For Orders i can make a Order class.(select * from order)
And can have one to one relationship between table(datareader) fields and object properties.

But what can i do if i have a query with multiple tables.that is with customer and orders in the query that is
select c.cutomername,c.cutomerid,c.address,o.date,o.isdelivered from order o,cutomer c where c.customerid=o.cutomerid.

Now in which object should i fill values from reader.
(if used both cutomer and order object then how to use them and How to relate them)

My response was:

There are multiple ways with which you could handle your scenario. These are typical obection relational mapping issues. But before i tell you any thing concrete i would suggest you to throughly study " Designing Data Tier Components and Passing Data Through Tiers" at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnbda/html/BOAGag.asp

Now coming towards a solution, you can create a Customer Entity that maps to customer table in your relational data store and you can also create Orders entity that again maps to your Orders table in your database. And then since i believe one customer is supposed to have zero or more orders then you can keep a collection of Orders entity in your Cutsomer entity. That is you create first a customer enity through iterating your reader and then create Order entities and putting them in Order Collection inside your customer entity.

Yours is a very typical scenario, there are far more complex situations one could face with Object Relational mapping. I will also suggest you to take a look at tools like IBatis( http://ibatis.apache.org/) and NHibernate(http://www.hibernate.org/343.html).