Relicbridge | Retiring ADO Recordsets: the data access layer nobody planned

Every WebForms, VB6 and Classic ASP system we open has the same layer in the middle, and it is never in the documentation. Somewhere between the screen and SQL Server there is ADO: ADODB.Recordset in VB6 and Classic ASP, System.Data.OleDb or SqlDataAdapter filling untyped DataSets in early WebForms. Nobody designed it. It accreted, one screen at a time, over fifteen or twenty years.

You cannot migrate a slice without moving this layer, because the slice's business rules are inside it: in the SQL string, in the loop that walks the recordset, and in the null handling around both. This tutorial is the method we use. It works on any of the five stacks, and it runs query by query, so you can stop halfway and still have a working system.

You need the legacy source, read access to the production database schema, and a restorable copy of the database to test against. Nothing here requires changing the legacy application until the last step.

Step 1: inventory the SQL before you touch anything

Estimates that come from reading code are guesses. Count first.

Most legacy data access is string-built SQL, so grep gets you surprisingly far:

# VB6 and Classic ASP
grep -rniE "(\.Open|\.Execute|CommandText)\s*[=(]" --include=*.bas --include=*.cls \
  --include=*.frm --include=*.asp --include=*.inc . > sql-sites.txt

# WebForms code-behind
grep -rniE "new Sql(Command|DataAdapter)|CommandText\s*=" --include=*.cs --include=*.vb . >> sql-sites.txt

wc -l sql-sites.txt

Then classify every hit into four buckets. We keep it in a spreadsheet, one row per call site, because this table is what the migration plan is built from:

Bucket	What it looks like	Where it goes on .NET 8
Plain read	`SELECT` filling a grid or list	A query method returning a typed record
Plain write	`INSERT`/`UPDATE` from one form	A command method in a transaction
Stored procedure call	`EXEC sp_something`	Usually stays; call it, do not rewrite it
Logic in the loop	`Do Until rs.EOF` with `If` statements and running totals	A decision, and the expensive bucket

On a mid-size line-of-business app expect several hundred call sites and a few dozen in that last bucket. The count is the number you estimate from. Everything above the last row is mechanical.

While you are there, write down the duplicates. The same SELECT against the customer table, pasted into eleven screens with small differences, is one method on the new side, and finding that early is most of where the savings come from.

Step 2: pin the behavior, including the ugly parts

Before a single query moves, record what the current code returns. We covered the HTTP-level harness in golden-master tests for legacy web apps; at the data layer the recording is narrower and easier. Run the legacy query against the restorable database and snapshot the result set as text:

[Fact]
public async Task OpenInvoices_MatchesRecordedResultSet()
{
    const string legacySql = @"
        SELECT i.invoice_no, c.name, i.total, i.due_date
        FROM invoices i, customers c
        WHERE i.cust_id = c.id AND i.paid_flag = 0
        ORDER BY i.due_date";

    var text = await Snapshot.RunAsText(legacySql);
    await GoldenMaster.Verify("open-invoices", text);
}

Snapshot.RunAsText is twenty lines: execute the reader, write a header row of column names and types, then each row pipe-delimited with a literal (null) for nulls. The types and the nulls are the part that matters, and they are exactly what a careless port changes.

Three behaviors we make a point of recording, because they bite on every engagement:

Implicit ordering. A query with no ORDER BY that has "always" come back in insertion order. It has not; it has come back in whatever order the current index and plan produce. Record it, then add an explicit ORDER BY on the new side and expect the diff.
Null and empty string. VB6 and Classic ASP coerce Null to "" on the way to the screen. DBNull in C# throws instead. Decide per column whether empty and null are the same thing to this business, and write the decision down.
Money and rounding. ADO hands decimal(19,4) to a VB6 Currency or a VARIANT double, and the rounding that happens after that is behavior. Keep it in decimal on the new side and compare totals against the recording, not against arithmetic you do in your head.

Step 3: port one query, behind an interface

Pick a plain read from a screen you are about to strangle. On .NET 8, the new side is an interface the slice depends on, and a Dapper or ADO.NET implementation behind it:

public sealed record OpenInvoice(
    string InvoiceNo, string CustomerName, decimal Total, DateOnly DueDate);

