Adding Navigation Controls to Move Between Records

Every example since Lesson 2 has displayed VirtualBookShelf's entire Inventory table at once, a reasonable approach while the catalog stays small. This lesson adds a different kind of page, one that shows a single book at a time, with First, Previous, Next, and Last links to move to its neighbors. That's a genuinely new problem, not a smaller version of the list page: a list page retrieves everything it needs in one query, but a single-record navigator has to know, on every request, which record is "current" and which records sit just before and after it, without ever loading the whole table to figure that out.

A tool built for this exact problem in an older .NET technology, ASP.NET Web Forms, solved it by keeping a currentIndex value in ViewState, a hidden field the server writes into every page and reads back on the next postback. That pattern depends on something Razor Pages simply doesn't have: a persistent idea of "the same page" across more than one request. Web Forms simulated that persistence by round-tripping state through the page itself; Razor Pages, like Classic ASP before it, treats every request as independent, exactly the point Lesson 4 made about Razor Pages sitting closer to the thin-client end of the spectrum. There's nothing to port from ViewState because Razor Pages was never designed to need it.

What a stateless request does have is its own URL, and that's where "which record is current" has to live instead. A route value naming the current book, carried in the address bar itself, survives perfectly well across requests, because each request simply says what it needs rather than relying on the server remembering anything from the last one.

This page takes an optional id parameter identifying which book is current, the same asp-route-id pattern Lesson 4 introduced for linking to a book's detail page:

public async Task<IActionResult> OnGetAsync(int? id)
{
    using SqlConnection connection = new(connectionString);
    await connection.OpenAsync();

    FirstID = await GetScalarIdAsync(connection, "SELECT MIN(BookID) FROM Inventory");
    LastID = await GetScalarIdAsync(connection, "SELECT MAX(BookID) FROM Inventory");

    int currentId = id ?? FirstID ?? 0;

    PreviousID = await GetScalarIdAsync(connection,
        "SELECT TOP 1 BookID FROM Inventory WHERE BookID < @CurrentID ORDER BY BookID DESC",
        currentId);
    NextID = await GetScalarIdAsync(connection,
        "SELECT TOP 1 BookID FROM Inventory WHERE BookID > @CurrentID ORDER BY BookID ASC",
        currentId);

    Current = await GetBookAsync(connection, currentId);
    return Page();
}

When no id is present at all, the page falls back to FirstID, so a visitor arriving at this page for the first time sees the first book rather than an error. Every other value, PreviousID, NextID, LastID, and the Current book itself, gets computed fresh from currentId, nothing is carried over from an earlier request.

Fetching the record itself reuses the exact retrieval pattern Lesson 1 through 3 already established, a parameterized SELECT against the one BookID this page needs:

async Task<Book?> GetBookAsync(SqlConnection connection, int bookId)
{
    SqlCommand command = new("SELECT BookID, Title, Author, Price FROM Inventory WHERE BookID = @BookID", connection);
    command.Parameters.AddWithValue("@BookID", bookId);

    using SqlDataReader reader = await command.ExecuteReaderAsync();
    if (await reader.ReadAsync())
    {
        return new Book
        {
            BookID = reader.GetInt32(reader.GetOrdinal("BookID")),
            Title = reader.GetString(reader.GetOrdinal("Title")),
            Author = reader.GetString(reader.GetOrdinal("Author")),
            Price = reader.GetDecimal(reader.GetOrdinal("Price"))
        };
    }
    return null;
}

Nothing about this method is new; it's the same SqlCommand/SqlDataReader pair this module has used since Lesson 2, just narrowed to a single row with a WHERE clause instead of looping over every row Inventory has.

The four neighbor queries are small, but they're exactly the kind of statement worth confirming directly in Azure Data Studio before they go anywhere near a SqlCommand, the same habit this course has followed since Module 3. Running SELECT TOP 1 BookID FROM Inventory WHERE BookID < 3 ORDER BY BookID DESC directly against VirtualBookShelf's Inventory table confirms it returns exactly one row, the book just before BookID 3, and running the same query with BookID set to the lowest value in the table confirms it correctly returns no rows at all, exactly the boundary case GetScalarIdAsync needs to handle. Seeing that empty result set directly in the query tool, rather than discovering it as an unexpected null somewhere inside a page handler, is the entire reason this course has kept coming back to Azure Data Studio before writing a single line of C# against a new query.

