本篇同步發布於個人Blog: [PoEAA] Domain Logic Pattern - Transaction Script

1. What is Transaction Script

According to [PoEAA], this definition is "A Transaction Script organizes all this logic primarily as a single procedure, making calls directly to the database or through a thin database wrapper."

For example, when I buy a book on a ecommerce shop, the system executes a series of action, including receiving a book id and customer session, getting a book detail and customer detail from the database, and inserting an order into the database. These actions are organized in a transaction script.

Usually, a database transaction maps to a transaction script. So it is easily to implement data isolation.

2. Pattern Practice: The Revenue Recognition Problem

2.1 Problem Description

In [PoEAA], the author wrote a simple case with this transaction script pattern. Assume a company that sells three kinds of software products: word processors, databases, and spreadsheets.

There are some revenue rules when you sign a contract with these products:

1. For word processor, you can book all the revenue

2. For spreadsheet, you can book one-third today, one-third in sixty days, and one-third in ninety days

3. For database, you can book one-third today, one-third in thirty days, and one-third in sixty days

Design 3 tables to record these data as Figure 1 shows.

Figure 1: Table relationship of Products/Contracts/RevenueRecognitions

2.2 Implementation by C# and SQLite

This pattern is implemented by C# and database SQLite. SQLite is a very lightweight database to image our ideas.

2.2.1 Initialize table and data

When program starts running, it creates a SQLite file and the above 3 tables. The Create Table SQL:

CREATE TABLE Products (Id int primary key, Name TEXT, Type TEXT);
CREATE TABLE Contracts (Id int primary key, Product int, Revenue decimal, DateSigned date);
CREATE TABLE RevenueRecognitions (Contract int, Amount decimal, RecognizedOn date, PRIMARY KEY(Contract, RecognizedOn));

Insert 3 kinds of products as Figure 2 shows:

Figure 2: Insert 3 products

Every product maps to a contract as Figure 3 shows:

Figure 3: Insert 3 contracts

2.2.2 Table Data Gateway

A table data gateway wraps the SQL queries. And this case is simple so the author creates only one Gateway class rather than one for each table.

The Gateway class has 3 functions to complete the above problem:

using CodeParadise.Money;
using System;
using System.Data;
using System.Data.SQLite;

namespace PoEAA_TransactionScript
{
    public class Gateway
    {
        private const string FindRecognitionsStatement =
            @"
                SELECT Amount FROM RevenueRecognitions 
                WHERE Contract = $contractId AND RecognizedOn <= $beforeDate
            ";

        private const string FindContractStatement =
            @"
                SELECT * FROM Contracts c, Products p 
                WHERE c.Id = $contractId AND c.product = p.Id
            ";

        private const string InsertRecognitionsStatement =
            @"
                INSERT INTO RevenueRecognitions VALUES ($contractId, $amount, $recognizedOn)
            ";

        public DataTable FindRecognitionsFor(int contractId, DateTime beforeDate)
        {
            var result = new DataTable();
            using var connection = DbManager.CreateConnection();
            connection.Open();
            var command = connection.CreateCommand();
            command.CommandText = FindRecognitionsStatement;
            command.Parameters.AddWithValue("$contractId", contractId);
            command.Parameters.AddWithValue("$beforeDate", beforeDate);

            using(var sqlDataAdapter = new SQLiteDataAdapter(command))
            {
                 sqlDataAdapter.Fill(result);
            }
           
            return result;
        }

        public DataTable FindContract(int contractId)
        {
            var result = new DataTable();
            using var connection = DbManager.CreateConnection();
            connection.Open();
            var command = connection.CreateCommand();
            command.CommandText = FindContractStatement;
            command.Parameters.AddWithValue("$contractId", contractId);

            using(var sqlDataAdapter = new SQLiteDataAdapter(command))
            {
                 sqlDataAdapter.Fill(result);
            }
           
            return result;
        }

        public void InsertRecognitions(int contractId, Money amount, DateTime recognizedOn)
        {
            using var connection = DbManager.CreateConnection();
            connection.Open();
            var command = connection.CreateCommand();
            command.CommandText = InsertRecognitionsStatement;
            command.Parameters.AddWithValue("$contractId", contractId);
            command.Parameters.AddWithValue("$amount", amount.Amount);
            command.Parameters.AddWithValue("$recognizedOn", recognizedOn);
            command.ExecuteNonQuery();
        }
    }
}

