Build a Natural Gas Trade Lifecycle System

Build a C# ETRM system with trade capture, positions, and MTM P&L.

Introduction

30 Second Summary

Every time you turn on your stove or heat your home, natural gas has already passed through a complex chain of trades, hedges, and risk calculations. Energy companies buy and sell billions of cubic feet of gas every day, and they need systems to track every deal from the moment it's captured to its final settlement.

In this project, you will build a simplified version of the trade management system that Allegro consultants work with daily. You'll capture gas trades, calculate net positions, and run mark-to-market valuations using C# and SQL Server.

What You'll Build

A console-based natural gas trade management system that captures physical and financial trades, aggregates positions across delivery points, and calculates unrealized P&L against a market price curve.

By the end of this project, you'll have:

  • A SQL Server database with tables for counterparties, delivery points, trades, and market prices.
  • T-SQL stored procedures that aggregate net positions and compute mark-to-market P&L.
  • A C# .NET 8 console application that provides trade entry, position reporting, and P&L display.
  • Secret Mission: Add counterparty credit exposure tracking to flag when trading limits are breached.

Are there any prerequisites?

This project assumes basic familiarity with C# syntax and SQL queries. You should already have Visual Studio and SQL Server installed on your machine.

Set Up the Project

Before you can capture trades and run mark-to-market valuations, you need a C# project to hold your application logic and a working database connection to SQL Server.

In this step, you'll create a .NET 8 console application in Visual Studio, install the database driver package, and confirm your local SQL Server instance is reachable.

In this step, get ready to:
  • Create a C# Console App project targeting .NET 8.0.
  • Install the Microsoft.Data.SqlClient NuGet package.
  • Verify your SQL Server connection.
Create the Console App project

A console application gives you a clean entry point for writing trade lifecycle logic without the overhead of a UI framework.

  • In Visual Studio, click File in the top menu bar.
  • Select New, then Project.
  • In the template search box, type Console App.
  • Select the Console App template that shows C# as the language tag.
  • Click Next.
  • In the Project name field, enter GasTradeETRM.
  • Choose a location on your machine for the project, then click Next.
  • In the Framework dropdown, select .NET 8.0 (Long Term Support).
  • Click Create.

Visual Studio creates the project and opens Program.cs with a default Hello, World! template.

What is a Console App?

A Console App is a .NET application that runs in a terminal window. It reads input and writes output to the command line. This makes it ideal for building data-processing tools where the focus is on logic, not visual design.

Don't see the Console App template?

You need the .NET desktop development workload installed. Open the Visual Studio Installer, click Modify on your installation, check .NET desktop development, and click Modify again. This also installs the .NET 8 SDK.

Help me find the Console App template in Visual Studio

Install the Microsoft.Data.SqlClient NuGet package

Your application needs a database driver to talk to SQL Server. The Microsoft.Data.SqlClient package is Microsoft's official ADO.NET provider for SQL Server connections.

  • In the top menu, click Project.
  • Select Manage NuGet Packages.
  • In the NuGet Package Manager window, click the Browse tab.
  • In the search box, type Microsoft.Data.SqlClient.
  • Select Microsoft.Data.SqlClient from the results list.
  • In the Version dropdown on the right, select 7.0.0.
  • Click Install.
  • If prompted to accept license terms, click I Accept.
  • Confirm the package is installed by checking Solution Explorer. Expand Dependencies then Packages. You should see Microsoft.Data.SqlClient (7.0.0) listed.

Why Microsoft.Data.SqlClient?

This is Microsoft's modern ADO.NET data provider for SQL Server. It replaces the older System.Data.SqlClient and includes features like Always Encrypted support, Azure Active Directory authentication, and improved connection resiliency.

Allegro's .NET extensions use similar ADO.NET patterns for database connectivity.

Package install fails or version not found?

Make sure your Package source dropdown (top right of the NuGet window) is set to nuget.org. If you see a network error, check your internet connection and try again.

Help me install Microsoft.Data.SqlClient in Visual Studio

Verify your SQL Server connection

Before writing any code, you should confirm that Visual Studio can reach your local SQL Server instance. This avoids frustrating debugging later when you try to insert trades.

  • In Visual Studio, click View in the top menu bar.
  • Select SQL Server Object Explorer.
  • In the SQL Server Object Explorer panel, right-click the SQL Server node.
  • Select Add SQL Server.
  • In the Server Name field, enter (local) for a default instance, or .\SQLEXPRESS if you are running SQL Server Express.
  • Leave Authentication set to Windows Authentication.
  • Click Connect.
  • Expand the connected server node. You should see a Databases folder containing your existing databases (such as master and tempdb).

What is SQL Server Object Explorer?

SQL Server Object Explorer is a panel built into Visual Studio that lets you browse and manage SQL Server databases directly from your IDE. It works similarly to SQL Server Management Studio (SSMS) but lives inside your development environment.

Cannot connect to SQL Server?

Confirm SQL Server is running by opening Services (search for it in the Windows Start menu) and checking that SQL Server (MSSQLSERVER) or SQL Server (SQLEXPRESS) shows a status of Running. If it says Stopped, right-click the service and select Start.

Help me connect to my local SQL Server from Visual Studio

Your project is ready and connected. Next up, you'll design the trade data model that stores counterparties, delivery points, and trade records in SQL Server.

Design the Trade Data Model

Your project is set up and connected to SQL Server. Now you need a database to hold trade data.

Every ETRM system starts with a data model that captures who you trade with, where gas is delivered, and what deals have been made. In this step, you will build that foundation by creating tables for counterparties, delivery points, trades, and market prices.

In this step, get ready to:
  • Create the GasTradeDB database with counterparty and delivery point reference tables.
  • Design the Trades table for physical and financial gas deals.
  • Set up the MarketPrices table and load sample data for position reporting.
Create the database and reference tables

You will write all the SQL for this step in a single script file. This keeps your database schema versioned alongside your C# code.

  • In Visual Studio's Solution Explorer, right-click the GasTradeETRM project node.
  • Select Add, then New Folder.
  • Name the folder Database.
  • Right-click the new Database folder and select Add, then New Item.
  • Select Text File from the template list and name it CreateDatabase.sql. Click Add.

Why a SQL script file in the project?

Storing your database creation script alongside your application code means anyone who clones the project can recreate the database from scratch. This is standard practice in ETRM consulting where environments need to be reproducible.

  • Paste the following SQL into CreateDatabase.sql to create the database and reference data tables:
-- Create the database
IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = 'GasTradeDB')
BEGIN
    CREATE DATABASE GasTradeDB;
END
GO

USE GasTradeDB;
GO

-- Counterparties table
IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'Counterparties')
BEGIN
    CREATE TABLE Counterparties (
        CounterpartyId INT IDENTITY(1,1) PRIMARY KEY,
        Name NVARCHAR(100) NOT NULL,
        ShortCode NVARCHAR(10) NOT NULL,
        CreditLimit DECIMAL(18,2) NULL
    );
END
GO

-- Delivery Points table
IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'DeliveryPoints')
BEGIN
    CREATE TABLE DeliveryPoints (
        DeliveryPointId INT IDENTITY(1,1) PRIMARY KEY,
        Name NVARCHAR(100) NOT NULL,
        PipelineZone NVARCHAR(50) NOT NULL
    );
END
GO

What does this code do?

  • IF NOT EXISTS checks prevent errors if you run the script more than once. The database and tables are only created if they do not already exist.
  • The Counterparties table stores the companies you trade with. Each has a short code (like SHELL or BP) used in reports and a credit limit for risk tracking.
  • The DeliveryPoints table defines where gas physically changes hands. Each point belongs to a pipeline zone (like Gulf Coast or Permian Basin).

You will not execute this script yet. You will add the remaining tables first, then run the complete script at the end.

Create the Trades table

The Trades table is the heart of any ETRM system. It must capture both physical trades (actual gas deliveries) and financial trades (cash-settled hedges) in the same structure.

  • Add the following SQL below the DeliveryPoints block in CreateDatabase.sql:
-- Trades table
IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'Trades')
BEGIN
    CREATE TABLE Trades (
        TradeId INT IDENTITY(1,1) PRIMARY KEY,
        TradeDate DATE NOT NULL DEFAULT GETDATE(),
        TradeType NVARCHAR(10) NOT NULL CHECK (TradeType IN ('Physical', 'Financial')),
        Direction NVARCHAR(4) NOT NULL CHECK (Direction IN ('Buy', 'Sell')),
        CounterpartyId INT NOT NULL FOREIGN KEY REFERENCES Counterparties(CounterpartyId),
        DeliveryPointId INT NOT NULL FOREIGN KEY REFERENCES DeliveryPoints(DeliveryPointId),
        DeliveryMonth DATE NOT NULL,
        VolumePerDay DECIMAL(18,2) NOT NULL,
        PricePerMMBtu DECIMAL(18,4) NOT NULL
    );
