Chapter 5. Working with relational databases

This chapter covers

• Accessing databases with .NET Standard System.Data classes
• Using the SQLite in-memory database
• Transforming data between objects and tables
• Unit testing data-access code

Widget Corporation needs to keep better track of their supply chain, so they hire you to transform their paper-based process into software. You find that each part used in the manufacturing process comes from a different supplier, and that Widget Corp. is having trouble ordering the right supplies at the right time.

To solve this problem, you decide to use a relational database. The database will persist data, making it resilient to shutdown or power failure. It will enforce relationships between data, so that no one can add an order without indicating the supplier or part. It also makes the data queryable, which allows anyone to determine what parts are running out and need to be ordered soon. The employees of Widget Corp. shouldn’t have to learn the SQL commands to enter the data into the database manually. You’ll need to create software that makes it easy for them.

Now you get to apply your .NET Core skills. .NET Core is a great choice for database applications because of the powerful built-in data-access classes. In this chapter, you’ll explore the low-level data-access capability that’s built into the .NET Standard—encapsulated in the System.Data namespace. But before you start, you need a database.

5.1. Using SQLite for prototyping

If you’re not already familiar with SQLite, it’s an embedded SQL database engine. It supports both in-memory and file-based options. No installation required—simply reference the NuGet package and you have a database. Microsoft provides a SQLite client library for .NET Standard, which allows you to use SQLite in your .NET Core applications.

The best way to learn about SQLite is to start using it. Start by creating a new console application. The following command-line command creates the new project:

dotnet new console -o SqliteConsoleTest                 1

• 1 -o creates a new folder for the project with the name given.

Edit the SqliteConsoleTest.csproj file as follows.

Listing 5.1. Including the SQLite dependency in the project file

<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>netcoreapp2.0</TargetFramework>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="Microsoft.Data.Sqlite"          1
                      Version="2.0.0" />
  </ItemGroup>

</Project>

• 1 Adds a reference to Microsoft.Data.Sqlite

Open the Program.cs file and add the following code.

Listing 5.2. SQLite test code

using System;
using Microsoft.Data.Sqlite;

namespace SqliteConsoleTest
{
  public class Program
  {
    public static void Main(string[] args)
    {
      using (var connection = new SqliteConnection(           1
        "Data Source=:memory:"))                              2
      {
        connection.Open();
        var command = new SqliteCommand(                      3
          "SELECT 1;", connection);                           4
        long result = (long)command.ExecuteScalar();          5
        Console.WriteLine($"Command output: {result}");
      }                                                       6
    }
  }
}

• 1 Creates a connection to the database
• 2 Specifies in-memory database in the connection string
• 3 You want to issue a command to the database.
• 4 “SELECT 1;” is the SQL command you want to execute.
• 5 You expect the result of the command to be a scalar (single value).
• 6 End of the using block disposes of the connection object

If you’ve never worked with relational databases before, there are a lot of new concepts in listing 5.2. Let’s explore these a bit more.

The first concept is the connection. The database is typically a separate entity from your program. It manages its own memory, disk, network, and CPU resources. SQLite is a rare exception in that it runs inside your process. You wouldn’t want to use it in a production environment because your application and your database would be competing for resources within the same process.

Because the database is typically separate, you need to connect to it to do anything. Most database providers, which are part of the client libraries, manage a pool of database connections for you, keyed off of the connection string. The connection string contains the information that the provider needs to know to establish a connection to the database or the data file.

The contents of a connection string are usually spelled out in the database documentation, but this documentation can be lengthy and perhaps a little intimidating. When you need a connection string, I recommend https://connectionstrings.com as a must-have in your developer tool belt. It’s a great, quick reference and covers every database you can think of.

Once you’re connected to the database, you perform all of your operations through commands. You don’t explicitly specify what type of command you’re issuing in listing 5.2. It uses the default value Text, which means a SQL statement or statements.

Don’t know SQL?

Don’t worry if you’re new to SQL. This chapter sticks to simple SQL statements and provides explanations. To get deeper into SQL, check out Ben Brumm’s SQL in Motion video courses from Manning Publications.

The SQL command you’re executing in listing 5.2 is a query. Typically, you’d query data from a table, but in this case you’re querying a constant value: 1. If you expect only one value to come back from a query, you tell the command object to execute the command as a scalar, and the return value for ExecuteScalar is an object. The data provider will typically map the databases data types to .NET data types for you. The value 1 matches the long value type most closely, so you can cast the returned object to a long using the (long) syntax.

Execute dotnet run to run the application. The command output should be 1.

As you can see, SQLite is quick and easy to set up. Plus, it works great with .NET Core. The “SELECT 1;” query verifies that the SQLite engine is working. Next, let’s explore how you can build a solution for Widget Corp.

5.2. Planning the application and database schema

Before you design your application, you first must understand what Widget Corp. needs. Their primary concern is that inventory is counted by hand and recorded on paper, so the values are inaccurate and outdated. Sometimes this results in parts being ordered too late and causes disruptions in the manufacturing process. Other times, it results in too many parts being ordered, which takes up valuable warehouse space and can sometimes lead to disposing of unused parts when a design change requires a different part. Widget Corp. needs to be able to order parts at the right times, which is easier if the inventory numbers are accurate.

You also need to get the floor personnel to want to use your system. That means it should be less complicated or less work than what they’re currently doing.

Ordering can be automated, requiring intervention only when parts are no longer needed. Getting the inventory count can be reduced to checking shipments and marking orders as fulfilled (either by mobile app or web). You won’t try to handle late, incomplete, or incorrect order fulfillment in this example.

5.2.1. Tracking inventory

There are three things you need to track Widget Corp.’s inventory accurately:

• The current, accurate inventory counts
• An adjustment every time an order is fulfilled
• An adjustment every time a part is removed from inventory

Widget Corp. does a full inventory check every month. That gives your software a targeted release date so that you can take advantage of a count that’s already occurring. The supervisor at the loading dock is in charge of handling incoming shipments, so instead of supervisors recording incoming shipments on paper, you could provide a simple mobile app where they could mark an order as fulfilled when it comes in. You expect there to be several incoming shipments per week.

All suppliers mark their parts with barcodes, so you can provide a mobile app that allows a factory worker to scan the barcode of a part before it’s used or discarded. In the current system, a warehouse manager keeps track of everything going in and out. This manager becomes a bottleneck when there are many simultaneous requests. By allowing each factory worker unhindered access to the warehouse, they can get the parts they need more quickly. The barcode system also means that parts can be located closer to their stations, rather than in a central warehouse.

Relational databases like SQLite organize data into tables. There are other constructs besides tables, but they’re not necessary for this example. A database design is called a schema. To create a schema, you’ll need to design your tables, the columns for those tables, and how they relate to each other.

Let’s start with a table that holds data about the type of part and a table that holds the inventory counts. These are shown in tables 5.1 and 5.2.

Table 5.1. The PartType table for storing each type of part used by Widget Corp

Each of the parts that Widget Corp. uses has a unique name. Although you could use those names directly in all the other tables, that would result in the data being repeated everywhere. If the name of the part were to change, you’d have to change it everywhere that part name is used in the database. Plus, it’s a waste of storage space. By adding an Id column, you’re allowing other tables to reference the part with only an integer. Each row of the PartType table will have a unique Id.

Table 5.2. The InventoryItem table for storing inventory data

The InventoryItem table is an example of a table that refers to a part. Instead of storing the name of the part directly, it references the PartType table’s Id column. Notice that InventoryItem doesn’t have its own Id column. This is because you only want one count per part. Therefore, the PartTypeId can be treated as the unique Id of the InventoryItem table.

