Castle ActiveRecord with DetachedCriteria

My current development environment is Visual Studio Express C# Edition (read that as free), Castle ActiveRecord's latest svn trunk(usually within a few days), and NHibernate svn trunk. As of NHibernate version 1.2.0, there is a very cool new class out there ... DetachedCriteria. This class lets you set all of your Castle relational attributes like BelongsTo, HasMany, etc. as lazy fetch, and over-ride this for searches, reports, or anytime you know ahead of time that you will be touching the related classes by calling detachedCriteria.SetFetchMode(..., FetchEnum.Eager).

As a good netizen, I have tried to contribute to NHibernate and Castle ActiveRecord even if only in the smallest of ways.

Oh yeah, I tried mapping to a SQL VIEW, and it worked GREAT! I received a comment after my last post, indicating that there is a better way, and I am sure of it, but the view guaranteed that I only have one database request for my dataset. NHibernate was wanting to re-fetch my missing associated instances. They were not there 1 millisecond ago, when we did our outer join, but NHibernate wants to make sure, so it does another 25 selects or so (this depends on how bad the data is). To be fair, NHibernate is improving every day, and in a few months, this might be wrong. The view also kept me from lying about the associated table's primary key. NHibernate seemed to want my foreign key to be the primary key, not just a unique key field.

  [ActiveRecord("VW_HistorySearch", Mutable=false)]
  public class HistorySearch : ActiveRecordValidationBase<HistorySearch>
  {
    // primary key of the master table for this search
    long _h_id;

    [PrimaryKey(Generator=PrimaryKeyType.Assigned)]
    public long h_id
    {
      get { return _h_id; }
      set { _h_id = value; }
    }
    
    // ... standard class implementation goes here

    // Builder class, used to move from search to mutable instance
    public History GetHistory()
    {
      // return History(...) or History.Find(_h_id);
      return new History(full list of properties);
    }
  }

  class MySearchForm
  {
    // +++++++++ Find Method +++++++++

    // Using the view (just like I would use a table)
    List<ICriterion> whereClause;

    //... populate whereClause ...

    DetachedCriteria dc = DetachedCriteria.For(typeof(HistorySearch));
    foreach (ICriterion cn in whereClause)
    {
      dc.Add(cn);
    }
    // this method is in patch NH-973
    CountQuery cq = new CountQuery(typeof(HistorySearch), dc);
    int count = (int)ActiveRecordMediator.ExecuteQuery(cq);
    dc.SetProjection(null);

    // find activation history records 
    IList<HistorySearch> hList = HistorySearch.SlicedFindAll(0, limit, dc,
      sortOrder.OrderBy);

    Debug.WriteLine(String.Format("Records found: {0} of {1}", hList.Count, count));
  }

It looks like NHibernate does not support HQL update statements in queries yet, so I am forced to decide between a series of instance updates (hopefully batched), or a raw SQL call for each modification. I wrote and tested a pure (Microsoft) SQL version that works for me.

// Change [History] Records's [Status]
// parameter 1: either IList<History> or IList
private void changeStatus(IList<History> hList, Status newStatus)
{
  /* HQL would be ...
   * UPDATE History h SET h.Status = :newStatus where ah.ID in (:idList)
   */
  IList<Int64> hIdList = new List<Int64>(hList.Count);
  for (int i = 0; i < hList.Count; i++)
  {
    hIdList.Add(hList[i].ID);
  }

  StringBuilder dml = new StringBuilder(
    "UPDATE dbo.CIHistory SET Status_ID = :newStatus WHERE ID IN (:idList)");
  ARHelper.AddParameter<Int32>(dml, ":newStatus", newStatus.KeyID);
  ARHelper.AddParameterList<Int64>(dml, ":idList", hIdList);

  int rowsAffected = ARHelper.ExecuteNonQuery(typeof(History), dml.ToString());

  // THE non-SQL alternative should be this ...
  // it would be nice if ahList could do an HQL update
  /*
  foreach (History h in hList)
  {
    h.Status = stat;
    h.Save();
  }
  */
}

// a collection of methods dependent on the ActiveRecord library
class ARHelper
{
  internal static void AddParameter<T>(StringBuilder dml, string param, T value)
  {
    dml.Replace(param, value.ToString());
  }

  // only works for a list of IDs, since it does not add quotes
  internal static void AddParameterList<T>(StringBuilder dml, string param, IList<T> valueList)
  {
    // build a comma separated list of IDs
    StringBuilder values = new StringBuilder(valueList.Count * 9);
    for (int i = 0; i < valueList.Count; i++) 
    {
      if (i > 0) 
      {
        values.Append(',');
      }
      values.Append(valueList[i].ToString());
    }
    dml.Replace(param, values.ToString());
  }

#region Execute Insert or Update

  // returns number of records affected
  protected internal static int ExecuteNonQuery(Type targetType, string dml)
  {
    ISessionFactoryHolder holder = ActiveRecordMediator.GetSessionFactoryHolder();
    NHibernate.ISession session = holder.CreateSession(targetType);
    int rowsAffected = -1;
    IDbConnection conn = null;
    IDbCommand cmd = null;

    try
    {
      conn = session.Connection;
      cmd = conn.CreateCommand();
      cmd.CommandText = dml;
      cmd.CommandType = CommandType.Text;
      rowsAffected = cmd.ExecuteNonQuery();
    }
    catch (Exception ex)
    {
      throw new ActiveRecordException("Could not perform ExecuteNonQuery for " 
        + targetType.Name, ex);
    }
    finally
    {
      if (cmd != null) cmd.Dispose();
      holder.ReleaseSession(session);
    }

    //TODO: assert statement contains "INSERT" or "UPDATE"
    return rowsAffected;
  }

#endregion

}