NHibernate 3 Data Type Mapping for SQL Server

August 8, 2012

When I recently tried to add a DOUBLE column in a project using NHibernate 3.2.0 GA to connect to a SQL Server database, the schema validation failed since the T-SQL DOUBLE/C# double mapping did not work out. So I wondered how the NHibernate schema validator decides which column data types are supported for each of the C# data types.

So I checked the following sources:

  • the MsSql****Dialect.cs files in the \src\NHibernate\Dialect\ directory of 3.2.0GA

The result, sorted by the DbType enumeration, looks like this:

DbType .Net version C# NH Mapping 7/2000 2005 2008
AnsiString 1.1 VARCHAR(255)
VARCHAR(n)
TEXT
VARCHAR(MAX)
AnsiString-
FixedLength
1.1 CHAR(255)
CHAR(n)
Binary 1.1 byte[] VARBINARY(8000)
VARBINARY(n)
IMAGE
VARBINARY(MAX)
Boolean 1.1 bool BIT
Byte 1.1 byte TINYINT
Currency 1.1 MONEY
Date 1.1 DATETIME
DATE
DateTime 1.1 DateTime DATETIME
DateTime2 3.5 DATETIME2
DateTime-
Offset
3.5 DateTime-
Offset
DATETIME-
OFFSET
Decimal 1.1 decimal DECIMAL(19,5)
DECIMAL(n,n)
Double 1.1 double DOUPLE PRECISION (synonym for FLOAT(53))
Guid 1.1 Guid UNIQUEIDENTIFIER
Int16 1.1 short SMALLINT
Int32 1.1 int INT
Int64 1.1 long BIGINT
Single 1.1 float REAL (synonym for FLOAT(24))
String 1.1 string NVARCHAR(255)
NVARCHAR(n)
NTEXT
NVARCHAR(MAX)
String-
FixedLength
1.1 NCHAR(255)
NCHAR(n)
Time 1.1 TimeSpan DATETIME
TIME
Xml 2.0 XML

Note that some data types are not supported:

  • DbType: Object, Sbyte, UInt16, UInt32, UInt64, VarNumeric
  • C# data types: char, object
  • T-SQL data types: sql_variant, rowversion, smalldatetime, timestamp, and CLR types
Advertisements

Getting Started with SQLite and Loquacious NHibernate 3.2

July 5, 2012

After I figured out how to create and access a SQLite database in C#, the obvious (for me, at least) next step was to use NHibernate for database access.

In NH 2.1.2 GA I preferred the Fluent configuration rather than the XML-based HBM files, which has been replaced by Loquacious. (You can read more on Loquacious here and here.)

You start with classes which represent a table (or view), and class maps that define the mapping of table fields to class properties:

public partial class DirectorySelection
{
  public virtual int ID { get; protected set; }
  public virtual string Path { get; set; }
}
public partial class DirectorySelection_Map 
  : ClassMapping<DirectorySelection>
{
  public DirectorySelection_Map()
  {
    Table("DirectorySelection");
    Id(x => x.ID, map =>
    {
      map.Generator(Generators.Identity);
    });
    Property(x => x.Path, map =>
    {
      map.Column("Path");
      map.NotNullable(true);
      map.Length(2000);
      map.Unique(true);
    });
  }
}

Class maps require the inclusion of NH namespaces NHibernate.Mapping.ByCode and NHibernate.Mapping.ByCode.Conformist.