END
GO

What does this code do?

  • TradeType uses a CHECK constraint to enforce only two allowed values: Physical (actual gas delivery) or Financial (cash-settled derivative).
  • Direction is either Buy or Sell. This is critical for the signed quantity convention where Buy = positive and Sell = negative in position calculations.
  • VolumePerDay is measured in MMBtu/day, the standard unit for natural gas trading. PricePerMMBtu stores the agreed price in dollars per MMBtu.
  • Foreign key references to Counterparties and DeliveryPoints enforce referential integrity so every trade links to a valid counterparty and location.

What are physical vs. financial trades?

A physical trade means actual gas flows through a pipeline. You buy gas at Henry Hub and your counterparty delivers it. A financial trade is a cash-settled contract. No gas moves. Instead, at settlement, one party pays the other the difference between the contract price and the market price.

Both types live in the same Trades table because Allegro values them with the same mark-to-market formula. The TradeType column lets you separate them in reports.

Create the MarketPrices table and load sample data

Mark-to-market valuation requires a forward price curve. The MarketPrices table stores the current market price for each delivery point and month so you can compare it against your trade prices later.

  • Add the following SQL below the Trades block in CreateDatabase.sql:
-- Market Prices table
IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'MarketPrices')
BEGIN
    CREATE TABLE MarketPrices (
        MarketPriceId INT IDENTITY(1,1) PRIMARY KEY,
        DeliveryPointId INT NOT NULL FOREIGN KEY REFERENCES DeliveryPoints(DeliveryPointId),
        DeliveryMonth DATE NOT NULL,
        PricePerMMBtu DECIMAL(18,4) NOT NULL,
        PriceDate DATE NOT NULL DEFAULT GETDATE()
    );
END
GO

-- Insert reference data
INSERT INTO Counterparties (Name, ShortCode, CreditLimit) VALUES
('Shell Energy', 'SHELL', 5000000.00),
('BP Gas Marketing', 'BP', 3000000.00),
('Chevron Natural Gas', 'CVX', 4000000.00);

INSERT INTO DeliveryPoints (Name, PipelineZone) VALUES
('Henry Hub', 'Gulf Coast'),
('Waha Hub', 'Permian Basin'),
('Algonquin Citygate', 'Northeast');

-- Insert market prices (forward curve)
INSERT INTO MarketPrices (DeliveryPointId, DeliveryMonth, PricePerMMBtu, PriceDate) VALUES
(1, '2026-07-01', 2.8500, GETDATE()),
(1, '2026-08-01', 2.9200, GETDATE()),
(1, '2026-09-01', 3.0100, GETDATE()),
(2, '2026-07-01', 2.6000, GETDATE()),
(2, '2026-08-01', 2.7100, GETDATE()),
(3, '2026-07-01', 3.4500, GETDATE()),
(3, '2026-08-01', 3.5800, GETDATE());

-- Insert sample trades
INSERT INTO Trades (TradeDate, TradeType, Direction, CounterpartyId, DeliveryPointId, DeliveryMonth, VolumePerDay, PricePerMMBtu) VALUES
('2026-06-01', 'Physical', 'Buy', 1, 1, '2026-07-01', 10000, 2.7500),
('2026-06-05', 'Physical', 'Sell', 2, 1, '2026-07-01', 5000, 2.8000),
('2026-06-08', 'Physical', 'Buy', 3, 2, '2026-08-01', 8000, 2.6500),
('2026-06-10', 'Financial', 'Sell', 1, 1, '2026-07-01', 5000, 2.8200),
('2026-06-12', 'Financial', 'Buy', 2, 3, '2026-07-01', 3000, 3.4000),
('2026-06-15', 'Financial', 'Sell', 3, 2, '2026-08-01', 4000, 2.7000);
GO

What does this code do?

  • The MarketPrices table stores the forward curve. Each row is a market price for a specific delivery point and month. This is what you compare trade prices against to calculate P&L.
  • Three counterparties are inserted (Shell, BP, Chevron) with credit limits in dollars. These represent the companies on the other side of your trades.
  • Three delivery points represent real natural gas hubs: Henry Hub (the benchmark), Waha Hub (Permian Basin supply), and Algonquin Citygate (Northeast demand).
  • The sample trades include 3 physical trades and 3 financial trades across different counterparties and delivery points. This gives you a realistic trading book to test against.

Now you need to execute the complete script against your SQL Server to create the database.

  • Save CreateDatabase.sql.
  • In Visual Studio, open SQL Server Object Explorer by clicking View in the top menu bar, then selecting SQL Server Object Explorer.
  • Expand your local SQL Server instance and right-click the Databases node.
  • Select New Query. A query editor tab opens.
  • Copy the entire contents of CreateDatabase.sql and paste it into the query editor.
  • Press Ctrl+Shift+E (or click the Execute button) to run the script.

Using SSMS instead?

If you prefer SQL Server Management Studio, open SSMS, connect to your local instance, click New Query, paste the script, and press F5 to execute.

  • In SQL Server Object Explorer, right-click the Databases node and select Refresh.
  • You should see GasTradeDB listed. Expand it, then expand Tables. You should see Counterparties, DeliveryPoints, MarketPrices, and Trades listed.

Don't see GasTradeDB?

  • Check the Messages pane at the bottom of the query editor for red error text. A common issue is forgetting to include the GO statements between batches.
  • Make sure your query editor is connected to your local SQL Server instance, not a different server.
  • If you see a permissions error, ensure you are running Visual Studio with the same Windows account that has SQL Server admin access.

Help me troubleshoot my SQL Server script execution.

✔️ Awesome, I've got everything!

Great. Double check you have saved CreateDatabase.sql and all four tables are visible in SQL Server Object Explorer.

ⓧ I'd like to double check the full code

Here is the complete Database/CreateDatabase.sql file:

-- Create the database
IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = 'GasTradeDB')
BEGIN
    CREATE DATABASE GasTradeDB;
END
GO

USE GasTradeDB;
GO

-- Counterparties table
IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'Counterparties')
BEGIN
    CREATE TABLE Counterparties (
        CounterpartyId INT IDENTITY(1,1) PRIMARY KEY,
        Name NVARCHAR(100) NOT NULL,
        ShortCode NVARCHAR(10) NOT NULL,
        CreditLimit DECIMAL(18,2) NULL
    );
END
GO

-- Delivery Points table
IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'DeliveryPoints')
BEGIN
    CREATE TABLE DeliveryPoints (
        DeliveryPointId INT IDENTITY(1,1) PRIMARY KEY,
        Name NVARCHAR(100) NOT NULL,
        PipelineZone NVARCHAR(50) NOT NULL
    );
END
GO

-- Trades table
IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'Trades')
BEGIN
    CREATE TABLE Trades (
        TradeId INT IDENTITY(1,1) PRIMARY KEY,
        TradeDate DATE NOT NULL DEFAULT GETDATE(),
        TradeType NVARCHAR(10) NOT NULL CHECK (TradeType IN ('Physical', 'Financial')),
        Direction NVARCHAR(4) NOT NULL CHECK (Direction IN ('Buy', 'Sell')),
        CounterpartyId INT NOT NULL FOREIGN KEY REFERENCES Counterparties(CounterpartyId),
        DeliveryPointId INT NOT NULL FOREIGN KEY REFERENCES DeliveryPoints(DeliveryPointId),
        DeliveryMonth DATE NOT NULL,
        VolumePerDay DECIMAL(18,2) NOT NULL,
        PricePerMMBtu DECIMAL(18,4) NOT NULL
    );
END
GO

-- Market Prices table
IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'MarketPrices')
BEGIN
    CREATE TABLE MarketPrices (
        MarketPriceId INT IDENTITY(1,1) PRIMARY KEY,
        DeliveryPointId INT NOT NULL FOREIGN KEY REFERENCES DeliveryPoints(DeliveryPointId),
        DeliveryMonth DATE NOT NULL,
        PricePerMMBtu DECIMAL(18,4) NOT NULL,
        PriceDate DATE NOT NULL DEFAULT GETDATE()
    );
END
GO

-- Insert reference data
INSERT INTO Counterparties (Name, ShortCode, CreditLimit) VALUES
('Shell Energy', 'SHELL', 5000000.00),
('BP Gas Marketing', 'BP', 3000000.00),
('Chevron Natural Gas', 'CVX', 4000000.00);

INSERT INTO DeliveryPoints (Name, PipelineZone) VALUES
('Henry Hub', 'Gulf Coast'),
('Waha Hub', 'Permian Basin'),
('Algonquin Citygate', 'Northeast');

