dataset-to-typed

name	dataset-to-typed
description	Use when refactoring ADO.NET DataSet/DataTable/DataRow code into typed C# DTOs and safe parameterized queries using Dapper (or parameterized SqlCommand). Focuses on minimal risk and incremental cutover, one query at a time.
argument-hint	[module/service name] [list of SQL queries or stored procs to refactor]

Dataset to Typed DTOs Skill

When to Use

• When an extracted C# service still receives DataSet/DataTable from the VB layer.
• When converting VB data-access methods to C# repository implementations.
• When any inline SQL needs to be parameterized and placed behind a repository interface.

Invariants (always enforce)

• No DataSet, DataTable, DataRow, or DataColumn in new C# code.
• No SELECT * — always project named columns.
• All SQL parameterized, never concatenated.
• No EF Core unless explicitly requested by the team.

Step-by-Step Procedure

Step 1 — Query Inventory

Build a catalog for the target module:

QueryID | SQL/StoredProc | Tables | Input Params | Columns Projected | Return Cardinality
Q01     | inline SELECT  | Orders | @CustomerId  | Id, Date, Total   | IReadOnlyList
Q02     | usp_SaveOrder  | Orders, Lines | @OrderId, ... | (none) | void/int affected

Flag any SQL with string concatenation as INJECT RISK — remediate first.

Step 2 — Parameterize Unsafe SQL in VB (safety first)

Before moving any query to C#, parameterize it IN VB if it currently uses string concat:

' ❌ Unsafe
cmd.CommandText = "SELECT Id, Name FROM Orders WHERE Status = '" & status & "'"

' ✅ Safe (still VB, still DataSet — but no injection risk)
cmd.CommandText = "SELECT Id, Name FROM Orders WHERE Status = @Status"
cmd.Parameters.AddWithValue("@Status", status)

Run integration tests to confirm no behavior change. Commit this fix independently.

Step 3 — Define C# DTO

// /src/App/Orders/OrderSummaryDto.cs
public sealed record OrderSummaryDto(int Id, string Name, string Status, DateOnly CreatedDate);

Step 4 — Define Repository Interface

// /src/App/Orders/IOrderRepository.cs
public interface IOrderRepository
{
    Task<IReadOnlyList<OrderSummaryDto>> GetByStatusAsync(string status, CancellationToken ct = default);
    Task<int> SaveOrderAsync(OrderInputDto input, CancellationToken ct = default);
}

Step 5 — Dapper Implementation

// /src/Infra/Orders/OrderRepository.cs
public sealed class OrderRepository(IDbConnection db) : IOrderRepository
{
    public async Task<IReadOnlyList<OrderSummaryDto>> GetByStatusAsync(
        string status, CancellationToken ct)
    {
        const string sql = """
            SELECT Id, Name, Status, CreatedDate
            FROM Orders
            WHERE Status = @Status
            ORDER BY CreatedDate DESC
            """;
        var results = await db.QueryAsync<OrderSummaryDto>(sql, new { Status = status });
        return results.ToList();
    }
}

For stored procedures:

var results = await db.QueryAsync<OrderSummaryDto>(
    "dbo.usp_GetOrdersByStatus",
    new { Status = status },
    commandType: CommandType.StoredProcedure);

Step 6 — Integration Tests

[Trait("Category", "Integration")]
public class OrderRepositoryTests(DatabaseFixture db) : IClassFixture<DatabaseFixture>
{
    [Fact]
    public async Task GetByStatus_ActiveOrders_ReturnsSortedList()
    {
        // Arrange: seed orders with Status = "Active"
        // Act: call repository
        // Assert: returns expected count, sorted correctly, no DataSet involved
    }

    [Fact]
    public async Task GetByStatus_SqlInjectionAttempt_DoesNotReturnExtraRows()
    {
        // Verify parameterization holds: "' OR '1'='1" as status returns 0 rows
        var results = await _repo.GetByStatusAsync("' OR '1'='1");
        Assert.Empty(results);
    }
}

Step 7 — Register and Wire

// /src/App/DependencyInjection.cs or Program.cs
services.AddScoped<IOrderRepository, OrderRepository>();
services.AddScoped<IDbConnection>(_ =>
    new SqlConnection(configuration.GetConnectionString("Default")));

Step 8 — Verify Full Suite

dotnet test

Zero regressions. All integration tests for new repository pass.

Multi-Join DataSet Migration

When a DataSet contained multiple related tables (e.g., Order + OrderLines):

// Option A: Two queries, assembled in service
var order = await orderRepo.GetByIdAsync(orderId);
var lines = await lineRepo.GetByOrderIdAsync(orderId);
return new OrderDetailDto(order, lines);

// Option B: Single JOIN query mapped to nested DTO
public sealed record OrderDetailDto(int Id, string Status, IReadOnlyList<OrderLineDto> Lines);
// Requires Dapper multi-mapping: db.QueryAsync<OrderDto, OrderLineDto, OrderDetailDto>(...)

Prefer Option A for simplicity unless performance requires Option B.