Calling an Oracle stored procedure using NHibernate

1. The stored procedure must have an out sys_refcursor parameter as the first argument.
2. Tested with NHibernate 2.1.2 and Oracle 11g.
3. Needs Oracle.DataAccess (not System.Data.OracleClient). Tested with Oracle.DataAccess 2.111.6.0 AMD64.

NHibernate config:

<hibernate-configuration xmlns="urn:nhibernate-configuration-2.2">
  <session-factory>
    <property name="connection.driver_class">
      NHibernate.Driver.OracleDataClientDriver
    </property>
  </session-factory>
</hibernate-configuration>

NHibernate mapping:

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="SampleProject" namespace="SampleProject">
  <sql-query name="GetEmployeesByDepartmentId">
    <return />
    { call GetEmployeesByDepartmentId(:p_departmentid) }
  </sql-query>
</hibernate-mapping>

C# code:

var employees = nhibernateSession
    .GetNamedQuery("GetEmployeesByDepartmentId")
    .SetParameter("p_departmentid", departmentId)
    .List<Employee>();

To execute a stored procedure that has no out parameters:

nhibernateSession
    .CreateSQLQuery("call DoSomething(:p_parameter1)")
    .SetParameter("p_parameter1", parameter1)
    .ExecuteUpdate();

17 January 2012 | .NET, C# | Comments

• Pages
  • About
  • Professional Profile

• Blogroll

  • Baby Boon
  • bibliobibuli
  • Dark Angel
  • eric forbes's book addict's guide
  • fadzli.com
  • GojiSoft Blog
  • jusupov.com
  • Linux or Windows
  • mang0.net
  • My Manhood Blog
  • My way in life
  • nazham.com
  • Nurba
  • octalforty
  • onenaught.com
  • Prashant Rajan
  • Random thoughts on software
  • Rasyadi's Blog
  • Rojak Campoq Blog
  • romantika.name
  • Sa'eed & 'Aatikah
  • stainedhead
  • Synersov Alive
  • The Melody
  • Tze's Blog
  • Unpredictability and Surprises
  • Writing by Amir
  • [18:18pm]
• Categories:
  • .NET
  • Agile
  • AJAX
  • Ancient history
  • Applications
  • ASP
  • ASP.NET
  • Book review
  • C#
  • Cars
  • Cartoons
  • Crystal Reports
  • CSS
  • English
  • Entity Framework
  • FCKEditor
  • Fiction
  • HTML
  • HTTP
  • Javascript
  • jQuery
  • LAMP
  • LINQ
  • MS SQL
  • MySQL
  • PHP
  • Politics
  • Project management
  • Silverlight
  • Software engineering
  • Travel
  • uISV
  • Uncategorized
  • VB6
  • Windows
  • WordPress
• Meta:
  • RSS
  • Comments RSS
  • Valid XHTML
  • XFN
  • WP