Selecting Records Based on User Input: From Search Box to Parameter

Every parameterized query this course has written so far has used a value already sitting in a C# variable or a page-model property, a BookID from a route, a price threshold typed directly into an example's own code. This lesson covers where that value actually comes from in a real application: a visitor typing something into a search box. Completing that loop, from an HTML form through to a SqlCommand parameter, is this lesson's real subject, not parameterization itself, which Module 3 already established thoroughly.

Microsoft's own tooling moved on from Visual InterDev to Visual Web Developer not long after this course's legacy material was written, folded into the Visual Studio family starting with Visual Studio 2005. That's a real piece of history, but not one this lesson needs to dwell on; Visual Studio 2026, Razor Pages, and SQL Server 2025 remain this course's actual target, the same stack established since Module 1.

Practically every web site today has some level of search capability. The typical scenario is familiar: a visitor picks a search criterion, author or title, say, types in a term, and submits it to the server. What makes this different from every query this course has written so far is when the actual search value becomes known. A query written directly into code, like every example through Lesson 6, is known in full at design time, while the application is being written. A search query is only known at runtime, while the application is actually running, since nobody can know in advance what a visitor will type. That distinction, not the mechanics of parameterization itself, is what makes a search feature different from everything this module has built so far: the SQL statement's shape is fixed in advance, but one of its values isn't filled in until a real visitor submits a real request.

Lesson 4 introduced [BindProperty] for binding a posted form field to a page-model property; a search box uses the same idea with one difference, it should use SupportsGet = true instead:

[BindProperty(SupportsGet = true)]
public string? Author { get; set; }

A search is read-only, it doesn't change any data, so it belongs on a GET request rather than a POST, the same reasoning Lesson 6 already applied to record-to-record navigation. A GET request's values travel in the URL itself, which means a search result page has its own shareable, bookmarkable address, ?Author=Rowling, something a POST-based search would never provide. The search box itself uses the same asp-for Tag Helper from Lesson 4:

<form method="get">
    <label asp-for="Author">Search by author</label>
    <input asp-for="Author" />
    <button type="submit">Search</button>
</form>

Every WHERE clause this course has written so far has looked for an exact match, WHERE BookID = @BookID, WHERE Author = @Author in Lesson 6's own forward-looking FAQ entry. A visitor searching for an author rarely types a name exactly as it's stored, though; someone searching "Rowling" reasonably expects to find "J.K. Rowling" without typing the whole thing. SQL Server's LIKE operator, paired with the % wildcard, matches a pattern rather than an exact value:

SELECT BookID, Title, Author, Price FROM Inventory
WHERE Author LIKE @SearchPattern

The wildcard itself has to be built carefully. It's tempting to concatenate it directly into the SQL text, "WHERE Author LIKE '%" + author + "%'", but that's exactly the string-concatenation pattern Module 3 already established as the SQL injection risk this whole module has been avoiding. The right approach builds the pattern in C#, as a single parameter value, and lets SqlCommand pass it through safely:

string searchPattern = $"%{Author}%";
command.Parameters.AddWithValue("@SearchPattern", searchPattern);

The % characters become part of the value handed to @SearchPattern, never part of the SQL statement's own text. SQL Server still interprets them as wildcards once the parameter's value reaches the query engine, but a visitor typing a string that happens to contain a quote character or a semicolon can't use it to change what the statement itself says, the same protection parameterization has provided since Module 3, extended here to a pattern match instead of an exact one.

It's worth seeing the unsafe version once, directly, rather than only being told it's unsafe. A visitor's search term dropped straight into a C# interpolated string looks almost identical to the safe version above:

string unsafeQuery = $"SELECT BookID, Title, Author, Price FROM Inventory WHERE Author LIKE '%{Author}%'";
SqlCommand command = new(unsafeQuery, connection);