You’ll use a simple mechanism for determining when to place an order for new parts. In the InventoryItem table, the OrderThreshold column indicates that when the Count is at or below the threshold, a new order will be placed.

5.2.2. Creating tables in SQLite

Try creating a table in SQLite to see how it works. You can create the tables using SQL statements.

Because the creation of the tables isn’t part of the application you’re building, you can split that process out into a separate step. Instead of modifying the console application from the previous section, create a new set of projects using the following commands:

cd ..
mkdir CreateTablesTest
cd CreateTablesTest
dotnet new classlib -o WidgetScmDataAccess         1
dotnet new xunit -o SqliteScmTest                  2

• 1 Data-access project
• 2 Unit-test project to exercise the data-access project

WidgetScmDataAccess will have the data-access logic for the application. SqliteScmTest will be the unit-test project. The test project will create the tables in SQLite and populate them with example data.

Data-access projects usually don’t create or alter the database schema—they’re concerned with the data itself. The responsibility of creating the schema typically falls outside of the data access project. To create the schema in an in-memory database, you’ll have the unit test project do that work on startup.

In chapter 4 you learned about using class fixtures in xUnit as a means to share context between tests. A class fixture serves as a great place to initialize a SQLite database for testing. Add a new file to the SqliteScmTest project called SampleScmDataFixture.cs, and add the following code.

Listing 5.3. Contents of SampleScmDataFixture.cs

using System;
using System.Data.Common;                                       1
using Microsoft.Data.Sqlite;

namespace SqliteScmTest
{
  public class SampleScmDataFixture : IDisposable               2
  {
    public SqliteConnection Connection { get; private set; }

    public SampleScmDataFixture()
    {
      var conn = new SqliteConnection("Data Source=:memory:");
      Connection = conn;
      conn.Open();

      var command = new SqliteCommand(
        @"CREATE TABLE PartType(                                3
            Id INTEGER PRIMARY KEY,                             4
            Name VARCHAR(255) NOT NULL                          5
          );", conn);
      command.ExecuteNonQuery();                                6
      command = new SqliteCommand(
        @"INSERT INTO PartType                                  7
            (Name)                                              8
            VALUES
            ('8289 L-shaped plate')",                           9
        conn);
      command.ExecuteNonQuery();
    }

    public void Dispose()
    {
      if (Connection != null)
        Connection.Dispose();
    }
  }
}

• 1 System.Data.Common defines common data-access types.
• 2 The connection object is disposable, so the fixture should dispose of it.
• 3 SQL statement to create the PartType table
• 4 Creates the Id column; sets it as primary key
• 5 VARCHAR(255) means variable length of characters, as many as 255
• 6 You’re not querying anything, so it’s a NonQuery.
• 7 Adds an entry to the PartType table
• 8 List of columns you’re providing data for
• 9 The data for the Name column

In this example, the data fixture executes a SQL statement that creates the PartType table. The statement includes specifications for each column in the format “[Name] [Type] [Constraints]”, and the types vary for each database. Often, database types are more specific than the value types in a programming language. For example, a database cares a lot about how much space data will take up. When designing a database schema, you have to consider this, as it will have an impact on what types you’ll use.

There are two different constraints used in listing 5.2: NOT NULL and PRIMARY KEY. NOT NULL ensures that the column can’t have a null value. You’ll get an error if you try to insert a null value into the Name column. PRIMARY KEY is actually a combination of NOT NULL and UNIQUE. UNIQUE indicates that each row in the table must have a distinct value for the Id column. Notice, though, that you don’t insert a value for Id, and the code still works. In this case, SQLite automatically adds a value for Id. In other databases, like SQL Server, this doesn’t happen automatically unless the column is indicated as an identity column.

Return value of ExecuteNonQuery

Although ExecuteNonQuery means the command isn’t a query, that doesn’t mean data doesn’t get returned. The return value of ExecuteNonQuery is an integer indicating the number of rows affected by the command. You can also use this method if you’re executing a command that has output parameters or return values mapped to parameters. We won’t go into these uses in this book.

This class fixture creates the PartType table and inserts a single row. It exposes one property called Connection of type Microsoft.Data.SqliteConnection, which inherits from System.Data.Common.DbConnection.

The System.Data.Common namespace has some fundamental common types for interacting with data stores, and most data-access client libraries implement the abstract classes and interfaces from that namespace. A data-access library should stick to the System.Data.Common namespace as much as possible so that different databases can be used, such as SQLite for development and test and SQL Server for production. Because SqliteConnection inherits from DbConnection, you can use that connection object in your data-access library.

Now modify UnitTest1.cs to use SampleScmDataFixture. To do this, try the following code.

Listing 5.4. Modifying UnitTest1.cs to use SampleScmDataFixture

using Xunit;

namespace SqliteScmTest
{
  public class UnitTest1 : IClassFixture<SampleScmDataFixture>
  {
    private SampleScmDataFixture fixture;

    public UnitTest1(SampleScmDataFixture fixture)
    {
      this.fixture = fixture;
    }

    [Fact]
    public void Test1()                         1
    {
      Assert.True(true);
    }
  }
}

• 1 As long as one test is run, the class fixture is used.

The test method in this class doesn’t do anything interesting, but its presence is enough to trigger the constructor of the test class, and therefore the constructor and Dispose method of SampleScmDataFixture. Before executing the test, edit the project file as follows.

Listing 5.5. Dependencies to add to the SqliteScmTest.csproj

<ItemGroup>
  <PackageReference Include="Microsoft.NET.Test.Sdk" Version="15.3.0" />
  <PackageReference Include="xunit" Version="2.2.0" />
  <PackageReference Include="xunit.runner.visualstudio" Version="2.2.0" />
  <PackageReference Include="Microsoft.Data.Sqlite"                      1
                    Version="2.0.0" />
  <PackageReference Include="System.Data.Common"                         2
                    Version="4.3.0" />
  <PackageReference Include="System.Runtime.Serialization.Primitives"
                    Version="4.3.0" />
</ItemGroup>

• 1 Add dependency on SQLite
• 2 System.Data.Common is not part of .NET Standard.

Now you can run dotnet test. If the test is successful, the creation of the table and row insert was done correctly.

5.3. Creating a data-access library

You’ve created the test harness for your data-access library, including a database schema and some sample data. Now you’ll add some code to the WidgetScmDataAccess library created in the previous section.

The first thing to think about is a class to hold the data for each part. Add a PartType.cs file to the WidgetScmDataAccess folder with the following code.

Listing 5.6. Code for the PartType class that will hold the data for each part

namespace WidgetScmDataAccess
{
  public class PartType
  {
    public int Id { get; internal set; }              1
    public string Name { get; set; }
  }
}

• 1 Don’t allow modifying the Id outside of this library.

The next thing you need is a way to hydrate the PartType objects. Hydrate is the term commonly used for the process of setting all the members of an object. The data-access library reads the values from a row in the PartType table and sets the properties of the PartType object representing that row. There are many ways to do this, but the one you’ll use is a single context class—context meaning that the class understands how to work with all the objects and tables that are related to each other in a certain context.

Your context class will take a DbConnection object and populate a list with all the part type data from the source table. For this example, as shown in listing 5.7, you’re only interested in retrieving the data, not creating, updating, or deleting it. You’ll also cache all the part data. This isn’t something you’d do lightly in a real application where parts can change, but it keeps your sample code simple.

