I’m writing a new application on top of an existing Oracle database. The current program doesn’t have proper domain classes and just sends raw SQL statements to the database server. Being the good developer that I am, I have decided to write proper models and then use NHibernate to map those models to the database tables.
I have 3 conditions where a User Type becomes very useful.
- I would like to map yes or no (“Y”/”N”) values to boolean values.
- I would like to map active or inactive (“A”/”I”) values to boolean values. The property would be something like
public virtual bool IsActive { get; set; }. - I would like to map string columns that hold URLs to System.Uri types.
The first part was to write an abstract base class. This takes care of a lot of the boilerplate code when writing your own custom user type. For this, I used Darrell Mozingo’s very helpful post.
public abstract class ImmutableUserType<T> : IUserType
{
public abstract SqlType[] SqlTypes { get; }
public abstract object NullSafeGet(IDataReader rs, string[] names, object owner);
public abstract void NullSafeSet(IDbCommand cmd, object value, int index);
public Type ReturnedType
{
get { return typeof(T); }
}
public bool IsMutable
{
get { return false; }
}
public new bool Equals(object x, object y)
{
if (ReferenceEquals(x, y))
{
return true;
}
if (x == null || y == null)
{
return false;
}
return x.Equals(y);
}
public int GetHashCode(object x)
{
return x.GetHashCode();
}
public object DeepCopy(object value)
{
return value;
}
public object Replace(object original, object target, object owner)
{
return original;
}
public object Assemble(object cached, object owner)
{
return cached;
}
public object Disassemble(object value)
{
return value;
}
}
This base class is going to make it much easier to write my custom types.
public class StatusType : ImmutableUserType<bool>
{
private const string TRUE_STRING = "A";
private const string FALSE_STRING = "I";
public override SqlType[] SqlTypes
{
get { return new[] { new SqlType(DbType.String) }; }
}
public override object NullSafeGet(IDataReader rs, string[] names, object owner)
{
string statusValue = NHibernateUtil.String.NullSafeGet(rs, names[0]).ToString();
return statusValue == TRUE_STRING;
}
public override void NullSafeSet(IDbCommand cmd, object value, int index)
{
bool boolValue = value.ToBoolean();
string valueToSave = boolValue ? TRUE_STRING : FALSE_STRING;
NHibernateUtil.String.NullSafeSet(cmd, valueToSave, index);
}
}
The ToBoolean() is a simple extension method wrapper for Convert.ToBoolean(). Writing the type, once you figure it out, really isn’t that difficult. The next part that you need to think about it mapping. That’s also not that difficult. The Fluent NHibernate mapping looks like this. Take note of the UserType configuration method.
public class MyObjectMap : ClassMap<:MyObject>
{
public MyObjectMap()
{
// snip
Map(x => x.IsActive).Column("ACTIVE_FLAG").UserType<StatusType>();
// snip
}
}
So we’ve written it, we’ve mapped it, and now we want to test it. The following example uses Moq and NUnit. I’m using test cases to reduce the number of tests that I need to write.
[TestFixture]
public class StatusTypeTests
{
[Test]
[TestCase("", Result = false)]
[TestCase("A", Result = true)]
[TestCase("I", Result = false)]
[TestCase(null, Result = false)]
public bool NullSafeGetTests(string status)
{
var dataReader = new Mock<IDataReader>();
dataReader.Setup(x => x.IsDBNull(0)).Returns(false);
dataReader.Setup(x => x[0]).Returns(status);
var userType = new StatusType();
bool value = (bool)userType.NullSafeGet(dataReader.Object, new[] { status }, null);
return value;
}
[Test]
[TestCase(true, Result = "A")]
[TestCase(false, Result = "I")]
[TestCase(null, Result = "I")]
public object NullSafeSetTests(bool status)
{
var dbCommand = new Mock<IDbCommand>();
var dataParameter = new Mock<IDataParameter>();
dataParameter.SetupProperty(x => x.Value, "");
var dataParameterCollection = new Mock<IDataParameterCollection>();
dataParameterCollection.Setup(x => x[0]).Returns(dataParameter.Object);
dbCommand.Setup(x => x.Parameters).Returns(dataParameterCollection.Object);
var userType = new StatusType();
userType.NullSafeSet(dbCommand.Object, status, 0);
var paramValue = ((IDataParameter)dbCommand.Object.Parameters[0]).Value;
return paramValue == DBNull.Value ? null : paramValue;
}
}
The first unit test is testing values from the database. Any nulls, empty strings, or anything that isn’t an “A” should return false. The second test is making sure that only “A” and “I” is ever sent to the database.
HTH, Jarrett