Thursday, April 10, 2008

NHibernate Tales n°1 - Mapping Oracle RAW [Guid]

Hi, recently I read many requests about how to map an Oracle RAW() Type. About 2 months ago, I was one of the those who was asking for a solution, I really cannot believe how could be possible there is not a solution, so searching in the Net I found 3 kind of solution, then, I decided what was the better for me and so I've implemented in my project, let's begin.

First of all, in general a RAW column data type describe a raw binary data, generally describing a Guid. Even generally, when using a RAW data type mean, as .NET world, using a GUID, so our need is to use a RAW as GUID.

Normally there are 2 modes of reading a RAW data type, in the ADO.NET way, from a command:

  • if not is specified the RAW column returns as a Byte[] array
  • if specified, it's possible to use during reading the RAWTOHEX(columnName) that converts from a raw byte array to a binary string, and vice versa during writing the HEXTORAW(columnName) that converts back from a binary string to a raw byte array.

but what if we want to map that column data type in a HBM mapping? Well, there are 3 ways (usually I use the last one):

  • Creating a custom NHibernate Dialect inheriting from the OracleDialect and map the Guid data type so the convertion is made while reading clearly by the engine
  • Specify a "formula" surrounding the column name and decorate the column name with the RAWTOHEX function, bu the problem came up when we want to write and we should use the HEXTORAW function
  • Creating a custom IUserType that converts to and from the RAW type.

As I said before I prefer the last one, so here is my solution: (here i will discuss the code in pieces, then in the end of the post is available the code to download)

Firts of all, we start creating a class named (in my case) RawType:

using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using NHibernate;
using NHibernate.SqlTypes;
using NHibernate.UserTypes;

namespace MySolution.Data.Types
{
public class RawType : IUserType
{



The class must inherits from IUserType, this interface instructs NHibernate we want map a specifed database column name with a custom type. Next, to correctly implement the type we need to specify the NHibernate SqlType represented by our custom type, I used the DbType.Binary in a static SqlType array variable as:



private staticSqlType[] types = newSqlType[] { newSqlType(DbType.Binary) };



Next the core part of the implementation are the methods NullSafeGet and NullSafeSet that are used to handle the "raw ado read value" and translate it depending if we are reading or writing. In order, the implementation handles this situation as follow:



Reading - the value is converted from the raw binary data to System.String, this is done using the System.Guid structure that permit to create a Guid from binary array assuming a valid 32 byte array, the the ToString method with the "N" format creates a guid strings without wpecial characters



Writing - the value is converted from System.String to a raw binary representation, this is yet done using the System.Guid structure ToByteArray method



public object NullSafeGet(IDataReader rs, string[] names, object owner)
{
string result = null;
byte[] buffer = (byte[])NHibernateUtil.Binary.NullSafeGet(rs, names[0]);
if (null != buffer)
{
result = new Guid(buffer).ToString("N");
Array.Clear(buffer, 0, buffer.Length);
}
return result;
}
public void NullSafeSet(IDbCommand cmd, object value, int index)
{
if (null != value)
{
byte[] buffer = new Guid(((string)value)).ToByteArray(); NHibernateUtil.Binary.NullSafeSet(cmd, buffer, index);
Array.Clear(buffer, 0, buffer.Length);
}
}



The rest part of the class is a normal implementation of a NHibernate custom type.



Now the interesting part is the hbm mapping file. We can use our type in any property even as id, declaring the generator as "assigned" because we can't use any algorithm to assign the id value.



<?xml version="1.0" encoding="utf-8" ?>
<
hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
assembly="MySolution"
namespace="MySolution.Common.Entities">
<
class name="Class1"
table="TABLE1">
<id name="Id"
type="MySolution.Data.Types.RawType, MySolution.Data">
<
column name="ID" />
<
generator class="assigned" />
</
id>



The last thing, I implemented an helper static method which generates a new "guid" from scratch.



public static string GenerateNewId()
{
Guid nextVal = Guid.NewGuid();
return nextVal.ToString("N");
}


I hope this helps, I spend much time in finding a solution to my problem, because in the net there's no so much on the argument, so I had to "create" a solution for my project. The next post would be another kind of serializing/deserializing binary raw to and from an Oracle Database taking care when the RAW column is not sure that contains a "well formed" binary data to build a System.Guid.



Here is the complete RawType code:



using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using NHibernate;
using NHibernate.SqlTypes;
using NHibernate.UserTypes;

namespace MySolution.Data.Types
{
public class RawType : IUserType
{
public static string GenerateNewId()
{
Guid nextVal = Guid.NewGuid();
return nextVal.ToString("N");
}

private static SqlType[] types = new SqlType[] { new SqlType(DbType.Binary) };
public object Assemble(object cached, object owner)
{
return DeepCopy(cached);
}

public new bool Equals(object x, object y)
{
return (x == null ? false : x.Equals(y));
}

public object DeepCopy(object value)
{
return value;
}

public object Disassemble(object value)
{
return DeepCopy(value);
}

public int GetHashCode(object x)
{
return x.GetHashCode();
}

public bool IsMutable
{
get { return true; }
}

public object NullSafeGet(IDataReader rs, string[] names, object owner)
{
string result = null;
byte[] buffer = (byte[])NHibernateUtil.Binary.NullSafeGet(rs, names[0]);
if (null != buffer)
{
result = new Guid(buffer).ToString("N");
Array.Clear(buffer, 0, buffer.Length);
}
return result;
}

public void NullSafeSet(IDbCommand cmd, object value, int index)
{
if (null != value)
{
byte[] buffer = new Guid(((string)value)).ToByteArray();
NHibernateUtil.Binary.NullSafeSet(cmd, buffer, index);
Array.Clear(buffer, 0, buffer.Length);
}
}

public object Replace(object original, object target, object owner)
{
return original;
}

public Type ReturnedType
{
get { return typeof(string); }
}

public SqlType[] SqlTypes
{
get { return types; }
}
}
}



Ciao!



Technorati Tag: ,

3 comments:

Anonymous said...

Hey, great post! For some reason, we can't get it to work. If we leave it as-is, we get an InvalidCastException when casting value to Guid (in NullSafeSet). We tried using value.ToString() instead but that only gets us a bit further when we get another InvalidCastException in (NHibernate.Tuple.Entity.PocoEntityTuplizer.SetPropertyValuesWithOptimizer(Object entity, Object[] values))
We are hitting a SQL server and an Oracle database with the same code (not the same runtime instance) so your solution seems to be the preferred solution for us.
Thanks for any pointers.

Regards,

Eric.

Kiquenet said...

Hi,

I have a string of a RAW(16) Oracle

string raw16 = "231BDE7199668043ACFE34CC88668CC3";

I need convert it to System.Guid type in .NET C#

any help, please ??

Kiquenet said...

This code fails !!!

string raw16 = "231BDE7199668043ACFE34CC88668CC3";
ASCIIEncoding enc = new ASCIIEncoding();
byte[] braw = enc.GetBytes(raw16);

Guid g1 = new Guid( new System.Runtime.Remoting.Metadata.W3cXsd2001.SoapHexBinary( (byte[])braw).ToString() );