CODEWITHSUNDEEP
nhibernate
MoB
MoB

Reputation: 61

NHibernate GetNamedQuery can not execute stored procedure

So I have a stored procedure named get_by_cat_and_tag, it should return table MS, and in my C# code it should return a list of MS objects, where MS.hbm.xml contains the following:

<sql-query name="get_by_cat_and_tag">
    <!-- return type must be an NHibernate mapped entity -->
    <return class="MS">

      <return-property column="Id" name="Id" />
      <return-property column="FileName" name="FileName" />
      <return-property column="DisplayTitle" name="DisplayTitle" />
      <return-property column="IsEnabled" name="IsEnabled" />
      <return-property column="MediaType" name="MediaType" />
      <return-property column="Priority" name="Priority" />
      <return-property column="DateCreated" name="DateCreated" />
      <return-property column="DateLastModified" name="DateLastModified" />
      <return-property column="Description" name="Description" />
      <return-property column="ImagePath" name="ImagePath" />

    </return>
    <!--exec get_by_cat_and_tag@categoryId=:catId, @tagId=:tId-->
    exec get_by_cat_and_tag:catId, :tId
</sql-query>

All column names returned from this stored procedure are correct and for ease I made them the same.

The data access layer contains the call to the stored procedure:

var query = session.GetNamedQuery("get_by_cat_and_tag")
                   .SetParameter("catId", categoryId).SetParameter("tId", tagId)
                   .List<MS>();

The exception message:

could not execute query

[ exec get_by_cat_and_tag @p0, @p1 ]
Name:catId - Value:130  Name:tId - Value:449
[SQL: exec get_by_cat_and_tag @p0, @p1]

The stack trace:
   at System.Data.SqlClient.SqlDataReader.GetOrdinal(String name)
   at NHibernate.Type.NullableType.NullSafeGet(IDataReader rs, String name)
   at NHibernate.Loader.Loader.GetInstanceClass(IDataReader rs, Int32 i, ILoadable persister, Object id, ISessionImplementor session)
   at NHibernate.Loader.Loader.InstanceNotYetLoaded(IDataReader dr, Int32 i, ILoadable persister, EntityKey key, LockMode lockMode, String rowIdAlias, EntityKey optionalObjectKey, Object optionalObject, IList hydratedObjects, ISessionImplementor session)
   at NHibernate.Loader.Loader.GetRow(IDataReader rs, ILoadable[] persisters, EntityKey[] keys, Object optionalObject, EntityKey optionalObjectKey, LockMode[] lockModes, IList hydratedObjects, 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)

Any help is appreciated.

Upvotes: 1

Views: 2442

Answers (1)

Rippo
Rippo

Reputation: 22424

You are missing a space after the word tag:-

exec get_by_cat_and_tag:catId, :tId

should be:-

exec get_by_cat_and_tag :catId, :tId

Also note that all columns returned must also match in case, its always worth checking that one.

Upvotes: 2

Related Questions