Unable to execute stored procedure using named query in nhibernate

EDN Admin · May 27, 2013

I am facing strange problem while executing stored procedure using nhibernate. When I am going through stack trace then I got Index Out Of Range exception and name of property / column. I research on google and stackoverflow and found that if any property is mapped two times then it cause problem. I checked all mapping but nothing seems to be helpful for me. Here I am writing all hbl files and codes which I am using.

Hbm file code<?xml version="1.0" encoding="utf-8"?>
<hibernate-mapping assembly="ConsoleApplication2" namespace="ConsoleApplication2" xmlns="urn:nhibernate-mapping-2.2

<class name="tARCustomer" table="tARCustomer" lazy="true" >
<id name="fCustomerID
<generator class="guid.comb" />
</id>
<version name="fTimestamp" generated="always" unsaved-value="null" type="BinaryBlob

</version>
<property name="fCustomerNumber
</property>
<property name="fName
</property>
<property name="fAddress1
</property>
<property name="fAddress2
</property>
<property name="fCity
</property>
<property name="fLocalityID
</property>
<property name="fPostalCode
</property>
<property name="fDeliveryMethod
</property>
<property name="fDoNotAllowFinanceCharges
</property>
<property name="fNotes
</property>
<property name="fInactive
</property>
<property name="fCompanyID
</property>
<property name="fPropertyID
</property>
<property name="fEnterpriseID
</property>
<property name="fDateAdded
</property>
<property name="fAddedBy
</property>
<property name="fDateModified
</property>
<property name="fModifiedBy
</property>
<property name="fShipToSameAsBilling
</property>
<property name="fShipToAddress1
</property>
<property name="fShipToAddress2
</property>
<property name="fShipToCity
</property>
<property name="fShipToLocalityID
</property>
<property name="fShipToPostalCode
</property>
<property name="fCustomerTotalBalance
</property>
<property name="fSuspend
</property>
<property name="fCreditLimitActive
</property>
<property name="fCreditLimitAmt
</property>

<bag name="Invoices" inverse="true" cascade="none" lazy="true
<key column="fCustomerID" />
<one-to-many class="tARInvoice" />
</bag>

<many-to-one name="Enterprise" insert="false" update="false" cascade="none" class="ConsoleApplication2.tSCEnterprise,ConsoleApplication2"
column="fEnterpriseID" not-null="false"/>

<many-to-one name="Company" insert="false" update="false" cascade="none" class="ConsoleApplication2.tSCCompany,ConsoleApplication2"
column="fCompanyID" not-null="false"/>

<many-to-one name="Property" insert="false" update="false" cascade="none" class="ConsoleApplication2.tSCProperty,ConsoleApplication2"
column="fPropertyID" not-null="false"/>

</class>



<sql-query name="ar_tARCustomer_ReadOverdueByContext" >
<return class="tARCustomer
</return>
exec ar_tARCustomer_ReadOverdueByContext :CustomerActive ,:CompanyID,

ropertyID ,

ateFrom,

ateTo, :InvoiceTypes
</sql-query>

</hibernate-mapping>
and domain class code.using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace ConsoleApplication2
{
[Serializable]
public class tARCustomer
{
#region Constructor
/// <summary>
/// Initializes a new instance of the <see cref="tARCustomer"/> class.
/// </summary>
public tARCustomer()
{

}

/// <summary>
/// Initializes a new instance of the <see cref="tARCustomer"/> class.
/// </summary>
/// <param name="fCustomerID The Payment Type ID.</param>
public tARCustomer(System.Guid fCustomerID)
{
////base.ID = fCustomerID;
}
#endregion

#region Properties

/// <summary>
/// Gets or sets customer ID.
/// </summary>
public virtual System.Guid fCustomerID { get; set; }

public virtual string fCustomerNumber { get; set; }

public virtual string fName { get; set; }

public virtual string fAddress1 { get; set; }

public virtual string fAddress2 { get; set; }

public virtual string fCity { get; set; }

public virtual Guid? fLocalityID { get; set; }

public virtual string fPostalCode { get; set; }

public virtual string fDeliveryMethod { get; set; }

public virtual bool fDoNotAllowFinanceCharges { get; set; }

public virtual string fNotes { get; set; }

public virtual bool fInactive { get; set; }

public virtual bool fSuspend { get; set; }

public virtual bool fCreditLimitActive { get; set; }

public virtual decimal fCreditLimitAmt { get; set; }

public virtual Guid? fCompanyID { get; set; }

public virtual Guid? fPropertyID { get; set; }

public virtual Guid? fEnterpriseID { get; set; }

public virtual DateTime? fDateAdded { get; set; }

public virtual string fAddedBy { get; set; }

public virtual DateTime? fDateModified { get; set; }

public virtual string fModifiedBy { get; set; }

public virtual byte[] fTimestamp { get; set; }

public virtual bool fShipToSameAsBilling { get; set; }

public virtual string fShipToAddress1 { get; set; }

public virtual string fShipToAddress2 { get; set; }

public virtual string fShipToCity { get; set; }

public virtual Guid? fShipToLocalityID { get; set; }

public virtual string fShipToPostalCode { get; set; }

public virtual decimal fCustomerTotalBalance { get; set; }

#endregion

public virtual tSCCompany Company { get; set; }
public virtual tSCProperty Property { get; set; }
public virtual tSCEnterprise Enterprise { get; set; }

public virtual IList<tARInvoice> Invoices { get; set; }

#region Methods
/// <summary>
/// To retrieve Hash Code.
/// </summary>
/// <returns>The retrieve hash code.</returns>
public override int GetHashCode()
{
return this.GetHashCode();
}
#endregion
}
}