Next, we need to define a Session Factory which will create us a NH Session every time we access the database:

  public class ConfigureNH
  {
    static ISessionFactory sessionFactory = NHSessionFactory();

    static ISessionFactory NHSessionFactory()
    {
      var mapper = new ModelMapper();
      mapper.AddMappings(Assembly.GetExecutingAssembly().GetExportedTypes());
      HbmMapping domainMapping = 
        mapper.CompileMappingForAllExplicitlyAddedEntities();

      var f = Path.Combine(
        System.Environment.GetFolderPath(
          System.Environment.SpecialFolder.LocalApplicationData), 
        "mydatabase.db");
      var cn = 
        (new System.Data.SQLite.SQLiteConnectionStringBuilder { DataSource = f })
          .ConnectionString;

      var configuration = new NHibernate.Cfg.Configuration();
      configuration.DataBaseIntegration(c =>
      {
        c.Driver<NHibernate.Driver.SQLite20Driver>();
        c.Dialect<SQLiteDialect>();
        c.ConnectionString = cn;
        c.KeywordsAutoImport = Hbm2DDLKeyWords.AutoQuote;
        c.SchemaAction = SchemaAutoAction.Update;    

        c.LogFormattedSql = true;
        c.LogSqlInConsole = true;
      });
      configuration.AddMapping(domainMapping);
      sessionFactory = configuration.BuildSessionFactory();
      return sessionFactory;
    }

    public static ISessionFactory SessionFactory
    {
      get
      {
        return sessionFactory;
      }
    }
  }

Note that this configuration class needs to be contained in the same assembly as the NH data classes and maps, since we use Assembly.GetExecutingAssembly().GetExportedTypes() to retrieve all DAL classes.

To create an NH session, we simply open one via the session factory:

var session = ConfigureNH.SessionFactory.OpenSession();

Generating NHibernate Access to MSSQL Stored Procedures

February 23, 2012

This series describes the generation of C# classes and database access for NHibernate 2.1.2GA.

To store the information on which Stored Procedures to script out, we need a table Meta_Procedure, similar to Meta_Table presented earlier.

CREATE TABLE [dbo].[Meta_Procedure](
  [OID] [int] IDENTITY(1,1) NOT NULL,
  [ID] [nvarchar](50) NOT NULL,
  [UseDAL] [bit] NOT NULL,
  CONSTRAINT [PK_Meta_Procedure] PRIMARY KEY CLUSTERED
  ( [OID] ASC )
)

We fill this table from the list of stored procedures stored in sys.objects:

INSERT INTO Meta_Procedure (ID)
SELECT o.name
FROM sys.objects o
LEFT OUTER JOIN Meta_Procedure t ON o.name = t.id
WHERE o.type IN ('P')
AND t.OID IS NULL;

The developer now only needs to set the UseDAL field to true (1) for each procedure to be scripted.

Declaration of stored procedures for NHibernate consists of two parts: a .hbm.xml containing named queries with a TSQL statement to invoke each stored procedure, which is compiled as Embedded Resource:

CREATE PROCEDURE [dbo].[dev_Generate_DAL_HBM_XML] AS

  PRINT '<?xml version="1.0" encoding="utf-8"?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
    assembly="My.Project" namespace="My.Project.DAL">'
  
  DECLARE @oid INT, @id NVARCHAR(50)

  DECLARE cT CURSOR FOR
  SELECT object_id, name
  FROM sys.procedures p
  INNER JOIN Meta_Procedure mp ON p.name = mp.ID
  WHERE mp.UseDAL = 1
  ORDER BY name

  OPEN cT
  FETCH cT INTO @oid, @id

  WHILE @@FETCH_STATUS = 0 BEGIN
    DECLARE @cid NVARCHAR(50), @datatype NVARCHAR(50), 
      @isoutput BIT, @parameterid INT
    
    DECLARE cP cursor FOR
    SELECT  REPLACE(p.name, '@', ''), t.name, p.is_output, p.parameter_id
    FROM  sys.parameters p
    INNER JOIN sys.types t ON p.user_type_id = t.user_type_id
    WHERE   p.object_id = @oid
    ORDER BY p.parameter_id

    PRINT '    <sql-query name="' + @id + '" callable="true">'

    OPEN cP
    FETCH cP INTO @cid, @datatype, @isoutput, @parameterid

    WHILE @@FETCH_STATUS = 0 BEGIN
      IF @datatype = 'nvarchar' 
        SET @datatype = 'string'
      ELSE IF @datatype = 'date'
        SET @datatype = 'DateTime?'
      ELSE
        SET @datatype = @datatype + '?'

      PRINT '        <query-param name="' + @cid + '" type="' + @datatype + '" />'
      
      FETCH cP INTO @cid, @datatype, @isoutput, @parameterid
    END

    CLOSE cP

    PRINT 'begin'
    PRINT '    execute ' + @id 
    
    OPEN cP
    FETCH cP INTO @cid, @datatype, @isoutput, @parameterid

    WHILE @@FETCH_STATUS = 0 BEGIN
      IF @datatype = 'nvarchar' 
        SET @datatype = 'string'
      ELSE IF @datatype = 'date'
        SET @datatype = 'DateTime?'
      ELSE
        SET @datatype = @datatype + '?'

      IF @parameterid = 1
        PRINT '        :' + @cid
      ELSE BEGIN
        PRINT '        , :' + @cid
      END
      
      FETCH cP INTO @cid, @datatype, @isoutput, @parameterid
    END
    CLOSE cP

    DEALLOCATE cP
    
    PRINT 'end'
    PRINT '    </sql-query>'
    
    FETCH cT into @oid, @id
  END

  CLOSE cT
  DEALLOCATE cT

  PRINT '</hibernate-mapping>'

