Getting objects from a relational table in with Oracle pl/sql procedure.

Question

I want to retrieve rows from an oracle table and convert them into objects.

I am currently using a refcursor result and a datareader in c# to manually convert the rows to objects but this seems messy. Is their a better way of converting rows from a relational table to objects?

EDIT: The project I'm on is not using any ORM tools so unfortunately it is down to little old me to do the mapping!

Answer 1

You could use an ORM like NHibernate, Fluent NHibernate, Linq To Sql, or Entity Framework. ORMs (Object Relational Mappers) exist to turn datatables into objects. You specify which table points to which class, along with which columns point to which members, and the ORM will cast rows into objects. The also take care of your normal CRUD operations, so as soon as you map the tables, you can start interacting with them.

Edit: If you are using .Net 3.5 or later, you get Linq To Sql and Entity Framework (they are included in .Net). If you can't use an ORM, you are stuck doing the casting yourself (essentially, you are the ORM in that case)

Answer 2

You can use UDTs and utilize the new ODP functionality to get the data.

this is a walkthrough on getting started: http://www.oracle.com/technology/obe/hol08/dotnet/udt/udt_otn.htm

while this is a bit more detailed: http://download.oracle.com/docs/html/E10927_01/featUDTs.htm

but the real meat & potatoes are already installed on your computer after you install ODP in the Samples directory: %ORA_HOME%\product\11.1.0\client_1\odp.net\samples\2.x\UDT

Utilizing UDTs has helped out our origination and the time response is great.