Lesson 1
Data Access using Web Programming
The previous three modules built VirtualBookShelf's database and the SQL statements that read and change its data: SELECT, WHERE, ORDER BY, UPDATE, INSERT, and DELETE, all written and run directly against SQL Server through Azure Data Studio. This module shifts from writing SQL by hand to running it from inside a web application: specifically, from an ASP.NET Core Razor Pages application built on .NET 10, talking to SQL Server 2025 through C# code instead of a query tool.
That shift has a direct predecessor. The original version of this course taught the same idea using Classic ASP, VBScript, and a COM-based data access layer called ADO, short for Active Data Objects, not to be confused with ADO.NET, its modern .NET successor and the subject of this module. Classic ASP pages retrieved rows into an object called a Recordset, issued SQL through an object called a Command, and displayed the results using Design Time Controls, a Visual InterDev feature with no direct modern equivalent, already covered in Module 2. Every one of those pieces has a named, current replacement, and this module introduces them one at a time rather than all at once.
How a Razor Page Actually Displays Data
A Razor Page doesn't use anything like a Design Time Control. Instead, a Razor Page is backed by a page model class, a C# class whose job is to prepare whatever data the page needs before the page itself renders. The page model retrieves data, in this module's case by running SQL against SQL Server through ADO.NET, and stores the result in a property. The page's markup, written in a mix of HTML and a templating syntax called Razor, then reads that property and turns it into HTML, often with the help of Tag Helpers, attributes that look like ordinary HTML attributes but run C# code to generate the actual markup.
At a preview level, without worrying about exact syntax yet, the shape looks like this. A page model class exposes a list of books:
public class CatalogModel : PageModel
{
public List<Book> Inventory { get; set; } = new();
public void OnGet()
{
// Inventory is filled in here using ADO.NET,
// covered later in this module.
}
}
and the page's markup loops over that property to build an HTML table:
<table>
@foreach (var book in Model.Inventory)
{
<tr>
<td>@book.Title</td>
<td>@book.Author</td>
<td>@book.Price</td>
</tr>
}
</table>
Nothing here is dragged onto the page from a toolbox the way a Design Time Control was. The page model decides what data exists, and the Razor markup decides how it's presented, a clean separation that Classic ASP's mix of inline script and HTML never really had. This module's later lessons fill in the one piece left blank above: how
OnGet actually populates
Inventory from VirtualBookShelf's
Inventory table.
Connection Strings: Where the Database Address Lives
Before any of the code above can run, the application needs to know where SQL Server actually is, which database to connect to, and how to authenticate. In Classic ASP, that information was often typed directly into the page that needed it, sometimes more than once across different pages. ASP.NET Core centralizes it instead, in a single configuration file named
appsettings.json, as a connection string:
{
"ConnectionStrings": {
"VirtualBookShelf": "Server=localhost;Database=VirtualBookShelf;Trusted_Connection=True;TrustServerCertificate=True"
}
}
Every page model that needs a
SqlConnection reads this same string rather than hardcoding it, so changing the database's address, say, moving from a local development server to a production one, means editing one file instead of hunting through every page that talks to the database. ASP.NET Core's dependency injection system, configured in a project's
Program.cs, is the usual way a page model gets access to this configuration without reading the file directly itself. The exact wiring belongs to this module's next lesson; for now, the point is simply that a connection string is a configuration value, not something written into application logic.
What ADO.NET Replaces: Recordset and Command
ADO.NET is the direct successor to classic ADO's Recordset and Command objects, and the mapping between old and new is close enough that anyone who worked with the legacy course's data access layer will recognize the shape immediately. Three objects do almost all of the work:
SqlConnection, which opens and holds a connection to SQL Server, replacing ADO's Connection object.
SqlCommand, which holds a SQL statement and any parameters it needs, replacing ADO's Command object.
SqlDataReader, which steps through the rows a query returns one at a time, replacing the row-by-row access a Recordset provided.
A typical sequence opens a connection, builds a command around a SQL statement, executes it, and reads the result:
using SqlConnection connection = new(connectionString);
connection.Open();
SqlCommand command = new("SELECT BookID, Title, Author, Price FROM Inventory", connection);
using SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
string title = reader.GetString(reader.GetOrdinal("Title"));
// ...and so on for each column
}
This is deliberately a preview, not a working lesson; the connection string, the exact column-reading calls, and where this code actually lives inside a page model are each covered properly later in this module. What matters here is the shape: write the SQL in Azure Data Studio first, confirm it returns what's expected, then move that exact statement into a
SqlCommand once it's correct. Testing a statement outside the application first, the same habit this course has been building since Module 3, saves time once that statement is wrapped in C# code and harder to inspect directly.
Rendering Results on the Page
Once a SqlDataReader has read a row, that data still has to get onto the page somehow. Classic ASP bound a Recordset directly to a data-bound control and let the control handle rendering. A Razor Page takes an extra, deliberate step: the page model copies each row's values into an ordinary C# object, such as the Book class hinted at above, and collects those objects into a list. The page's Razor markup then renders that list using a Tag Helper or a plain @foreach loop, as shown earlier.
The extra step is worth it. Because the page model's data is just a list of ordinary objects, it can be sorted, filtered, or passed to a unit test without touching SQL Server at all, something that was awkward at best with a live Recordset. This module's hands-on lessons build that habit from the first working example onward, rather than connecting a reader straight to markup the way ADO's bound controls once did.
Paging Through a Result Set
The legacy course's Design Time Controls included navigation controls, buttons a visitor could click to move forward and backward through a long result set one page at a time. Nothing in ASP.NET Core ships a drop-in equivalent, because paging in a modern web application is really just a SQL concept wearing a web page's clothing: retrieve a limited number of rows, remember how many rows have already been shown, and ask for the next batch when the visitor asks for it.
Concretely, that means a query like retrieving VirtualBookShelf's Inventory rows twenty at a time rather than all at once, tracking which batch, or page, the visitor is currently viewing, and changing which rows the query returns when they move to the next one. SQL Server's own syntax for this, OFFSET and FETCH NEXT, wasn't covered in Module 3 and is a natural fit for one of this module's later, hands-on lessons once a basic retrieval page is already working. For now, the idea to hold onto is simply that paging is a query pattern, not a control you drag onto a page.
Parameterized Queries: Still Essential
Of everything this lesson has walked through, one objective from the original course carries forward completely unchanged: using parameterized queries to select data that meets specific criteria. This was true with classic ADO, and it's just as true with ADO.NET, because the underlying risk it guards against, SQL injection, has nothing to do with which data access library an application happens to use.
The unsafe way to build a filtered query is to paste a value directly into a SQL string:
string unsafeSql = "SELECT * FROM Customer WHERE LastName = '" + lastName + "'";
If
lastName comes from something a visitor typed into a search box, a visitor who types
O'Brien' OR '1'='1 instead of an ordinary last name can change what the query actually does, potentially returning every row in the table or worse. A
SqlCommand avoids this entirely by keeping the value separate from the SQL text and letting ADO.NET handle it safely:
SqlCommand command = new("SELECT * FROM Customer WHERE LastName = @LastName", connection);
command.Parameters.AddWithValue("@LastName", lastName);
Here,
@LastName is a placeholder, and whatever value gets assigned to it is treated strictly as data, never as part of the SQL statement itself, no matter what characters it contains. Any lesson in this module that filters VirtualBookShelf's data by something a visitor supplies builds its
SqlCommand this way, with no exceptions.
Handling Errors in a Data-Access Layer
The original course's final objective, handling errors to keep the application stable, also carries forward, reframed for C# instead of VBScript. VBScript leaned on statements like
On Error Resume Next and
On Error GoTo, which quietly continue execution after a failure unless a developer remembers to check for one explicitly. .NET uses structured exception handling instead: a
try block wraps code that might fail, and a
catch block runs only when something actually does.
try
{
using SqlConnection connection = new(connectionString);
connection.Open();
// ...run a command here...
}
catch (SqlException ex)
{
// Log the error and show the visitor something
// more useful than a stack trace.
}
A database can fail to respond for reasons that have nothing to do with the SQL statement itself: the server might be down for maintenance, a network connection might drop, or a query might simply time out under load. Wrapping data access in
try/
catch means one of these failures produces a handled error message instead of crashing the whole page, exactly the stability the original objective was reaching for, with a mechanism built directly into the language rather than bolted on statement by statement.
Module Objectives
With Classic ASP's data access layer replaced piece by piece above, this module's six objectives are:
- Retrieve data with ADO.NET and bind the result to a Razor Page's model.
- Use the
SqlCommand object, together with SqlDataReader, to retrieve data from SQL Server.
- Render query results into a Razor Page's markup using Tag Helpers.
- Page through a result set rather than retrieving it all at once.
- Use parameterized queries to select data that meets specified criteria.
- Handle errors with structured exception handling to keep the application stable.
Each objective maps directly onto something the legacy course already taught, Recordsets, Command objects, Design Time Controls' navigation and data binding, parameterized ADO queries, and VBScript's own error handling, just expressed in ADO.NET and Razor Pages terms instead.
Frequently Asked Questions
Is ADO.NET the only way to access SQL Server from an ASP.NET Core application? No. Entity Framework Core, a higher-level data access technology also built for .NET 10, is the other major option, and this course introduces it later, once ADO.NET's fundamentals are in place. EF Core lets a developer work with C# objects almost entirely and generates the SQL behind the scenes, while ADO.NET, this module's subject, works directly with SQL statements and is closer to what the legacy course's Recordset and Command objects did. Learning ADO.NET first makes EF Core's behavior easier to understand later, since it's no longer a black box.
What does the using keyword do in the SqlConnection example? A database connection is a limited resource, and SqlConnection implements an interface called IDisposable that guarantees it can be cleaned up properly. Wrapping it in a using block tells C# to close and release the connection automatically once that block finishes, even if an error occurs partway through, rather than relying on a developer to remember to close it manually the way Classic ASP sometimes required.
Does SqlDataReader load an entire result set into memory before an application can use it? No. A SqlDataReader is forward-only and reads one row at a time directly from the stream SQL Server sends back, which is part of why it's fast but also why its data has to be copied into ordinary objects, as this lesson described, before it can be sorted, filtered, or held onto after the connection closes.
Can SQL Server 2025 features from Module 3, like REGEXP_LIKE or JSON_CONTAINS, be used through ADO.NET? Yes, without any special handling. A SqlCommand simply carries whatever valid SQL text is assigned to it; ADO.NET has no awareness of which specific functions or clauses a statement uses. Anything that runs correctly in Azure Data Studio, including a WITH clause defining a common table expression, or a comparison against a vector column using VECTOR_DISTANCE, runs the same way once that statement is handed to a SqlCommand instead. None of those are covered in this introductory lesson, but none of them require a different approach from the one shown here either.
Is asynchronous programming, async and await, required for database calls in ASP.NET Core? Not strictly, but it's common in production code, since it lets a web server handle other requests while waiting on a slow database call instead of sitting idle. This lesson's examples use the synchronous forms, such as ExecuteReader, to keep the preview focused on ADO.NET's core objects; this module's hands-on lessons can introduce the asynchronous equivalents, like ExecuteReaderAsync, once the synchronous pattern is solid.
Can a Razor Page call a stored procedure instead of sending inline SQL text? Yes. A SqlCommand can be configured to execute a stored procedure by name instead of a raw SQL string, passing parameters the same way a parameterized inline query does. This course hasn't covered writing stored procedures yet, so this module sticks to inline SQL statements, the direct equivalent of what the legacy ADO Command object ran.
What should happen inside the catch block when a database call fails? At minimum, the error should be recorded somewhere a developer can find it later, typically through ASP.NET Core's built-in logging rather than a hand-rolled solution, and the visitor should see a plain, non-technical message rather than the raw exception. Showing a visitor the full text of a SqlException, including server names or connection details, is a real security concern as well as a poor experience, which is one more reason the try/catch pattern shown earlier matters beyond simply keeping the page from crashing.
What's Ahead
This lesson stayed at the preview level on purpose: naming SqlConnection, SqlCommand, and SqlDataReader, and showing roughly where each one fits, without yet writing a complete, working example. The next lesson in this module picks up exactly where this one left off, building a real connection string, pointing it at SQL Server 2025, and opening an actual SqlConnection to VirtualBookShelf's database from inside a Razor Page.
From there, this module works through retrieving VirtualBookShelf's data with ADO.NET, displaying it on a page, filtering it safely with parameterized queries, and handling the errors that come up along the way, the same ground this lesson just surveyed, covered one hands-on lesson at a time.
It's worth pausing on how much of this module's shape was already set by Modules 2 and 3. Module 2 established VirtualBookShelf's Customer and Inventory tables; Module 3 wrote and ran the SQL statements that read and change their data. Every example this module builds, from the first working SqlConnection to the last error-handling lesson, runs that same SQL against those same two tables, just from inside an application instead of from Azure Data Studio. Nothing about the database changes here, only how a web page reaches it.