For each stored procedure, we need a C# method which calls the corresponding named query. The SP’s parameters are retrieved from the sys.parameters catalog view, and their data types translated just as we handled table columns.

CREATE PROCEDURE [dbo].[dev_Generate_DAL_Procedures] AS

  PRINT 'using System;'
  PRINT 'using System.Collections;'
  PRINT 'using NHibernate;'
  PRINT ''
  PRINT 'namespace My.Project.DAL'
  PRINT '{'
  PRINT '    public static class Procedures'
  PRINT '    {'
  
  DECLARE @oid INT, @id NVARCHAR(50)

  DECLARE cT CURSOR FOR
  SELECT object_id, name
  FROM sys.procedures p
  INNER JOIN Meta_Procedure mp ON p.name = mp.ID
  WHERE mp.UseDAL = 1
  ORDER BY name

  OPEN cT
  FETCH cT INTO @oid, @id

  WHILE @@FETCH_STATUS = 0 BEGIN
    DECLARE @cid NVARCHAR(50), @datatype NVARCHAR(50), @isoutput BIT
    
    DECLARE cP cursor FOR
    SELECT  REPLACE(p.name, '@', ''), t.name, p.is_output
    FROM  sys.parameters p
    INNER JOIN sys.types t ON p.user_type_id = t.user_type_id
    WHERE   p.object_id = @oid
    ORDER BY p.parameter_id

    PRINT '        public static IList ' + @id + '(ISession session'

    OPEN cP
    FETCH cP INTO @cid, @datatype, @isoutput

    WHILE @@FETCH_STATUS = 0 BEGIN
      IF @datatype = 'nvarchar' 
        SET @datatype = 'string'
      ELSE IF @datatype = 'date'
        SET @datatype = 'DateTime?'
      ELSE
        SET @datatype = @datatype + '?'

      PRINT '            , ' + 
        CASE WHEN @isoutput = 1 THEN 'out ' ELSE '' END + 
        @datatype + ' ' + @cid
      
      FETCH cP INTO @cid, @datatype, @isoutput
    END

    PRINT '            )'
    PRINT '        {'
    PRINT '            IQuery sp = session.GetNamedQuery("' + @id + '");'

    CLOSE cP

    OPEN cP
    FETCH cP INTO @cid, @datatype, @isoutput

    WHILE @@FETCH_STATUS = 0 BEGIN
      IF @datatype = 'nvarchar' 
        SET @datatype = 'string'
      ELSE IF @datatype = 'date'
        SET @datatype = 'DateTime?'
      ELSE
        SET @datatype = @datatype + '?'

      IF @datatype = 'string'
        PRINT '            sp.SetParameter<' + @datatype + 
          '>("' + @cid + '", ' + @cid + ');'
      ELSE BEGIN
        PRINT '            sp.SetParameter<' + @datatype + 
          '>("' + @cid + '", ' + @cid + ');'
      END
      
      FETCH cP INTO @cid, @datatype, @isoutput
    END
    CLOSE cP

    DEALLOCATE cP
    
    PRINT '            return sp.List();'
    PRINT '        }'
    
    FETCH cT INTO @oid, @id
  END

  CLOSE cT
  DEALLOCATE cT

  PRINT '    }'
  PRINT '}'

