本篇同步發布於個人Blog: [PoEAA] Data Source Architectural Pattern - Row Data Gateway

1. What is Row Data Gateway

According to [PoEAA], this definition is "An object that acts as a Gateway to a single record in a data source. There is one instance per row."

Figure 1. Row Data Gateway (From PoEAA Page)

1.1 How it works

In a gateway, the property/field is mapped to a record's attribute. For finding function, there are Finder  Class and Gateway Class to retrieve a table's records. As the following sequence diagram shows:

Figure 2. Interactions for a find with a row-based Row Data Gateway.

1.2 When to use it

1. Consider whether the architecture needs a gateway and what gateway to use (Table/Row Data Gateway).
2. Transaction Script pattern fits this pattern.
3. Domain Model pattern is not recommended to use this pattern.
4. When use Transaction Script and Row Data Gateway and the application becomes more complex, the Row Data Gateway will evolve into a Active Record.
5. This pattern can combine with Data Mapper when gateway is generated by Metadata Mapping

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 Row Data Gateway 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 - Row Data Gateway of PoEAA. The database is SQLite.

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

2.1.1 PersonGateway

This gateway creates Insert/Update basic functions to manipulate person table. One static Load function is used by Finder Class.

class PersonGateway : BaseGateway
{
	public PersonGateway(int id, string lastName, string firstName, int numberOfDependents)
	{
		Id = id;
		LastName = lastName;
		FirstName = firstName;
		NumberOfDependents = numberOfDependents;
	}

	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 string LastName { get; set; }
	public string FirstName { get; set; }
	public int NumberOfDependents { get; set; }

	public static PersonGateway Load(IDataReader reader)
	{
		object[] resultSet = new object[reader.FieldCount];
		reader.GetValues(resultSet);

		int id = (int) resultSet[0];
		PersonGateway 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 PersonGateway(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);
		}
	}

	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 BaseGateway
{
	public int Id { get; set; }

	public BaseGateway()
	{
		
	}
}

2.1.2 PersonFinder

This finder class queries single/multiple record(s) with PersonGateway.

class PersonFinder
{
	private const string FindStatementString = @"
		SELECT id, lastname, firstname, numberOfDependents
		from person
		WHERE id = $id";

	private const string FindResponsibleStatementString = @"
		SELECT id, lastname, firstname, numberOfDependents
		from person
		WHERE numberOfDependents > 0";

	public PersonGateway Find(int id)
	{
		PersonGateway 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 = PersonGateway.Load(reader);
			return result;
		}
		catch (Exception ex)
		{
			throw new ApplicationException(ex.Message);
		}
	}

	public List<PersonGateway> FindResponsibles()
	{
		List<PersonGateway> result = new List<PersonGateway>();
		try
		{
			using var conn = DbManager.CreateConnection();
			conn.Open();
			using IDbCommand comm = new SQLiteCommand(FindResponsibleStatementString, conn);
			using IDataReader reader = comm.ExecuteReader();
			while (reader.Read())
			{
				result.Add(PersonGateway.Load(reader));
			}

			return result;
		}
		catch (Exception ex)
		{
			throw new ApplicationException(ex.Message);
		}
	}
}

2.1.3 Registry

The above finder/gateway classes have used a Registry. This Registry pattern holds a kind of dictionary that keeps one key mapped to a instance. In this article the Registry has a <id, PersonGateway> dictionary for finding/insertion functions. When insertion completes, Registry adds this Person; When finding is called, it first checks the Registry whether has the Person. If the Person exists, return it. Otherwise it queries Person from the database and stores it in Registry.

internal class Registry
{
	private static readonly Registry Instance = new Registry();
	private readonly Dictionary<int, PersonGateway> _personsMap = new Dictionary<int, PersonGateway>();

	private Registry()
	{

	}

	public static void AddPerson(PersonGateway personGateway)
	{
		Instance._personsMap.Add(personGateway.Id, personGateway);
	}

	public static PersonGateway 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
{
	static void Main(string[] args)
	{
		InitializeData();

		Console.WriteLine("Get responsible persons");
		PersonFinder finder = new PersonFinder();
		var people = finder.FindResponsibles();
		PrintPersonGateway(people);

		Console.WriteLine("Insert a new person");
		new PersonGateway(0, "Rose", "Jackson", 60).Insert();
		people = finder.FindResponsibles();
		PrintPersonGateway(people);

		Console.WriteLine("Update a person's first name");
		var firstPerson = finder.Find(1);
		firstPerson.FirstName = "Jack";
		firstPerson.Update();

		Console.WriteLine("Update a person's number of dependents");
		var secondPerson = finder.Find(2);
		secondPerson.NumberOfDependents = 0;
		secondPerson.Update();

		Console.WriteLine("Get responsible persons again");
		people = finder.FindResponsibles();
		PrintPersonGateway(people);

	}

	private static void PrintPersonGateway(IEnumerable<PersonGateway> people)
	{
		foreach (var person in people)
		{
			Console.WriteLine($"ID: {person.Id}, last name: {person.LastName}, first name: {person.FirstName}, number of dependents: {person.NumberOfDependents}");
		}
	}

	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

"Row Data Gateway" is also a simpler data source architectural pattern. I think of it as a fine-grained version of Table 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 - Active Record Gateway of PoEAA.

4. References

Patterns of Enterprise Application Architecture Book(Amazon)