CODEWITHSUNDEEP
nhibernatestored-proceduresnhibernate-mapping
xtblitz
xtblitz

Reputation: 33

NHibernate: Stored procedure and component

I am calling a stored procedure as a NamedQuery and it's working fine. The problems start when I try to load a component. The stored procedure returns a recordset like this

[ IDEmployee, EmployeeName, City, Country ]

Where IDEmployee and EmployeeName belong to the mapped entity and City and Country are from a component

Here comes the mapping: 

<class name="Employee" table="employees"> 
    <id name="IDEmployee"> 
      <column name="idemployee" /> 
      <generator class="native" /> 
    </id> 
    <property name="EmployeeName "> 
      <column name="employeename" /> 
    </property> 
    <component name="Address" class="Address"> 
      <property name="City"> 
        <column name="city" /> 
      </property> 
      <property name="Country"> 
        <column name="country" /> 
      </property> 
    </component> 
  </class>

This is the code I use for calling the stored procedure: 

  <sql-query name="GetEmployeesByCompany"> 
    <return class="Employee"> 
      <return-property column="idemployee" name="IDEmployee" /> 
      <return-property column="employeename" name="EmployeeName" /> 
      <return-property column="city" name="City" /> 
      <return-property column="country" name="Country" /> 
    </return> 
    EXEC GetEmployeeByCompany:idcompany 
  </sql-query>

Here the code I use to call the stored: 

 var result = 
    session<ISession>().GetNamedQuery("GetEmployeesByCompany") 
                .SetInt32("idcompany", companyId) 
                .List<Employee>().ToList();

Here the error I get when I run my app 

System.IndexOutOfRangeException: t1_282_0_ 
   at System.Data.SqlClient.SqlDataReader.GetOrdinal(String name) 
   at NHibernate.Type.NullableType.NullSafeGet(IDataReader rs, String name) 
in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Type\NullableType.cs:line 236 
   at NHibernate.Persister.Entity.AbstractEntityPersister.Hydrate(IDataReader rs, Object id, Object obj, ILoadable rootLoadable, String[][] suffixedPropertyColumns, Boolean allProperties, ISessionImplementor session)
in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Persister\Entity\AbstractEntityPersister.cs:line 2508
   at NHibernate.Loader.Loader.LoadFromResultSet(IDataReader rs, Int32 i, Object obj, String instanceClass, EntityKey key, String rowIdAlias, LockMode lockMode, ILoadable rootPersister, ISessionImplementor session) 
in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Loader.cs:line 991

If i remove from the map the component everything works fine. I also tried something with return-property with nested return-column with no success.

Any tip? Thank you!

Upvotes: 3

Views: 1307

Answers (1)

ab_732
ab_732

Reputation: 3887

I had to face this problem lately, this is the way I could come out.

<sql-query name="GetEmployeesByCompany">
  <return class="Employee">
    <return-property column="idemployee" name="IDEmployee" />
    <return-property column="employeename" name="EmployeeName" /> 
    <return-property name="Address">  <!-- name of the component -->
      <return-column name="city" />  <!-- name of the resultset field -->
      <return-column name="country" />  <!-- name of the resultset field -->
    </return-property>
</return> 
EXEC GetEmployeeByCompany:idcompany

The order you use for the fields inside the return property is very important it must match the one in the resultset. There is no documentation at all around, I hope this can help you!

Upvotes: 5

Related Questions