A visitor searching an ordinary name works exactly the same way here as it does with the parameterized version. The difference only shows up with a deliberately crafted search term, one containing a single quote followed by SQL of the attacker's choosing. Because Author is spliced directly into the statement's own text, that extra SQL becomes part of what actually runs, Module 3's SQL injection warning made concrete. The parameterized version earlier in this lesson never has this problem, because Author's value never becomes part of the SQL text at all, it travels to SQL Server as data, held separately from the statement, no matter what characters it contains.

Parameterizing a value closes the SQL injection risk, but it doesn't mean every value is automatically sensible to run a query with. Lesson 5 added a [StringLength(100)]-style guard rail to model properties through data annotations; the same idea applies here, nothing stops a visitor from submitting an extremely long string as a search term, and while SqlCommand would handle it without error, there's no reason to send an unreasonably long value to the database at all. A simple length check or trim, Author?.Trim() before it's used to build @SearchPattern, keeps the search term itself well-formed before it ever reaches SQL Server, a different, complementary concern from parameterization's injection protection.

A search box that requires a value before showing anything is a worse experience than one that shows the full catalog until a visitor actually searches. This page's handler checks for that case explicitly:

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

    SqlCommand command;
    if (string.IsNullOrWhiteSpace(Author))
    {
        command = new("SELECT BookID, Title, Author, Price FROM Inventory", connection);
    }
    else
    {
        command = new("SELECT BookID, Title, Author, Price FROM Inventory WHERE Author LIKE @SearchPattern", connection);
        command.Parameters.AddWithValue("@SearchPattern", $"%{Author}%");
    }

    using SqlDataReader reader = await command.ExecuteReaderAsync();
    while (await reader.ReadAsync())
    {
        Results.Add(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"))
        });
    }
}

An empty or missing Author runs the same unparameterized SELECT every list page in this module has used since Lesson 2; a real search term switches to the parameterized LIKE version. Rendering Results on the page reuses exactly the Display-attribute-driven table Lesson 5 built, nothing about displaying the rows changes based on which query produced them.

A search results table benefits from one addition Lesson 6 already built, a link from each row to that book's own single-record view. Each row's title, rendered with the anchor Tag Helper rather than plain text, carries a visitor straight from a list of search matches into the navigable detail page Lesson 6 built, First, Previous, Next, and Last links included:

<td><a asp-page="./Specials" asp-route-id="@book.BookID">@Html.DisplayFor(_ => book.Title)</a></td>

Nothing about Lesson 6's navigation logic changes here; a visitor arriving at that page from a search result simply starts at a different BookID than one arriving from the catalog's first entry.

Lesson 3 introduced CommandType.StoredProcedure as the other way a SqlCommand can run, naming an existing procedure instead of holding raw SQL text directly. A search feature fits that pattern just as naturally as the inline version above, assuming a procedure like usp_SearchBooksByAuthor already exists on the server:

SqlCommand command = new("usp_SearchBooksByAuthor", connection)
{
    CommandType = CommandType.StoredProcedure
};
command.Parameters.AddWithValue("@SearchPattern", $"%{Author}%");

using SqlDataReader reader = await command.ExecuteReaderAsync();

Everything about parameter safety stays exactly the same, the wildcard pattern still gets built in C# and passed as a single parameter value, never concatenated into anything. What changes is only where the SELECT statement itself lives, inline in the C# code, as shown earlier, or inside a procedure stored once on the server and called by name here. Lesson 3 kept the actual CREATE PROCEDURE syntax at FAQ level rather than teaching it directly, and that stays true here too; what matters for this lesson is that calling a stored procedure with a search parameter works exactly the same way calling one with any other parameter already does.

A LIKE pattern is worth confirming directly in Azure Data Studio before it goes anywhere near a SqlCommand, the same habit this course has followed since Module 3. Running SELECT BookID, Title, Author, Price FROM Inventory WHERE Author LIKE '%Rowling%' directly against VirtualBookShelf's Inventory table confirms the pattern actually matches the rows it should before a single line of C# gets involved, and it's a convenient place to double-check what happens with a search term that matches nothing at all, an empty result set, not an error.

