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 