- Posted by Jakob Andersen on March 23, 2008
Introduction
Lately I have been writing some posts(in Danish) introducing ActiveRecord and MonoRail. I have gotten some requests for providing a translation of these in English. This is the fist one and its heavily based on the getting started guide found on castle website.
This first post will introduce Castle ActiveRecord, an implementation of the ActiveRecord pattern on .NET. ActiveRecord is probably most known for being a part of Ruby on Rails
What is ActiveRecord?
ActiveRecord is a way to simplify data access to a relational database in an object oriented environment. We represent database tables as classes in our object oriented language and instances of these classes represent rows in the table. In C# this will be implemented by providing static members to operate on the whole table and instance members to represent a rows data and its functionality. Below is the outline of an ActiveRecord class providing access to a database table containing information about Blogs.
public class Blog{
//Static methods that operate on the whole table
public static List<Blog> FindAll(){..}
public static Blog Find(int identifier){..}
//Instance methods that operate on a single row
public void Update(Blog instance){..}
public void Create(Blog instance){..}
//Instance properties that describe the data keept in a row
public int ID{get;set;}
public string Name{get;set;}
public string Owner{get;set;}
}
Implementation
Implementing the above instance methods Update and Create is simple and in most cases trival. But each time you have to write code to adressed the following concerns:
- How to handle database connections
- Creation of UPDATE and INSERT statements
- Handling parameters to avoid SQL Injection and type differences between .NET and the database
- Handling expectations of rows affected
Implementing the FindAll and Find methods seems pretty straightforward as well, but it involves some code to handle the difference between the types in the database and the types in .NET. Again this code is most often trivial.
public static List<Blog> FindAll(){
List<blog> blogs = new List<Blog>();
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["BlogEngineConnection"]);
SqlCommand comm = conn.CreateCommand("SELECT ID, Name, Owner FROM Blogs");
conn.Open();
DataReader blogReader = comm.ExecuteReader();
while(blogReader.Read()){
Blog b = new Blog();
b.ID = blogReader.GetInt32(blogReader.GetOrdinal("ID"));
b.Name = blogReader.GetString(blogReader.GetOrdinal("Name"));
b.Owner = blogReader.GetString(blogReader.GetOrdinal("Owner"));
blogs.Add(b);
}
comm.Dispose();
conn.Close();
conn.Dispose();
return blogs;
}
The above code is very simple but when working with a real world domain model we often want to have relations between our objects. With the above Blog-sample we would for instance want our Owner property to be of a complex type representing the owner instead of just a string. Another idea for improving the Blog class could be to have a collection of posts in the blog represented. All this makes the code of populating objects even more complicated, and if we add the ability to not populate all relations each time it gets even more complicated. This is where Castle ActiveRecord comes to the rescue, in return for us describing how our classes map to our database structure we get functionality to eliminate the above "not-fun-to-write" code.
Describing the ActiveRecords
As hinted above i'm about to build the data model for a blog-system. The data model we define here will be used in the next posts. If you want to code as we go along feel free to fetch the binaries of the libraries used from the Castle Projects homepage. Even though there are tools to generate our ActiveRecord classes I show you how to do this by hand as it is a firm belief of mine that its important to know what goes on before in the code before allowing tools to generate it for us.
To instruct ActiveRecord that one of our classes represent a table in our database we mark it with an attribute, this attribute is called ActiveRecord and the simple case adding it without any parameters will look like this.
[ActiveRecord]
public class Blog
{
//..
}
All projects part of the Castle Project favor convention over configuration, in this case this means that ActiveRecord will assume that our table have the same name as our class. Of course we have the ability to specify otherwise, with the ActiveRecord attribute this can be done passing the name of the table as a parameter.
[ActiveRecord("Blogs")]
public class Blog
{
//..
}
Specifying the members
We need to add attributes to our members as well, first of all our primary key with the PrimaryKey attribute and ordinary fields with the Property attribute. Again we have the ability to specify column names as parameters or we can leave at as default. When applying this to our Blog class it will look like this.
[ActiveRecord("Blogs")]
public class Blog : ActiveRecordBase<Blog>
{
[PrimaryKey]
public int ID{
get;
set;
}
[Property]
public string Name
{
get;
set;
}
[Property]
public string Owner
{
get;
set;
}
}
If it isn't obvious from the above ID is our primary key and Name and Owner is columns in our table mapped to the properties. All attributes we have used so far is found in the Castle.ActiveRecord namespace so to compile the class above you need to import this and reference the Catle.ActiveRecord assembly.
One thing to note in the above is that i made our class inherit from ActiveRecordBase<T> this is not necessary(more about that in a future post) but it gives us a lot of functionality directly on our classes which gives us the ability to start working with ActiveRecord pretty fast
Configuration
The next step is to configure the ActiveRecord framework this includes telling us which database we use and where it can be found. This configuration can be placed in an XML file(among other options). An example of an ActiveRecord configuration is found here.
<?xml version="1.0" encoding="utf-8" ?>
<activerecord>
<config>
<add key="hibernate.connection.driver_class" value="NHibernate.Driver.SqlClientDriver" />
<add key="hibernate.dialect" value="NHibernate.Dialect.MsSql2005Dialect" />
<add key="hibernate.connection.provider" value="NHibernate.Connection.DriverConnectionProvider" />
<add key="hibernate.connection.connection_string" value="Data Source=.\SQLEXPRESS;Initial Catalog=BlogEngine;Integrated Security=SSPI" />
</config>
</activerecord>
To find out more about configuring ActiveRecord you can consult the nHibernate documentation as ActiveRecord in fact uses nHibernate as its persistence layer. But to explain it short the above informs about which driver should be used for connection, which SQL-dialect should be used for generating the SQL statements, which provider should be used for serving connections and lastly the connectionstring to the database.
As stated above ActiveRecord uses nHibernate, its generally recommended to know how nHibernate works when using ActiveRecord. First of all this makes debugging easier and secondly we will need some nHibernate mechanisms to do advanced querying of our data. So the nHibernate reference manual is a valuable resource.
Now we need to initialize our ActiveRecord classes with the configuration this is done using XmlConfigurationSource residing in the Castle.ActiveRecord.Framework.Config namespace, using this we can start up the framework.
XmlConfigurationSource source = new XmlConfigurationSource("ActiveRecordConfig.xml");
ActiveRecordStarter.Initialize(source, typeof(Blog));
Its worth noticing that the Intitialize method have multiple overloads for different configuration methods and to provide more ActiveRecord types.
Creating the database
I haven't guided you through the creation of the tables in the database we specified in our connectionstring. That's because ActiveRecord (in most cases) can generate the schema for us based on the attributes we specified. So a call to ActiveRecordStarter.CreateSchema() after vi have initialized the framework will create your tables in the database. Similar you can drop all the elements using the DropSchema method.
Persisting new instances
With the framework initialized and the schema created we can start using our ActiveRecord classes.
Blog b = new Blog();
b.Name = "Responding to change";
b.Owner = "Jakob Andersen";
b.Create();
The Create method used above is implemented on ActiveRecordBase<T> and saves a new instance to the database, if we investigate the SQL executed against our database it will be an INSERT statement.
INSERT INTO Blogs (Name, Owner) VALUES (@p0, @p1); select SCOPE_IDENTITY(); @p0 = 'Responding to change', @p1 = 'Jakob Andersen'
So based on our Attributtes on the classes ActiveRecord generates the SQL statement above with parameters and code for retrieving the primary key after insertion. The best of all is that we can easily add new fields to our data model by just adding them to the schema and adding a property to our objects. No digging around in the code of your hand made data access layer and ensure that all places using the Blogs table is updated to include the new field.
Of course ActiveRecord can work on objects retrieved from the database, if a persistent instance needs to be updated in the database the Update method is called. An alternative to the Create and Update method is the Save method that either update or create the object based on its primary key.
Simpel relatioins between our ActiveRecord classes
As i mentioned earlier the complexity of dataaccess code becomes higher when we have complex types being properties or collections on our persistent classes. So imagine we want to model the classes with their relations as shown here.
This is easy to accomplish using ActiveRecord, we can for instance modify our Owner property on the Blog class to be of the type Blogger instead of type string. This involves changing the attribute(and type) of the property in our ActiveRecord class.
[BelongsTo]
public Blogger Owner{get;set;}
Instead of the Property attribute we not specify a BelongsTo attribute on the property, this tells ActiveRecord that the table contains a foreign-key column and that this should be used to find the Blogger object based on this key. In the other end of the relation we specify that a Blogger has many blogs using the HasMany attribute on a Blog collection, this of course means that Blogger is a primary key table being referenced by a foreign key found in the table at the other end of the relation.
[HasMany]
public IList<Blog> Blogs
{
get;
set;
}
Many-to-Many relations
To introduce many-to-many relations in our data model we specify that a Post object contains a list of tags, but one tag can be used for many posts. To specify this we use the HasAndBelongsToMany attribute. Because the database needs to have an extra table to contain information about this type of relation we need to specify information about this table. More specifically it needs to know the name of the foreign key for each end of the relation and the name of the table to contain these keys. Specifically when talking about our Tag-Post relation this could be specified like this.
[ActiveRecord]
public clas Tag : ActiveRecordBase<Tag>{
.....
[HasAndBelongsToMany(Table = "PostTags", ColumnKey = "TagID", ColumnRef = "PostID", Inverse = true)]
public IList<Post> Posts
{
get;
set;
}
]
public class Post : ActiveRecordBase<Post>{
....
[HasAndBelongsToMany(Table = "PostTags", ColumnKey = "PostID", ColumnRef = "TagID")]
public IList<Tag> Tags{
get;
set;
}
}
The inverse parameter to the attribute specify which end of the relation is the controlling. That is, which of the collections we will use to maintain the relationship. In our case we want to add tags to a post and not the other way around.
Persisting related objects
Having relations in our model makes it important to specify when related objects should be saved. ActiveRecord gives us this ability by providing a cascade mechanism in oru model. This means that vi can specify that an instance related to another on a property should be saved when its containing instance is persisted. The default cascade behavior is none so we should handle saving all instances manually however if we specify SaveUpdate cascade the for instance we could get a Blogger instance associated to a Blog saved when we call Save on the Blog instance.
[BelongsTo(Cascade = CascadeEnum.SaveUpdate)]
public Blogger Owner { get; set; }
The options in the CascadeEnum are Delete, All, SaveUpdate and None. As i said None is default.
Mapping long texts
One thing you will see if you download the sample code is that i have specified a length on the Text property on the Post class, this is to force the database to create a column that can contain an arbitrary long text when using CreateSchema. This could be done by specifying the StringClob or by setting the length to high number. On that note CreateSchema will specify the same length for all your string properties so if using CreateSchema this should be remembered.
Next post
This post contained a quick intro to map your domain model to your database structure, i would recommend that you download the source code and investigate it and take a look at the generated SQL using SQL profiler or configuring nHibernate to output SQL to the Console as i have done in the sample. Also investigate the methods your ActiveRecord classes have courtesy of ActiveRecordBase<T>. These methods is partly the subject of my next post
VS2008 solution: ActiveRecordBlogSample.zip
UPDATE: The complete sample beeing coded