Generating NHibernate Classes for MSSQL in TSQL (2)

February 20, 2012

After retrieving the necessary meta data on tables and columns, we can now generate the C# classes for NHibernate and the necessary mapping information.

CREATE PROCEDURE [dbo].[dev_Generate_DAL_Classes] AS

  SET NOCOUNT ON

  PRINT 'using System;'
  PRINT 'using System.Collections.Generic;'
  PRINT 'using System.Linq;'
  PRINT 'using System.Text;'
  PRINT 'using System.ComponentModel;'
  PRINT 'using System.Runtime.Serialization;'
  PRINT 'using NHibernate.Validator.Constraints;'
  PRINT ''
  PRINT 'namespace My.Project.DAL'
  PRINT '{'

  DECLARE @oid INT, @id NVARCHAR(50), @tabletype NVARCHAR(1)

  DECLARE cT CURSOR FOR
  SELECT OID, ID, TableType
  FROM Meta_Table
  WHERE UseDAL = 1
  ORDER BY ID

  OPEN cT
  FETCH cT INTO @oid, @id, @tabletype

  WHILE @@FETCH_STATUS = 0 BEGIN
    PRINT '    public partial class ' + @id
    PRINT '    {'

    DECLARE @cid NVARCHAR(50), @datatype NVARCHAR(50), @length INT,
      @isnullable BIT, @isidentity BIT, @lookup NVARCHAR(50)

    DECLARE cC CURSOR FOR
    SELECT ID, DataType, Length, IsNullable, IsIdentity, LookupTable
    FROM   Meta_Column
    WHERE  Tbl_OID = @oid
    AND    IsActive = 1
    ORDER BY Seq

    OPEN cC
    FETCH cC INTO @cid, @datatype, @length, @isnullable,
      @isidentity, @lookup

    WHILE @@FETCH_STATUS = 0 BEGIN
      IF @datatype = 'nvarchar'
        SET @datatype = 'string'
      ELSE IF @datatype = 'varchar'
        SET @datatype = 'string'
      ELSE IF @datatype = 'date'
        SET @datatype = 'DateTime'
      ELSE IF @datatype = 'datetime'
        SET @datatype = 'DateTime'
      ELSE IF @datatype = 'bit'
        SET @datatype = 'bool'
      ELSE IF @datatype = 'varbinary' BEGIN
        SET @datatype = 'byte[]'
        SET @isnullable = 0
      END

      IF @isidentity = 1 OR @cid = 'OID'
        PRINT '        public virtual ' + @datatype + ' '
          + @cid + ' { get; private set; }'
      ELSE IF @lookup IS NOT NULL BEGIN
        IF @isnullable = 0
          PRINT '        [NotNull]'
        PRINT '        public virtual ' + @lookup + ' '
          + REPLACE(@cid, '_OID', '') + ' { get; set; }'
      END ELSE BEGIN
        IF @datatype = 'string' BEGIN
          IF @isnullable = 0
            PRINT '        [NotNullNotEmpty]'
          IF @length IS NOT NULL
            PRINT '        [Length('
              + CONVERT(NVARCHAR, @length) + ')]'
        END ELSE
          IF @isnullable = 1
            SET @datatype = @datatype + '?'

        PRINT '        public virtual ' + @datatype + ' '
          + @cid + ' { get; set; }'
      END
      FETCH cC INTO @cid, @datatype, @length, @isnullable,
        @isidentity, @lookup
    END

    CLOSE cC
    DEALLOCATE cC

    PRINT '    }'

    FETCH cT INTO @oid, @id, @tabletype
  END

  CLOSE cT
  DEALLOCATE cT

  PRINT '}'

This stored procedure generates a C# class for each of the tables with the UseDAL flag set to true by the developer. For the columns, the SQL Server data types need to be translated into C# (i.e. .Net data types)

And now for the Class Maps

