Using nHibernate to Transfer Non-Displayable Data to DTO

Question

So, I'm doing a postcode search provided by an external data provider in a database that we control with nHibernate. This includes calling a stored procedure and providing a zip code. In turn, I get several rows, each of which contains several columns that make up part of the address.

We have a DTO address. But I am struggling with how to attribute the database results to this object, since it is not tied to anything in the database. I'm doing it:

    Dim spCall As String = String.Format("exec AddressFindListPostCode '{0}'", postcode)
    Dim tran As Global.NHibernate.Transform.IResultTransformer = Global.NHibernate.Transform.Transformers.AliasToBean(GetType(Concrete.Cms.DataTransferObjects.Address))
    Dim streetList As IList(Of Concrete.Cms.DataTransferObjects.Address) = session.CreateSQLQuery(spCall).SetResultTransformer(tran).List(Of Concrete.Cms.DataTransferObjects.Address)()

But of course, he cannot convert the result set into an object without any help from any mapping.

The problem, in fact, is that SP returns a list of objects. Each object (equivalent to a row) contains sub-objects corresponding to the columns within the row. But I do not see the possibility of getting sub-objects. streetList (i, j) will not work, and there are no methods or properties on streetList that will allow me to access them.

How do I get the data to display it?

Cheers, Matt

Answer 1

You can try bulk loading data into a bulk class, for example:

 Dim result As IList(Of BulkLoadAddressList) = session.CreateSQLQuery(spCall)
    .SetResultTransformer(Transformers.AliasToBean(typeof(BulkLoadAddressList)))
    .List(Of BulkLoadAddressList)()

Read more: NHibernate Ad-hoc mapping