Listing 5.7. Code for the ScmContext class that will get data from the data store

using System.Collections.Generic;
using System.Data.Common;

namespace WidgetScmDataAccess
{
  public class ScmContext
  {
    private DbConnection connection;

    public IEnumerable<PartType> Parts { get; private set; }

    public ScmContext(DbConnection conn)
    {
      connection = conn;
      ReadParts();
    }

    private void ReadParts()
    {
      using (var command = connection.CreateCommand())
      {
        command.CommandText = @"SELECT Id, Name             1
          FROM PartType";
        using (var reader = command.ExecuteReader())        2
        {
          var parts = new List<PartType>();
          Parts = parts;
          while (reader.Read())                             3
          {
            parts.Add(new PartType() {                      4
              Id = reader.GetInt32(0),                      5
              Name = reader.GetString(1)                    6
            });
          }
        }
      }
    }
  }
}

• 1 Explicitly lists columns instead of using SELECT *
• 2 Output of command has multiple rows
• 3 Moves to next row, returns false if no more rows
• 4 Shortcut notation in C# to set properties on initialization
• 5 Reads the Id column as a 32-bit integer (int or Int32 in C#)
• 6 Reads the Name column as a string

This is the first time you’ve encountered the use of ExecuteReader. It returns an object that implements the System.Data.Common.DbDataReader class. A data reader is good for queries that return one or more rows of data. In this case, the SQL statement indicates that you want all the rows from the PartType table with the columns arranged Id first, then Name. The reader has an internal cursor that starts off before the first row, so your first call to Read() will move the cursor to the first row if there is one. If no rows were returned by the query, Read() returns false.

Once you’ve moved the cursor to a row, you can extract data from each column. In listing 5.7, you use GetInt32(0) and GetString(1) for the Id and Name columns respectively. The SQL statement laid out the order explicitly, so you can use that order when extracting the values. GetInt32(0) will get the value from the first column (using zero-based numbering) and attempt to cast it to a 32-bit integer. The data reader will throw an InvalidCastException if the provider doesn’t interpret the database type as a 32-bit integer. In other words, it only casts; it doesn’t attempt to convert the value. You couldn’t, for example, call GetString(0) on the Id column because the database type for that column isn’t interpreted as a string.

Limit access to your internal collections

By exposing the Parts property in listing 5.7 as an IEnumerable<T> instead of a List<T>, you’re indicating to developers who use this property that you intend this collection to be treated as read-only. Because the data type behind it is a List, a developer could simply cast Parts as a List and make modifications, so there’s no enforcement. But you could change the underlying collection type as long as it implements IEnumerable, so those who cast it to a List do so at their own risk.

Explicitly specify columns

Specifying the columns in the query is preferred to using a SELECT *, which returns all the columns but doesn’t guarantee their order. It’s also easy to forget to update the code if the columns in the table are changed.

Now it’s time to test this code. Start by modifying the project file of the WidgetScmDataAccess project to match the following listing.

Listing 5.8. Modifying WidgetScmDataAccess.csproj to include System.Data.Common

<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <TargetFramework>netstandard1.2</TargetFramework>         1
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="System.Data.Common"
                      Version="4.3.0" />
  </ItemGroup>

</Project>

• 1 Try to use the lowest possible .NET Standard version you can.

Remove the Class1.cs file in this project, because it’s not used.

Now switch back to the SqliteScmTest project and add a test. First, modify the project file to reference the data-access library by adding this line: <ProjectReference Include="../WidgetScmDataAccess/WidgetScmDataAccess.csproj" />. Then modify the UnitTest1.cs file to include Test1, as follows.

Listing 5.9. Modify UnitTest1.cs to test for the part created in SampleScmDataFixture

using System.Linq;                                             1
using Xunit;
using WidgetScmDataAccess;                                     2

namespace SqliteScmTest
{
  public class UnitTest1 : IClassFixture<SampleScmDataFixture>
  {
    private SampleScmDataFixture fixture;
    private ScmContext context;

    public UnitTest1(SampleScmDataFixture fixture)
    {
      this.fixture = fixture;
      this.context = new ScmContext(fixture.Connection);
    }

    [Fact]
    public void Test1()
    {
      var parts = context.Parts;
      Assert.Equal(1, parts.Count());                         3
      var part = parts.First();
      Assert.Equal("8289 L-shaped plate", part.Name);
    }
  }
}

• 1 Needed for Count() and First()
• 2 Add using for library project
• 3 There’s only one part type, and you know its contents.

From here, you can run dotnet test to get the test going. When xUnit starts the test, the fixture object will create the PartType table and add a row. Then the ScmContext object will use the connection exposed by the fixture to get all the rows from the PartType table and create a PartType object for each one. System.Linq exposes an extension method called Count() that can count how many elements are in an IEnumerable. The First() method is another System.Linq extension method that gets the first item in the collection.

With a relatively small amount of work, you’ve created a mock data store and a rudimentary data-access library. But you’ve only added the PartType table. You’ve yet to add inventory items to your database. To do that, you’ll need to explore relationships in databases and how they correlate to your .NET code.

5.3.1. Specifying relationships in data and code

The PartType table only has two columns: Id and Name. The InventoryItem table you’ll create next references the Id column from PartType in a one-to-one relationship. As mentioned before, I chose this approach because you have several tables that refer to parts, and you don’t want to copy the name of the part into each row of each table. It also gives you the flexibility to add more attributes to parts later on.

Figure 5.1 shows the database schema for your application. The line connecting the PartType and InventoryItem tables indicates that there is a foreign-key relationship. The rest of the tables and relationships will become clear later in the chapter.

Figure 5.1. Database schema of the supply-chain management application

In SQLite, you can specify foreign keys when you create the table. Add the InventoryItem table to your fixture by modifying SampleScmDataFixture.cs as follows.

Listing 5.10. Code to create tables in SampleScmDataFixture

public class SampleScmDataFixture : IDisposable
{
  private const string PartTypeTable =
      @"CREATE TABLE PartType(
          Id INTEGER PRIMARY KEY,
          Name VARCHAR(255) NOT NULL
        );";
  private const string InventoryItemTable =
    @"CREATE TABLE InventoryItem(
        PartTypeId INTEGER PRIMARY KEY,
        Count INTEGER NOT NULL,
        OrderThreshold INTEGER,
      FOREIGN KEY(PartTypeId) REFERENCES PartType(Id)              1
      );";

  public SampleScmDataFixture()
  {
    var conn = new SqliteConnection("Data Source=:memory:");
    Connection = conn;
    conn.Open();

    (new SqliteCommand(PartTypeTable, conn)).ExecuteNonQuery();
    (new SqliteCommand(InventoryItemTable, conn)).                 2
      ExecuteNonQuery();
    (new SqliteCommand(
      @"INSERT INTO PartType
          (Id, Name)                                               3
          VALUES
          (0, '8289 L-shaped plate')",
      conn)).ExecuteNonQuery();
    (new SqliteCommand(
      @"INSERT INTO InventoryItem
          (PartTypeId, Count, OrderThreshold)
          VALUES
          (0, 100, 10)",
      conn)).ExecuteNonQuery();
  }
}

• 1 Foreign key relationships can be defined in the table.
• 2 A short way to create an object and call a method on it
• 3 Specifies Id this time because you want the tests to be consistent

Calling a method on a new object

In listing 5.10, you use shorthand for creating the SqliteCommand objects and executing a method on them. You don’t use the objects for anything else in the code, so we don’t need to declare a variable.

