How to get NextVal from oracle sequence via NHibernate

Question

I am working on C # .net 4.0 and using NHibernate I'm talking to Oracle DB. You would think something as simple as it was already somewhere, but, unfortunately, it is not. I need NextVal from an Oracle sequence. I do not need to insert it into the database as part of an identifier or primary key. I just need to use the following val on the C # side.

Can someone help me with an xml mapping and a C # file (or link) to achieve this.

Thanks.

Sort of

int NextValueOfSequence = GetNextValueofSequence(); public int GetNextValueOfSequence() { // Access NHibernate to return the next value of the sequence. } 

Answer 1

Mapping:

  <sql-query name="GetSequence" read-only="true"> <return-scalar type="Int64"/> <![CDATA[ SELECT SeqName.NEXTVAL from DUAL; ]]> </sql-query> 

code:

 Int64 nextValue = session.GetNamedQuery("GetSequence").UniqueResult<System.Int64>(); 

Answer 2

It also does the trick.

  <your session variable>.CreateSQLQuery("select <your sequence>.NEXTVAL from dual").UniqueResult<Int64>(); 

Answer 3

With NH4, I use this extended ISIS extension method (obviously DB must support sequences)

 public static T GetSequenceNextValue<T>(this ISession session, string sequenceName) where T : struct { var dialect = session.GetSessionImplementation().Factory.Dialect; var sqlQuery = dialect.GetSequenceNextValString(sequenceName); return session.CreateSQLQuery(sqlQuery).UniqueResult<T>(); } 

Answer 4

There is a small correction in the mapping set by @Petr Kozelek

 <sql-query name="GetSequence" read-only="true"> <return-scalar column="NextNo" type="Int64"/> <![CDATA[ SELECT SeqName.NEXTVAL as NextNo from DUAL ]]> </sql-query>