I am using following code to call sp.var dd = session.GetNamedQuery("ar_tARCustomer_ReadOverdueByContext")
.SetParameter("CustomerActive", 2)
.SetParameter("CompanyID", Guid.Parse("3C5F1D66-75F3-4762-BAA1-6A0ADA8D6DD4"))
.SetParameter("PropertyID", Guid.Parse("53C4B765-C1B1-4353-98CB-AC9FBCE84F0C"))
.SetParameter("DateFrom", DateTime.Parse("5/1/2001"))
.SetParameter("DateTo", DateTime.Parse("4/24/2013"))
.SetParameter("InvoiceTypes", "Manual")
.SetResultTransformer(new AliasToBeanResultTransformer(typeof(tARCustomer)))
.SetResultTransformer(Transformers.ToList).List();

Except for that I tried so many combination but I am not able to solve it. I have following stored procedure.alter PROCEDURE [dbo].[ar_tARCustomer_ReadOverdueByContext]

@CustomerActive int,
@CompanyID uniqueidentifier,
@PropertyID uniqueidentifier,
@OverdueDateFrom datetime,
@OverdueDateTo datetime,
@InvoiceTypes varchar(100)

AS

----------------------------------------------------------------------------
-- Name: ar_tARCustomer_ReadOverdueByContext
-- Purpose: Get all customers with overdue invoices matching the given criteria at this point in the hierarchy
--
-- Input: N/A
-- Output: Recordset containing all customers with overdue invoices matching the given criteria
-- Return: 0 = Success
-- >0 = Error
--
----------------------------------------------------------------------------

SET NOCOUNT ON; -

SELECT fName
from tARCustomer
One more thing I want to say that when I am using select * from tARCustomer then this procedure is worked for me but when I am using select fCustomerID from tARCustomer then it gives error.

I dont know why it happens but am not able to solve it. Please help me to solve out this problem as soon as possible,

Here I am putting stack trace of exception.NHibernate.Exceptions.GenericADOException was caught
Message=could not execute query
[ exec ar_tARCustomer_ReadOverdueByContext @p0 ,@p1,@p2 ,@p3, @p4, @p5 ]
Name:CustomerActive - Value:2 Name:CompanyID - Value:3c5f1d66-75f3-4762-baa1-6a0ada8d6dd4 Name

ropertyID - Value:53c4b765-c1b1-4353-98cb-ac9fbce84f0c Name

ateFrom - Value:5/1/2001 12:00:00 AM Name

ateTo - Value:4/24/2013 12:00:00 AM Name:InvoiceTypes - Value:Manual
[SQL: exec ar_tARCustomer_ReadOverdueByContext @p0 ,@p1,@p2 ,@p3, @p4, @p5]
Source=NHibernate
SqlString=exec ar_tARCustomer_ReadOverdueByContext @p0 ,@p1,@p2 ,@p3, @p4, @p5
StackTrace:
at NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters)
at NHibernate.Loader.Loader.ListIgnoreQueryCache(ISessionImplementor session, QueryParameters queryParameters)
at NHibernate.Loader.Loader.List(ISessionImplementor session, QueryParameters queryParameters, ISet`1 querySpaces, IType[] resultTypes)
at NHibernate.Loader.Custom.CustomLoader.List(ISessionImplementor session, QueryParameters queryParameters)
at NHibernate.Impl.SessionImpl.ListCustomQuery(ICustomQuery customQuery, QueryParameters queryParameters, IList results)
at NHibernate.Impl.SessionImpl.List(NativeSQLQuerySpecification spec, QueryParameters queryParameters, IList results)
at NHibernate.Impl.SessionImpl.List(NativeSQLQuerySpecification spec, QueryParameters queryParameters)
at NHibernate.Impl.SqlQueryImpl.List()
at ConsoleApplication2.Program.Main(String[] args) in E:TestConsoleApplication2ConsoleApplication2Program.cs:line 23
InnerException: System.IndexOutOfRangeException
Message=fCustomerID
Source=System.Data
StackTrace:
at System.Data.ProviderBase.FieldNameLookup.GetOrdinal(String fieldName)
at System.Data.SqlClient.SqlDataReader.GetOrdinal(String name)
at NHibernate.Driver.NHybridDataReader.GetOrdinal(String name)
at NHibernate.Type.NullableType.NullSafeGet(IDataReader rs, String name)
at NHibernate.Type.NullableType.NullSafeGet(IDataReader rs, String[] names, ISessionImplementor session, Object owner)
at NHibernate.Loader.Loader.GetKeyFromResultSet(Int32 i, IEntityPersister persister, Object id, IDataReader rs, ISessionImplementor session)
at NHibernate.Loader.Loader.GetRowFromResultSet(IDataReader resultSet, ISessionImplementor session, QueryParameters queryParameters, LockMode[] lockModeArray, EntityKey optionalObjectKey, IList hydratedObjects, EntityKey[] keys, Boolean returnProxies)
at NHibernate.Loader.Loader.DoQuery(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies)
at NHibernate.Loader.Loader.DoQueryAndInitializeNonLazyCollections(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies)
at NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters)
InnerException:
Thanks

View the full article

Unable to execute stored procedure using named query in nhibernate

EDN Admin

Well-known member

Similar threads