本篇同步发布於个人Blog: [PoEAA] Data Source Architectural Pattern - Active Record

1. What is Active Record

According to [PoEAA], this definition is "An object that wraps a row in a database table or view, encapsulates the database access, and adds domain logic on that data."

Figure 1. Active Record (From PoEAA Page)

1.1 How it works

It is a Domain Model. Every Active Record's class is mapped to a record of the database and loads the data source to process with domain logic.
An approximate equality: Active Record ≈ Row Data Gateway + Domain Logic

1.2 When to use it

1. For simple domain logic.
2. When design Domain Model pattern, choose either Active Record or  Data Mapper.
3. If the application becomes more complex, Data Mapper is a better choice.
4. Active Record is hard to do refactoring because of tightly coupling with database.
5. When use Transaction Script, Row Data Gateway gradually evolves into Active Record.

2. Pattern Practice: The Person Management

This problem is introduced in the previous article [PoEAA] Data Source Architectural Pattern - Table Data Gateway. This article uses Active Record to build the data source architectural layer.

2.1 Implementation by C# & SQLite

This pattern is implemented by C# based on the content of Chapter 10 Data Source Architectural Patterns - Active Record of PoEAA. The database is SQLite.

By Martin's implementation, it doesn't contain a Delete function in a active record. So this article also has no Delete function.

2.1.1 Person class

This Person class creates Insert/Update/Find/Load basic functions to manipulate person table. One instance function GetExemption() is a business logic.

    class Person : BaseActiveRecord
    {
    	public string LastName { get; set; }
    	public string FirstName { get; set; }
    	public int NumberOfDependents { get; set; }
    	private const string FindStatementString = @"
    		SELECT id, lastname, firstname, numberOfDependents
    		FROM person
    		WHERE id = $id
    		";
    
    	private const string UpdateStatementString =
    		@"UPDATE person 
    				SET lastname = $lastname, firstname = $firstname, numberOfDependents = $numberOfDependents
    		where id = $id";
    
    	private const string InsertStatementString =
    		@"INSERT INTO person 
    				VALUES ($id, $lastname, $firstname, $numberOfDependents)";
    
    	public Person(int id, string lastName, string firstName, int numberOfDependents)
    	{
    		Id = id;
    		LastName = lastName;
    		FirstName = firstName;
    		NumberOfDependents = numberOfDependents;
    	}
    
    	public static Person Find(int id)
    	{
    		Person result = Registry.GetPerson(id);
    		if (result != null)
    		{
    			return result;
    		}
    
    		try
    		{
    			using var conn = DbManager.CreateConnection();
    			conn.Open();
    			using IDbCommand comm = new SQLiteCommand(FindStatementString, conn);
    			comm.Parameters.Add(new SQLiteParameter("$id", id));
    			using IDataReader reader = comm.ExecuteReader();
    			reader.Read();
    			result = Load(reader);
    			return result;
    		}
    		catch (Exception ex)
    		{
    			throw new ApplicationException(ex.Message);
    		}
    	}
    
    	public static Person Load(IDataReader reader)
    	{
    		object[] resultSet = new object[reader.FieldCount];
    		reader.GetValues(resultSet);
    
    		int id = (int)resultSet[0];
    		Person result = Registry.GetPerson(id);
    		if (result != null)
    		{
    			return result;
    		}
    
    		string lastName = resultSet[1].ToString();
    		string firstName = resultSet[2].ToString();
    		int numberOfDependents = (int)resultSet[3];
    		result = new Person(id, lastName, firstName, numberOfDependents);
    		Registry.AddPerson(result);
    		return result;
    	}
    
    	public void Update()
    	{
    		try
    		{
    			using var conn = DbManager.CreateConnection();
    			conn.Open();
    			using IDbCommand comm = new SQLiteCommand(UpdateStatementString, conn);
    			comm.Parameters.Add(new SQLiteParameter("$lastname", LastName));
    			comm.Parameters.Add(new SQLiteParameter("$firstname", FirstName));
    			comm.Parameters.Add(new SQLiteParameter("$numberOfDependents", NumberOfDependents));
    			comm.Parameters.Add(new SQLiteParameter("$id", Id));
    			comm.ExecuteNonQuery();
    		}
    		catch (Exception ex)
    		{
    			throw new ApplicationException(ex.Message);
    		}
    	}
    
    	public int Insert()
    	{
    		try
    		{
    			using var conn = DbManager.CreateConnection();
    			conn.Open();
    			using IDbCommand comm = new SQLiteCommand(InsertStatementString, conn);
    			Id = FindNextDatabaseId();
    			comm.Parameters.Add(new SQLiteParameter("$id", Id));
    			comm.Parameters.Add(new SQLiteParameter("$lastname", LastName));
    			comm.Parameters.Add(new SQLiteParameter("$firstname", FirstName));
    			comm.Parameters.Add(new SQLiteParameter("$numberOfDependents", NumberOfDependents));
    			comm.ExecuteNonQuery();
    			Registry.AddPerson(this);
    
    			return Id;
    		}
    		catch (Exception ex)
    		{
    			throw new ApplicationException(ex.Message);
    		}
    	}
    
    	public Money GetExemption()
    	{
    		Money baseExemption = Money.Dollars(1500d);
    		Money dependentExemption = Money.Dollars(750d);
    		return baseExemption.Add(dependentExemption.Multiply((double) NumberOfDependents));
    	}
    
    	private int FindNextDatabaseId()
    	{
    		string sql = "SELECT max(id) as curId from person";
    		using var conn = DbManager.CreateConnection();
    		conn.Open();
    		using IDbCommand comm = new SQLiteCommand(sql, conn);
    		using IDataReader reader = comm.ExecuteReader();
    		bool hasResult = reader.Read();
    		if (hasResult)
    		{
    			return ((int)((long)reader["curId"] + 1));
    		}
    		else
    		{
    			return 1;
    		}
    	}
    }
    
    internal class BaseActiveRecord
    {
    	public int Id { get; protected set; }
    }

