Data Access  «Prev  Next»

Module 4 Conclusion

Data Access and Display: What This Module Built

Module 3 wrote and ran every statement that reads or changes VirtualBookShelf's data, directly in Azure Data Studio, against its own Customer and Inventory tables. This module took that same SQL and moved it inside an actual web application, an ASP.NET Core Razor Pages application on .NET 10, talking to SQL Server 2025 through C# rather than a query tool. Nine lessons later, every piece of the legacy course's own data-access layer, the classic ADO Recordset and Command objects, Visual InterDev's Design Time Controls, VBScript's own error handling, has a named, working modern replacement, built one piece at a time rather than all at once.

This page pulls those nine lessons together: what each one built, how the legacy and modern pieces actually line up, and where this module's six stated objectives stand now that all nine are finished.

The Nine Lessons, in Order

Lesson 1: Data Access Using Web Programming
Previewed ADO.NET's three core objects, SqlConnection, SqlCommand, and SqlDataReader, against their classic ADO ancestors, Connection, Command, and Recordset, and named this module's six objectives before any of them had working code behind them yet.

Lesson 2: Binding Data-Bound Design Time Controls
Built the first complete, working round trip: a connection string in appsettings.json, read through IConfiguration, an open SqlConnection, a plain SELECT against Inventory, and a SqlDataReader copying each row into a Book object for a Razor Page's @foreach loop to render. Sorted Visual InterDev's full Design-Bound Control toolbox into what actually needed a modern replacement, Recordset, Grid, and RecordsetNavbar, and what didn't, since CSS and JavaScript handle what PageObject and Timelines once did.

Lesson 3: Creating a Command Object to Query the Database
Covered SqlCommand's three ways of running, ExecuteReader for rows, ExecuteNonQuery for an INSERT, UPDATE, or DELETE's affected-row count, and ExecuteScalar for a single value, alongside CommandType.Text versus CommandType.StoredProcedure for calling a procedure that already exists on the server.

Lesson 4: Smart Client Architecture
Stepped back from SQL entirely to ask where application logic should run, in the browser or on the server, placed Razor Pages' server-rendered default on that spectrum next to Classic ASP's own thin-client history, and introduced Tag Helpers, asp-page, asp-route-id, asp-for, as the mechanism this module's later lessons build forms and links with.

Lesson 5: Displaying Data Using Bound Controls
Added [Display(Name = "...")] and [DataType(...)] attributes to the Book and Customer classes, letting @Html.DisplayNameFor and @Html.DisplayFor generate a table's headers and formatted values straight from the model's own metadata instead of retyping each one by hand.

Lesson 6: Adding Navigation Controls to Move Between Records
Built a single-record navigator, First, Previous, Next, and Last links driven by a BookID route value and four ExecuteScalar queries, replacing ASP.NET Web Forms' ViewState-based approach with a design that needs no server-side memory between requests at all, since the URL itself carries which record is current.

Lesson 7: Selecting Records Based on User Input
Closed the loop from a visitor's own search box to a parameterized query: a [BindProperty(SupportsGet = true)]-bound form field, a LIKE pattern built safely in C# rather than concatenated into SQL text, and a direct side-by-side showing exactly what string interpolation would have gotten wrong.

Lesson 8: Scripting with Recordsets
Translated the legacy Recordset object's EOF, moveNext, and fields.getValue(i) pattern line by line onto SqlDataReader's Read(), FieldCount, and GetValue(i), rebuilding the legacy script's header-loop-then-row-loop structure against VirtualBookShelf's real Inventory table.

Lesson 9: Defensive Programming, Handling Errors
Built error handling in three layers, TRY...CATCH/THROW inside a SQL Server stored procedure, ordered SqlException-then-Exception catching in the page model, and global exception-handling middleware, replacing VBScript's On Error Resume Next with structured exception handling throughout.

From Recordset and Command to SqlConnection, SqlCommand, and SqlDataReader

One thread runs through every lesson in this module, named explicitly in Lesson 1 and returned to again and again: classic ADO's object model and ADO.NET's object model solve the same problems, under different names and with real safety improvements along the way. SqlConnection replaces ADO's Connection object, opening and holding the link to SQL Server. SqlCommand replaces ADO's Command object, holding a SQL statement or a stored procedure's name along with whatever parameters it needs, a job Lesson 3 covered in full. SqlDataReader replaces the Recordset's row-by-row access, and Lesson 8 went a step further, mapping the Recordset's own EOF, moveNext, and fields collection directly onto SqlDataReader's equivalent members one for one.

