DesigningEntitiesAndStoringThemInPostgresql.cs

using System;
using System.Linq;

using Dapper;

using Npgsql;

public class Program
{
    public static void Main()
    {
        var orderId = Save();

        Read(orderId);

        Console.WriteLine("Press Enter to exit...");

        Console.ReadLine();
    }

    private static Guid Save()
    {
        // 1. Connection string
        var connectionString = "Server=127.0.0.1;Database=test;User Id=postgres;Password=kalle;";

        // 2. Create product
        var product = new Product("SMS Sweden " + Guid.NewGuid().ToString().Substring(6), 100);

        var rows = new[]
            {
                new OrderRow(product, 2)
            };

        // 3. Save product
        var productsRepository = new ProductsRepository(connectionString);

        productsRepository.Save(product);

        // 4. Create order
        var order = new Order(rows);

        // 5. Save order
        var ordersRepository = new OrdersRepository(connectionString);

        ordersRepository.Save(order);

        return order.Id;
    }

    private static void Read(Guid orderId)
    {
        // 1. Connection string
        var connectionString = "Server=127.0.0.1;Database=test;User Id=postgres;Password=kalle;";

        // 2. Find order
        var ordersRepository = new OrdersRepository(connectionString);

        var order = ordersRepository.FindOne(orderId);

        // 3. Calculate total order value
        var productsRepository = new ProductsRepository(connectionString);

        var total = 0.0;

        foreach (var row in order.Rows)
        {
            var product = productsRepository.FindOne(row.ProductId);

            total += row.Amount * product.Price;
        }

        Console.WriteLine("Order {0} is worth {1} monies!", order.Id, total);
    }
}

public class Product
{
    public Product(string name, int price)
    {
        Name = name;
        Price = price;
        Id = Guid.NewGuid();
    }

    public Product()
    {
    }

    public Guid Id { get; set; }

    public string Name { get; set; }

    public int Price { get; set; }
}

public class Order
{
    public Order(OrderRow[] rows)
    {
        Id = Guid.NewGuid();
        Rows = rows;
    }

    public Order()
    {
    }

    public Guid Id { get; set; }

    public OrderRow[] Rows { get; set; }
}

public class OrderRow
{
    public OrderRow(Product product, int amount)
    {
        ProductId = product.Id;
        Amount = amount;
    }

    public OrderRow()
    {
    }

    public Guid ProductId { get; set; }

    public int Amount { get; set; }
}

public class ProductsRepository
{
    private readonly string _connectionString;

    public ProductsRepository(string connectionString)
    {
        _connectionString = connectionString;
    }

    public void Save(Product product)
    {
        const string Sql = @"
INSERT INTO products
(
    id,
    name,
    price
)
VALUES
(
    @Id,
    @Name,
    @Price
)
";

        using (var connection = new NpgsqlConnection(_connectionString))
        {
            connection.Execute(Sql, product);
        }
    }

    public Product FindOne(Guid id)
    {
        const string Sql = @"
SELECT id,
       name,
       price
FROM products
WHERE id = @Id
";

        using (var connection = new NpgsqlConnection(_connectionString))
        {
            var product = connection.Query<Product>(Sql, new { Id = id }).First();

            return product;
        }
    }
}

public class OrdersRepository
{
    private readonly string _connectionString;

    public OrdersRepository(string connectionString)
    {
        _connectionString = connectionString;
    }

    public void Save(Order order)
    {
        const string Sql = @"
INSERT INTO orders
(
    id
)
VALUES
(
    @Id
)
";

        using (var connection = new NpgsqlConnection(_connectionString))
        {
            connection.Execute(Sql, order);
        }

        SaveRows(order);
    }

    public Order FindOne(Guid id)
    {
        const string Sql = @"
SELECT id,
       orderid,
       productid,
       amount
FROM orders
LEFT JOIN orderrows ON id = orderid
WHERE id = @Id
";

        Func<Order, OrderRow, Order> map = (o, r) =>
        {
            var rows = o.Rows ?? new OrderRow[0];

            rows = rows.Concat(new[] { r }).ToArray();

            o.Rows = rows;

            return o;
        };

        using (var connection = new NpgsqlConnection(_connectionString))
        {
            var order = connection.Query(Sql, map, new { Id = id }, splitOn: "orderid").First();

            return order;
        }
    }

    private void SaveRows(Order order)
    {
        const string Sql = @"
INSERT INTO orderrows
(
    orderid,
    productid,
    amount
)
VALUES
(
    @OrderId,
    @ProductId,
    @Amount
)";

        using (var connection = new NpgsqlConnection(_connectionString))
        {
            foreach (var row in order.Rows)
            {
                var dto = new
                {
                    OrderId = order.Id,
                    ProductId = row.ProductId,
                    Amount = row.Amount
                };

                connection.Execute(Sql, dto);
            }
        }
    }
}