Scripting with Recordsets: From VBScript Loops to SqlDataReader

Every query this course has written since Module 3 has run against a connection, filled a model, and handed the result to a Razor Page for rendering. None of those lessons have looked closely at what actually happens between ExecuteReaderAsync returning and a row showing up on the page. This lesson does: the legacy course called the object that sits in that gap a recordset, a forward-only cursor over a query's results, and gave it a small set of properties and methods for walking through it one record at a time. ADO.NET's own version of that same idea is SqlDataReader, and the two map onto each other closely enough to translate line by line rather than needing an entirely new mental model.

The old Recordset Design-Time Control generated this kind of walking-through code automatically as boilerplate whenever a page was built in the Visual InterDev designer, initialization script a developer rarely needed to read, let alone write by hand. This course dropped that designer entirely back in Module 4's earlier lessons, in favor of writing the equivalent C# directly; this lesson is where that earlier decision pays off, because the code the designer used to generate is exactly the code this lesson now writes out in full, and seeing it written out is what makes the legacy object's behavior easy to follow.

The legacy recordset object exposed dozens of properties, methods, and events to scripting, most of them specific to the old Design-Time Control's own data-binding machinery and without a sensible modern equivalent at all. A handful of them, though, describe exactly what SqlDataReader does, just under different names:

Legacy Recordset	Modern SqlDataReader
EOF	the inverse of reader.Read()'s return value; false once there are no more rows
moveNext	reader.Read() (or await reader.ReadAsync()), which both advances the cursor and reports whether a row was found
fields.getCount()	reader.FieldCount
fields.getName(i)	reader.GetName(i)
fields.getValue(i)	reader[i] or reader.GetValue(i)
close	reader.Close(), though a using declaration handles this automatically
setSQLText(sql) / getSQLText()	the SQL string passed to SqlCommand's constructor, held in its CommandText property

Everything else in the legacy object's properties, methods, and events tables, absolutePosition, maintainState, getDHTMLDataSourceID, the onrowenter and onrowexit events, belonged to the Design-Time Control's own client-side data binding, a browser-specific mechanism this course's server-rendered Razor Pages don't use and don't need a replacement for.

The legacy page's own worked example queried a table called BookTable for rows with a Status column equal to 'S', meant to represent books on special. Neither BookTable nor a Status column exists anywhere in VirtualBookShelf's actual schema; this course's real inventory table has always been Inventory, with columns BookID, Title, Author, ISBN, Price, and QuantityOnHand, established back in Module 3 and used consistently in every lesson since. Rather than inventing yet another stand-in column to represent "on special," a condition the schema was never designed to express, this lesson's rebuilt example filters on a column that actually exists:

SELECT BookID, Title, Author, Price, QuantityOnHand
FROM Inventory
WHERE QuantityOnHand > 0

QuantityOnHand > 0, meaning "currently in stock," is a real, meaningful condition against the real table, and it serves the lesson's actual purpose just as well as an invented one would: something to filter on, so the recordset being walked through isn't simply every row in the table.

The legacy VBScript's first loop ran once before any row was displayed, walking across BookRecords.fields to print a table header for each column, whatever those columns happened to be. That same idea translates directly using reader.FieldCount and reader.GetName(i):

List<string> headers = new();
for (int i = 0; i < reader.FieldCount; i++)
{
    headers.Add(reader.GetName(i));
}

This loop doesn't know, and doesn't need to know, that the query selected BookID, Title, Author, Price, and QuantityOnHand specifically; it reads whatever column names the query actually returned, directly off the reader itself. That's exactly what the legacy script's fields.getName(i) loop did too, column names discovered at runtime rather than typed into the page in advance.

The legacy script's second loop is the more familiar one: Do While Not BookRecords.EOF, printing one table row per iteration, then calling moveNext before looping back. SqlDataReader collapses "check whether there's a row" and "move to it" into a single call, Read(), which returns true exactly when there was another row to move to:

List<List<string>> rows = new();
while (await reader.ReadAsync())
{
    List<string> row = new();
    for (int i = 0; i < reader.FieldCount; i++)
    {
        row.Add(reader.GetValue(i)?.ToString() ?? string.Empty);
    }
    rows.Add(row);
}

A while (await reader.ReadAsync()) loop is doing exactly what Do While Not BookRecords.EOF followed by a trailing BookRecords.moveNext did, just with the "are we done" check and the "move forward" step merged into one expression instead of two separate statements.