You need a class to hold the rows of the InventoryItem table. In the data-access library, add a new class called InventoryItem and add the following code.

Listing 5.11. InventoryItem class

namespace WidgetScmDataAccess
{
  public class InventoryItem
  {
    public int PartTypeId { get; set; }
    public int Count { get; set; }
    public int OrderThreshold { get; set; }
  }
}

You already have a ReadParts method in the ScmContext class. Now you need to add a new method to read the inventory. You’ll use the same approach of reading all the items in the constructor and exposing them through an IEnumerable property. Add the following code to ScmContext.

Listing 5.12. Adding code to the ScmContext class for inventory items

public class ScmContext
{
  public IEnumerable<InventoryItem> Inventory { get; private set; }

  public ScmContext(DbConnection conn)
  {
    connection = conn;
    ReadParts();
    ReadInventory();
  }

  private void ReadInventory()
  {
    using (var command = connection.CreateCommand())
    {
      command.CommandText = @"SELECT
          PartTypeId, Count, OrderThreshold
        FROM InventoryItem";
      using (var reader = command.ExecuteReader())
      {
        var items = new List<InventoryItem>();
        Inventory = items;
        while (reader.Read())
        {
          items.Add(new InventoryItem() {
            PartTypeId = reader.GetInt32(0),
            Count = reader.GetInt32(1),
            OrderThreshold = reader.GetInt32(2)
          });
        }
      }
    }
  }
}

Finally, modify the test to verify that the inventory items show up. Alter the Test1 method in UnitTest1 as follows.

Listing 5.13. Test method to verify InventoryItem objects are populated correctly

[Fact]
public void Test1()
{
  var parts = context.Parts;
  Assert.Equal(1, parts.Count());
  var part = parts.First();
  Assert.Equal("8289 L-shaped plate", part.Name);
  var inventory = context.Inventory;
  Assert.Equal(1, inventory.Count());
  var item = inventory.First();
  Assert.Equal(part.Id, item.PartTypeId);
  Assert.Equal(100, item.Count);
  Assert.Equal(10, item.OrderThreshold);
}

You’ve defined a relationship between the PartType and InventoryItem tables, but you haven’t yet created a relationship in the code. When you use an InventoryItem object, you probably want to know what the name of the part is, and there are many ways to get the PartType object. The following listing shows how you can use a LINQ expression to get the PartType.

Listing 5.14. Using a LINQ expression to populate the PartType of an InventoryItem

public class InventoryItem
{
  public int PartTypeId { get; set; }
  public PartType Part { get; set; }                    1
  public int Count { get; set; }
  public int OrderThreshold { get; set; }
}

using System.Linq;                                      2
public class ScmContext
{
  public ScmContext(DbConnection conn)
  {
    connection = conn;
    ReadParts();
    ReadInventory();
  }

  private void ReadInventory()
  {
    var command = connection.CreateCommand();
    command.CommandText = @"SELECT
        PartTypeId, Count, OrderThreshold
      FROM InventoryItem";
    var reader = command.ExecuteReader();
    var items = new List<InventoryItem>();
    Inventory = items;
    while (reader.Read())
    {
      var item = new InventoryItem() {
        PartTypeId = reader.GetInt32(0),
        Count = reader.GetInt32(1),
        OrderThreshold = reader.GetInt32(2)
      };
      items.Add(item);
      item.Part = Parts.Single(p =>                 3
        p.Id == item.PartTypeId);                   4
    }
  }
}

• 1 Adds a property to access the PartType directly
• 2 Adds a using for LINQ
• 3 Single ensures that there’s only one PartType.
• 4 Assumes Parts is already populated

There are many different ways to hydrate objects with relationships. For example, you could have the Part property on InventoryItem only retrieve the PartType object when it’s first asked for (using the get on the property). You could also use a technique called lazy-loading, where the PartType table would only be queried when a part was needed. This means the InventoryItem class would need to keep a reference to the context class. The InventoryItem class couldn’t be defined in a different assembly, because that would create a circular reference. These are things to keep in mind when designing your application.

5.3.2. Updating data

The inventory counts are now stored in the InventoryItem table. In response to an event, such as a factory worker getting parts from inventory, or a shipment arriving at the loading dock, you’ll need to update the count. But there are many factory workers and many shipments, and they all work at the same time. Modifying the count directly can be dangerous if two or more actors attempt it simultaneously.

To handle this situation, many applications used a technique called eventual consistency. In this case, instead of each inventory count change making a direct update to the InventoryItem table, you can record the decrease or increase in a separate table that acts like a queue. A single-threaded processor can then process those records serially. This is called eventual consistency because the inventory count will eventually catch up to reality, but there’s a possibility that the count you retrieve is stale. Importance is placed on the availability of the inventory numbers rather than their accuracy.

CAP theorem

Choosing availability over consistency is a tradeoff I make because of the CAP theorem. CAP stands for consistency, availability, and partition-tolerance, and the theorem states that you can only have two of the three at the same time in a service. We won’t get into partition-tolerance in this book, but it’s generally considered not optional if you want a distributed service that scales. That leaves the choice between consistency and availability. An argument can be made for both sides when it comes to keeping inventory numbers. If you’re curious, you should look up the CAP theorem, sometimes called Brewer’s theorem, after the computer scientist who created it.

The way you’ll achieve eventual consistency is through a technique called Command Query Responsibility Segregation (CQRS). In this technique, queries are performed against one object (the InventoryItem table) while updates are performed against different objects. Updates will be stored in a command object you’ll call PartCommand (see table 5.3).

Table 5.3. The PartCommand table contains all the commands made to modify the inventory.

Each row in the PartCommand table records an action against the part inventory: the inventory for a particular part is increased or decreased by a count. It’s therefore not important in what order the commands are processed (although there is the possibility of a negative count). It’s more important that a command is executed only once. If you need an accurate part count, you can wait until all the commands are processed—after business hours or on weekends, for example.

With this approach, a single-threaded processor will read the commands and update the inventory count. It’s easier to guarantee consistency with a single-writer, multiple-reader system than it is with a multiple-writer, multiple-reader system, which is what you would have if you didn’t use commands and updated the inventory table directly.

Add a new file to WidgetScmDataAccess called PartCommand.cs with the following code. This class will be used to add rows to or remove them from the PartCommand table.

Listing 5.15. Contents of PartCommand class

namespace WidgetScmDataAccess
{
  public class PartCommand
  {
    public int Id { get; set; }
    public int PartTypeId { get; set; }
    public PartType Part { get; set; }
    public int PartCount { get; set; }
    public PartCountOperation Command { get; set; }
  }

  public enum PartCountOperation                           1
  {
    Add,
    Remove
  }
}

• 1 Enumerations are used for discrete sets of values.

PartCountOperation is an enumeration, which makes it easier to work with in code. Otherwise, you’d need to use a string or an integer, and define somewhere what the acceptable values were for that field. C# enum types are backed by integers, so you can choose to store the command in the database as either an integer (0 or 1) or a string ("Add" or "Remove"). I prefer to store the string, because other applications may read the same database (such as reporting software) and not understand the integer.

Database normalization

You could alternatively create a PartCountOperation table with an Id column and a foreign key relationship to the Command column in PartCommand. This is much like how you created the PartType table. The goal here is to reduce redundant data (like multiple copies of the strings "Add" and "Remove") and improve data integrity (by enforcing that only "Add" and "Remove" can be used). This is called normalization.

