本篇同步發布於個人Blog: [PoEAA] Data Source Architectural Pattern - 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)
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
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.
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.
This Person class creates Insert/Update/Find/Load basic functions to manipulate person table. One instance function GetExemption() is a business logic.
glj8989332
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; }
}
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;
}
}
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:
"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.
Patterns of Enterprise Application Architecture Book(Amazon)
iThome鐵人賽
看影片追技術