-- Insert market prices (forward curve)
INSERT INTO MarketPrices (DeliveryPointId, DeliveryMonth, PricePerMMBtu, PriceDate) VALUES
(1, '2026-07-01', 2.8500, GETDATE()),
(1, '2026-08-01', 2.9200, GETDATE()),
(1, '2026-09-01', 3.0100, GETDATE()),
(2, '2026-07-01', 2.6000, GETDATE()),
(2, '2026-08-01', 2.7100, GETDATE()),
(3, '2026-07-01', 3.4500, GETDATE()),
(3, '2026-08-01', 3.5800, GETDATE());

-- Insert sample trades
INSERT INTO Trades (TradeDate, TradeType, Direction, CounterpartyId, DeliveryPointId, DeliveryMonth, VolumePerDay, PricePerMMBtu) VALUES
('2026-06-01', 'Physical', 'Buy', 1, 1, '2026-07-01', 10000, 2.7500),
('2026-06-05', 'Physical', 'Sell', 2, 1, '2026-07-01', 5000, 2.8000),
('2026-06-08', 'Physical', 'Buy', 3, 2, '2026-08-01', 8000, 2.6500),
('2026-06-10', 'Financial', 'Sell', 1, 1, '2026-07-01', 5000, 2.8200),
('2026-06-12', 'Financial', 'Buy', 2, 3, '2026-07-01', 3000, 3.4000),
('2026-06-15', 'Financial', 'Sell', 3, 2, '2026-08-01', 4000, 2.7000);
GO

Your database is live with a realistic gas trading book. Next up, you will write the C# code to capture new trades and query this data programmatically.

Build Trade Capture

Your database is ready with counterparties, delivery points, and a market price curve. Now it's time to write the C# code that actually talks to that data.

In this step, you'll build the data access layer and a console menu that lets you capture new gas trades. This is the "deal capture" phase of the trade lifecycle, the first thing a trader does when they agree to buy or sell gas.

In this step, get ready to:
  • Write a DatabaseHelper class that manages the SQL Server connection and provides methods to insert and query trade data.
  • Build a console menu in Program.cs with a trade entry workflow.
  • Verify the sample trades already in your database and understand the trading book.
Create the DatabaseHelper class

Every ETRM system needs a layer between the application logic and the database. Your DatabaseHelper class will handle opening connections, inserting new trades, and retrieving data through stored procedures.

  • In Visual Studio, right-click your GasTradeETRM project in the Solution Explorer panel.
  • Click Add, then Class....
  • Name the file DatabaseHelper.cs and click Add.
  • Delete any template code in the new file and paste the following code that sets up the class with its constructor and connection test:
using Microsoft.Data.SqlClient;
using System.Data;

namespace GasTradeETRM;

public class DatabaseHelper
{
    private readonly string _connectionString;

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

    public bool TestConnection()
    {
        try
        {
            using var connection = new SqlConnection(_connectionString);
            connection.Open();
            return true;
        }
        catch (SqlException ex)
        {
            Console.WriteLine($"Connection failed: {ex.Message}");
            return false;
        }
    }

    public void InsertTrade(string tradeType, string direction, int counterpartyId,
        int deliveryPointId, DateTime deliveryMonth, decimal volumePerDay, decimal pricePerMMBtu)
    {
        const string sql = @"
            INSERT INTO Trades (TradeDate, TradeType, Direction, CounterpartyId,
                DeliveryPointId, DeliveryMonth, VolumePerDay, PricePerMMBtu)
            VALUES (GETDATE(), @TradeType, @Direction, @CounterpartyId,
                @DeliveryPointId, @DeliveryMonth, @VolumePerDay, @PricePerMMBtu)";

        using var connection = new SqlConnection(_connectionString);
        using var command = new SqlCommand(sql, connection);
        command.Parameters.AddWithValue("@TradeType", tradeType);
        command.Parameters.AddWithValue("@Direction", direction);
        command.Parameters.AddWithValue("@CounterpartyId", counterpartyId);
        command.Parameters.AddWithValue("@DeliveryPointId", deliveryPointId);
        command.Parameters.AddWithValue("@DeliveryMonth", deliveryMonth);
        command.Parameters.AddWithValue("@VolumePerDay", volumePerDay);
        command.Parameters.AddWithValue("@PricePerMMBtu", pricePerMMBtu);

        connection.Open();
        command.ExecuteNonQuery();
    }
}

What does this code do?

The DatabaseHelper class wraps all database interactions in one place. The constructor stores a connection string, and each method opens its own connection, does its work, then disposes it automatically with the using keyword.

  • TestConnection() tries to open a connection and returns true or false. This gives you an immediate health check when the app starts.
  • InsertTrade() uses parameterized SQL to safely insert a new trade row. Parameters prevent SQL injection and handle type conversion automatically.
  • Now add the remaining query methods below the InsertTrade method's closing brace, but still inside the class:
    public DataTable GetPositions()
    {
        return ExecuteStoredProcedure("sp_GetPositions");
    }

    public DataTable GetMTM()
    {
        return ExecuteStoredProcedure("sp_CalculateMTM");
    }

    public DataTable GetCreditExposure()
    {
        return ExecuteStoredProcedure("sp_GetCreditExposure");
    }

    public DataTable GetCounterparties()
    {
        const string sql = "SELECT CounterpartyId, Name, ShortCode FROM Counterparties";
        return ExecuteQuery(sql);
    }

    public DataTable GetDeliveryPoints()
    {
        const string sql = "SELECT DeliveryPointId, Name, PipelineZone FROM DeliveryPoints";
        return ExecuteQuery(sql);
    }

    private DataTable ExecuteStoredProcedure(string procedureName)
    {
        using var connection = new SqlConnection(_connectionString);
        using var command = new SqlCommand(procedureName, connection);
        command.CommandType = CommandType.StoredProcedure;

        var dataTable = new DataTable();
        connection.Open();
        using var reader = command.ExecuteReader();
        dataTable.Load(reader);
        return dataTable;
    }

    private DataTable ExecuteQuery(string sql)
    {
        using var connection = new SqlConnection(_connectionString);
        using var command = new SqlCommand(sql, connection);

        var dataTable = new DataTable();
        connection.Open();
        using var reader = command.ExecuteReader();
        dataTable.Load(reader);
        return dataTable;
    }

What do these methods do?

The public methods are thin wrappers that call one of two private helpers. This keeps the class clean and consistent.

  • GetPositions(), GetMTM(), and GetCreditExposure() each call a stored procedure by name. You'll create those stored procedures in the next step.
  • GetCounterparties() and GetDeliveryPoints() run simple SELECT queries to populate dropdown-style menus during trade entry.
  • ExecuteStoredProcedure() and ExecuteQuery() are private helpers that handle the repetitive open-read-load pattern. Both return a DataTable for easy row iteration.
  • Save DatabaseHelper.cs.

Seeing red squiggles under SqlConnection?

Make sure the using Microsoft.Data.SqlClient; line is at the top of your file. If the squiggles persist, confirm that the Microsoft.Data.SqlClient NuGet package is installed by checking Dependencies > Packages in Solution Explorer.

help me fix the SqlConnection error in DatabaseHelper.cs

✔️ Awesome, I've got everything!

Great. Double check you've saved DatabaseHelper.cs before moving on.

ⓧ I'd like to double check the full code

using Microsoft.Data.SqlClient;
using System.Data;

namespace GasTradeETRM;

public class DatabaseHelper
{
    private readonly string _connectionString;

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

    public bool TestConnection()
    {
        try
        {
            using var connection = new SqlConnection(_connectionString);
            connection.Open();
            return true;
        }
        catch (SqlException ex)
        {
            Console.WriteLine($"Connection failed: {ex.Message}");
            return false;
        }
    }

    public void InsertTrade(string tradeType, string direction, int counterpartyId,
        int deliveryPointId, DateTime deliveryMonth, decimal volumePerDay, decimal pricePerMMBtu)
    {
        const string sql = @"
            INSERT INTO Trades (TradeDate, TradeType, Direction, CounterpartyId,
                DeliveryPointId, DeliveryMonth, VolumePerDay, PricePerMMBtu)
            VALUES (GETDATE(), @TradeType, @Direction, @CounterpartyId,
                @DeliveryPointId, @DeliveryMonth, @VolumePerDay, @PricePerMMBtu)";

        using var connection = new SqlConnection(_connectionString);
        using var command = new SqlCommand(sql, connection);
        command.Parameters.AddWithValue("@TradeType", tradeType);
        command.Parameters.AddWithValue("@Direction", direction);
        command.Parameters.AddWithValue("@CounterpartyId", counterpartyId);
        command.Parameters.AddWithValue("@DeliveryPointId", deliveryPointId);
        command.Parameters.AddWithValue("@DeliveryMonth", deliveryMonth);
        command.Parameters.AddWithValue("@VolumePerDay", volumePerDay);
        command.Parameters.AddWithValue("@PricePerMMBtu", pricePerMMBtu);

        connection.Open();
        command.ExecuteNonQuery();
    }