CREATE PROCEDURE [dbo].[dev_Generate_DAL_ClassMaps] AS

  SET NOCOUNT ON

  PRINT 'using System;'
  PRINT 'using System.Collections.Generic;'
  PRINT 'using System.Linq;'
  PRINT 'using System.Text;'
  PRINT 'using FluentNHibernate.Mapping;'
  PRINT 'using NHibernate.Validator.Constraints;'
  PRINT ''
  PRINT 'namespace My.Project.DAL'
  PRINT '{'

  DECLARE @oid INT, @id NVARCHAR(50), @tabletype NVARCHAR(1)

  DECLARE cT CURSOR FOR
  SELECT OID, ID, TableType
  FROM Meta_Table
  WHERE UseDAL = 1
  ORDER BY ID

  OPEN cT
  FETCH cT INTO @oid, @id, @tabletype

  WHILE @@FETCH_STATUS = 0 BEGIN
    PRINT '    public partial class ' + @id + '_Map: ClassMap<' + @id + '>'
    PRINT '    {'
    PRINT '        public ' + @id + '_Map()'
    PRINT '        {'
    PRINT '            Table("' + @id + '");'

    DECLARE @cid NVARCHAR(50), @datatype NVARCHAR(50), @length INT,
      @isnullable BIT, @isidentity BIT, @lookup NVARCHAR(50)

    DECLARE cC CURSOR FOR
    SELECT ID, DataType, Length, IsNullable, IsIdentity, LookupTable
    FROM  Meta_Column
    WHERE  Tbl_OID = @oid
    AND    IsActive = 1
    ORDER BY Seq

    OPEN cC
    FETCH cC INTO @cid, @datatype, @length, @isnullable,
      @isidentity, @lookup

    WHILE @@FETCH_STATUS = 0 BEGIN
      IF @datatype = 'nvarchar'
        SET @datatype = 'string'
      ELSE IF @datatype = 'varchar'
        SET @datatype = 'string'
      ELSE IF @datatype = 'date'
        SET @datatype = 'DateTime'
      ELSE IF @datatype = 'datetime'
        SET @datatype = 'DateTime'
      ELSE IF @datatype = 'bit'
        SET @datatype = 'bool'
      ELSE IF @datatype = 'varbinary'
        SET @datatype = 'byte[]'

      IF @isidentity = 1
        PRINT '            Id(x => x.' + @cid + ').GeneratedBy.Identity();'
      ELSE IF @cid = 'OID'
        PRINT '            Id(x => x.' + @cid + ');'
      ELSE IF @cid = 'DateCreated'
        PRINT '            Map(x => x.' + @cid + ').Generated.Insert();'
      ELSE BEGIN
        IF @lookup IS NOT NULL BEGIN
          PRINT '            References(x => x.' +
              REPLACE(@cid, '_OID', '') + ', "' + @cid + '")' +
            CASE WHEN @isnullable = 0 THEN '.Not.Nullable()' ELSE '' END +
            '.LazyLoad();'
        END ELSE BEGIN
          PRINT '            Map(x => x.' + @cid + ')' +
            CASE WHEN @isnullable = 0 THEN '.Not.Nullable()' ELSE '' END +
            ';'
        END
      END
      FETCH cC INTO @cid, @datatype, @length, @isnullable,
        @isidentity, @lookup
    END

    CLOSE cC
    DEALLOCATE cC

    PRINT '        }'
    PRINT '    }'

    FETCH cT INTO @oid, @id, @tabletype
  END

  CLOSE cT
  DEALLOCATE cT

  PRINT '}'

These stored procedures can be invoked from the command line using the SQL Server osql tool

set osql=osql -S localhost -U [user] -P [pwd] -d [database] -n -w 1000
%osql% -Q "exec dev_Generate_DAL_Classes" -o "d:\path\to\Classes.cs"
%osql% -Q "exec dev_Generate_DAL_ClassMaps" -o "d:\path\to\ClassMaps.cs"

Generating NHibernate Classes for MSSQL in TSQL

February 19, 2012

This series describes the generation of C# classes and database access for NHibernate 2.1.2GA and FluentNHibernate 1.0. These are not the most current versions, but I need to document my existing code to prepare support of current versions of these libraries. I have previously written about NHibernate accessing Oracle in various post.