A factory worker pulls items from inventory, so in your application you’ll create a PartCommand object that captures this action. It will need to be saved to the database, but we’ll skip creating the table, because you’re familiar with that code by now. The full code for creating all the tables is included in the companion GitHub repo for this book.

Let’s move on to creating the new row in the PartCommand table. Add the CreatePartCommand method to the ScmContext class using the following code.

Listing 5.16. Method to add a part command to the database

public void CreatePartCommand(PartCommand partCommand)
{
  var command = connection.CreateCommand();
  command.CommandText = @"INSERT INTO PartCommand
    (PartTypeId, Count, Command)
    VALUES
    (@partTypeId, @partCount, @command);                             1
    SELECT last_insert_rowid();";                                    2
  AddParameter(command, "@partTypeId", partCommand.PartTypeId);
  AddParameter(command, "@partCount", partCommand.PartCount);
  AddParameter(command, "@command",
    partCommand.Command.ToString());                                 3
  long partCommandId = (long)command.ExecuteScalar();                4
  partCommand.Id = (int)partCommandId;                               5
}

• 1 The @ denotes a parameter.
• 2 Gets the Id for the row inserted to the PartCommand table
• 3 Sets the Id on the PartCommand object, in case the caller wants it
• 4 The scalar returned is the Id.
• 5 Converts the enum to a string (“Add” or “Remove”)

In the SQL statement, you use parameters instead of adding the values directly into the command text string. You didn’t do this when inserting rows in your test code because it’s test code. The ScmContext class is intended for use in production. Whenever you add values to a SQL statement, you should use parameters. We’ll get into some of the reasons why later in this chapter.

Another odd thing in listing 5.16 is the statement SELECT last_insert_rowid(). This function is part of SQLite. When inserting a row into the PartCommand table, SQLite automatically populates the Id column. You use the last_insert_rowid() function to get the value that SQLite used for that Id column.

The CreatePartCommand code also makes use of a helper method called AddParameter, which creates a DbParameter and adds it to the DbCommand object. The following listing shows the code for this method.

Listing 5.17. AddParameter method creates DbParameter objects

private void AddParameter(DbCommand cmd, string name, object value)
{
  var p = cmd.CreateParameter();
  if (value == null)
    throw new ArgumentNullException("value");              1
  Type t = value.GetType();
  if (t == typeof(int))                                    2
    p.DbType = DbType.Int32;
  else if (t == typeof(string))
    p.DbType = DbType.String;
  else if (t == typeof(DateTime))
    p.DbType = DbType.DateTime;
  else
    throw new ArgumentException(                           3
      $"Unrecognized type: {t.ToString()}", "value");      4
  p.Direction = ParameterDirection.Input;                  5
  p.ParameterName = name;
  p.Value = value;
  cmd.Parameters.Add(p);                                   6
}

• 1 Your code isn’t smart enough to handle null values.
• 2 typeof() isn’t a constant, so you can’t use a switch.
• 3 If the type of value isn’t recognized, throws an exception
• 4 Type.ToString() will write the full type name.
• 5 You’re only using this helper method for input parameters.
• 6 Adds the parameter to the command

The AddParameter method doesn’t handle null values, but it’s reasonable that a parameter passed to a command could have a null value, because certain columns allow null values. The problem is that you can’t call a method, such as GetType(), on a null value. You need to specify a DbType that matches the column’s type, so in listing 5.17 you’re using the .NET type of the value parameter to infer a DbType. If you had an overload of the AddParameter method that took a DbType parameter, you wouldn’t have to throw the exception for a null value.

Your single-threaded, eventually consistent processor will read the commands from the PartCommand table and make updates to the InventoryItem table. The following listing has the code to retrieve all the PartCommand objects in order by Id.

Listing 5.18. GetPartCommands method: reads all the PartCommand rows in the table

public IEnumerable<PartCommand> GetPartCommands()
{
  var command = connection.CreateCommand();
  command.CommandText = @"SELECT
      Id, PartTypeId, Count, Command
    FROM PartCommand
    ORDER BY Id";                                    1
  var reader = command.ExecuteReader();
  var partCommands = new List<PartCommand>();
  while (reader.Read())
  {
    var cmd = new PartCommand() {
      Id = reader.GetInt32(0),
      PartTypeId = reader.GetInt32(1),
      PartCount = reader.GetInt32(2),
      Command = (PartCountOperation)Enum.Parse(      2
        typeof(PartCountOperation),
        reader.GetString(3))
    };
    cmd.Part = Parts.Single(p => p.Id == cmd.PartTypeId);
    partCommands.Add(cmd);
  }

  return partCommands;
}

• 1 Orders by Id ascending; Ids are usually incremented for each row inserted.
• 2 Converts the string back to an enum

PartCountOperation operation;
if (Enum.TryParse<PartCountOperation>(reader.GetString(3), out operation))
  cmd.Command = operation;

5.3.3. Managing inventory

You can now add rows to and retrieve rows from the PartCommands table. But you still haven’t handled updating theInventoryItem table.

Add another method to the ScmContext class with the following code.

Listing 5.19. Method to update the part count in the InventoryItem table

public void UpdateInventoryItem(
  int partTypeId, int count)                           1
{
  var command = connection.CreateCommand();
  command.CommandText = @"UPDATE InventoryItem         2
    SET Count=@count                                   3
    WHERE PartTypeId=@partTypeId";                     4
  AddParameter(command, "@count", count);
  AddParameter(command, "@partTypeId", partTypeId);
  command.ExecuteNonQuery();
}

• 1 You’ll use partTypeId to identify the InventoryItem.
• 2 SQL statement to update rows in a table
• 3 You can list multiple columns separated by commas.
• 4 Only updates rows that have the partTypeId you specify

After PartCommand has been applied to the inventory, you need to delete the record from the table so you don’t process it again. Add another method to ScmContext to delete the row, as follows.

Listing 5.20. Method to delete the PartCommand row from the table

public void DeletePartCommand(int id)
{
  var command = connection.CreateCommand();
  command.CommandText = @"DELETE FROM PartCommand       1
    WHERE Id=@id";
  AddParameter(command, "@id", id);
  command.ExecuteNonQuery();
}

• 1 SQL statement to delete rows from a table

Next, create a new class called Inventory to manage the inventory, as shown in the next listing.

Listing 5.21. Creating the Inventory class

using System;
using System.Linq;                                 1
namespace WidgetScmDataAccess
{
  public class Inventory
  {
    private ScmContext context;
    public Inventory(ScmContext context)           2
    {
      this.context = context;
    }

    public void UpdateInventory() {}               3
  }
}

• 1 You’ll be using these namespaces later.
• 2 Must have the context to get and update data
• 3 This method will be filled in later.

The UpdateInventory method is the one you want to execute in a single thread, because it will be performing the database updates. The first step in UpdateInventory is to go through all the PartCommand objects and update the inventory counts. Add the following code to the UpdateInventory method.

Listing 5.22. Updates the inventory counts based on the PartCommands

foreach (var cmd in context.GetPartCommands())         1
{
  var item = context.Inventory.Single(i =>             2
    i.PartTypeId == cmd.PartTypeId);
  if (cmd.Command == PartCountOperation.Add)           3
    item.Count += cmd.PartCount;
  else
    item.Count -= cmd.PartCount;

  context.UpdateInventoryItem(item.PartTypeId,
    item.Count);
  context.DeletePartCommand(cmd.Id);                   4
}

• 1 Gets all the PartCommands from the table
• 2 Gets the InventoryItem for the part mentioned in the command
• 3 Updates the count
• 4 Deletes the PartCommand so you don’t duplicate it

