The Outbox Pattern in C#

This post is part of the third annual C# Advent. Be sure to check out the other great posts as well. In this post, I'm going to implement the outbox pattern in C#. The outbox pattern can be used when one business transaction needs to happen as a result of another business transaction but it's not possible to combine them into the same database transaction. An example is when an order is placed through an online ordering system and an independent fulfillment system needs to be notified so the purchased items can be packaged and shipped. The outbox pattern can ensure the fulfillment system is updated correctly. When the order is placed, the online ordering system is updated, and at the same time, a message is placed into an outbox. A separate process comes along and processes any waiting entries in the outbox. This allows the outbox entry to be created at the same time the online ordering system is updated. It's important to note that it's possible for the outbox to process the message but then fail to update the outbox row. This means the row can be processed more than once. In a real application, the outbox processor or destination should account for this scenario.

The Example Application

For this example, I created two console applications. The first lets the user add titles and authors of their favorite books. When a new book is added, an entry is added to the outbox. The second app processes the outbox. The books and outbox are stored in a SQL database. Since there are a lot of moving parts, I won't be going over every line of code here. The full example is available on GitHub. It requires .NET Core 2.1 and SQL Server 2017 express.

Database Setup

The database consists of two tables. One to store the books and one to store the outbox. There are also some stored procedures for getting and setting data in the tables. Below are the table definitions. The GitHub repository contains a complete create script for all database objects.

    CREATE TABLE [dbo].[Outbox](
        [OutboxId] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED([OutboxId] ASC),
        [RequestMessage] [nvarchar](max) NOT NULL,
        [QueuedDate] [datetime] NOT NULL DEFAULT (getdate()),
        [ProcessedDate] [datetime] NULL,
        [ProcessedStatus] [nvarchar](256) NOT NULL DEFAULT ('queued'),
        [ErrorMessage] [nvarchar](max) NULL
     )
     GO

    CREATE TABLE [dbo].[Book] (
        [BookId] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED([BookId] ASC),
        [Title] [nvarchar](512) NOT NULL,
        [Author] [nvarchar](512) NOT NULL
    )
    GO

Adding a Book

The book manager has 3 options, add a new book, load books from the database, and display all books that are currently loaded in the application. After creating a book, a row is added to the outbox as shown below.

Menu	Save Book

Below is the code that adds a new book. Notice I add the book and outbox row as part of the same database transaction. This could also be done in the same SQL stored procedure but I'm assuming mostly C# programmers are reading this so I did the database transaction in C#.

    public static void SaveBook(Book book)
    {
        using (var connection = new SqlConnection(ConnectionString))
        {
            using (var command = connection.CreateCommand())
            {
                command.CommandType = CommandType.StoredProcedure;
                connection.Open();
                command.Transaction = command.Connection.BeginTransaction();
                command.CommandText = "SaveBook";
                command.Parameters.AddWithValue("@Title", book.Title);
                command.Parameters.AddWithValue("@Author", book.Author);
                command.ExecuteNonQuery();

                command.CommandText = "InsertOutbox";
                command.Parameters.Clear();
                command.Parameters.AddWithValue("@RequestMessage", book.Title + "," + book.Author);
                command.ExecuteNonQuery();

                command.Transaction.Commit();
            }
        }
    }

Processing the Outbox

The second application has one option to process the outbox. In this case "processing the outbox" simply means displaying the message to the console. The row in the outbox table gets updated with a timestamp of when the row was processed and a status of 'processed'.

Process Outbox	Processing Outbox

Conclusion

In conclusion, this is a simplified example of the outbox pattern in C#. The outbox pattern can be used when one business transaction needs to happen as a result of another business transaction but it's not possible to combine them into the same database transaction. In this post, I showed how adding a book, adds an entry to the outbox which can be handled by a separate process. Check out the full example on GitHub. You can run both apps side by side and insert books in one, and process them in the other. Finally, head over to this year's C# Advent for the other great posts.