What changed alongside the renaming matters more than the renaming itself. A classic ADO Command built from a value pasted directly into a SQL string had no built-in defense against SQL injection; SqlCommand's Parameters collection, used in every single example since Lesson 3, keeps a value separate from the statement text no matter where that value came from, a visitor's search box included, as Lesson 7 demonstrated directly. Design Time Controls bound a page to a still-open Recordset and let the control handle rendering; a Razor Page copies each row into an ordinary Book or Customer object first, closing the connection before the page ever renders, which is exactly why that data can be sorted, filtered, or tested without touching SQL Server again. Neither improvement required abandoning the underlying idea classic ADO already had right: a connection, a command, and a way to step through results. This module replaced the implementation, not the concept.

The Six Module Objectives, Revisited

Lesson 1 named six objectives before any of them had working code behind them. Checking back against what actually got built is worth doing honestly, including the one place this module's scope shifted along the way:
  1. Retrieve data with ADO.NET and bind the result to a Razor Page's model. Delivered in Lesson 2, and used in every lesson since.
  2. Use the SqlCommand object, together with SqlDataReader, to retrieve data from SQL Server. Delivered in Lesson 3, with ExecuteReader, ExecuteNonQuery, and ExecuteScalar all covered, plus CommandType.StoredProcedure for calling an existing procedure.
  3. Render query results into a Razor Page's markup using Tag Helpers. Delivered across Lessons 4 and 5, asp-page/asp-route-id for links, asp-for/asp-validation-for for forms, and @Html.DisplayNameFor/DisplayFor for attribute-driven table rendering.
  4. Page through a result set rather than retrieving it all at once. This is the one objective worth being precise about rather than claiming cleanly met. Lesson 6 built real navigation, First, Previous, Next, and Last links moving one record at a time through Inventory, and it explicitly distinguished that from true SQL-level list paging, retrieving a limited batch of several rows at once with OFFSET and FETCH NEXT. That second, originally previewed capability was never actually built in this module; single-record navigation is a related but genuinely different feature, and it's the one this module delivered.
  5. Use parameterized queries to select data that meets specified criteria. Delivered thoroughly, an exact-match WHERE BookID = @BookID in Lesson 6, a LIKE-based partial match in Lesson 7, with a direct unsafe-versus-safe comparison showing exactly what string concatenation would have risked.
  6. Handle errors with structured exception handling to keep the application stable. Delivered in Lesson 9, across all three layers, SQL Server, the page model, and the application's own global middleware.
Five of six objectives landed exactly as scoped. The fourth is the one honest exception, and it's worth saying so plainly rather than quietly treating single-record navigation as though it had always been the same thing as list-level paging.

Why Every Query Was Tested in Azure Data Studio First

One habit, not a single piece of syntax, carried through every lesson in this module without exception: confirm a statement directly in Azure Data Studio before it goes anywhere near a SqlCommand. Lesson 2 raised it first, Lesson 3 extended it to UPDATE and DELETE, where an overly broad WHERE clause changes real data rather than just returning the wrong rows, Lesson 6 used it to confirm a boundary case, what a neighbor query returns when there is no neighbor, and Lesson 9 extended it one step further, deliberately testing the failure path, a THROW that should fire, and not only the success path. C# has no way to check a SQL string's contents against the actual database schema while compiling, so a mistake inside a SqlCommand's text only surfaces once the page actually runs, as a SqlException, not a compiler error. Catching that mistake in a tool built for exactly this, before it's buried inside application code, is the one piece of advice that applies to literally everything else this module covered.

Parameterized Queries, End to End

Module 3 established the danger, a visitor's input spliced directly into SQL text, and the defense, a named parameter keeping that value separate from the statement itself. This module carried that discipline through every single query that touched a value from outside the application, with no exceptions anywhere across nine lessons. Lesson 3's ExecuteNonQuery examples parameterized an INSERT's every value. Lesson 6 parameterized an exact BookID match used for navigation. Lesson 7 extended the same discipline to a LIKE pattern, building the wildcard in C# rather than in the SQL string, and showed the unsafe alternative directly rather than only describing it. Lesson 9's stored procedure parameterized both of its inputs before ever reaching the database, and its error handling made a point of never echoing a raw, potentially schema-revealing error message back to a visitor either, a different kind of leak than SQL injection, but the same underlying principle: nothing that originates outside the application's own trusted code reaches somewhere sensitive unfiltered.

Frequently Asked Questions