Money class is the implementation of the Money Pattern described in PoEAA. I created the project in this Github Repository and published it to Nuget. This class in this case is responsible for returning the money.

2.2.3 Service

Service is responsible for business logic. So every service is thought as a transaction script.

The RecognitionService class has 2 functions:

using CodeParadise.Money;
using System;

namespace PoEAA_TransactionScript
{
    class RecognitionService
    {
        public Money RecognizedRevenue(int contractNumber, DateTime beforeDate)
        {
            Money result = Money.Dollars(0m);
            Gateway db = new Gateway();
            var dt = db.FindRecognitionsFor(contractNumber, beforeDate);
            for (int i = 0; i < dt.Rows.Count; ++i)
            {
                var amount = (decimal) dt.Rows[i]["Amount"];
                result += Money.Dollars(amount);
            }

            return result;
        }

        public void CalculateRevenueRecognitions(int contractId)
        {
            Gateway db = new Gateway();
            var contracts = db.FindContract(contractId);
            Money totalRevenue = Money.Dollars((decimal) contracts.Rows[0]["Revenue"]);
            DateTime recognitionDate = (DateTime) contracts.Rows[0]["DateSigned"];
            string type = contracts.Rows[0]["Type"].ToString();

            if(type == "S")
            {
                Money[] allocation = totalRevenue.Allocate(3);
                db.InsertRecognitions(contractId, allocation[0], recognitionDate);
                db.InsertRecognitions(contractId, allocation[1], recognitionDate.AddDays(60));
                db.InsertRecognitions(contractId, allocation[2], recognitionDate.AddDays(90));
            }
            else if(type == "W")
            {
                db.InsertRecognitions(contractId, totalRevenue, recognitionDate);
            }
            else if(type == "D")
            {
                Money[] allocation = totalRevenue.Allocate(3);
                db.InsertRecognitions(contractId, allocation[0], recognitionDate);
                db.InsertRecognitions(contractId, allocation[1], recognitionDate.AddDays(30));
                db.InsertRecognitions(contractId, allocation[2], recognitionDate.AddDays(60));
            }
        }
    }
}

CalculateRevenueRecognitions function is calculating the revenue recognitions on an existing contract. RecognizedRevenue function sum the amounts based on the contract id and the recognized date. These services functions are based on the result sets from Gateway class and implement their business logics.

2.2.4 Demo

Create a console program and create a RecognitionService to calculate the revenue recognitions for the 3 products.

As the following code:

RecognitionService service = new RecognitionService();

// database product
service.CalculateRevenueRecognitions(1);
var databaseRevenue = service.RecognizedRevenue(1, new System.DateTime(2020, 1, 25));
Console.WriteLine($"database revenue before 2020-01-25 = {databaseRevenue.Amount}");

// spreadsheet product
service.CalculateRevenueRecognitions(2);
var spreadsheetRevenue = service.RecognizedRevenue(2, new System.DateTime(2020, 6, 1));
Console.WriteLine($"spreadsheet revenue before 2020-06-01 = {spreadsheetRevenue.Amount}");

 // word processor product
service.CalculateRevenueRecognitions(3);
var wordProcessorRevenue = service.RecognizedRevenue(3, new System.DateTime(2020, 9, 30));
Console.WriteLine($"word processor revenue before 2020-09-30 = {wordProcessorRevenue.Amount}");

The console shows:

Figure 4: Console execution result

Let's look at the table RevenueRecognitions of the the database, and it's records are inserted by CalculateRevenueRecognitions function.

Figure 5: Calculated revenue recognitions for 3 contracts

3. Conclusions

Everybody can write "Transaction Scripts". Yes, it's just a script. If the business logic is very simple, just use this pattern. For complex applications, Transaction Script is not maintainable. Even one service function that contains hundreds of / thousands of code lines is the nightmare for developers.

The above sample code is uploaded to this Github Repository.

For next article I will write Domain Model pattern. It's more complex but maintainable for complex applications. Modern .NET solution with Entity Framework is based on Domain Model. Some developers have silently used this pattern but I want to dive in this classic concept.

The above sample code is uploaded to this Github Repository.

Although I have developed .NET software for some years and almost read the documents with MSDN, the classic concepts of patterns/architecture are organized by Martin Fowler. PoEAA is a masterpiece that every programmer should read. Despite of 20 years past, it is still valuable for modern software.

4. References

Patterns of Enterprise Application Architecture Book(Amazon)