We generate the C# classes from the meta model contained in SQL Server system catalog views. However, generation does not directly access the catalog views, but caches information in meta tables that can hold additional information on tables and columns:

CREATE TABLE [dbo].[Meta_Table](
    [OID] [int] IDENTITY(1,1) NOT NULL,
    [ID] [nvarchar](50) NOT NULL,
    [TableType] [varchar](1) NOT NULL,
    [UseDAL] [bit] NOT NULL,
    CONSTRAINT [PK_Meta_Table] PRIMARY KEY CLUSTERED 
    ( [OID] ASC )
)
CREATE TABLE [dbo].[Meta_Column](
    [OID] [int] IDENTITY(1,1) NOT NULL,
    [Tbl_OID] [int] NOT NULL,
    [IsActive] [bit] NOT NULL,
    [Seq] [int] NOT NULL,
    [ID] [nvarchar](50) NOT NULL,
    [DataType] [varchar](50) NOT NULL,
    [Length] [int] NULL,
    [IsNullable] [bit] NOT NULL,
    [IsIdentity] [bit] NOT NULL,
    [LookupTable] [nvarchar](50) NULL,
    CONSTRAINT [PK_Meta_Column] PRIMARY KEY CLUSTERED 
    ( [OID] ASC )
) 

These tables are filled from the system catalog views using the following stored procedure

CREATE PROCEDURE [dbo].[dev_Retrieve_Meta] AS

  SET NOCOUNT ON;

  INSERT INTO Meta_Table (ID, TableType)
  SELECT o.name, o.type
  FROM sys.objects o
  LEFT OUTER JOIN Meta_Table t ON o.name = t.ID
  WHERE o.type IN ('V', 'U')
  AND t.OID IS NULL  ;

  UPDATE Meta_Column SET IsActive = 0;

  INSERT INTO Meta_Column (Tbl_OID, Seq, ID, DataType)  
  SELECT t.OID, c.clumn_id, c.name, ty.name 
  FROM sys.objects o
  INNER JOIN Meta_Table t ON o.name = t.ID
  INNER JOIN sys.columns c ON o.object_id = c.object_id
  INNER JOIN sys.types ty ON c.user_type_id = ty.user_type_id
  LEFT OUTER JOIN Meta_Column mc 
    ON mc.Tbl_OID = t.OID AND mc.ID = c.name
  WHERE mc.OID IS NULL;

  UPDATE Meta_Column
  SET Seq = c.column_id, 
    DataType = ty.name, 
    Length = 
      CASE WHEN ty.name IN ('varchar', 'nvarchar') 
          AND c.max_length > -1 
        THEN c.max_length 
        ELSE NULL 
      END,
    IsNullable = c.is_nullable, 
    IsIdentity = c.is_identity,
    LookupTable = lt.name,
    IsActive = 1
  FROM Meta_Column mc
  INNER JOIN meta_table t ON mc.Tbl_OID = t.OID 
  INNER JOIN sys.objects o ON o.name = t.ID
  INNER JOIN sys.columns c 
    ON o.object_id = c.object_id AND mc.ID = c.name
  INNER JOIN sys.types ty ON c.user_type_id = ty.user_type_id
  LEFT OUTER JOIN sys.foreign_key_columns fkc 
    ON fkc.parent_object_id = o.object_id 
    AND fkc.parent_column_id = c.column_id
  LEFT OUTER JOIN sys.objects lt 
    ON fkc.referenced_object_id = lt.object_id

The first INSERT statement retrieves all tables and views which are not already stored in the Meta_Table table.

Next, the new column names are retrieved from the sys.columns catalog view, and the columns’ IsActive field is set to false.

Finally, the UPDATE Meta_Column retrieves the column attributes from the catalog view, and sets the IsActive field to true for all columns still present in the catalog view (this is necessary to deal with changes in the data model).


Dapper.Net

January 2, 2012

If you develop database applications in .Net, you will almost certainly be confronted with the questions “How do I access my data?” and “Which DAL/ORM framework should I use?” etc.