Now test this code. In the SqliteScmTest project, open UnitTest1.cs and add a new test method with the following code.

Listing 5.23. Test if the part commands correctly update the inventory count

[Fact]
public void TestPartCommands()
{
  var item = context.Inventory.First();
  var startCount = item.Count;
  context.CreatePartCommand(new PartCommand() {
    PartTypeId = item.PartTypeId,
    PartCount = 10,
    Command = PartCountOperation.Add
  });
  context.CreatePartCommand(new PartCommand() {
    PartTypeId = item.PartTypeId,
    PartCount = 5,
    Command = PartCountOperation.Remove
  });
  var inventory = new Inventory(context);
  inventory.UpdateInventory();
  Assert.Equal(startCount + 5, item.Count);          1
}

• 1 InventoryItem object is updated with database

In this test, you’re adding 10 items to the inventory and removing 5 items. You want to make sure both commands are processed correctly.

5.3.4. Using transactions for consistency

There’s a nasty flaw in listing 5.23 that could cause you some sleepless nights and cause Widget Corp. not to trust the inventory count. What would happen if the deletion of the row from thePartCommand table failed, or if it never executed because of a process crash, hardware failure, power outage, or some other disastrous event? When the application starts up again, it would process the same PartCommand it’s already processed. The inventory count would be wrong, and the only way you’d know is if you stopped everything, did a full count, and compared the numbers.

You need something that can guarantee that if a failure occurs while you’re updating InventoryItem or deleting from PartCommand, both of these commands will be undone. The term for this is a transaction. A transaction groups several actions and ensures that either all of those actions occur or none of them do.

SQLite is a transactional database, though the durability part doesn’t apply if you’re using an in-memory database like you are here.

To get a transaction, you’ll need a DbTransaction object, which is created from the DbConnection object. Because you don’t expose the connection used inside ScmContext, you’ll need to provide a method to get a transaction object. Add the following code to ScmContext.

Listing 5.24. Method to get a DbTransaction object from the ScmContext object

public DbTransaction BeginTransaction()
{
  return connection.BeginTransaction();
}

Now add transactions to the UpdateInventory method of the Inventory class. Modify the code as follows.

Listing 5.25. Update the inventory counts based on the PartCommands

foreach (var cmd in context.GetPartCommands())
{
  var item = context.Inventory.Single(i =>
    i.PartTypeId == cmd.PartTypeId);
  var oldCount = item.Count;                                1
  if (cmd.Command == PartCountOperation.Add)
    item.Count += cmd.PartCount;
  else
    item.Count -= cmd.PartCount;

  var transaction = context.BeginTransaction();             2
  try {
    context.UpdateInventoryItem(item.PartTypeId,            3
      item.Count, transaction);
    context.DeletePartCommand(cmd.Id, transaction);         4
    transaction.Commit();                                   5
  }
  catch {
    transaction.Rollback();                                 6
    item.Count = oldCount;                                  7
    throw;
  }
}

• 1 Stores the old count in case the transaction fails
• 2 Creates a new database transaction
• 3 UpdateInventoryItem needs to use the same transaction object.
• 4 Deletes the PartCommand under the transaction as well
• 5 Commits the transaction if there are no errors
• 6 Rolls back the transaction if an error occurred
• 7 Sets the item count back to its old value

Transaction rollbacks don’t affect objects

DbTransaction only applies to the database, so you have to manually restore the InventoryItem object back to its original state. Therefore, you need to set the Count property back to its original value.

In listing 5.25 you pass the DbTransaction object to the UpdateInventoryItem and DeletePartCommand methods so they can be used on the DbCommand objects. Update those methods with the following code.

Listing 5.26. Update DeletePartCommand and UpdateInventoryItem

public void DeletePartCommand(int id, DbTransaction transaction)
{
  var command = connection.CreateCommand();
  if (transaction != null)
    command.Transaction = transaction;                    1
  command.CommandText = @"DELETE FROM PartCommand
    WHERE Id=@id";
  AddParameter(command, "@id", id);
  command.ExecuteNonQuery();
}

public void UpdateInventoryItem(int partTypeId, int count,
  DbTransaction transaction)
{
  var command = connection.CreateCommand();
  if (transaction != null)
    command.Transaction = transaction;                    1
  command.CommandText = @"UPDATE InventoryItem
    SET Count=@count
    WHERE PartTypeId=@partTypeId";
  AddParameter(command, "@count", count);
  AddParameter(command, "@partTypeId", partTypeId);
  command.ExecuteNonQuery();
}

• 1 Transaction only needs to be applied to the DbCommand

To test this out, try throwing an exception from DeletePartCommand or UpdateInventory just before the call to Commit(). You should see in both the InventoryItem object and the database that the part count hasn’t changed. You can rest assured that at least this operation is resilient.

Now that you have the inventory handled, let’s move on to handling orders.

5.4. Ordering new parts from suppliers

Widget Corp. orders parts from its suppliers via email. For this example, let’s assume that each supplier only supplies one part. In the real world, suppliers offer multiple parts and at different prices, which becomes too complicated for this example.

Given these restrictions, your Supplier table looks like table 5.4.

Table 5.4. Supplier table containing the names, email addresses, and part types of each supplier

We established earlier that there’s an order threshold in the InventoryItem table that tells the application when to order new parts. When the threshold is exceeded (when the part count goes below the threshold), the application should send an email to the supplier. You only want to send one email at a time until the order is fulfilled. Otherwise, you could end up with duplicate orders and receive too many parts.

To handle this, you’ll create two records in the database: one for the order and one for the command to send an email. The tables for these are outlined in tables 5.5 and 5.6 respectively.

Table 5.5. Order table contains all the orders made to suppliers

Table 5.6. SendEmailCommand table contains commands to send order emails

The same single-threaded processor we use to update the inventory will create the orders. After processing all the PartCommand objects, it checks each item to see if the part count is below the threshold. If there are no outstanding orders for an item, it creates new records in the Order and SendEmailCommand tables. Another processor is responsible for sending the emails.

5.4.1. Creating an Order

An order consists of two things, a row in the Order table and a row in the SendEmailCommand table, so you should create these records as part of a transaction. Start by defining the Order class, as shown in the next listing.

Listing 5.27. Order class

using System;

namespace WidgetScmDataAccess
{
  public class Order
  {
    public int Id { get; set; }
    public int SupplierId { get; set; }
    public Supplier Supplier { get; set; }
    public int PartTypeId { get; set; }
    public PartType Part { get; set; }
    public int PartCount { get; set; }
    public DateTime PlacedDate { get; set; }
  }
}

Listing 5.28 shows how you’ll create the records in the Order and SendEmailCommand tables. The CreateOrder method, shown in the following listing, takes an Order object with all the properties filled in (except the Id property).

Listing 5.28. The CreateOrder method