    public DataTable GetPositions()
    {
        return ExecuteStoredProcedure("sp_GetPositions");
    }

    public DataTable GetMTM()
    {
        return ExecuteStoredProcedure("sp_CalculateMTM");
    }

    public DataTable GetCreditExposure()
    {
        return ExecuteStoredProcedure("sp_GetCreditExposure");
    }

    public DataTable GetCounterparties()
    {
        const string sql = "SELECT CounterpartyId, Name, ShortCode FROM Counterparties";
        return ExecuteQuery(sql);
    }

    public DataTable GetDeliveryPoints()
    {
        const string sql = "SELECT DeliveryPointId, Name, PipelineZone FROM DeliveryPoints";
        return ExecuteQuery(sql);
    }

    private DataTable ExecuteStoredProcedure(string procedureName)
    {
        using var connection = new SqlConnection(_connectionString);
        using var command = new SqlCommand(procedureName, connection);
        command.CommandType = CommandType.StoredProcedure;

        var dataTable = new DataTable();
        connection.Open();
        using var reader = command.ExecuteReader();
        dataTable.Load(reader);
        return dataTable;
    }

    private DataTable ExecuteQuery(string sql)
    {
        using var connection = new SqlConnection(_connectionString);
        using var command = new SqlCommand(sql, connection);

        var dataTable = new DataTable();
        connection.Open();
        using var reader = command.ExecuteReader();
        dataTable.Load(reader);
        return dataTable;
    }
}
Build the trade entry workflow in Program.cs

The main program needs to connect to the database, present a menu, and let the trader enter new deals. This mirrors how an Allegro user captures a trade: selecting trade type, direction, counterparty, delivery point, volume, and price.

  • Open Program.cs in the Solution Explorer (it already exists from the project template).
  • Delete all the existing template code and replace it with the following connection setup and menu loop:
using System.Data;
using GasTradeETRM;

const string connectionString = "Server=(local);Database=GasTradeDB;Integrated Security=true;TrustServerCertificate=true;";

var db = new DatabaseHelper(connectionString);

if (!db.TestConnection())
{
    Console.WriteLine("Cannot connect to database. Please check your SQL Server is running.");
    Console.WriteLine("Press any key to exit.");
    Console.ReadKey();
    return;
}

Console.WriteLine("=== Natural Gas Trade Lifecycle System ===");
Console.WriteLine("Connected to GasTradeDB successfully.\n");

bool running = true;
while (running)
{
    Console.WriteLine("\n--- Main Menu ---");
    Console.WriteLine("1. View Positions");
    Console.WriteLine("2. View Mark-to-Market P&L");
    Console.WriteLine("3. Enter New Trade");
    Console.WriteLine("4. View Credit Exposure");
    Console.WriteLine("5. Exit");
    Console.Write("\nSelect option: ");

    var choice = Console.ReadLine();
    Console.WriteLine();

    switch (choice)
    {
        case "1":
            DisplayPositions(db);
            break;
        case "2":
            DisplayMTM(db);
            break;
        case "3":
            EnterNewTrade(db);
            break;
        case "4":
            DisplayCreditExposure(db);
            break;
        case "5":
            running = false;
            break;
        default:
            Console.WriteLine("Invalid option.");
            break;
    }
}

Console.WriteLine("Goodbye.");

What does this code do?

This is the top-level program flow using C# top-level statements (no Main method needed in .NET 8).

  • The connection string uses Windows Authentication (Integrated Security=true) so you don't need a username or password. TrustServerCertificate=true is required for local development with Microsoft.Data.SqlClient 7.0.0.
  • The TestConnection() call at startup gives an immediate pass/fail signal before entering the menu.
  • The menu references DisplayPositions, DisplayMTM, and DisplayCreditExposure methods. You'll define those in the next step. For now, the menu structure is in place.
  • Now add the EnterNewTrade method below the Console.WriteLine("Goodbye."); line at the bottom of Program.cs:
