Whenever I call SQL Server stored procedures from an NHibernate (3.2) application, I generate proxy methods that look like this
IList<T> MyProcedure<T>(ISession session
, [some parameters]
)
{
IQuery sp = session.CreateSQLQuery(@"BEGIN
EXECUTE MyProcedure
:SomeParameter
END")
.AddEntity(typeof(T));
sp.SetParameter<[datatype]>("SomeParameter", SomeParameter);
...;
sp.SetResultTransformer(Transformers.RootEntity);
//sp.SetTimeout(timeout);
return sp.List<T>();
}
and manually create the data class MyProcedureResult and the corresponding mapping class MyProcedure_Result.
The SP is invoked like this:
var result = Procedures.MyProcedure<MyProcedureResult>(session, [parameters]);
This usually works, but recently I experienced an exception when calling a specific SP:
[Data] Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.IndexOutOfRangeException: [Data] Source Error: Stack Trace: [IndexOutOfRangeException: [Data]] System.Data.ProviderBase.FieldNameLookup.GetOrdinal(String fieldName) +5043194 System.Data.SqlClient.SqlDataReader.GetOrdinal(String name) +67 NHibernate.Driver.NHybridDataReader.GetOrdinal(String name) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Driver\NHybridDataReader.cs:363 NHibernate.Type.NullableType.NullSafeGet(IDataReader rs, String name) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Type\NullableType.cs:236 NHibernate.Type.NullableType.NullSafeGet(IDataReader rs, String[] names, ISessionImplementor session, Object owner) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Type\NullableType.cs:195 NHibernate.Type.AbstractType.Hydrate(IDataReader rs, String[] names, ISessionImplementor session, Object owner) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Type\AbstractType.cs:131 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:2511 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:988 NHibernate.Loader.Loader.InstanceNotYetLoaded(IDataReader dr, Int32 i, ILoadable persister, EntityKey key, LockMode lockMode, String rowIdAlias, EntityKey optionalObjectKey, Object optionalObject, IList hydratedObjects, ISessionImplementor session) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Loader.cs:943 NHibernate.Loader.Loader.GetRow(IDataReader rs, ILoadable[] persisters, EntityKey[] keys, Object optionalObject, EntityKey optionalObjectKey, LockMode[] lockModes, IList hydratedObjects, ISessionImplementor session) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Loader.cs:875 NHibernate.Loader.Loader.GetRowFromResultSet(IDataReader resultSet, ISessionImplementor session, QueryParameters queryParameters, LockMode[] lockModeArray, EntityKey optionalObjectKey, IList hydratedObjects, EntityKey[] keys, Boolean returnProxies) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Loader.cs:334 NHibernate.Loader.Loader.DoQuery(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Loader.cs:468 NHibernate.Loader.Loader.DoQueryAndInitializeNonLazyCollections(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Loader.cs:246 NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Loader.cs:1590 [GenericADOException: could not execute query [ BEGIN EXECUTE MyProcedure @p0 ,@p1 END ] [SQL: BEGIN EXECUTE MyProcedure @p0 ,@p1 END]] NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Loader.cs:1599 NHibernate.Loader.Loader.ListIgnoreQueryCache(ISessionImplementor session, QueryParameters queryParameters) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Loader.cs:1497 NHibernate.Loader.Loader.List(ISessionImplementor session, QueryParameters queryParameters, ISet`1 querySpaces, IType[] resultTypes) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Loader.cs:1487 NHibernate.Impl.StatelessSessionImpl.ListCustomQuery(ICustomQuery customQuery, QueryParameters queryParameters, IList results) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Impl\StatelessSessionImpl.cs:376 NHibernate.Impl.StatelessSessionImpl.List(NativeSQLQuerySpecification spec, QueryParameters queryParameters, IList results) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Impl\StatelessSessionImpl.cs:353 NHibernate.Impl.StatelessSessionImpl.List(NativeSQLQuerySpecification spec, QueryParameters queryParameters) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Impl\StatelessSessionImpl.cs:363 NHibernate.Impl.SqlQueryImpl.List() in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Impl\SqlQueryImpl.cs:160 Version Information: Microsoft .NET Framework Version:4.0.30319; ASP.NET Version:4.0.30319.547
As you can see, NH expects the column name [Data], which does not exist in the result set, causing GetOrdinal() to throw an exception.
What’s going on here?
Well, the stored procedure returns a result set containing the column name Data, but NH tries to find the column [Data].
After stepping through the source code of NH 3.2, I claim to partially understand how this happens.
When the entities and mappings are initialized, NH calls connection.GetSchema(DbMetaDataCollectionNames.ReservedWords) to check whether column names are reserved words. If a column name is found to be a reserved word, the name is flagged to be quoted and/or aliased when used in SQL.
If, however, the column name occurs in the result set of an SP, aliasing cannot be applied, and quoting is not required.
Quoting using backticks (`name`) as proposed on SO did not help either.
After many experiments, my solution was to simply rename the columns in the SP (DataX), and add a column name mapping
map.Column("DataX");
to the class map.
devioblog 