Did this module ever actually use Entity Framework Core? No, by design. EF Core was named in nearly every lesson as this course's later data-access topic, and Lesson 4 made the point directly that EF Core answers a different question entirely from where application logic lives. ADO.NET, taught directly throughout this module, is what EF Core generates underneath its own API; understanding it first is what makes EF Core's behavior legible once this course reaches it.

Which lesson in this module would be hardest to skip? Lesson 3. Every later lesson's data access runs through SqlCommand, and every one of its three execution methods, ExecuteReader, ExecuteNonQuery, ExecuteScalar, gets used somewhere later in this module, ExecuteScalar especially, reused four times over in Lesson 6 alone.

Was anything in this module actually new to SQL Server 2025, rather than just new ADO.NET syntax? Less than it might seem. Most of this module's material, SqlConnection, SqlCommand, SqlDataReader, Tag Helpers, data annotations, structured exception handling, is ADO.NET and ASP.NET Core material that predates SQL Server 2025 by years. Native JSON support, vector search, and Optimized Locking came up at FAQ level in a few lessons as real SQL Server 2025 capabilities, but VirtualBookShelf's own schema never used any of them directly in this module's examples.

Why did Lesson 6 build single-record navigation instead of the list-level paging Lesson 1 originally previewed? Because they're genuinely different features, not two versions of the same one. Reading Lesson 6's actual legacy content made that clear partway through this module, and rather than stretching single-record navigation to also claim it delivered list paging, this module built the feature its own legacy material actually described and said so directly, both in Lesson 6 itself and again in this conclusion's objectives review above.

Does this module's error handling in Lesson 9 cover every kind of failure a page could have? No, and it was never meant to. Lesson 9 covered database-originated failures specifically, connection problems, constraint violations, timeouts, deadlocks, plus a general catch-all for anything else. A validation failure, a visitor submitting an incomplete form, is handled by ModelState.IsValid instead, a different mechanism this module's earlier lessons already relied on without needing try/catch at all.

Could VirtualBookShelf's Customer and Inventory tables have changed structurally anywhere in this module? No. Every lesson in this module read from or wrote to the exact schema Module 2 defined, Customer(CustomerID, FirstName, LastName, Email, Phone, DateJoined) and Inventory(BookID, Title, Author, ISBN, Price, QuantityOnHand), unchanged. This module changed how the application reaches that schema, never the schema itself.

What's the one habit from this module worth carrying forward past it? Testing first, covered above, and parameterizing everything that touches outside input, covered just before it. Both showed up in every lesson this module built, and neither one depends on anything specific to Razor Pages or SQL Server 2025; they apply to any application talking to any database.

How does this module's structure compare to Module 3's? Module 3 compared one statement three ways in every lesson, raw SQL, ADO.NET, and a preview of Entity Framework Core, to prove the three were always generating the same thing underneath. This module instead compared one era to another in every lesson, the legacy Classic ASP and classic ADO approach against its modern ADO.NET and Razor Pages replacement, to show that the underlying problems, connecting to a database, running a command, stepping through results, handling an error, haven't changed even though nearly every piece of syntax used to solve them has.

Did any lesson in this module reuse code or classes from an earlier one, rather than starting over each time? Constantly, and that reuse is itself part of what this module was demonstrating. The Book class built in Lesson 2 gained Display attributes in Lesson 5 without its four original properties changing at all, then got rendered one record at a time in Lesson 6 using that same attribute-driven markup. The connection string set up once in Lesson 2 never needed touching again through Lesson 9. Building incrementally on the same handful of classes and the same configuration, rather than introducing a new one in every lesson, is closer to how an actual application grows than nine unrelated examples would have been.

What's Ahead

Before moving on, the Module 4 quiz below is worth taking, a check across ADO.NET's core objects, Tag Helpers, parameterized queries, and structured exception handling, the ground this module covered across all nine lessons.

Everything built across this module carries forward unchanged. The connection string established in Lesson 2, the Book and Customer classes built out with Display attributes in Lesson 5, and the parameterization and error-handling habits reinforced in every lesson since, all stay exactly as they are. What's still open, named directly above rather than left implicit, is true SQL-level list paging with OFFSET and FETCH NEXT, a capability this course previewed back in Lesson 1 but hasn't yet built; whenever this course returns to it, it builds on top of everything this module already put in place, the same connection, the same two tables, the same parameterization discipline, rather than replacing any of it.

Parameterized Queries - Quiz

In this Quiz, you will test your knowledge of data access using parameterized queries.
Parameterized Queries - Quiz

SEMrush Software 10 SEMrush Banner 10