本篇同步发布于个人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

For simple domain logic.When design Domain Model pattern, choose either Active Record or  Data Mapper.If the application becomes more complex, Data Mapper is a better choice.Active Record is hard to do refactoring because of tightly coupling with database.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)