Put together, the header loop and the row loop give a single OnGetAsync handler that reproduces the legacy page's entire behavior, listing in-stock books with column headers discovered at runtime, using nothing but Microsoft.Data.SqlClient:

public List<string> Headers { get; set; } = new();
public List<List<string>> Rows { get; set; } = new();

public async Task OnGetAsync()
{
    using SqlConnection connection = new(connectionString);
    await connection.OpenAsync();

    SqlCommand command = new(
        "SELECT BookID, Title, Author, Price, QuantityOnHand FROM Inventory WHERE QuantityOnHand > 0",
        connection);

    using SqlDataReader reader = await command.ExecuteReaderAsync();

    for (int i = 0; i < reader.FieldCount; i++)
    {
        Headers.Add(reader.GetName(i));
    }

    while (await reader.ReadAsync())
    {
        List<string> row = new();
        for (int i = 0; i < reader.FieldCount; i++)
        {
            row.Add(reader.GetValue(i)?.ToString() ?? string.Empty);
        }
        Rows.Add(row);
    }
}

And the Razor markup that renders it, the direct equivalent of the legacy page's own <TABLE>, header <TR>, and row loop:

<table>
<tr>
@foreach (var header in Model.Headers)
{
    <th>@header</th>
}
</tr>
@foreach (var row in Model.Rows)
{
    <tr>
    @foreach (var cell in row)
    {
        <td>@cell</td>
    }
    </tr>
}
</table>

Nothing here needed ViewState, a Recordset Design-Time Control, or any client-side binding machinery at all; a connection, a command, a reader, and two small loops reproduce the legacy behavior completely.

Lesson 6 used ExecuteScalar four separate times, once each for First, Previous, Next, and Last, because each of those queries only ever needed a single value back, one BookID, not a set of rows to walk through. SqlDataReader solves a different problem: a query that can return any number of rows, zero, one, or many, each with potentially several columns, exactly this lesson's in-stock listing. Choosing between them comes down to what the query itself is shaped to return, not a preference between styles. A query written with SELECT TOP 1 and a single column is a natural fit for ExecuteScalar; a plain SELECT over a result set that could hold any number of rows needs SqlDataReader's row-by-row walk instead. Reaching for ExecuteScalar against a multi-row query would simply discard every row after the first, and reaching for a full SqlDataReader loop to retrieve one known value is harmless but needlessly heavier than a single scalar call.

This lesson's dynamic header-and-row approach is worth knowing, but it isn't what most pages in this course, or most real applications, should reach for by default. Lesson 5's [Display(Name = "...")] and [DataType(...)] attributes, applied to a strongly-typed Book or Customer class, give friendlier column labels than a raw database column name, and they format values correctly, currency symbols, dates, phone numbers, rather than calling ToString() on whatever the reader happens to return. The dynamic approach this lesson builds earns its place specifically when the columns themselves aren't known in advance, a generic data browser, an ad hoc reporting tool, something closer to what the legacy Recordset DTC was actually built for. For an application like VirtualBookShelf, where every page already knows exactly which columns its own query selects, Lesson 5's typed, attribute-driven rendering remains the better default; this lesson's value is in the direct translation from the legacy script, not in replacing what Lesson 5 already established.

SQL Server 2025 actually offers more than one way to work with something resembling a recordset, and it's worth being direct about why most of them don't belong here. SQL Server Integration Services has its own Recordset Destination component, but that's an extract-transform-load feature meant for data pipelines, not for a web page reading rows for display. T-SQL can instantiate a COM ADODB.Recordset object directly through sp_OACreate, but that requires re-enabling a server option, OLE Automation Procedures, that's disabled by default specifically because of the security exposure it carries; it has no place in an ordinary web application and isn't demonstrated here. And a client application can still open an ADODB.Recordset against SQL Server 2025 the same way the legacy VBScript did, but that's the exact classic-ADO pattern this course has been modernizing away from since this module began, not something to adopt freshly in 2025. SqlDataReader, built on Microsoft.Data.SqlClient, is the one approach that actually fits a modern Razor Pages application, and it's the only one this lesson teaches.

Before either loop above runs against a live connection, the underlying query is worth confirming directly: SELECT BookID, Title, Author, Price, QuantityOnHand FROM Inventory WHERE QuantityOnHand > 0, run in Azure Data Studio against VirtualBookShelf's own database, the same habit this course has followed since Module 3. It's also a convenient place to see reader.FieldCount and reader.GetName(i)'s actual output before writing a single line of the header loop: Azure Data Studio's own results grid already shows the column names a query returns, which is exactly what that loop reproduces on a rendered page.