public void CreateOrder(Order order)
{
  var transaction = connection.BeginTransaction();
  try {
    var command = connection.CreateCommand();
    command.Transaction = transaction;
    command.CommandText = @"INSERT INTO [Order]                       1
      (SupplierId, PartTypeId, PartCount,
      PlacedDate) VALUES (@supplierId,
      @partTypeId, @partCount, @placedDate);
      SELECT last_insert_rowid();";
    AddParameter(command, "@supplierId", order.SupplierId);
    AddParameter(command, "@partTypeId", order.PartTypeId);
    AddParameter(command, "@partCount", order.PartCount);
    AddParameter(command, "@placedDate", order.PlacedDate);
    long orderId = (long)command.ExecuteScalar();
    order.Id = (int)orderId;                                          2

    command = connection.CreateCommand();
    command.Transaction = transaction;
    command.CommandText = @"INSERT INTO SendEmailCommand
      ([To], Subject, Body) VALUES                                    3
      (@To, @Subject, @Body)";
    AddParameter(command, "@To",
      order.Supplier.Email);                                          4
    AddParameter(command, "@Subject",
      $"Order #{orderId} for {order.Part.Name}");                     5
    AddParameter(command, "@Body", $"Please send {order.PartCount}" +
      $" items of {order.Part.Name} to Widget Corp");
    command.ExecuteNonQuery();

    transaction.Commit();
  }
  catch {
    transaction.Rollback();
    throw;
  }
}

• 1 Order is a special keyword in SQLite, so you have to surround it with brackets.
• 2 Sets the Id in case the caller of CreateOrder needs it
• 3 To is another SQLite keyword.
• 4 The Supplier property must be populated.
• 5 The Part property must be populated.

See companion repo for table creation code

In listing 5.28, I’m making the assumption that the Order and SendEmailCommand tables already exist. You should already be pretty familiar with creating tables, but if you don’t want to type all of that code, the full source code for this book is available on GitHub at http://mng.bz/F146.

command.CommandText = $@"INSERT INTO SendEmailCommand
  ([To], Subject, Body) VALUES
  ('{order.Supplier.Email}', '{order.Part.Name}', '{body}')";

Notice that in order to send the email, you need to have the Supplier and Part properties filled in on the Order object. One issue that could occur is that the caller of CreateOrder doesn’t specify one of these properties. This would result in a NullReferenceException, which would cause the transaction to roll back.

Test this out. In the SqliteScmTest project, add a new test to UnitTest1.cs with the following code. This code shows how you can execute CreateOrder, encounter an exception that causes a rollback, and verify that the order record wasn’t created.

Listing 5.29. Unit test to verify that CreateOrder is transactional

[Fact]
public void TestCreateOrderTransaction()
{
  var placedDate = DateTime.Now;
  var supplier = context.Suppliers.First();
  var order = new Order()
  {
    PartTypeId = supplier.PartTypeId,                                  1
    SupplierId = supplier.Id,
    PartCount = 10,
    PlacedDate = placedDate
  };
  Assert.Throws<NullReferenceException>(() =>                          2
    context.CreateOrder(order));                                       3
  var command = new SqliteCommand(
    @"SELECT Count(*) FROM [Order] WHERE                               4
      SupplierId=@supplierId AND                                       5
      PartTypeId=@partTypeId AND
      PlacedDate=@placedDate AND
      PartCount=10 AND
      FulfilledDate IS NULL",
    fixture.Connection);
  AddParameter(command, "@supplierId", supplier.Id);                   6
  AddParameter(command, "@partTypeId", supplier.PartTypeId);
  AddParameter(command, "@placedDate", placedDate);
  Assert.Equal(0, (long)command.ExecuteScalar());                      7
}

• 1 Supplies the part and supplier IDs, but not the objects
• 2 You expect the code you’re calling to throw a NullReferenceException.
• 3 xUnit catches and checks that it’s a NullReferenceException.
• 4 “SELECT Count(*)” counts the number of rows matching the query.
• 5 Looks for the specific order, because there may be other tests
• 6 Copies the AddParameter code from ScmContext to the unit test class
• 7 Verifies the count is 0

In this test, the PartType and Supplier objects aren’t set in the Order object. You know from the code in listing 5.29 that the first INSERT command will work but the second will fail. The test verifies that the transaction successfully rolls back the first change.

Simpler transactions with System.Transactions

In the .NET Framework, there’s another way to create transactions that doesn’t involve explicitly setting the transaction on each command. This is particularly helpful if you’re using an external library that’s performing database work but that doesn’t let you pass in a DbTransaction object. This functionality is built into a library called System.Transactions.

Limited support of System.Transactions

System.Transactions was only added to .NET Core as of version 2.0. SQLite currently doesn’t support it as I’m writing this book. However, it’s likely to be supported soon.

System.Transactions is part of .NET Core but not of .NET Standard, so you’ll have to modify WidgetScmDataAccess.csproj as follows.

Listing 5.30. Change project to .NET Core for System.Transactions

<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <TargetFramework>netcoreapp2.0</TargetFramework>            1
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="System.Data.Common"
                      Version="4.3.0" />
  </ItemGroup>

</Project>

• 1 The setting to modify

The code for using a System.Transactions transaction is a little simpler than for the DbTransaction method: create a TransactionScope and use the using block and dispose pattern to automatically roll back in case of a failure. The following listing shows how the CreateOrder method could be rewritten to use TransactionScope.

Listing 5.31. CreateOrder rewritten to use TransactionScope

using System.Transactions;                                      1

public void CreateOrderSysTx(Order order)
{
  using (var tx = new TransactionScope())                       2
  {
    var command = connection.CreateCommand();                   3
    command.CommandText = @"INSERT INTO [Order]
      (SupplierId, PartTypeId, PartCount,
      PlacedDate) VALUES (@supplierId,
      @partTypeId, @partCount, @placedDate);
      SELECT last_insert_rowid();";
    AddParameter(command, "@supplierId", order.SupplierId);
    AddParameter(command, "@partTypeId", order.PartTypeId);
    AddParameter(command, "@partCount", order.PartCount);
    AddParameter(command, "@placedDate", order.PlacedDate);
    long orderId = (long)command.ExecuteScalar();
    order.Id = (int)orderId;

    command = connection.CreateCommand();
    command.CommandText = @"INSERT INTO SendEmailCommand
      ([To], Subject, Body) VALUES
      (@To, @Subject, @Body)";
    AddParameter(command, "@To", order.Supplier.Email);
    AddParameter(command, "@Subject",
      $"Order #{orderId} for {order.Part.Name}");
    AddParameter(command, "@Body", $"Please send {order.PartCount}" +
      $" items of {order.Part.Name} to Widget Corp");
    command.ExecuteNonQuery();

    tx.Complete();                                              4
  }
}

• 1 Adds the System.Transactions namespace
• 2 Creates an ambient transaction
• 3 Commands that are run within the transaction scope automatically participate.
• 4 Tells the TransactionScope that everything is good

Transactions and asynchronous code

The TransactionScope creates an ambient transaction on the thread. If you call other methods from your code that create database operations, they’ll also participate in the transaction. You can also use TransactionScope in async methods, which can move to different threads, by specifying the TransactionScopeAsyncFlowOption.

As I warned earlier, data-access providers have to opt in to take advantage of the TransactionScope. Because this is relatively new to .NET Core 2.0, it may not be available immediately in your data-access library of choice. But it is a useful feature, and it’s worth knowing about when writing applications with .NET Core.

Distributed transactions not supported in .NET Core

In the .NET Framework, System.Transactions was capable of enlisting operations on multiple databases into a single transaction. This is not available in .NET Core. In order to support multiple databases, System.Transactions will promote a transaction from local to distributed. Distributed transactions are managed by a Windows component called the Distributed Transaction Coordinator (DTC), but there’s no equivalent on other operating systems. System.Transactions in .NET Core only supports local transactions, meaning all transactions for the same database.

5.4.2. Checking if parts need to be ordered

After processing all of the PartCommand records and updating the InventoryItem table, the next step of UpdateInventory is to determine what part counts have dropped below the order threshold. Orders need to be created for those parts unless you already have outstanding orders for them.