These four queries all order by BookID, VirtualBookShelf's IDENTITY primary key, rather than, say, Title. That choice matters more than it looks. Module 3's own lesson on ORDER BY showed that a result set has no guaranteed order at all unless a query explicitly asks for one, and nothing stops two different pages, or two different visits to this same page, from asking for Inventory in two different orders. Ordering this page's navigation by Title instead would mean "Next" and "Previous" shift whenever a book's title changes, or when two books happen to share a title and their relative order becomes ambiguous. BookID never changes once a row exists and never repeats, since it's the table's own IDENTITY column, so First, Previous, Next, and Last always mean the same thing for a given book regardless of when or how often a visitor navigates through them. A page built to browse by title alphabetically, if VirtualBookShelf ever added one, would be a legitimately different feature from this one, not a variation of it, precisely because it would need its own, separate notion of what "next" means.

Lesson 3 introduced ExecuteScalar for a query whose whole purpose is one value, a row count in that lesson's example. Finding a book's neighbors is the same shape of problem four times over: MIN(BookID), MAX(BookID), and the closest BookID below or above the current one are each a single number, not a set of rows to loop through. A small helper method wraps that pattern once:

async Task<int?> GetScalarIdAsync(SqlConnection connection, string sql, int? currentId = null)
{
    SqlCommand command = new(sql, connection);
    if (currentId.HasValue)
    {
        command.Parameters.AddWithValue("@CurrentID", currentId.Value);
    }

    object result = await command.ExecuteScalarAsync();
    return result is int value ? value : null;
}

That last line is worth slowing down on. ExecuteScalar can hand back two different kinds of "nothing," and this method treats both the same way on purpose. MIN(BookID) run against a table that genuinely has rows always returns one row, so ExecuteScalar only ever returns DBNull.Value there if Inventory is completely empty, an aggregate with nothing to aggregate. The TOP 1 ... WHERE BookID < @CurrentID queries behave differently: when the current book is already the first one, that query returns zero rows outright, and Lesson 3's own FAQ already covered what ExecuteScalar does then, it returns a plain C# null rather than throwing. result is int value catches both cases in one check, neither DBNull.Value nor a genuine null is an int, so either one quietly becomes "there is no such neighbor" without the two distinct reasons needing separate handling here.

With FirstID, PreviousID, NextID, and LastID each either holding a real BookID or null, the anchor Tag Helper from Lesson 4 renders each link only when one exists:

<nav>
@if (Model.PreviousID is not null)
{
    <a asp-page="./Specials" asp-route-id="@Model.PreviousID">|< First</a>
    <a asp-page="./Specials" asp-route-id="@Model.PreviousID">< Previous</a>
}
else
{
    <span>|< First</span>
    <span>< Previous</span>
}
@if (Model.NextID is not null)
{
    <a asp-page="./Specials" asp-route-id="@Model.NextID">Next ></a>
    <a asp-page="./Specials" asp-route-id="@Model.LastID">Last >|</a>
}
else
{
    <span>Next ></span>
    <span>Last >|</span>
}
</nav>

A visitor viewing the very first book sees First and Previous rendered as plain, unclickable text rather than a link pointing nowhere useful; the very last book does the same for Next and Last. Displaying the current record itself reuses exactly what Lesson 5 built, the Display-attribute-driven table cells reading straight from the same Book class, just rendering one record instead of looping over a whole list:

<table>
    <tr><th>@Html.DisplayNameFor(m => m.Current.Title)</th><td>@Html.DisplayFor(m => m.Current.Title)</td></tr>
    <tr><th>@Html.DisplayNameFor(m => m.Current.Author)</th><td>@Html.DisplayFor(m => m.Current.Author)</td></tr>
    <tr><th>@Html.DisplayNameFor(m => m.Current.Price)</th><td>@Html.DisplayFor(m => m.Current.Price)</td></tr>
</table>

The older Web Forms version loaded every record in the dataset into ViewState the first time the page loaded, regardless of whether a visitor ever clicked past the first record or not, and kept that entire copy traveling with the page on every postback afterward. This lesson's version retrieves exactly five things per request: the current book's four fields and, separately, up to four small scalar values describing its neighbors. A catalog of a dozen books or a hundred thousand books costs the same five lookups either way, since nothing here depends on how large Inventory actually is. That's a direct consequence of there being no server-side memory to fill in the first place: each request only ever asks for what that one request specifically needs.

