Mapping boolean to string field

badgrs · **Joined:** Tue Mar 13, 2007 6:25 am **Posts:** 8

Hi,

I am looking to move to NHibernate from the VS DataSets and have a quick question regarding mappings that is quite a serious requirement for us.

As SQL Server doesn't support boolean fields we are using a char() field with the value 'Y' or 'N' (why chars instead of ints I don't know - I had nothing to do with it!). Is there a way we can use NHibernate to map a boolean property in our domain object to one fo these fields? The contents of the fields can also be null or empty, but in either case translates to false (ie anything other than 'Y' is false).
Ideally this would be without adding extra conversion logic or extra properties to the domain object. Creating some sort of re-usable plugin method would be OK.

Thanks,
Rory

devonl · **Posted:** Tue Mar 13, 2007 7:59 pm

i'm not sure if this solves your problem but it might:

Code:

<property name="hibernate.query.substitutions">true 1, false 0</property>

http://www.hibernate.org/hib_docs/nhibernate/html/queryhql.html#queryhql-expressions

here is an example of mine:

Code:

<nhibernate>
   ...
   <add key="hibernate.query.substitutions" value="true 1, false 0" />
</nhibernate>

Also, I'm not sure what you mean about SQLServer not supporting Boolean. Doesn't the "bit" datatype do it for you? That would be 1, 0, or null (personally nullable booleans give me a nervous twitch, though, so I usually give them a NOT NULL constraint...)

-devon

badgrs · **Joined:** Tue Mar 13, 2007 6:25 am **Posts:** 8

Thanks for the reply. Yes a bit datatype would have been much better, but as I said I didn't design the database and its not feasable at the minute to make that kind of change (much to my frustration). I'll have a look at your suggestion, do you have any idea if this would work for a string value as well as numerical?

devonl · **Posted:** Wed Mar 14, 2007 9:14 pm

well, i'm using NH 1.0.4.0 and I found this in SettingsFactory.cs:
IDictionary querySubstitutions = PropertiesHelper.ToDictionary( Environment.QuerySubstitutions, " ,=;:\n\t\r\f", properties );

that string as the second param passed into the PropertiesHelper method is a delimiter so to me, that seems to suggest that you can replace anything with anything and use any of those delimiters. this _should_ work:

<add key="hibernate.query.substitutions" value="true Y, false N" />

-devon

badgrs · **Joined:** Tue Mar 13, 2007 6:25 am **Posts:** 8

Thanks thats great, I'll give it a shot.

Nels_P_Olsen · **Posted:** Fri Mar 16, 2007 6:12 pm

We have nullable NCHAR(1) columns holding Y/N. We handle it by using the query substitutions mentioned above, plus this user type:

Code:

using System;
using System.Data;

using NH  = NHibernate;
using NNH = Nullables.NHibernate;

namespace MyCompany.Framework.DataAccess.Providers.NHibernate.UserTypes
{
   /// <summary>
   /// A NHibernate <see cref="NH::Type.IType"/> for a <see cref="System.Nullable&lt;Boolean&gt;"/>
   /// mapped to a CHAR(1) database column with values 'Y', 'N' or NULL.
   /// </summary>
   public class NullableYesNoType : NNH.NullableTypesType
   {
      public NullableYesNoType() : base(new NH.SqlTypes.StringFixedLengthSqlType(1))
      {
      }

      public override object NullValue
      {
         get {return null;}
      }

      public override System.Type ReturnedClass
      {
         get {return typeof(bool?);}
      }

      public override object Get(IDataReader rs, int index)
      {
         object value = rs[index];

         if (value == DBNull.Value)
         {
            return null;
         }
         else
         {
            return (Convert.ToChar(value) == 'Y');
         }
      }

      public override void Set(IDbCommand cmd, object value, int index)
      {
         IDataParameter parameter = (IDataParameter) cmd.Parameters[index];
         bool? nullableValue = (bool?) value;

         if (nullableValue.HasValue)
         {
            parameter.Value = (nullableValue.Value ? 'Y' : 'N');
         }
         else
         {
            parameter.Value = DBNull.Value;
         }
      }

      public override object FromStringValue(string xml)
      {
         if (xml == null || xml.TrimEnd().Length == 0)
         {
            return null;
         }
         else
         {
            return (xml[0] == 'Y');
         }
      }
   }
}

badgrs · **Joined:** Tue Mar 13, 2007 6:25 am **Posts:** 8

That looks exactly like what we need! Nice one!

thinkrama · **Joined:** Thu Mar 15, 2007 10:34 am **Posts:** 8

Im having problems converting varchar2 (Oracle column) to boolean

Columns have either Y or N which must be converted to true or false

App.Config
<add key="hibernate.query.substitutions" value="'Y' true, 'N' false" />

also tried this...
<addkey="hibernate.query.substitutions" value="true 1, false 0, yes 'Y', no 'N', Y 'YesA', N 'NoA'"/>

Is there anything else I have to do?

Thanks for all the help guys