Skip to main content

Castle ActiveRecord class with stored procedures

Update 05/22/2007: No response from the community. I think Hammett is working on releasing a new version of Castle (It's much more than just ActiveRecord!), so I have not received any feedback on my patch.

I have just posted patch AR-156 to Castle's ActiveRecord project to add support for XML free mapping of insert, update and delete stored procedures to ActiveRecord classes. This requires a bit of work, since the stored procedures must match the field order generated by NHibernate, rather than being controlled by the application developer. This means that we have to create the class normally, run it through a test cycle with logging turned on, capture the prepared statement's field order, and then re-write the stored procedure to match what NHibernate wants. This is because behind the scenes the stored procedure replaces a prepared sql statement that NHibernate would otherwise be using. This will be a mess to maintain until or unless someone contributes a patch to NHibernate that allows us to map :name (or some other named parameter syntax) to the name property of the class. You should notice that NHibernate consistently places the primary key at the end of the parameter list. While it is not perfect, it allows me to meet my requirements to journal all database changes to certain tables.

The code that I submitted is quite simple since I avoided the loader and sql-query mapping elements that would supply Find(primary_key) support. It should theoretically allow stored procedures to be used with any driver and database, although my own testing was limited to SQL Server 2000. It also lacks a critical feature - a working test case. This is the only way that such a feature might reasonably enter the source repository.

The implementation is very easy, and you do not have to provide all of the stored procedures - if you only care about updates, just supply the update attribute ...

[ActiveRecordWithProcs("tb_Order", 
 insertProc="exec pr_Order_Insert ?, ?, ?",
 updateProc="exec pr_Order_Update ?, ?, ?", 
 deleteProc="exec pr_Order_Delete ?")] 
public class Order : ActiveRecordBase
{
}


Remember, if you want this patch to be accepted, download it, apply it to your source version of ActiveRecord, build a test case, and vote for it.
Labels:

Comments

Post a Comment

Popular posts from this blog

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 as
1 comment
Read more

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 ... write the ARHelper.ExecuteNonQuery(targetType, dmlString) method that gets a connection for the supplied type, executes dmlString, and closes it. write the ARHelper.RegisterCustomMapping(targetType, xmlString) method that allows me to add mappings that refer to my auto-gener
2 comments
Read more

Castle Active Record Code Generator

I have finally released my Code Generator to Google Code as Active-Record-Gen . What does it generate? It generates ActiveRecord classes mainly, but I have used it to generate stored procedures and sys-admin scripts as well. This code generator does not (yet) generate a full Windows application project or a Mono-Rail web site, but the generated code could be used in either. In fact, with a few tweaks, this could be used to generate NHibernate "poco" and .xbm files. If you want to know more, look at the screen shots above, or head over to Google Code and run it. In my haste to make my first EXE release before supper, I forgot to add the Template directory, which should be at the same directory level as the EXE and config files. I just (1.5 hours later) uploaded a new EXE, but 2 people have already downloaded the EXE (not the source though). As for the basic table object, it is built with the following assumptions: Table name is plural, class name is singular. Field &quo
2 comments
Read more