This also answers what the legacy course's own separate "Grid DTC" was for, showing several records at once rather than one. That job doesn't need a new technology here either; it's the exact job this course's own @foreach-based table rendering has handled since Lesson 2. A single-record navigator and a full-list table are two different pages built from the same underlying tools, not two different technologies.

It's also worth being precise about what this lesson is not. "Navigation controls" here means moving one record at a time through an ordered sequence, not splitting a large result set into pages of several rows each; that's a related but different problem, SQL Server's OFFSET/FETCH NEXT clause, that this course still hasn't reached. A page showing Previous and Next links for a single book, and a page showing "Page 2 of 5" for a hundred-row catalog, solve genuinely different problems even though both get loosely called "paging" in casual conversation. This lesson only builds the first one.

Could the current record's position be kept in server-side session state instead of the URL? ASP.NET Core does support session state for cases that genuinely need it, but using it here would reintroduce exactly the problem this lesson is avoiding, a server-side value that has to be remembered between otherwise-independent requests. The URL already carries everything this page needs, so there's nothing here that session state would improve.

What happens if a visitor edits the URL to an id that doesn't exist, say a BookID that was deleted? GetBookAsync's underlying query would simply find no matching row, and Current would come back null. A production version of this page would check for that and show a message rather than attempt to render a null book; this lesson's examples assume a valid, existing BookID to keep the navigation logic itself the clear focus.

Why ExecuteScalar instead of ExecuteReader for these four lookups? Each one is a single value, not a set of rows to loop through, exactly the case Lesson 3 introduced ExecuteScalar for. Using ExecuteReader here would work, but it would mean opening a reader, calling Read() once, and immediately closing it, more steps for the same single value.

Could this same pattern navigate within a filtered subset, say only books by a particular author, rather than the whole catalog? Yes, and that's genuinely the next step this course takes. Lesson 7 covers parameterized queries built from a visitor's own input, a search or filter condition; adding a WHERE Author = @Author clause to all four of this lesson's queries, alongside the parameter from that visitor's search, would scope First, Previous, Next, and Last to that same filtered subset rather than the whole table.

Is there a risk that a book gets added or removed between the time this page loads and when a visitor clicks Next? Yes, in principle, the same risk Lesson 3's ExecuteNonQuery FAQ already touched on for a stale row count. Each click here is its own fresh request, though, running its own fresh queries against whatever Inventory actually contains at that moment, so a visitor always sees a currently valid neighbor even if the exact set of books changed slightly since the previous click.

What about Blazor, or a third-party grid component, or a JavaScript grid library talking to a REST API? All three are real, capable ways other projects solve the "show many records, with rich interaction" problem, and nothing about this lesson argues otherwise. None of them are what this course is building, though; Module 1 chose Razor Pages specifically for its closeness to this course's own legacy page model, and a single-record navigator built from a route value and four scalar queries fits that same architecture without reaching for a different framework or a paid component.

Does the order FirstID, LastID, PreviousID, and NextID get computed in the code above matter? Not for correctness, each is independent of the others. The example above computes FirstID and LastID before resolving currentId, since FirstID doubles as the fallback when no id arrives at all; PreviousID and NextID need currentId settled first, which is why they're computed after it.

Why order these queries by BookID instead of something more meaningful to a reader, like Title? BookID is stable and unique in a way Title isn't guaranteed to be, covered above. A future version of this page could add a second navigation mode ordered by Title, but it would need its own ORDER BY Title in all four queries and its own tie-breaking rule for two books that happen to share a title, rather than simply swapping the column name in the existing ones.

Could all five values, FirstID, PreviousID, NextID, LastID, and the current book, be retrieved in a single query instead of five separate round trips to the database? With a more advanced query, yes, a single statement using window functions could return all five in one result set. That's beyond what this course has covered so far, though, and five small, individually understandable queries, each doing exactly one job, fit this point in the course better than one query doing all five at once.

In the next lesson, parameterized queries built from a visitor's own input take center stage: retrieving specific records based on what someone actually typed into a search box, rather than navigating a fixed order one neighbor at a time.

Click the Exercise link below to practice adding navigation controls to move between records.
Adding Navigation Controls - Exercise