static void EnterNewTrade(DatabaseHelper db)
{
    Console.WriteLine("=== Enter New Trade ===\n");

    Console.WriteLine("Trade Type: 1=Physical, 2=Financial");
    Console.Write("Select: ");
    string tradeType = Console.ReadLine() == "1" ? "Physical" : "Financial";

    Console.WriteLine("Direction: 1=Buy, 2=Sell");
    Console.Write("Select: ");
    string direction = Console.ReadLine() == "1" ? "Buy" : "Sell";

    Console.WriteLine("\nCounterparties:");
    var counterparties = db.GetCounterparties();
    foreach (DataRow row in counterparties.Rows)
    {
        Console.WriteLine($"  {row[\"CounterpartyId\"]}. {row[\"Name\"]}");
    }
    Console.Write("Select counterparty ID: ");
    int counterpartyId = int.Parse(Console.ReadLine() ?? "1");

    Console.WriteLine("\nDelivery Points:");
    var deliveryPoints = db.GetDeliveryPoints();
    foreach (DataRow row in deliveryPoints.Rows)
    {
        Console.WriteLine($"  {row[\"DeliveryPointId\"]}. {row[\"Name\"]} ({row[\"PipelineZone\"]})";
    }
    Console.Write("Select delivery point ID: ");
    int deliveryPointId = int.Parse(Console.ReadLine() ?? "1");

    Console.Write("\nDelivery month (yyyy-MM, e.g. 2026-07): ");
    var monthInput = Console.ReadLine() ?? "2026-07";
    DateTime deliveryMonth = DateTime.Parse(monthInput + "-01");

    Console.Write("Volume (MMBtu/day): ");
    decimal volume = decimal.Parse(Console.ReadLine() ?? "0");

    Console.Write("Price ($/MMBtu): ");
    decimal price = decimal.Parse(Console.ReadLine() ?? "0");

    db.InsertTrade(tradeType, direction, counterpartyId, deliveryPointId, deliveryMonth, volume, price);
    Console.WriteLine($"\nTrade entered: {direction} {volume:N0} MMBtu/d {tradeType} at ${price:F4}/MMBtu");
}

What does EnterNewTrade do?

This method walks the trader through each field of a deal ticket, the same information an Allegro user fills in when capturing a trade.

  • Trade type distinguishes physical delivery from financial (cash-settled) trades.
  • Direction is Buy or Sell. In the next step, Buy will become a positive quantity and Sell becomes negative when calculating positions.
  • The method queries the database for counterparties and delivery points so the user picks from the actual reference data rather than memorizing IDs.
  • Save Program.cs.

Why does the project not compile yet?

The menu references DisplayPositions, DisplayMTM, and DisplayCreditExposure methods that don't exist yet. You'll create these in Step 4 after building the stored procedures they depend on. The compiler errors are expected and will disappear once those methods are added.

✔️ Awesome, I've got everything!

Great. Double check you've saved Program.cs before moving on.

ⓧ I'd like to double check the full code

using System.Data;
using GasTradeETRM;

const string connectionString = "Server=(local);Database=GasTradeDB;Integrated Security=true;TrustServerCertificate=true;";

var db = new DatabaseHelper(connectionString);

if (!db.TestConnection())
{
    Console.WriteLine("Cannot connect to database. Please check your SQL Server is running.");
    Console.WriteLine("Press any key to exit.");
    Console.ReadKey();
    return;
}

Console.WriteLine("=== Natural Gas Trade Lifecycle System ===");
Console.WriteLine("Connected to GasTradeDB successfully.\n");

bool running = true;
while (running)
{
    Console.WriteLine("\n--- Main Menu ---");
    Console.WriteLine("1. View Positions");
    Console.WriteLine("2. View Mark-to-Market P&L");
    Console.WriteLine("3. Enter New Trade");
    Console.WriteLine("4. View Credit Exposure");
    Console.WriteLine("5. Exit");
    Console.Write("\nSelect option: ");

    var choice = Console.ReadLine();
    Console.WriteLine();

    switch (choice)
    {
        case "1":
            DisplayPositions(db);
            break;
        case "2":
            DisplayMTM(db);
            break;
        case "3":
            EnterNewTrade(db);
            break;
        case "4":
            DisplayCreditExposure(db);
            break;
        case "5":
            running = false;
            break;
        default:
            Console.WriteLine("Invalid option.");
            break;
    }
}

Console.WriteLine("Goodbye.");

static void EnterNewTrade(DatabaseHelper db)
{
    Console.WriteLine("=== Enter New Trade ===\n");

    Console.WriteLine("Trade Type: 1=Physical, 2=Financial");
    Console.Write("Select: ");
    string tradeType = Console.ReadLine() == "1" ? "Physical" : "Financial";

    Console.WriteLine("Direction: 1=Buy, 2=Sell");
    Console.Write("Select: ");
    string direction = Console.ReadLine() == "1" ? "Buy" : "Sell";

    Console.WriteLine("\nCounterparties:");
    var counterparties = db.GetCounterparties();
    foreach (DataRow row in counterparties.Rows)
    {
        Console.WriteLine($"  {row[\"CounterpartyId\"]}. {row[\"Name\"]}");
    }
    Console.Write("Select counterparty ID: ");
    int counterpartyId = int.Parse(Console.ReadLine() ?? "1");

    Console.WriteLine("\nDelivery Points:");
    var deliveryPoints = db.GetDeliveryPoints();
    foreach (DataRow row in deliveryPoints.Rows)
    {
        Console.WriteLine($"  {row[\"DeliveryPointId\"]}. {row[\"Name\"]} ({row[\"PipelineZone\"]})";
    }
    Console.Write("Select delivery point ID: ");
    int deliveryPointId = int.Parse(Console.ReadLine() ?? "1");

    Console.Write("\nDelivery month (yyyy-MM, e.g. 2026-07): ");
    var monthInput = Console.ReadLine() ?? "2026-07";
    DateTime deliveryMonth = DateTime.Parse(monthInput + "-01");

    Console.Write("Volume (MMBtu/day): ");
    decimal volume = decimal.Parse(Console.ReadLine() ?? "0");

    Console.Write("Price ($/MMBtu): ");
    decimal price = decimal.Parse(Console.ReadLine() ?? "0");

    db.InsertTrade(tradeType, direction, counterpartyId, deliveryPointId, deliveryMonth, volume, price);
    Console.WriteLine($"\nTrade entered: {direction} {volume:N0} MMBtu/d {tradeType} at ${price:F4}/MMBtu");
}
Verify the sample trades in your trading book

Your database creation script from Step 2 already inserted 6 sample trades. These represent a realistic gas trading book with both long (Buy) and short (Sell) positions across different delivery points and trade types.

  • Return to the SQL Server Object Explorer in Visual Studio (or SSMS).
  • Right-click the Trades table under GasTradeDB and click View Data (or run the query below in a new query window):
SELECT t.TradeId, t.TradeType, t.Direction, c.ShortCode AS Counterparty,
       dp.Name AS DeliveryPoint, FORMAT(t.DeliveryMonth, 'MMM yyyy') AS Period,
       t.VolumePerDay, t.PricePerMMBtu
FROM Trades t
INNER JOIN Counterparties c ON t.CounterpartyId = c.CounterpartyId
INNER JOIN DeliveryPoints dp ON t.DeliveryPointId = dp.DeliveryPointId
ORDER BY t.TradeId;

What does this trading book tell you?

The 6 trades form a realistic gas book with both physical supply contracts and financial hedges.

  • 3 physical trades represent actual gas flowing through pipelines. Buying 10,000 MMBtu/day at Henry Hub, selling 5,000 back, and buying 8,000 at Waha Hub.
  • 3 financial trades are cash-settled hedges. They offset price risk without moving physical gas. Selling 5,000 financially at Henry Hub hedges part of the physical long position there.
  • The mix of Buy and Sell across delivery points creates both long and short positions. In the next step, the signed quantity convention (Buy = positive, Sell = negative) will let you aggregate these into net positions.
  • Confirm you see 6 rows in the query results. You should see trades spanning Henry Hub, Waha Hub, and Algonquin Citygate with delivery months in July and August 2026.

Don't see 6 trades?

If the Trades table is empty, your CreateDatabase.sql script from Step 2 may not have run completely. Re-run the full script and check for errors in the Messages tab. The INSERT statements at the bottom depend on the Counterparties and DeliveryPoints tables having data first.

help me figure out why my Trades table has no data

Your data access layer is wired up and your trading book has realistic sample data. Next up, you'll create stored procedures that aggregate these trades into net positions and calculate mark-to-market P&L.

Calculate Positions and Mark-to-Market

You have trades in the database, but a pile of individual trades does not tell you whether you are long or short at any given delivery point. Traders need to see their net position to understand exposure.

In this step, you will create stored procedures that aggregate positions and calculate unrealized P&L. These are the two most common reporting tasks in any ETRM system.

In this step, get ready to:
  • Create a stored procedure that aggregates net positions by delivery point and period.
  • Create a stored procedure that computes unrealized P&L using mark-to-market.
  • Add display methods to the C# application that call these procedures and format results.
Create the position aggregation stored procedure

Position aggregation answers the question: across all my trades at a given delivery point and month, am I net long (buying more than selling) or net short? The key is the signed quantity convention: Buy volumes are positive, Sell volumes are negative.

  • In your GasTradeETRM project in Visual Studio, right-click the project in Solution Explorer and select Add, then New Folder.
  • Name the folder Database.
  • Right-click the Database folder and select Add, then New Item.
  • Choose a text file and name it StoredProcedures.sql.
  • Paste the following SQL into StoredProcedures.sql:
USE GasTradeDB;
GO

-- Position aggregation stored procedure
IF OBJECT_ID('sp_GetPositions', 'P') IS NOT NULL DROP PROCEDURE sp_GetPositions;
GO

CREATE PROCEDURE sp_GetPositions
AS
BEGIN
    SELECT
        dp.Name AS DeliveryPoint,
        FORMAT(t.DeliveryMonth, 'MMM yyyy') AS DeliveryPeriod,
        t.TradeType,
        SUM(CASE WHEN t.Direction = 'Buy' THEN t.VolumePerDay
                 WHEN t.Direction = 'Sell' THEN -t.VolumePerDay
            END) AS NetPosition
    FROM Trades t
    INNER JOIN DeliveryPoints dp ON t.DeliveryPointId = dp.DeliveryPointId
    GROUP BY dp.Name, t.DeliveryMonth, t.TradeType
    ORDER BY dp.Name, t.DeliveryMonth, t.TradeType;
END
GO

What does this code do?

This stored procedure implements the signed quantity convention that every ETRM system uses.

  • The CASE statement flips Sell volumes to negative. Buy = +volume, Sell = -volume.
  • SUM groups these signed volumes by delivery point, month, and trade type.
  • A positive NetPosition means you are net long (buying more than selling). A negative value means net short.

Why signed quantities?

Instead of tracking Buy and Sell separately and computing the difference later, the signed convention lets you simply sum one column to get net exposure. This is the universal standard in energy trading systems including Allegro.

You will not execute this file yet. The next substep adds more procedures to the same file.

Create the mark-to-market stored procedure

Mark-to-market answers the question: if I closed all my positions at today's market price, how much money would I make or lose? The formula is straightforward.

Unrealized P&L = (Market Price - Trade Price) x Signed Quantity.

  • In StoredProcedures.sql, add the following code below the last GO statement:
-- Mark-to-Market stored procedure
IF OBJECT_ID('sp_CalculateMTM', 'P') IS NOT NULL DROP PROCEDURE sp_CalculateMTM;
GO

CREATE PROCEDURE sp_CalculateMTM
AS
BEGIN
    SELECT
        t.TradeId,
        dp.Name AS DeliveryPoint,
        FORMAT(t.DeliveryMonth, 'MMM yyyy') AS DeliveryPeriod,
        t.TradeType,
        t.Direction,
        t.VolumePerDay,
        t.PricePerMMBtu AS TradePrice,
        mp.PricePerMMBtu AS MarketPrice,
        CASE WHEN t.Direction = 'Buy' THEN t.VolumePerDay
             WHEN t.Direction = 'Sell' THEN -t.VolumePerDay
        END AS SignedQuantity,
        (mp.PricePerMMBtu - t.PricePerMMBtu) *
            CASE WHEN t.Direction = 'Buy' THEN t.VolumePerDay
                 WHEN t.Direction = 'Sell' THEN -t.VolumePerDay
            END AS UnrealizedPnL,
        c.Name AS Counterparty
    FROM Trades t
    INNER JOIN DeliveryPoints dp ON t.DeliveryPointId = dp.DeliveryPointId
    INNER JOIN Counterparties c ON t.CounterpartyId = c.CounterpartyId
    INNER JOIN MarketPrices mp ON t.DeliveryPointId = mp.DeliveryPointId
        AND t.DeliveryMonth = mp.DeliveryMonth
    ORDER BY dp.Name, t.DeliveryMonth, t.TradeType;
END
GO

What does this code do?

  • The procedure joins each trade against the MarketPrices table on delivery point and month to find the current market price.
  • It computes SignedQuantity (positive for Buy, negative for Sell) and multiplies by the price difference.
  • A positive UnrealizedPnL on a Buy trade means the market price rose above your trade price. You bought low.
  • A positive UnrealizedPnL on a Sell trade means the market price dropped below your trade price. You sold high.
  • Still in StoredProcedures.sql, add the credit exposure stored procedure below the last GO:
-- Credit Exposure stored procedure (Secret Mission)
IF OBJECT_ID('sp_GetCreditExposure', 'P') IS NOT NULL DROP PROCEDURE sp_GetCreditExposure;
GO

CREATE PROCEDURE sp_GetCreditExposure
AS
BEGIN
    SELECT
        c.Name AS Counterparty,
        c.CreditLimit,
        SUM(
            CASE WHEN
                (mp.PricePerMMBtu - t.PricePerMMBtu) *
                CASE WHEN t.Direction = 'Buy' THEN t.VolumePerDay
                     WHEN t.Direction = 'Sell' THEN -t.VolumePerDay
                END > 0
            THEN
                (mp.PricePerMMBtu - t.PricePerMMBtu) *
                CASE WHEN t.Direction = 'Buy' THEN t.VolumePerDay
                     WHEN t.Direction = 'Sell' THEN -t.VolumePerDay
                END
            ELSE 0
            END
        ) AS CreditExposure,
        CASE WHEN SUM(
            CASE WHEN
                (mp.PricePerMMBtu - t.PricePerMMBtu) *
                CASE WHEN t.Direction = 'Buy' THEN t.VolumePerDay
                     WHEN t.Direction = 'Sell' THEN -t.VolumePerDay
                END > 0
            THEN
                (mp.PricePerMMBtu - t.PricePerMMBtu) *
                CASE WHEN t.Direction = 'Buy' THEN t.VolumePerDay
                     WHEN t.Direction = 'Sell' THEN -t.VolumePerDay
                END
            ELSE 0
            END
        ) > c.CreditLimit THEN 'BREACH'
        ELSE 'OK'
        END AS Status
    FROM Trades t
    INNER JOIN DeliveryPoints dp ON t.DeliveryPointId = dp.DeliveryPointId
    INNER JOIN Counterparties c ON t.CounterpartyId = c.CounterpartyId
    INNER JOIN MarketPrices mp ON t.DeliveryPointId = mp.DeliveryPointId
        AND t.DeliveryMonth = mp.DeliveryMonth
    GROUP BY c.Name, c.CreditLimit
    ORDER BY c.Name;
END
GO

What does this code do?

This procedure calculates counterparty credit exposure. It sums only positive unrealized P&L per counterparty (positive P&L means the counterparty owes you money, which is your credit risk). It then compares the exposure against each counterparty's credit limit and flags any breaches.

  • Save StoredProcedures.sql.
  • Execute the full SQL script against your database. In Visual Studio, open SQL Server Object Explorer (from the View menu), right-click your GasTradeDB database, and select New Query.
  • Paste the full contents of StoredProcedures.sql into the query window and click the green Execute button (or press Ctrl+Shift+E).
  • You should see "Command(s) completed successfully" in the results pane.

Seeing an error when executing?

  • Make sure you are connected to your local SQL Server instance in SQL Server Object Explorer. If you do not see any server listed, click Add SQL Server and connect to (local).
  • Confirm that GasTradeDB already exists. You should have created it in Step 2. If it is missing, re-run your CreateDatabase.sql script first.
  • Check that each GO statement is on its own line with no extra characters.

Why won't my stored procedures execute?

Add position and MTM display methods to the application

The stored procedures live in the database. Now you need C# methods that call them and format the results for the console.

  • In Program.cs, find the closing brace of the EnterNewTrade method at the bottom of the file. Add the following DisplayPositions method below it:
static void DisplayPositions(DatabaseHelper db)
{
    Console.WriteLine("=== Position Report ===");
    Console.WriteLine($"{"Delivery Point",-20} {"Period",-12} {"Type",-12} {"Net Position",12}");
    Console.WriteLine(new string('-', 60));

    var positions = db.GetPositions();
    foreach (DataRow row in positions.Rows)
    {
        Console.WriteLine($"{row["DeliveryPoint"],-20} {row["DeliveryPeriod"],-12} {row["TradeType"],-12} {row["NetPosition"],12:N0} MMBtu/d");
    }
}

What does this code do?

This method calls db.GetPositions() which executes the sp_GetPositions stored procedure you just created. It then formats each row into a fixed-width console table showing delivery point, period, trade type, and the net signed position in MMBtu per day.

  • Add the DisplayMTM method below DisplayPositions:
static void DisplayMTM(DatabaseHelper db)
{
    Console.WriteLine("=== Mark-to-Market P&L Report ===");
    Console.WriteLine($"{"ID",4} {"Point",-16} {"Period",-10} {"Type",-10} {"Dir",-5} {"Vol",8} {"Trade$",8} {"Mkt$",8} {"Unreal P&L",12}");
    Console.WriteLine(new string('-', 90));

    var mtm = db.GetMTM();
    decimal totalPnL = 0;
    foreach (DataRow row in mtm.Rows)
    {
        decimal pnl = Convert.ToDecimal(row["UnrealizedPnL"]);
        totalPnL += pnl;
        Console.WriteLine($"{row["TradeId"],4} {row["DeliveryPoint"],-16} {row["DeliveryPeriod"],-10} {row["TradeType"],-10} {row["Direction"],-5} {row["VolumePerDay"],8:N0} {row["TradePrice"],8:F4} {row["MarketPrice"],8:F4} {pnl,12:C0}");
    }
    Console.WriteLine(new string('-', 90));
    Console.WriteLine($"{"TOTAL",-75} {totalPnL,12:C0}");
}

What does this code do?

  • It calls db.GetMTM() which executes sp_CalculateMTM and returns every trade with its unrealized P&L.
  • The method accumulates a running total in totalPnL and prints it at the bottom.
  • Each row shows trade price vs. market price side-by-side so you can see exactly where profits and losses come from.
  • Add the DisplayCreditExposure method below DisplayMTM:
static void DisplayCreditExposure(DatabaseHelper db)
{
    Console.WriteLine("=== Counterparty Credit Exposure ===");
    Console.WriteLine($"{"Counterparty",-25} {"Exposure",14} {"Limit",14} {"Status",8}");
    Console.WriteLine(new string('-', 65));

    var exposure = db.GetCreditExposure();
    foreach (DataRow row in exposure.Rows)
    {
        string status = row["Status"].ToString() ?? "OK";
        decimal exp = Convert.ToDecimal(row["CreditExposure"]);
        decimal limit = row["CreditLimit"] == DBNull.Value ? 0 : Convert.ToDecimal(row["CreditLimit"]);
        Console.WriteLine($"{row["Counterparty"],-25} {exp,14:C0} {limit,14:C0} {status,8}");
    }
}

What does this code do?

This method calls db.GetCreditExposure() which executes sp_GetCreditExposure. It shows how much credit risk you have with each counterparty compared to their limit. The menu already has option 4 wired to call this method.

  • Save Program.cs.
  • Build your project by pressing Ctrl+Shift+B. You should see "Build succeeded" in the Output window.

Build errors?

  • If you see an error about DisplayPositions or DisplayMTM not existing, make sure you added both methods below the existing EnterNewTrade method, not inside it.
  • Check that each method starts with static void and has matching opening and closing braces.
  • Ensure you have using System.Data; at the top of Program.cs (it should already be there from the previous step).

Help me fix my build errors in Program.cs

✔️ Awesome, I've got everything!

Great. Double check that you have saved both StoredProcedures.sql and Program.cs.

ⓧ I'd like to double check the full code

Here is the full Database/StoredProcedures.sql:

USE GasTradeDB;
GO

-- Position aggregation stored procedure
IF OBJECT_ID('sp_GetPositions', 'P') IS NOT NULL DROP PROCEDURE sp_GetPositions;
GO

CREATE PROCEDURE sp_GetPositions
AS
BEGIN
    SELECT
        dp.Name AS DeliveryPoint,
        FORMAT(t.DeliveryMonth, 'MMM yyyy') AS DeliveryPeriod,
        t.TradeType,
        SUM(CASE WHEN t.Direction = 'Buy' THEN t.VolumePerDay
                 WHEN t.Direction = 'Sell' THEN -t.VolumePerDay
            END) AS NetPosition
    FROM Trades t
    INNER JOIN DeliveryPoints dp ON t.DeliveryPointId = dp.DeliveryPointId
    GROUP BY dp.Name, t.DeliveryMonth, t.TradeType
    ORDER BY dp.Name, t.DeliveryMonth, t.TradeType;
END
GO

-- Mark-to-Market stored procedure
IF OBJECT_ID('sp_CalculateMTM', 'P') IS NOT NULL DROP PROCEDURE sp_CalculateMTM;
GO

CREATE PROCEDURE sp_CalculateMTM
AS
BEGIN
    SELECT
        t.TradeId,
        dp.Name AS DeliveryPoint,
        FORMAT(t.DeliveryMonth, 'MMM yyyy') AS DeliveryPeriod,
        t.TradeType,
        t.Direction,
        t.VolumePerDay,
        t.PricePerMMBtu AS TradePrice,
        mp.PricePerMMBtu AS MarketPrice,
        CASE WHEN t.Direction = 'Buy' THEN t.VolumePerDay
             WHEN t.Direction = 'Sell' THEN -t.VolumePerDay
        END AS SignedQuantity,
        (mp.PricePerMMBtu - t.PricePerMMBtu) *
            CASE WHEN t.Direction = 'Buy' THEN t.VolumePerDay
                 WHEN t.Direction = 'Sell' THEN -t.VolumePerDay
            END AS UnrealizedPnL,
        c.Name AS Counterparty
    FROM Trades t
    INNER JOIN DeliveryPoints dp ON t.DeliveryPointId = dp.DeliveryPointId
    INNER JOIN Counterparties c ON t.CounterpartyId = c.CounterpartyId
    INNER JOIN MarketPrices mp ON t.DeliveryPointId = mp.DeliveryPointId
        AND t.DeliveryMonth = mp.DeliveryMonth
    ORDER BY dp.Name, t.DeliveryMonth, t.TradeType;
END
GO

-- Credit Exposure stored procedure (Secret Mission)
IF OBJECT_ID('sp_GetCreditExposure', 'P') IS NOT NULL DROP PROCEDURE sp_GetCreditExposure;
GO

CREATE PROCEDURE sp_GetCreditExposure
AS
BEGIN
    SELECT
        c.Name AS Counterparty,
        c.CreditLimit,
        SUM(
            CASE WHEN
                (mp.PricePerMMBtu - t.PricePerMMBtu) *
                CASE WHEN t.Direction = 'Buy' THEN t.VolumePerDay
                     WHEN t.Direction = 'Sell' THEN -t.VolumePerDay
                END > 0
            THEN
                (mp.PricePerMMBtu - t.PricePerMMBtu) *
                CASE WHEN t.Direction = 'Buy' THEN t.VolumePerDay
                     WHEN t.Direction = 'Sell' THEN -t.VolumePerDay
                END
            ELSE 0
            END
        ) AS CreditExposure,
        CASE WHEN SUM(
            CASE WHEN
                (mp.PricePerMMBtu - t.PricePerMMBtu) *
                CASE WHEN t.Direction = 'Buy' THEN t.VolumePerDay
                     WHEN t.Direction = 'Sell' THEN -t.VolumePerDay
                END > 0
            THEN
                (mp.PricePerMMBtu - t.PricePerMMBtu) *
                CASE WHEN t.Direction = 'Buy' THEN t.VolumePerDay
                     WHEN t.Direction = 'Sell' THEN -t.VolumePerDay
                END
            ELSE 0
            END
        ) > c.CreditLimit THEN 'BREACH'
        ELSE 'OK'
        END AS Status
    FROM Trades t
    INNER JOIN DeliveryPoints dp ON t.DeliveryPointId = dp.DeliveryPointId
    INNER JOIN Counterparties c ON t.CounterpartyId = c.CounterpartyId
    INNER JOIN MarketPrices mp ON t.DeliveryPointId = mp.DeliveryPointId
        AND t.DeliveryMonth = mp.DeliveryMonth
    GROUP BY c.Name, c.CreditLimit
    ORDER BY c.Name;
END
GO

Here is the full Program.cs:

using System.Data;
using GasTradeETRM;

const string connectionString = "Server=(local);Database=GasTradeDB;Integrated Security=true;TrustServerCertificate=true;";

var db = new DatabaseHelper(connectionString);

if (!db.TestConnection())
{
    Console.WriteLine("Cannot connect to database. Please check your SQL Server is running.");
    Console.WriteLine("Press any key to exit.");
    Console.ReadKey();
    return;
}

Console.WriteLine("=== Natural Gas Trade Lifecycle System ===");
Console.WriteLine("Connected to GasTradeDB successfully.\n");

bool running = true;
while (running)
{
    Console.WriteLine("\n--- Main Menu ---");
    Console.WriteLine("1. View Positions");
    Console.WriteLine("2. View Mark-to-Market P&L");
    Console.WriteLine("3. Enter New Trade");
    Console.WriteLine("4. View Credit Exposure");
    Console.WriteLine("5. Exit");
    Console.Write("\nSelect option: ");

    var choice = Console.ReadLine();
    Console.WriteLine();

    switch (choice)
    {
        case "1":
            DisplayPositions(db);
            break;
        case "2":
            DisplayMTM(db);
            break;
        case "3":
            EnterNewTrade(db);
            break;
        case "4":
            DisplayCreditExposure(db);
            break;
        case "5":
            running = false;
            break;
        default:
            Console.WriteLine("Invalid option.");
            break;
    }
}

Console.WriteLine("Goodbye.");

static void DisplayPositions(DatabaseHelper db)
{
    Console.WriteLine("=== Position Report ===");
    Console.WriteLine($"{"Delivery Point",-20} {"Period",-12} {"Type",-12} {"Net Position",12}");
    Console.WriteLine(new string('-', 60));

    var positions = db.GetPositions();
    foreach (DataRow row in positions.Rows)
    {
        Console.WriteLine($"{row["DeliveryPoint"],-20} {row["DeliveryPeriod"],-12} {row["TradeType"],-12} {row["NetPosition"],12:N0} MMBtu/d");
    }
}

static void DisplayMTM(DatabaseHelper db)
{
    Console.WriteLine("=== Mark-to-Market P&L Report ===");
    Console.WriteLine($"{"ID",4} {"Point",-16} {"Period",-10} {"Type",-10} {"Dir",-5} {"Vol",8} {"Trade$",8} {"Mkt$",8} {"Unreal P&L",12}");
    Console.WriteLine(new string('-', 90));

    var mtm = db.GetMTM();
    decimal totalPnL = 0;
    foreach (DataRow row in mtm.Rows)
    {
        decimal pnl = Convert.ToDecimal(row["UnrealizedPnL"]);
        totalPnL += pnl;
        Console.WriteLine($"{row["TradeId"],4} {row["DeliveryPoint"],-16} {row["DeliveryPeriod"],-10} {row["TradeType"],-10} {row["Direction"],-5} {row["VolumePerDay"],8:N0} {row["TradePrice"],8:F4} {row["MarketPrice"],8:F4} {pnl,12:C0}");
    }
    Console.WriteLine(new string('-', 90));
    Console.WriteLine($"{"TOTAL",-75} {totalPnL,12:C0}");
}

static void EnterNewTrade(DatabaseHelper db)
{
    Console.WriteLine("=== Enter New Trade ===\n");

    Console.WriteLine("Trade Type: 1=Physical, 2=Financial");
    Console.Write("Select: ");
    string tradeType = Console.ReadLine() == "1" ? "Physical" : "Financial";

    Console.WriteLine("Direction: 1=Buy, 2=Sell");
    Console.Write("Select: ");
    string direction = Console.ReadLine() == "1" ? "Buy" : "Sell";

    Console.WriteLine("\nCounterparties:");
    var counterparties = db.GetCounterparties();
    foreach (DataRow row in counterparties.Rows)
    {
        Console.WriteLine($"  {row["CounterpartyId"]}. {row["Name"]}");
    }
    Console.Write("Select counterparty ID: ");
    int counterpartyId = int.Parse(Console.ReadLine() ?? "1");

    Console.WriteLine("\nDelivery Points:");
    var deliveryPoints = db.GetDeliveryPoints();
    foreach (DataRow row in deliveryPoints.Rows)
    {
        Console.WriteLine($"  {row["DeliveryPointId"]}. {row["Name"]} ({row["PipelineZone"]})");
    }
    Console.Write("Select delivery point ID: ");
    int deliveryPointId = int.Parse(Console.ReadLine() ?? "1");

    Console.Write("\nDelivery month (yyyy-MM, e.g. 2026-07): ");
    var monthInput = Console.ReadLine() ?? "2026-07";
    DateTime deliveryMonth = DateTime.Parse(monthInput + "-01");

    Console.Write("Volume (MMBtu/day): ");
    decimal volume = decimal.Parse(Console.ReadLine() ?? "0");

    Console.Write("Price ($/MMBtu): ");
    decimal price = decimal.Parse(Console.ReadLine() ?? "0");

    db.InsertTrade(tradeType, direction, counterpartyId, deliveryPointId, deliveryMonth, volume, price);
    Console.WriteLine($"\nTrade entered: {direction} {volume:N0} MMBtu/d {tradeType} at ${price:F4}/MMBtu");
}

static void DisplayCreditExposure(DatabaseHelper db)
{
    Console.WriteLine("=== Counterparty Credit Exposure ===");
    Console.WriteLine($"{"Counterparty",-25} {"Exposure",14} {"Limit",14} {"Status",8}");
    Console.WriteLine(new string('-', 65));

    var exposure = db.GetCreditExposure();
    foreach (DataRow row in exposure.Rows)
    {
        string status = row["Status"].ToString() ?? "OK";
        decimal exp = Convert.ToDecimal(row["CreditExposure"]);
        decimal limit = row["CreditLimit"] == DBNull.Value ? 0 : Convert.ToDecimal(row["CreditLimit"]);
        Console.WriteLine($"{row["Counterparty"],-25} {exp,14:C0} {limit,14:C0} {status,8}");
    }
}

Your stored procedures and display methods are ready. Next up, you will run the full application and see how positions, P&L, and hedging all connect in a live trade lifecycle.

Run the Full Trade Lifecycle

In the previous step, you built stored procedures for position aggregation and mark-to-market calculation, then connected them to your C# application. Your entire system is now complete.

Now it is time to run the full trade lifecycle end-to-end. You will view your trading book, enter a hedge to manage risk, and simulate a market move to watch P&L update in real time. This is exactly what Allegro consultants demonstrate during system validation.

In this step, get ready to:
  • Run the application and view position and MTM P&L reports.
  • Enter a financial hedge trade and observe how it changes your net position.
  • Update market prices and confirm the system revalues P&L correctly.
Run the application and view your trading book

The position report shows your net exposure at each delivery point. The MTM report compares every trade price against the current market to calculate unrealized profit or loss.

  • In Visual Studio, press F5 or click the green Start button in the toolbar to run your application.
  • A console window opens displaying the main menu.

You should see the banner === Natural Gas Trade Lifecycle System === followed by a successful connection message and the five menu options.

  • Type 1 and press Enter to view the Position Report.

What should I see in the Position Report?

The report groups your six sample trades by delivery point, period, and trade type. It uses the signed quantity convention: Buy volumes are positive, Sell volumes are negative.

For example, at Henry Hub for Jul 2026, your Physical net position is +5,000 MMBtu/d (10,000 bought minus 5,000 sold). Your Financial net position there is -5,000 MMBtu/d (a sell). This tells you that you are long physical gas but have a financial short that partially offsets it.

  • Type 2 and press Enter to view the Mark-to-Market P&L Report.

The MTM report shows every trade alongside its market price, signed quantity, and unrealized P&L. A total at the bottom sums your entire book.

How is Unrealized P&L calculated?

The formula is: Unrealized P&L = (Market Price - Trade Price) x Signed Quantity.

If you bought gas at $2.75 and the market is now $2.85, you are up $0.10 per MMBtu on that volume. If you sold gas at $2.80 and the market moved to $2.85, you lost $0.05 per MMBtu because you locked in a lower selling price than today's market.

Application not starting?

Make sure SQL Server is running. Check that your connection string in Program.cs uses Server=(local) and that you executed both SQL scripts from the previous steps.

If you see a message about missing tables or procedures, re-run CreateDatabase.sql and StoredProcedures.sql in SQL Server Object Explorer.

Help me debug my SQL Server connection

Enter a hedge trade to offset your position

In energy trading, a financial hedge offsets physical exposure. Your current book has a -5,000 MMBtu/d financial short at Henry Hub for Jul 2026. Entering a financial buy at the same delivery point and period will flatten that position to zero.

  • Type 3 and press Enter to open the trade entry workflow.
  • When prompted for Trade Type, type 2 for Financial.
  • When prompted for Direction, type 1 for Buy.
  • When the counterparty list appears, type 1 to select Shell Energy.
  • When the delivery points list appears, type 1 to select Henry Hub.
  • For delivery month, type 2026-07.
  • For volume, type 5000.
  • For price, type 2.8400.

You should see a confirmation: Trade entered: Buy 5,000 MMBtu/d Financial at $2.8400/MMBtu.

  • Type 1 to view the Position Report again.

Look at the Henry Hub, Jul 2026, Financial row. The net position has changed from -5,000 to 0 MMBtu/d. Your new buy completely offset the existing financial short.

Why does hedging matter?

Physical gas traders are exposed to price movements between the time they commit to a deal and the time they deliver. A financial hedge locks in a price, reducing that uncertainty.

In this example, your net financial position at Henry Hub Jul is now zero. The physical position (+5,000) remains, but the financial offset means your total book risk at that point is reduced. This is the core of what Allegro's risk module tracks.

Update market prices and observe P&L revaluation

Market prices move constantly. Every time prices change, unrealized P&L must be recalculated. This daily revaluation process is exactly what ETRM systems automate.

  • Type 5 to exit the application.
  • Open SQL Server Object Explorer in Visual Studio (or switch to SSMS if you prefer).
  • Right-click your GasTradeDB database and select New Query.
  • Simulate a market rally at Henry Hub by running this query:
-- Simulate a market price increase at Henry Hub for July
UPDATE MarketPrices
SET PricePerMMBtu = 3.1000, PriceDate = GETDATE()
WHERE DeliveryPointId = 1 AND DeliveryMonth = '2026-07-01';

What does this simulate?

You are moving the Henry Hub July market price from $2.85 to $3.10 per MMBtu. This is a $0.25 increase. Any trade with exposure to that delivery point and period will see its unrealized P&L change.

Buy positions gain value when the market rises (you locked in a lower price). Sell positions lose value (you committed to a price now below market).

  • Run the application again by pressing F5 in Visual Studio.
  • Type 2 to view the MTM P&L Report.

Compare the P&L values to what you saw earlier. The Henry Hub July trades now show larger gains for buy positions and larger losses for sell positions. The total P&L at the bottom has shifted to reflect the new market.

Why does this prove the system works?

The MTM stored procedure joins each trade against the current market price at query time. You changed nothing in your application code. The stored procedure automatically picks up the new price and recalculates everything.

This is exactly how production ETRM systems operate. Market data feeds update prices, and reports instantly reflect the new valuations. Allegro consultants configure and validate this same flow for enterprise clients.

P&L values did not change?

Make sure the UPDATE query executed without errors. Confirm the WHERE clause matches DeliveryPointId = 1 (Henry Hub) and DeliveryMonth = '2026-07-01'.

You can verify by running SELECT * FROM MarketPrices WHERE DeliveryPointId = 1 and checking that the July row shows 3.1000.

Help me debug why my MTM P&L did not update after changing market prices

Secret mission

Add Counterparty Credit Exposure

Your system already tracks positions and P&L. But how much money could you lose if a counterparty defaults? Extend your understanding to credit exposure tracking, a core Allegro middle-office function that monitors how much risk you carry with each trading partner.

Clean Up Your Resources

Clean Up Your Resources

This project runs entirely locally, so there are no ongoing costs. Decide whether to keep your resources in place for interview practice or delete them entirely.

Resources you used:

  • GasTradeDB database on your local SQL Server instance.
  • GasTradeETRM Visual Studio project folder (C# source files and SQL scripts).

Keep everything running

No action needed. Choose this if you are still actively experimenting with the trade system or want to keep it ready for interview demos.

  • Your GasTradeDB database uses minimal disk space and has no ongoing cost.
  • You can reopen the GasTradeETRM solution in Visual Studio at any time to run it again.
  • Add more trades or update market prices to practice different scenarios before your interview.

Pause - I'll come back to this later

There are no ongoing costs or running processes to pause. Your database and project files stay on disk until you decide to delete them.

  • SQL Server stores the database files locally. They persist even after you restart your machine.
  • No action required. Everything will be exactly as you left it when you return.

Delete - I don't want to use this again

Remove all project resources and start fresh. Follow both steps below to fully clean up.

  • Drop the GasTradeDB database by opening a new query window in SQL Server Object Explorer (or SSMS) and running this command:
DROP DATABASE GasTradeDB;
  • Delete the GasTradeETRM project folder. In File Explorer, navigate to the location where you created the project and delete the entire GasTradeETRM folder.

Nice Work!

Nice Work!

Nice work! You just built a fully functioning natural gas trade management system that mirrors what Allegro does at enterprise scale.

You've learned how to:

  • Built a SQL Server trade data model mirroring Allegro's entity structure (counterparties, delivery points, trades, and market data).
  • Wrote T-SQL stored procedures for position aggregation and mark-to-market calculation, the two most common Allegro reporting tasks.
  • Built a C# .NET 8 console application demonstrating the full trade lifecycle from capture to valuation.
  • Secret Mission: Added counterparty credit exposure tracking with limit breach detection.

Ready to quiz yourself?