Does SqlDataReader support moving backward, the way the legacy recordset's moveFirst or moveAbsolute did? No. SqlDataReader is forward-only by design, matching exactly how the legacy recordset's own moveNext-and-EOF pattern was actually used in every example this lesson looked at; neither one ever moved backward. A result set that genuinely needs to be read more than once, or read out of order, is better loaded into a DataTable or a plain in-memory list first, rather than reopening the reader.

What happened to the legacy recordset's update-related methods, addRecord, deleteRecord, updateRecord? SqlDataReader is read-only; it has no equivalent to any of them. Modifying data in this course's stack means executing a separate INSERT, UPDATE, or DELETE statement through SqlCommand.ExecuteNonQueryAsync, a genuinely different operation from reading rows, not a method call on the reader itself.

Why loop over column positions with reader.GetValue(i) instead of reading each column by name, the way earlier lessons did with reader.GetString(reader.GetOrdinal("Title"))? Reading by name works well when the handler already knows which columns to expect, exactly Lesson 7's search handler. This lesson's header-and-row loops exist specifically to handle a query whose columns aren't known in advance, matching the legacy script's own behavior, so they read positionally instead.

Does closing the connection also close the reader, or does it need its own using? Give the reader its own using SqlDataReader reader = ... declaration, as shown above. A SqlDataReader holds the connection in a busy state while it's open, and disposing it explicitly, rather than relying only on the connection's own disposal, releases that state as soon as the loop finishes reading.

Could this same header-and-row pattern work with a stored procedure instead of inline SQL? Yes. Setting command.CommandType = CommandType.StoredProcedure, the pattern Lesson 3 introduced, changes nothing about how the reader is walked afterward; FieldCount, GetName(i), and ReadAsync() all behave identically regardless of whether the SELECT came from inline text or from a procedure.

Is there a risk of SQL injection in this lesson's query, since it has no parameters at all? No, because the query has no visitor-supplied value anywhere in it; QuantityOnHand > 0 is a fixed literal, not user input. The parameterization discipline Module 3 established, and Lesson 7 extended to LIKE patterns, still applies the moment any part of a query's WHERE clause comes from outside the application.

Does Entity Framework Core have anything resembling a recordset? Conceptually, yes: an EF Core query enumerated with await foreach against an IAsyncEnumerable<T> walks forward through results one at a time, much like this lesson's reader loop, just returning typed objects instead of raw column values. This course still hasn't introduced EF Core directly, so SqlDataReader remains what VirtualBookShelf actually uses.

What does reader.GetValue(i) return for a database NULL? DBNull.Value, not C#'s own null, the same distinction Lesson 6 ran into with ExecuteScalar. The ?.ToString() ?? string.Empty pattern in this lesson's row loop handles that case by rendering an empty cell rather than throwing, since DBNull.Value.ToString() itself returns an empty string rather than failing.

Why does VirtualBookShelf's own historical database matter here? It doesn't, for this lesson. The legacy course's original simulation ran against a Microsoft Access database rather than SQL Server, a detail about this course's own history rather than anything this lesson's modernized version needs to account for; SQL Server 2025 has been this course's single target since Module 1, and nothing about reading a recordset changes based on what database engine the original course used years ago.

Does the header loop slow anything down compared to reading columns by name directly? Not meaningfully. reader.FieldCount and reader.GetName(i) read metadata the reader already holds in memory once the query has executed; the extra loop that builds Headers runs once per request, not once per row, so its cost is negligible next to the database round trip itself.

What happens if the query in this lesson's example returns zero rows, no books currently in stock at all? The header loop still runs, since reader.FieldCount is known as soon as the query executes, regardless of how many rows come back; the row loop simply never executes its body, because reader.ReadAsync() returns false immediately. The rendered page would show a header row with no data rows beneath it, the same behavior Do While Not BookRecords.EOF would have produced against an empty legacy recordset.

What's Ahead

Every example in this lesson assumed the connection opened cleanly, the query ran without issue, and the reader always returned exactly the rows expected. None of that is guaranteed in a real application: a connection can fail, a query can be malformed, a server can simply be unreachable when a request arrives. Handling that gap, what a Razor Page does when a database call doesn't succeed rather than when it does, is a natural next step from everything this module has built so far.