Introduction

I decided to try and write a series about different data access methods. Main motivation being to learn them better. The first one in the series is Active Record. I am mostly a .NET-guy these days and I really want to get to know linq better, so I will be using Linq to SQL and C#.

Martin Fowler describes Active Record -pattern in his book Patterns of Enterprise Application Architecture:

An object that wraps a row in a database table or view, encapsulates the database access, and adds domain logic to the data

This means that for every database table or view a class will be created. Columns of the database table will be the properties of the corresponding class. CRUD-functionality is provided by methods of the class. If there is additional business logic involved in data persisting it will be implemented internally in the active record class.

With Active Record your tables are essentially your domain objects, so it is a good data access pattern if your database has a simple enough structure. If your database structure is going to be complicated or doesn’t map well to object model you might want to consider some other strategy.

Design

We will be designing a simple todo-list application. Using SQL Server database we create a database table called “Tasks”.

Picture above displays structure of the Tasks-table. The structure is very simple:

• id – Incremental id for task
• title – Name of the task
• content – Additional notes for the task
• completed – Boolean value indicating whether the task is completed or not

According to active record -pattern the the database columns will become properties of the corresponding class.

The class also needs methods for performing data access:

• FetchAll() – Return collection of Task-objects
• FetchById() – Returns a single Task-object
• Save() – Persists object to data store
• Delete() – Deletes the record from data store

Implementation

First step in implementation is setting up SQL Server Database and creating the Tasks-table. After that create a new C#-project in Visual Studio and from Add – New Item –dialog choose “Linq to SQL classes”. After that you can just drag and drop database tables on the design surface. For more detailed description check ScottGu's blog

I’ve changed the default name of the Linq to SQL -class from Task to TaskTable, because Task will be the name of the Active Record -class.

Data Retrieval

Like I said earlier, each column in the table becomes a property of the class.

public class Task
{
    public int id { get; set; }
    public string title { get; set; }
    public string content { get; set; }
    public bool completed { get; set; }
}

Then we can start adding the functionality we specified. Let’s start with data retrieval. FetchAll() returns all rows in the datastore.

public static List<Task> FetchAll()
{
    List<Task> lst = new List<Task>();
    using (var ctx = new DataClassesDataContext())
    {
        var query = from task in ctx.TaskTables
        select new Task
        {
            id = task.id,
            title = task.title,
            content = task.content,
            completed = task.completed
        };
        lst = query.ToList();
    }
    return lst;
}

The method needs to be static, because we must be able to call it before we have created any instances of the Task-class. Query is a Linq query that simply selects the appropriate columns into Task-objects. The query is executed when we call ToList(). We could use the method like this:

var tasks = Task.FetchAll();

We will also want to be able to get a hold of a single Task-record. That’s where we’ll need FetchByID()-method:

public static Task FetchByID(int id)
{
    Task t = new Task();
    using (var ctx = new DataClassesDataContext())
    {
        var query = from task in ctx.TaskTables
            where task.id==id
            select new Task
            {
                id = task.id,
                title = task.title,
                content = task.content,
                completed=task.completed
            };
        t = query.First();
    }
    return t;
}

It’s the same as FetchAll() but with added where-clause to specify a single record we want to fetch. This is severely breaking the DRY-principle, and we’ll probably want to refactor this later.

Conclusion

The codes can be found in Bitbucket repo.

As seen in this post, implementing data retrieval with active record can be quite simple. In the next part we’ll implement about create-, update- and delete -functionality and do some refactoring.