public interface IInvoiceQueries
{
    Task<IReadOnlyList<OpenInvoice>> GetOpenInvoicesAsync(CancellationToken ct = default);
}

public sealed class InvoiceQueries(SqlConnection connection) : IInvoiceQueries
{
    public async Task<IReadOnlyList<OpenInvoice>> GetOpenInvoicesAsync(
        CancellationToken ct = default)
    {
        const string sql = """
            SELECT i.invoice_no AS InvoiceNo,
                   c.name       AS CustomerName,
                   i.total      AS Total,
                   i.due_date   AS DueDate
            FROM invoices i
            JOIN customers c ON c.id = i.cust_id
            WHERE i.paid_flag = 0
            ORDER BY i.due_date, i.invoice_no
            """;

        var rows = await connection.QueryAsync<OpenInvoice>(
            new CommandDefinition(sql, cancellationToken: ct));
        return rows.ToList();
    }
}

Four things changed and each was deliberate. The comma join became an explicit JOIN, because the old form hides outer-join mistakes. The ordering became total, with invoice_no as a tiebreaker, so the result is stable. The result is a record with real types instead of a DataTable indexed by string. And the shape is an interface, so the slice can be tested without a database.

We are not putting Entity Framework in front of a schema like this on the first pass. EF Core is a reasonable target later, for the tables a rebuilt slice fully owns. Against a twenty-year-old schema with composite natural keys, triggers and columns named FLD7, mapping it costs more than the SQL is worth. Dapper keeps the SQL visible, which is what you want while the recordings are still the spec.

Now run the same recording against the new method. The comparison is column names, types, null placement and row order, so a small adapter that renders IReadOnlyList<OpenInvoice> in the same pipe-delimited format as Snapshot.RunAsText lets you reuse the approved file directly. Green means this query is done. Red means you have found either a bug you are choosing to fix, or a behavior you did not know about.

Step 4: the loop bucket

The last bucket from step 1 is not a data-access problem wearing a disguise. It is business logic:

Do Until rs.EOF
    If rs("class") = "W" And rs("qty") > 0 Then
        subtotal = subtotal + (rs("qty") * rs("price"))
        If rs("cust_type") = "D" Then
            subtotal = subtotal - (subtotal * 0.02)
        End If
    End If
    rs.MoveNext
Loop

That two-percent line is a pricing rule. It is not written down anywhere else, and the person who added it in 2007 is gone. Do not fold it into SQL, and do not let a model rewrite it into something tidier. Port it to C# as literally as you can stand — same order of operations, same rounding points, same odd class = "W" check — get it green against the recorded totals, and only then refactor with the tests holding.

This is where we use AI heavily and carefully. Generating the mechanical port of a few hundred plain reads is exactly the kind of bulk work a model is good at, and we use it that way. On the loop bucket a model will confidently produce a cleaner, faster, slightly different calculation, and "slightly different" on a money path is the whole problem. A senior engineer decides which behaviors are contracts; the tests prove the port kept them.

Step 5: cut over one call site at a time

With the new method green, change the legacy call site to use it, or route the whole screen to the rebuilt slice behind the YARP shim. One call site, one deploy, harness green before and after. The inventory spreadsheet gets a date in the done column.

The useful property of working this way is that there is no data-layer big bang and no point where the system is half-ported and unrunnable. Stop after forty queries and you have a system where forty queries are tested, typed and reusable, and the rest works exactly as it did.

Two things we will not do

We will not run a tool that turns every Recordset into a DataTable. The output compiles and nothing is better: the same untyped, string-indexed access with newer syntax, and the null and rounding behavior silently different. If you are going to touch every call site, come out the other end with types.

We will not fix the schema at the same time. Renaming FLD7, adding foreign keys and normalizing the address columns are all worth doing, and each one turns a green recording red for a reason unrelated to the migration. Move the data access first. Change the schema afterward, with the harness in place to tell you what it broke.

If you are staring at a few hundred of these call sites and trying to work out what the port costs, the number comes from the inventory in step 1, not from reading the code. Count it, record two or three of the risky paths, and port one query end to end. A week of that tells you more than any estimate written from a walkthrough.