C# NHibernate & Oracle Managed Client

cmanagednhibernateoraclevisual studio

I have a big project that conceals another 16 project (Tests, Webs & App like Core, Email etc:.). I use C# MVC4 for my main website project. If I use a non Managed Client need me to do a project folder /bin recorded library Oracle.DataAccess.dll and everything works fine ( I must set(change) in Web.config -> param:

<connectionStrings>
   <add name="PC13" connectionString="User Id=TEST; Password=TEST; Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=DBTEST)(PORT=1521))(CONNECT_DATA=(SERVER = DEDICATED)(SERVICE_NAME = DBTEST)));" />   
</connectionStrings>

And DB connection works fine without ORA exception TNS_NAME ). I must set Data source to full path with tnsnames.ora when I use only alias like TEST i get a message "Exception: ORA-12154: TNS:could not resolve the connect identifier specified", but if i set full tns code for alias -> all works great.

I use hibernate.cfg.xml file:

<hibernate-configuration xmlns="urn:nhibernate-configuration-2.2">
   <session-factory>
      <property name="connection.driver_class">NHibernate.Driver.OracleDataClientDriver</property>
      <property name="show_sql">true</property>
      <property name="dialect">NHibernate.Dialect.Oracle10gDialect</property>
      <property name="command_timeout">60</property>
      <property name="cache.provider_class">NHibernate.Caches.SysCache2.SysCacheProvider, NHibernate.Caches.SysCache2</property>
      <property name="cache.use_second_level_cache">true</property>
      <property name="cache.use_query_cache">true</property>
      <property name="cache.default_expiration">120</property>
   </session-factory>

My Web.config file (for managed client add oracle.manageddataaccess.client part):

<connectionStrings>
   <add name="PC13" connectionString="User Id=TEST; Password=TEST; Data Source=DBTEST;" />   
</connectionStrings>

<oracle.manageddataaccess.client>
    <version number="*">
      <dataSources>
        <dataSource alias="DBTEST" descriptor="(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=DBTEST)(PORT=1521))(CONNECT_DATA=(SERVER = DEDICATED)(SERVICE_NAME = DBTEST)))"/>
      </dataSources>
    </version>
</oracle.manageddataaccess.client>

I have dedicated DB server & I programming on Visual Studio 2010 SP1 32bit (Win 7 64b). I have installed Oracle client 11g (32 & 64b version). File hibernate.cfg.xml is is used in another 3 project in package like "IntegrationTest", "Core" etc:. I run program (F5) with Debug mode and x86 platform.

In project I use:

NHibernate v. 3.3.1.4000
StructureMap v. 2.6.4.
and other unimportant Packages

What do I have to install the package and how to set it up?
I Try add reference with Nuget

Official Oracle ODP.NET, Managed Driver 12.1.22
Oracle Data Provider for .NET (ODP… 121.1.2

When I install some of these packages (add references from Nuget) to my Website project and i change hibernate.cfg.xml to:

<property name="connection.driver_class">NHibernate.Driver.OracleManagedDriver</property>

I got message from VS Exception "Could not create the driver from NHibernate.Driver.OracleManagedDriver"

If i go to the View -> Server Exploler -> to the Data Cennection and i set a Add Connection . . . (see link -> An Easy Drive to .NET Manual) . I dont get a ODP.NET Managed option, only .NET Framework data provider for Oracle option, and when I try connection i get this message "BadImageFormatException. This will occur when running in 64 bit mode with the 32 bit Oracle client components installed"

My question is

What can I use for reference (driver) ?
How can I set it ?

An Easy Drive to .NET Manual

Thanks a lot

Best Answer

I am using NHibernate 4.0.4 and I have installed the "Oracle.ManagedDataAccess" nuget package (https://www.nuget.org/packages/Oracle.ManagedDataAccess/).

In order to configure NHibernate to use the Oracle Managed Driver it is necessary to change just a tad bit the hibernate.cfg.xml file - and use the NHibernate.Driver.OracleManagedDataClientDriver as the "connection.driver_class".

Therefore, my xml config file is as it follows:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-configuration xmlns="urn:nhibernate-configuration-2.2">
  <session-factory>
    <property name="connection.driver_class">NHibernate.Driver.OracleManagedDataClientDriver</property>
    <property name="connection.connection_string">User Id=user;Password=pws;Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.10.18)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=SRV)))</property>
    <property name="show_sql">true</property>
    <property name="dialect">NHibernate.Dialect.Oracle10gDialect</property>
    <property name="query.substitutions">true 1, false 0, yes 'Y', no 'N'</property>
  </session-factory>
</hibernate-configuration>

Good luck - I know that using Oracle and ORM can be quite an annoying experience, but one that is worth the effort in the end.

Related Solutions

Sql – Get list of all tables in Oracle

SELECT owner, table_name
  FROM dba_tables

This is assuming that you have access to the DBA_TABLES data dictionary view. If you do not have those privileges but need them, you can request that the DBA explicitly grants you privileges on that table, or, that the DBA grants you the SELECT ANY DICTIONARY privilege or the SELECT_CATALOG_ROLE role (either of which would allow you to query any data dictionary table). Of course, you may want to exclude certain schemas like SYS and SYSTEM which have large numbers of Oracle tables that you probably don't care about.

Alternatively, if you do not have access to DBA_TABLES, you can see all the tables that your account has access to through the ALL_TABLES view:

SELECT owner, table_name
  FROM all_tables

Although, that may be a subset of the tables available in the database (ALL_TABLES shows you the information for all the tables that your user has been granted access to).

If you are only concerned with the tables that you own, not those that you have access to, you could use USER_TABLES:

SELECT table_name
  FROM user_tables

Since USER_TABLES only has information about the tables that you own, it does not have an OWNER column – the owner, by definition, is you.

Oracle also has a number of legacy data dictionary views-- TAB, DICT, TABS, and CAT for example-- that could be used. In general, I would not suggest using these legacy views unless you absolutely need to backport your scripts to Oracle 6. Oracle has not changed these views in a long time so they often have problems with newer types of objects. For example, the TAB and CAT views both show information about tables that are in the user's recycle bin while the [DBA|ALL|USER]_TABLES views all filter those out. CAT also shows information about materialized view logs with a TABLE_TYPE of "TABLE" which is unlikely to be what you really want. DICT combines tables and synonyms and doesn't tell you who owns the object.

Sql – How to limit the number of rows returned by an Oracle query after ordering

You can use a subquery for this like

select *
from  
( select * 
  from emp 
  order by sal desc ) 
where ROWNUM <= 5;

Have also a look at the topic On ROWNUM and limiting results at Oracle/AskTom for more information.

Update: To limit the result with both lower and upper bounds things get a bit more bloated with

select * from 
( select a.*, ROWNUM rnum from 
  ( <your_query_goes_here, with order by> ) a 
  where ROWNUM <= :MAX_ROW_TO_FETCH )
where rnum  >= :MIN_ROW_TO_FETCH;

(Copied from specified AskTom-article)

Update 2: Starting with Oracle 12c (12.1) there is a syntax available to limit rows or start at offsets.

SELECT * 
FROM   sometable
ORDER BY name
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

See this answer for more examples. Thanks to Krumia for the hint.

Best Answer

Related Solutions

Sql – Get list of all tables in Oracle

Sql – How to limit the number of rows returned by an Oracle query after ordering

Related Topic