Home > .NET > ResultSet to Object Mapper

ResultSet to Object Mapper

sebenarnya klo di NHibernate ada subselect seperti di java gak bakalan buat yg ginian sih.

kemaren pas kerja dengan Java aku ada belajar bahwa sql query result dapat langsung di map ke object. kira2 mappingnya seperti ini.

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="Welly.Exp.NHSubselect.ViewModel" namespace="Welly.Exp.NHSubselect.ViewModel">
  <class name="StockCardSnapshot" lazy="false">
    <subselect>
      select sc.scid, sc.partid, prt.prtname, prc.prcid, prc.prcname, prt.prtgroupid, prg.prgname, loc.locationid, loc.locname, sc.scavailable, sc.scbookingstock, sc.scbalance, uom.uomname
      from tblstockcard sc inner join
      tblpart prt on sc.partid = prt.partid inner join
      .......
    </subselect>
    <synchronize table="tblstockcard"/>
    <synchronize table="tblpart"/>
    .....
    
    <id name="ID" column="scid"/>
    <property name="Part" column="partid" type="String"/>
    <property name="Principal" column="prcname" type="String"/>
    ...
  </class>
</hibernate-mapping>

tapi aku coba jalanin di .NET. katanya subselect gak dikenalin di xml parser nya. Beh. Sial.

Akhirnya aku coba buatin yg sederhana untuk ngemap result set tersebut. Aku make AdoTemplate dari Spring .NET untuk ngemap plus Reflection.

public class QueryObjectMapper : IQueryObjectMapper
    {
        public AdoTemplate AdoTemplate { get; set; }
        public IList<T> Map<T>(string query) where T : IViewModel, new()
        {
            return AdoTemplate.QueryWithRowMapperDelegate<T>(CommandType.Text, query, (dataReader, rowNum) => 
            {
                T t = new T();

                var properties = typeof(T).GetProperties(BindingFlags.Instance | BindingFlags.Public);
                
                foreach (var property in properties)
                {
                    property.SetValue(t, dataReader[property.Name] is DBNull ? null : dataReader[property.Name], null);
                }
                return t;
            });
        }
    }

Karena aku pengemar berat dari Convention over Configuration maka konvensi yg ditetapkan adalah untuk setiap property yg ada di kelas tersebut akan disesuaikan dengan field yg ada di database. sehingga untuk object view model berikut

public class StockCardSnapshot : IViewModel
    {
        public long ID { get; set; }
        public long PartId { get; set; }
        public string Part { get; set; }
        public string Principal { get; set; }
        public string PartGroup { get; set; }
    }

querynya disesuaikan dengan aliasing field dari view

select sc.scid as ID, sc.partid as PartId, prt.prtname as Part, prc.prcname as Principal, prg.prgname as PartGroup, .....

begini cara menggunakan mapper tersebut

        [Test]
        public void Should_return_object_with_mapper()
        {
            string sql = @"select sc.scid as 
            ........

            IQueryObjectMapper queryObjectMapper = (QueryObjectMapper)ContextRegistry.GetContext().GetObject("QueryObjectMapper");
            IList<StockCardSnapshot> stockCardSnapshot = queryObjectMapper.Map<StockCardSnapshot>(sql);
            foreach (var stockCard in stockCardSnapshot)
            {
                Console.WriteLine("{0} : {1}", stockCard.Part, stockCard.PartId);
            }
        }

configurasi di spring nya

<db:provider id="DbProvider"
						 provider="Npgsql-2.0"
						 connectionString="Server=localhost;Database=test;uid=user;pwd=pwd;Timeout=1024;CommandTimeout=3000;"/>

<object id="AdoTemplate" type="Spring.Data.Generic.AdoTemplate, Spring.Data" singleton="false" autowire="autodetect"/>
      <object id="QueryObjectMapper" type="Welly.Exp.Infra.Data.QueryObjectMapper, Welly.Exp.Infra.Data" singleton="false" autowire="autodetect"/>

mudah2an dapat bermanfaat

Categories: .NET Tags: , , ,
  1. weltam
    March 10, 2010 at 7:40 am

    referensi AdoTemplate dapat ditemukan di sini

    http://www.springframework.net/docs/1.3.0/reference/html/ado.html

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: