Skip to main content

Castle ActiveRecord calling a Stored Procedure

Update: I have contributed patch AR-156 that allows full integration of Insert, Update and Delete to ActiveRecord models.

If you've been reading my blog lately, you know that I have been seriously testing the Castle ActiveRecord framework out. I really love it, but I have an existing Microsoft SQL Server database with many stored procedures in it. I have tested the ActiveRecord model out, and I am sure that I will learn enough to be able to use it for standard CRUD (create, read, update, delete aka. insert, select, update, delete) functionality. BUT ... If I really want to integrate with my existing billing
procedures, etc, I will have to be able to call stored procedures. I have taken
two approaches ...
  1. write the ARHelper.ExecuteNonQuery(targetType, dmlString) method that gets a connection for the supplied type, executes dmlString, and closes it.

  2. write the ARHelper.RegisterCustomMapping(targetType, xmlString) method that allows me to add mappings that refer to my auto-generated Castle objects. Otherwise, the sql query or procedure would have to return scalars.

Any sql-query registered with the RegisterCustomMapping method below MUST return something, according to the documentation (I use Hibernate v3 documentation alot, and check NHibernate when things do not work). For calls that do not return anything (other than a count of records affected), look at my ExecuteNonQuery method.


SAMPLE USAGE CODE
Type targetType = typeof(Status);
string customMap = "<sql-query> ...";
ARHelper.RegisterCustomMapping(targetType, customMap);
ISessionFactoryHolder holder = ActiveRecordMediator.GetSessionFactoryHolder();
ISession sess = holder.CreateSession(targetType);
try
{
string sqlProc = "Test_ActivationStatus_SP";
IQuery query = sess.GetNamedQuery(sqlProc);
IList list = (IList)query.List();

foreach (object obj in list)
{
Debug.WriteLine(String.Format("{0}", obj.ToString()));
}
}
finally
{
holder.ReleaseSession(sess);
}



LIBRARY CODE
using Castle.ActiveRecord;
using Castle.ActiveRecord.Framework;
using Castle.ActiveRecord.Framework.Config;

using NHibernate;
using NHibernate.Cfg;
using NHibernate.Expression;

... namespace and class declarations go here ...

// 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", "UPDATE" or "EXEC"
return rowsAffected;
}

/*
* Sample Custom Mapping to call a stored procedure ...
* "<sql-query name='Test_Status_SP'>" +
* "<return class='Status'/>" +
* //"select * from dbo.LStatus" +
* "EXEC dbo.MyApp_Get_KeyValue 'dbo.LStatus', 0, 0" +
* "</sql-query>*/
public static void RegisterCustomMapping(
Type targetType,
string mapping_hbm_xml
)
{
ISessionFactoryHolder holder = ActiveRecordMediator.GetSessionFactoryHolder();
Configuration config = holder.GetConfiguration(holder.GetRootType(targetType));

// auto-build <hibernate-mapping> wrapper element,
// so user does not need to maintain this
mapping_hbm_xml = "<hibernate-mapping xmlns='"
+ Configuration.MappingSchemaXMLNS + "'"
+ " assembly='" + targetType.Assembly.FullName + "'"
+ " namespace='" + targetType.Namespace + "'>\n" + mapping_hbm_xml
+ "\n</hibernate-mapping>";

config.AddXmlString(mapping_hbm_xml);
}

Comments

kvieres said…
cant this method work with Oracle?
Roy Tate said…
I do not see any reason why this solution would be limited to Microsoft SQL Server. This patch was never accepted by the Castle developers. In another patch, I hacked support for ActiveRecord objects that called stored procedures for Add, Update and Delete methods, but it required a hack to NHibernate, since they don't expose a clean parameter list.

Popular posts from this blog

How does Rails scaffolding select HTML input tags?

Recently, a reader saw my fix for SQL Server booleans, and asked me a followup question: why does Rails display a yes/no selection instead of a checkbox? The short answer is look in {RUBY_HOME} /lib/ruby/gems/1.8 /gems/actionpack-1.10.2 /lib/action_view/helpers, but your path may vary depending on whether you are using gem, "edge rails", etc. Anyway, look in the file "active_record_helper.rb" for a method called "all_input_tags", and notice that it calls "default_input_block" if you don't supply an input_block. Now notice that "default_input_block" creates a label and calls "input(record, column.name)" which in turn calls "InstanceTag#to_tag" which finally looks at the datatype and maps boolean to a select tag. Perhaps a wiser Rails explorer can provide us with the rationale for this, but I guess we could add a MixIn for InstanceTag that redefines the to_tag() method, or just do a dirty and unmaintainable hack l...

Destiny 2 - The Corrupted Nightfall

 == Overview == This Dreaming City Nightfall is already considered one of the most irritating strikes, in part because of bugs that sometimes prevent players from proceeding to the next encounter.  Notable bugged areas are the transition from the Elevator to the Knights Steps, and after the passage, the temple sometimes doesn't reveal a way to the shadow realm where you will take your leap. Important Note: After a "de-buff" from Particle Deconstruction, shoot the boss in the head!  It is quite effective, and can melt her with some coordination. Champions: Unstoppable and Overload Shields: Arc, Solar and Void (many shields!) Don't place your well in the open where boss can snipe the sword!  She will break your super. Pass the "Relic" (Orb) twice to maximize damage. == Loadouts == General: * Add Taken spec to every weapon Titans: * Bottom Tree Solar + Path of the Burning Steps or Phoenix Cradle * Sentinal Shield + Ursa, Aeon Gauntlets Warlocks: * Stasis + Blea...

Updated ActiveRecord Code Generator

Today, I updated the ActiveRecord Code Generator a bit. I checked in changes to use primary and foreign key details from INFORMATION_SCHEMA. The original code used naming conventions to decide what various fields were used for - ID = Primary Key, Field_ID = Foreign Key to table Fields. If you want to use naming conventions, let me know and I can add a setting in App.Config to allow this (along with any "real" key constraints).