Why a named parameter, @SearchPattern, instead of a plain ? placeholder? SqlCommand, Microsoft's ADO.NET provider for SQL Server, requires named parameters; a positional ? placeholder is OLE DB and ODBC syntax, and SqlCommand doesn't accept it at all. Every parameter in this course, since Module 3, has used the named @ form for exactly this reason.

Is LIKE case-sensitive? It depends on the database's collation, but SQL Server's default collation is case-insensitive, so '%rowling%' and '%Rowling%' typically match the same rows without any extra handling. A database deliberately configured with a case-sensitive collation would behave differently, worth confirming directly in Azure Data Studio if it's ever in question.

What happens if a visitor's search term itself contains a % or _ character, SQL Server's own wildcard characters? Those characters would be interpreted as wildcards within the pattern, not literal text, which could match more than intended. SQL Server's LIKE supports an ESCAPE clause for exactly this case, letting a chosen escape character mark a wildcard as literal; this lesson's examples don't need it, since VirtualBookShelf's author names are unlikely to contain either character, but it's worth knowing the tool exists for a search field where they might.

Could this same search box look across more than one column, say Title in addition to Author? Yes, with an OR, WHERE Author LIKE @SearchPattern OR Title LIKE @SearchPattern, reusing the same parameter value against two columns. Nothing about parameterization changes by adding a second condition; the same single @SearchPattern parameter can appear in a WHERE clause as many times as needed.

The legacy version of this lesson mentioned stored procedures as an alternative to parameterized queries. Does that distinction still apply here? Yes, and Lesson 3 already covered it: a stored procedure called through CommandType.StoredProcedure and a parameterized CommandType.Text query like this lesson's both protect against SQL injection the same way, by keeping user-entered values out of the SQL text itself. A search feature like this one could just as easily call an existing stored procedure instead, passing @SearchPattern as that procedure's parameter rather than as part of an inline SELECT.

Does SQL Server 2025 offer anything beyond LIKE for this kind of matching? Yes, REGEXP_LIKE brings full regular-expression matching to a WHERE clause, letting a pattern match things LIKE's simple wildcards can't, an author name in more than one possible spelling, for instance. VirtualBookShelf's search doesn't need that flexibility yet, so this lesson sticks with LIKE, but it's a real, current SQL Server 2025 capability worth knowing exists for a more demanding search requirement.

Does Entity Framework Core change anything about building a search feature like this? Conceptually, no, a LINQ query like .Where(b => b.Author.Contains(author)) generates its own parameterized SQL underneath, the same LIKE-with-a-parameter pattern this lesson builds by hand. This course still hasn't introduced EF Core, so this lesson's SqlCommand version is what VirtualBookShelf actually uses for now.

Should every query in this course switch to LIKE now, even ones that only ever need an exact match? No. BookID = @BookID, the kind of exact match Lesson 6's navigation relies on, is both simpler to read and generally faster to run than a LIKE pattern search, since an exact match against a primary key can use SQL Server's index directly in a way a leading-wildcard pattern often can't. LIKE earns its place specifically where a visitor's input is a partial phrase rather than a known, exact value, a search box, not a lookup by ID.

What if a visitor types more than one word, a first and last name together? WHERE Author LIKE '%J.K. Rowling%' still works as a single pattern, matching the whole phrase as typed, spaces included. A more flexible search, matching each word independently so word order doesn't matter, would need separate LIKE conditions joined with AND, one per word, which goes beyond what this lesson's single search box needs but is a reasonable next step for a more capable search feature later.

Does this page need its own connection string, separate from the one Lesson 1 set up? No. Every page in this module reads the same connection string from appsettings.json, through the same IConfiguration injection Lesson 2 established; a search page is just another Razor Page using that same configuration, nothing about it needs its own database configuration.

What's Ahead

This lesson closed the loop from a visitor's own input to a safely parameterized query, read-only, through a GET request. That same loop, binding a value a visitor supplies to a parameter rather than to the SQL text itself, is exactly what any feature accepting input eventually needs, whether it's searching, inserting, or updating a record. The connection string, command, and parameter pattern built across this module carries forward unchanged into whatever this course builds next; what changes from here is what that input is used for, not how safely it gets there.