Lately, I’ve been playing around with Castle ActiveRecord tolearn about this particular technology. For those of you who are unaware, Castle ActiveRecord is an open sourceimplementation of the ActiveRecord pattern written in .NET and built onNHibernate.
The ActiveRecord pattern represents rows in a table asinstances of a class (with column values as instance methods) while providingaccess to the table itself as static methods of the class.
For example, a database table named Users might berepresented by a class called User. The User class would have a static method called FindRow which takes ina numeric value corresponding to the primary key of that table. User.FindRow(5) would return aninstance of a User object, which would have various properties such as Name.
The benefit of this is it allows developers to access andmanipulate data in a database while providing a layer of abstraction from theactual database syntax. This codecan be database agnostic and hide the user from SQL queries and what not. It also allows simplifying databasecode and being able to perform basic database operations in one or two lines ofcode.
One of the benefits of Castle ActiveRecord is you can defineyour entire database schema using .NET code and even have the engine create thedatabase for you. For example, tocreate a User table, we could use the following code:
[ActiveRecord(“Users”)]
public class User : ActiveRecordBase<User>
{
string name;
[Property(NotNull = true)]
public string Name {get {return name;} set {name = value;}}
}
Using various attributes, you can specify database typemappings, primary keys, foreign constraints, indexes, etc. When you’re done, you can instructActiveRecord to create the database using a given connection string.
We can now create a row in this table with the followingcode:
User user = new User();
user.Name = “Mike”;
user.Create();
This eliminates the need to use ADO.NET or construct SQLstatements or stored procedure calls.
One interesting thing you can do is specify the relationsbetween tables. If I had an Ordertable, I can specify a User that placed the order:
Public class Order
{
….
[BelongsTo(“Name”, NotNull = true)]
public User PlacedBy {get {return _placedby;}}
You’ll notice this method actually returns a User object, notjust an int or a guid or what not. When an Order row is queried, Castle ActiveRecord joins in the Userstable and also populates the PlacedBy instance of the order. You can enable a feature called “lazyloading” which will not query the Users table until the property getter isaccessed. This would prevent thegeneration of queries with lots of joins.
So far, I’ve found Castle ActiveRecord to do a great jobhandling simple database operations for me, however it definitely has itsdrawbacks. I’ve found it lackingin creating any sort of complicated database schema, as it doesn’t seem to beable to create indexes on foreign keys or allow the user to specify clusteredvs non-clustered indexes. However,it can also generate a SQL script to create your database and output to a fileso you can make changes before provisioning your database. For more complicated scenarios, I wouldnot use Castle ActiveRecord for database provisioning.
There were a few instances where I wanted to be able toreturn only certain columns from the database and control exactly what tablesget join’ed in. It seems CastleActiveRecord wants to return all columns all the time, or no columns and generate new SELECTstatements every time you access a property. It would be nice if we could simply specify what columns wewant to query for, and the remaining properties would remain null. For these scenarios, CastleActiveRecord can create an IDbCommand and you can fire off your own storedprocedure or SQL statement. I’vefound using a mix of regular ADO.NET and ActiveRecord calls for the simplerstuff is a great approach for more complicated applications. The ActiveRecord base class also allows you to override various methods, and pretty much any part of the system can be rewritten.
One thing that has disappointed me is it seems support fortransactions is completely broken! I can create a transaction like so:
using(TransactionScope t = new TransactionScope())
{
//Do some DB calls
t.VoteCommit();
}
In theory, if the code crashes, the transaction will neverbe committed and the Dispose method of TransactionScope will roll thetransaction back. However, when Itried this, I found the SQL code isn’t getting run and no SQL errors would begenerated until the Transaction is disposed, thus making it impossible to rollback the transaction due to database errors. On thesupport forums, I found several other people who ran into this problem but noone had a solution for it yet.
Overall, I think this technology is a great model for dataaccess using .NET applications and works perfectly for web applications aswell. It seems they need to ironout a few minor problems, but they have a large following and an active supportcommunity. It’s also open sourcewith a very flexible license, which would allow large applications to run ontheir own customized version of Castle ActiveRecord. I think the pattern is very solid and at the very least,should give you some ideas for creating a database access layer from scratch for yourweb based applications. I’d bevery interested in hearing from anyone using Castle ActiveRecord in theirapplications and what they like and don’t like about it.
For more information, visit: http://www.castleproject.org/activerecord/index.html
Mike