To determine if you have an outstanding order, add a property to Order that indicates if and when the order was fulfilled, as shown in the next listing.

Listing 5.32. Order class

using System;

namespace WidgetScmDataAccess
{
  public class Order
  {
    public int Id { get; set; }
    public int SupplierId { get; set; }
    public Supplier Supplier { get; set; }
    public int PartTypeId { get; set; }
    public PartType Part { get; set; }
    public int PartCount { get; set; }
    public DateTime PlacedDate { get; set; }
    public DateTime? FulfilledDate { get; set; }      1
  }
}

• 1 FulfilledDate can be null.

In the Inventory class’s UpdateInventory method, append the following code to the end of the method.

Listing 5.33. Code to add in UpdateInventory

var orders = context.GetOrders();

foreach (var item in context.Inventory)
{
  if (item.Count < item.OrderThreshold &&                  1
    orders.FirstOrDefault(o =>
    o.PartTypeId == item.PartTypeId &&                     2
    !o.FulfilledDate.HasValue) == null)                    3
  {
    OrderPart(item.Part, item.OrderThreshold);             4
  }
}

• 1 Checks if count is below threshold
• 2 Finds orders for this part
• 3 Checks if fulfilled date is null
• 4 Orders the same number of parts as the threshold

We haven’t yet defined the ScmContext.GetOrders or Inventory.OrderPart methods. OrderPart provides a reference for what logic the Inventory.UpdateInventory method uses. GetOrders will get all the orders in the Ordertable, and you can then use a LINQ query on the returned collection to check whether orders already exist for inventory items that have counts lower than the threshold. FirstOrDefault will return either the first item in the collection that matches the query, or null if nothing matches. The query looks for the PartTypeId and checks that the order is still unfulfilled (FulfilledDate is null).

The number of parts to order could be complicated. For this example, you’re using the order threshold, since it’s easier. Also remember that if an order isn’t fulfilled, it’s considered outstanding. That’s why you allowed FulfilledDate to be null in theOrder table. But that also means you have to handle null values in your code.

Handling null values

Notice in listing 5.33 that FulfilledDate has a property called HasValue. Let’s explore this a bit more. First, go back and look at the code in listing 5.33. The FulfilledDate property has the type DateTime?, where the “?” indicates that it’s a nullable type. HasValue is a Boolean property indicating whether the value is null.

When you read data from the Order table, you need to special-case the FulfilledDate property as follows.

Listing 5.34. Reading the Order records from the table

public IEnumerable<Order> GetOrders()
{
  var command = connection.CreateCommand();
  command.CommandText = @"SELECT
      Id, SupplierId, PartTypeId, PartCount, PlacedDate, FulfilledDate
    FROM [Order]";
  var reader = command.ExecuteReader();
  var orders = new List<Order>();
  while (reader.Read())
  {
    var order = new Order() {
      Id = reader.GetInt32(0),
      SupplierId = reader.GetInt32(1),
      PartTypeId = reader.GetInt32(2),
      PartCount = reader.GetInt32(3),
      PlacedDate = reader.GetDateTime(4),
      FulfilledDate = reader.IsDBNull(5) ?                         1
        default(DateTime?) : reader.GetDateTime(5)                 2
    };
    order.Part = Parts.Single(p => p.Id == order.PartTypeId);
    order.Supplier = Suppliers.First(s => s.Id == order.SupplierId);
    orders.Add(order);
  }

  return orders;
}

• 1 Checks if it’s null in the table with ternary operator
• 2 default() gives you a Nullable<DateTime> with HasValue false.

Ternary operator

The line that populates the FulfilledDate property in listing 5.34 uses something called a ternary operator. If you haven’t seen this in other programming languages before, it’s basically a shorthand for doing an if/else within an expression, where if and else both return a value of the same type. The syntax is <bool> ? <then> : <else>. Try not to confuse the ternary operator with the nullable type operator.

Filling in the Order object

The only method you haven’t yet completed is OrderPart. You’ve seen before that not populating all the properties in the Order object will cause an exception. OrderPart is a helper method to hydrate the Order object and create the order, as shown in the following listing.

Listing 5.35. OrderPart helper method to hydrate the Order object and create the order

public void OrderPart(PartType part, int count)
{
  var order = new Order() {
    PartTypeId = part.Id,
    PartCount = count,
    PlacedDate = DateTime.Now
  };
  order.Part = context.Parts.Single(p => p.Id == order.PartTypeId);
  order.Supplier = context.Suppliers.First(                          1
    s => s.PartTypeId == part.Id);
  order.SupplierId = order.Supplier.Id;
  context.CreateOrder(order);
}

• 1 Note that you’ll need to have data in the Supplier table first.

Now test out this whole process. In the SqliteScmTest project, add another test to UnitTest1.cs with the following code.

Listing 5.36. Test that UpdateInventory creates an Order when part count is zero

[Fact]
public void TestUpdateInventory()
{
  var item = context.Inventory.First();
  var totalCount = item.Count;
  context.CreatePartCommand(new PartCommand() {
    PartTypeId = item.PartTypeId,
    PartCount = totalCount,                         1
    Command = PartCountOperation.Remove
  });

  var inventory = new Inventory(context);
  inventory.UpdateInventory();                      2
  var order = context.GetOrders().FirstOrDefault(
    o => o.PartTypeId == item.PartTypeId &&
    !o.FulfilledDate.HasValue);
  Assert.NotNull(order);                            3

  context.CreatePartCommand(new PartCommand() {
    PartTypeId = item.PartTypeId,
    PartCount = totalCount,
    Command = PartCountOperation.Add                4
  });

  inventory.UpdateInventory();                      5
  Assert.Equal(totalCount, item.Count);
}

• 1 Drops the part count down to 0
• 2 UpdateInventory should check the thresholds and create the order.
• 3 Makes sure an order was created for this part
• 4 Puts the part count back to where it was
• 5 Need to run UpdateInventory to process the PartCommand

This test doesn’t check for the SendEmailCommand record, but that’s easy enough to add. In a real application, the UpdateInventory method would execute on a regular interval. New PartCommand records can be created at any time. The interval on which the SendEmailCommand records are processed can be separate, giving Widget Corp. a chance to evaluate them before the emails are sent.

Additional resources

To learn more about what we covered in this chapter, try the following resources:

• Ben Brumm, SQL in Motion—http://mng.bz/i433
• ADO.NET Overview—http://mng.bz/f0lM

Summary

In this chapter you learned about working with relational data in .NET Core and we covered some useful data-access features.

These are the key concepts from this chapter:

• System.Data.Common classes are database-agnostic, allowing you to prototype with SQLite or another lightweight database, yet still use a full-featured database in production.
• In-memory databases make unit testing data-access code predictable.
• Transactions allow multiple changes to be made in an all-or-nothing fashion, so data can be kept consistent.

You also used a few techniques that you should keep in mind when writing data-access code:

• Use identity columns in database tables to simplify your code.
• Use nullable types to allow nulls in value types that don’t support null.
• Use specific fields in your SELECT statements rather than using SELECT * to guarantee the order and number of fields, so your code doesn’t break when the database schema changes.

As you can see from this chapter, even a simple data model can turn into a lot of code. Most of the projects I’ve worked on have had relational databases. There are many ways to access databases from .NET, and libraries wax and wane in popularity—this chapter focused on the low-level way to access data in .NET. It provides a solid foundation; we’ll build a better understanding of the high-level data-access libraries in the next chapter.