The learning curve usually starts with plain-old SqlCommand and DataReader, goes on with “hacking your own” and ends with an undecidable option of existing frameworks.

Recently I came across Dapper.Net, which essentially is an extension class for the IDbConnection interface, and maps the result of a literal SQL command onto an object model. This is similar to my experiments (2 years ago already) that I developed here, but better optimized for performance as it is used at Stack Overflow.

See the author’s blog for discussion of the ORM and optimization of existing queries. The Dapper homepage also has some numbers comparing performance with other ORM’s.

 


First Steps with NHibernate, Fluent, Oracle, C#, and Visual Studio 2010 RC

April 13, 2010

A few days into a new project: C#, Oracle, ASP.Net, .Net 4, VS 2010. After creating a (very!) basic data model with a couple of tables, the first task was to check whether the intended tools (C# 4, NHibernate+Fluent, ODP.Net, Oracle 11g) can be persuaded to co-operate.

Versions of tools and libraries used:

After I had defined my classes and maps as described in Fluent’s Getting Started wiki page, the first major hurdle was to get the session factory builder to execute without exception:

public static ISessionFactory Configure()
{
  var c = Fluently.Configure();
  c.Database(OracleDataClientConfiguration.Oracle10
    .ConnectionString(x => x.FromConnectionStringWithKey("default"))
    .DefaultSchema("cpa"));
  c.Mappings(m => m.FluentMappings.AddFromAssemblyOf<ConfigureDAL>());
  return c.BuildSessionFactory();
}

Creating an NHibernate session factory with Fluent, connecting to an Oracle database via ODP.Net.

Fluent provides 3 classes for Oracle access in the FluentNHibernate.Cfg.Db namespace:

  • OracleClientConfiguration for the MS Oracle Client (System.Data.OracleClient, deprecated)
  • OracleConfiguration, obsolete and replaced by:
  • OracleDataClientConfiguration for use with ODP

The connection string is stored in app.config under the name “default”:

<connectionStrings>
  <add name="default"
    connectionString="Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)
      (HOST=orasrv)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)
      (SERVICE_NAME=orcl)));User Id=cpa;Password=the_password;" />
</connectionStrings>

Trying to execute the BuildSessionFactory() method raised the first exception:

The element ‘class’ in namespace ‘urn:nhibernate-mapping-2.2’ has invalid child element ‘property’ in namespace ‘urn:nhibernate-mapping-2.2’. List of possible elements expected: ‘meta, subselect, cache, synchronize, comment, tuplizer, id, composite-id’

I tried to step through the source code, and noticed that the downloaded source code version did not match the installed 1.0 RTM of Fluent. So I updated to build 636, which solved this problem, but raised a new one:

The IDbCommand and IDbConnection implementation in the assembly Oracle.DataAccess could not be found.
Ensure that the assembly Oracle.DataAccess is located in the application directory or in the Global Assembly Cache.
If the assembly is in the GAC, use <qualifyAssembly/> element in the application configuration file to specify the full name of the assembly.

Thanks to the interweb I found this blog which provided the solution: Force .Net to load a specific version of ODP (2.111.7.20 in my case), instead of looking for any version:

<runtime>
  <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
    <qualifyAssembly partialName="Oracle.DataAccess"
      fullName="Oracle.DataAccess, Version=2.111.7.20, Culture=neutral,
                PublicKeyToken=89b483f429c47342"/>
  </assemblyBinding>
</runtime>

Start the Visual Studio prompt and type

gacutil /l | find "Oracle"

to find out which version of ODP you have installed. The assemblies come in a version for .Net 1 and for .Net 2.

This finally caused the BuildSessionFactory to execute successfully, let me create a session and query a table in the database.

I had feared that ODP would not work using the .Net 4 profile, and had thus switched to .Net 2.0 and .Net 3.5 to debug. However, you don’t have Linq in .Net 2, and the Oracle assembly cannot be referenced by projects targeting .Net 3.5 Client Profile and .Net 4.0 Client Profile. Adding the qualified assembly worked in .Net 4, after all.