sqlserver-orders-tsql

An order-management platform whose business logic lives in SQL Server T-SQL stored procedures, with a .NET 9 data layer that does nothing but call them.

This is a test corpus for the "Move stored procedures to .NET" playbook: the workbench reads every procedure, maps it to the repository method that should replace it, and ports the logic out of T-SQL and into C# — one procedure at a time, each compiled before the next.

What's here

sql/                          24 stored procedures across 4 modules
  01_schema.sql               8 tables, identity keys, indexes
  02_ordering.sql             create → add lines → reprice → place → cancel
  03_fulfilment.sql           stock, allocation, shipping, returns
  04_billing.sql              payment capture, refunds, credit control
  05_analytics.sql            sales, lifetime value, funnel, housekeeping

src/Orders.Data/              the .NET side — four repositories, one per module
  OrderingRepository.cs       7 methods, every one a wrapper over dbo.usp_*
  FulfilmentRepository.cs     6 methods
  BillingRepository.cs        5 methods
  AnalyticsRepository.cs      6 methods
  Models.cs                   the row/result records the reports return

src/Orders.App/               a console host that wires the repositories together

The starting state

Every repository method today is a thin marshalling shim. This is the shape the migration exists to remove:

public async Task<decimal> PlaceOrderAsync(int orderId, CancellationToken ct = default)
{
    var p = new DynamicParameters();
    p.Add("@OrderId", orderId);
    p.Add("@PlacedTotal", dbType: DbType.Decimal, direction: ParameterDirection.Output);

    await _db.ExecuteAsync(new CommandDefinition(
        "dbo.usp_PlaceOrder", p,
        commandType: CommandType.StoredProcedure, cancellationToken: ct));

    return p.Get<decimal>("@PlacedTotal");
}

Nothing in C# knows that placing an order stamps placed_at, re-reads the total and writes an audit row inside a transaction — dbo.usp_PlaceOrder does all of that, invisibly.

What the corpus covers

Deliberately varied, so the migration has to handle more than one shape:

OUTPUT parameters	throughout — including procedures with 3 (usp_GetCustomerLifetimeValue) and BIT flags (usp_CheckAvailability, usp_CheckCreditLimit)
Bare SELECT result sets	7 procedures, several over multi-table joins and GROUP BY
TRY/CATCH transactions	usp_PlaceOrder, usp_ShipOrder, usp_CapturePayment — commit on success, ROLLBACK + THROW on failure
Multi-statement writes	13 procedures write; usp_ShipOrder touches shipments, orders and inventory in one call
Aliased UPDATE … FROM	usp_CancelOrder, usp_ShipOrder — UPDATE inv … FROM dbo.inventory inv JOIN …
@@ROWCOUNT / SCOPE_IDENTITY()	returned as OUTPUT values by several procedures
Branching business rules	tier thresholds in usp_RecalculateCustomerTier, credit checks in usp_CheckCreditLimit
Server-clock writes	SYSUTCDATETIME() stamped into payments, shipments and the audit log
TOP (@N) / IN (…) filters	usp_GetTopCustomers, usp_GetUnpaidOrders

Running it through the playbook

1. Open the repo in CogniDev Workbench (Open from GitHub → cognidevwb/sqlserver-orders-tsql).
2. Run Understand so the structural shards exist.
3. Run Move stored procedures to .NET.

The run plans one task per procedure, ports each into the repository that already calls it, compiles after every one, and finishes with a coverage report reconciling all 24 procedures against the delivered C# — including that each one still writes the tables it used to write.

Building

dotnet build -c Release

No database is required to build or run the console host.