2.1.2 Registry

The Registry has been used in [PoEAA] Data Source Architectural Pattern - Row Data Gateway. This article uses it to register Person instances.

    internal class Registry
    {
    	private static readonly Registry Instance = new Registry();
    	private readonly Dictionary<int, Person> _personsMap = new Dictionary<int, Person>();
    
    	private Registry()
    	{
    
    	}
    
    	public static void AddPerson(Person person)
    	{
    		Instance._personsMap.Add(person.Id, person);
    	}
    
    	public static Person GetPerson(int id)
    	{
    		if (Instance._personsMap.ContainsKey(id))
    		{
    			return Instance._personsMap[id];
    		}
    
    		return null;
    	}
    }

2.2 Demo

Create a console program and create 3 Persons in SQLite as the following code:

    class Program
    {
    	private const string FindAllPersonsStatementString = @"
    		SELECT id, lastname, firstname, numberOfDependents
    		FROM person
    		";
    
    	static void Main(string[] args)
    	{
    		InitializeData();
    
    		Console.WriteLine("Get persons");
    		var people = FindPersons();
    		PrintPerson(people);
    
    		Console.WriteLine("Insert a new person");
    		new Person(0, "Rose", "Jackson", 60).Insert();
    		people = FindPersons();
    		PrintPerson(people);
    
    		Console.WriteLine("Update a person's first name");
    		var firstPerson = Person.Find(1);
    		firstPerson.FirstName = "Jack";
    		firstPerson.Update();
    
    		Console.WriteLine("Update a person's number of dependents");
    		var secondPerson = Person.Find(2);
    		secondPerson.NumberOfDependents = 0;
    		secondPerson.Update();
    
    		Console.WriteLine("Get persons again");
    		people = FindPersons();
    		PrintPerson(people);
    	}
    
    	private static List<Person> FindPersons()
    	{
    		List<Person> result = new List<Person>();
    		try
    		{
    			using var conn = DbManager.CreateConnection();
    			conn.Open();
    			using IDbCommand comm = new SQLiteCommand(FindAllPersonsStatementString, conn);
    			using IDataReader reader = comm.ExecuteReader();
    			while (reader.Read())
    			{
    				result.Add(Person.Load(reader));
    			}
    
    			return result;
    		}
    		catch (Exception ex)
    		{
    			throw new ApplicationException(ex.Message);
    		}
    	}
    
    	private static void PrintPerson(IEnumerable<Person> people)
    	{
    		foreach (var person in people)
    		{
    			Console.WriteLine($"ID: {person.Id}, " +
    							  $"last name: {person.LastName}, " +
    							  $"first name: {person.FirstName}, " +
    							  $"number of dependents: {person.NumberOfDependents}, " +
    							  $"exemption: {person.GetExemption().Amount}");
    		}
    	}
    
    	private static void InitializeData()
    	{
    		using (var connection = DbManager.CreateConnection())
    		{
    			connection.Open();
    
    			using (var command = connection.CreateCommand())
    			{
    				command.CommandText =
    					@"
    					DROP TABLE IF EXISTS person;
    				";
    				command.ExecuteNonQuery();
    
    
    				command.CommandText =
    					@"
    					CREATE TABLE person (Id int primary key, lastname TEXT, firstname TEXT, numberOfDependents int);
    				";
    				command.ExecuteNonQuery();
    
    				command.CommandText =
    					@"
    				   
    				INSERT INTO person
    					VALUES (1, 'Sean', 'Reid', 5);
    
    				INSERT INTO person
    					VALUES (2, 'Madeleine', 'Lyman', 13);
    
    				INSERT INTO person
    					VALUES (3, 'Oliver', 'Wright', 66);
    				";
    				command.ExecuteNonQuery();
    			}
    
    		}
    	}
    }

The console shows:

3. Conclusions

"Active Record" is a advanced version of Row Data Gateway. The above sample code is uploaded to this Github Repository.

For next article I will write Active Record pattern according to Chapter 10 Data Source Architectural Pattern - Data Mapper of PoEAA.

4. References

Patterns of Enterprise Application Architecture Book(Amazon)