Calling a Stored Procedure as part of a NHibernate transaction

The scenario

Today I had the need to call a stored procedure after some domain objects got updated. Those domain objects are persisted via NHibernate. The question here is not whether it’s good or not to have calls to sprocs mixed with NHibernate persistence because this can be argued but sometimes you have no choice when dealing with legacy code.

This is all in the context of an application with a SQL Server database.

The problem

The root of the problem I faced was that we are using a DAO pattern where a NHibernate session is started at the beginning of a request and closed at the end of the request.

My first approach was to do my objects updates and then call a method in my data access layer executing the stored procedure through ADO.NET. The problem with this was that the changes done to the objects are not persisted into the database until the session closed and the transaction committed. As a result, the stored procedure was run immediately and my update statements issued afterward.

I then thought that all I had to do was flush my NHibernate session before I exec the sproc to commit my changes to the database. However, for some reason that created some lock or pending session somewhere so my connection attempt timed out.

The solution

The idea of flushing the session and then executing the sproc was close but the last missing piece was to call the sproc via NHibernate using the same session as opposed to ADO.NET. My code ended up looking like this:

// flush the NHibernate session to commit changes

// execute the stored procedure
IQuery query = Session.CreateSQLQuery("exec MySprocName @param=:param1");
query.SetString("param1", data);

Hope this helps


Mozilla Weave Sync, FAIL!

I decided to install Mozilla weave when they released version 1.0 after being bin beta for years. I assumed they had enough time to work out the kinks and that the 1.0 tag meant it was going to be stable.
But no, my bookmarks got moved around, taken out of folders, duplicated… very disappointing!

WILT: Query by Criteria with clause on component property with NHibernate

It took me a little bit to figure this one out but it makes sense and it’s pretty simple once we know:

Let’s say I have a class MyFirstClass with a component of type MySecondClass and I want to write a query to retrieve all MyFirstClass objects where the ID of MySecondClass is 1.
My first attempt went like this:

     .Eq("MySecondClass.ID", "1"));
That didn’t work because NHibernate doesn’t understand what I meant with MySecondClass.ID.
The solution is simply to use a sub criteria like so:
      .Eq("ID", "1"));

It is important to notice that the sub criteria is created using the Property name

Mapping a nullable bit column to an enum with NHibernate

I recently had to map an enum with 3 possible values (Pending, Approved, denied) to a nullable bit field in a SQL database. This type of mapping is not supported natively by NHibernate which by default translates enum values to their integer representation. I found several articles on handling the mapping to a string which is pretty straight forward using the … type but I haven’t found anything about this particular scenario.

This is where custom user types come to the rescue and this is what I am going to explain in this article.

To create a custom user type, we just need to create a class that implements NHibernate.UserTypes.IUserType. In our case, the implentation is pretty easy with the only methods that required a little bit of thinking being NullSafeGet and NullSafeSet.
This is how the class looks like:

public class NullableApprovalStatusType : IUserType
    public bool Equals(object x, object y)
        return x == null ? y == null : x.Equals(y);

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

    public object NullSafeGet(IDataReader rs, string[] names,
                              object owner)
        bool? dbValue = (bool?) NHibernateUtil.Boolean
                                 .NullSafeGet(rs, names);
        switch (dbValue)
            case true:
                return ApprovalStatusType.Approved;
            case false:
                return ApprovalStatusType.Denied;
                return ApprovalStatusType.Pending;

    public void NullSafeSet(IDbCommand cmd, object value,
                            int index)
        var obj = (ApprovalStatusType) value;
        bool? dbValue = null;
        switch (obj)
            case ApprovalStatusType.Approved:
                dbValue = true;
            case ApprovalStatusType.Denied:
                dbValue = false;
            case ApprovalStatusType.Pending:
                dbValue = null;
        NHibernateUtil.Boolean.NullSafeSet(cmd, dbValue,index);

    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 DeepCopy(cached);

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

    public SqlType[] SqlTypes
        get { return new[] {new SqlType(DbType.Boolean)}; }

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

    public bool IsMutable
        get { return false; }

Now that we have our custom user type ready we can just do out mapping:

<property name="ApprovalStatus" column="is_approved"
                MyAssembly" />

It is nice to notice that the custom user type doesn’t have to be in the same namespace or assembly as the mapped class which allows us to keep it in the data access layer and not introduce any NHibernate dependency to the business logic or domain objects.

WILT: NullPointerException when calling startActivity

That’s such a beginner error but I have no shame posting about it because I AM a beginner with Android  🙂

When trying to start a ListActivity from my main Activity, I kept on getting a NullPointerException. The code to launch my activity looked like this:

myActivityInstance.startActivity(new Intent(Intent.ACTION_VIEW));

Well, the problem was that I forgot to set the content view at the beginning of my launched activity. All I had to do was add this line at the beginning of my onCreate event handler:


Unit testing methods with date/time in .net

One of the very important rules of unit testing is to make sure that the result of the test is predictable and repeatable. This is fairly straight forward to achieve in most cases by using constants instead of random values for example but how do we deal with methods using the current date and/or time?

If we just use the DateTime.Now property, the value will change for each test run which will cause us to have unpredictable and sometimes unrepeatable results.

The solution to this problem is pretty simple, create a wrapper class for the DateTime methods that we may need so we can mock the call to methods returning time dependent values.

A very basic wrapper class could look like this:

   1: using System;


   3: public interface IDateTime

   4: {

   5:     #region Methods


   7:     DateTime Now();


   9:     #endregion Methods

  10: }


  12: public class DateTimeWrapper : IDateTime

  13: {

  14:     #region Public Methods


  16:     public DateTime Now()

  17:     {

  18:         return DateTime.Now;

  19:     }


  21:     #endregion Public Methods

  22: }


We can now use the DateTimeWrapper implementation of IDateTime in our code and the IDateTime interface can easily be mocked to return a constant value for the Now() method call.

WILT: XML encode a string in .net

Always wondered why I couldn’t find a method that would XML encode a string, effectively escaping the 5 illegal characters for XML. There is such a method but its location in the API is not intuitive at all. It’s in the System.Security namespace:

   1: public static string Escape(

   2:     string str

   3: )

Its usage is:

   1: tagText = System.Security.SecurityElement.Escape(tagText);

This will escape the 5 characters <, >, &, “ and ‘