| Lesson 6 | Direct Database Access |
| Objective | Execute database commands directly, without a Design Time Control standing in the way. |
Direct Database Access
Every lesson in this module so far has built a handler by writing a
The original version of this course taught the same idea, but from the opposite direction, because the tooling of its own era made the drag-and-drop path the default one. Visual InterDev's Design Time Controls (DTCs) and data-bound controls made it genuinely easy to add database access to a page: drag a Recordset control onto the page, bind a text box to one of its fields, and a working display appeared with very little code written by hand. That ease came with two real costs the original lesson named directly, and both still apply to any generated or scaffolded database tooling today. First, a DTC gave you whatever interface it was built to give you, and building something with a different look or a different interaction model meant fighting the control rather than using it. Second, a DTC's generated code and runtime support added weight to a page that a short, purpose-built block of hand-written script did not carry. Writing your own code instead of relying on a drag-and-drop control bought you a custom interface and a lighter page then, and hand-written Razor Pages code against VirtualBookShelf's own schema buys you the same two things now, for the same reasons.
SqlCommand directly: a connection string, a parameterized statement, a call to ExecuteNonQueryAsync or ExecuteScalarAsync, and nothing standing between that code and VirtualBookShelf's own Customer and Inventory tables. That's worth stopping to name explicitly, because it's exactly what this lesson is about. There's no drag-and-drop control generating that code on your behalf, no bound GridView or SqlDataSource wiring up the query for you behind the scenes, and no designer surface to configure. You've been doing direct database access since Module 4, one handler at a time, without a visual tool standing between your code and the database.
The original version of this course taught the same idea, but from the opposite direction, because the tooling of its own era made the drag-and-drop path the default one. Visual InterDev's Design Time Controls (DTCs) and data-bound controls made it genuinely easy to add database access to a page: drag a Recordset control onto the page, bind a text box to one of its fields, and a working display appeared with very little code written by hand. That ease came with two real costs the original lesson named directly, and both still apply to any generated or scaffolded database tooling today. First, a DTC gave you whatever interface it was built to give you, and building something with a different look or a different interaction model meant fighting the control rather than using it. Second, a DTC's generated code and runtime support added weight to a page that a short, purpose-built block of hand-written script did not carry. Writing your own code instead of relying on a drag-and-drop control bought you a custom interface and a lighter page then, and hand-written Razor Pages code against VirtualBookShelf's own schema buys you the same two things now, for the same reasons.
What the Data Environment Object Did
The specific tool the original lesson used to write script-based database access, as opposed to dragging a DTC onto the page, was the Data Environment object, usually just called
Getting to that point required two things: a database connection, defined once at the Data Environment level, and one or more command objects built against that connection, each one wrapping a specific SQL statement or stored procedure call. The page also had to have its Scripting Object Model, or SOM, enabled, a classic ASP setting that exposed the collection of objects a page could script against. None of that machinery has a direct equivalent in a modern ASP.NET Core project, and it doesn't need one: there's no separate "object model" to switch on before a Razor Page can talk to a database, because the ability to do so is simply part of what the framework and its dependency injection container already provide to every page.
A typical block of script using the Data Environment looked like this:
DE. The Data Environment was a centralized definition of a database connection and a set of named command objects, configured once and stored in the site's Global.asa file. Once defined, every one of those named commands became callable as if it were a method directly on the DE object itself: a command named GetInventory could be run with a line as short as DE.GetInventory, and if that command returned rows, the Data Environment runtime automatically built a Recordset for you, named by convention with an rs prefix, such as DE.rsGetInventory, ready to loop over immediately.
Getting to that point required two things: a database connection, defined once at the Data Environment level, and one or more command objects built against that connection, each one wrapping a specific SQL statement or stored procedure call. The page also had to have its Scripting Object Model, or SOM, enabled, a classic ASP setting that exposed the collection of objects a page could script against. None of that machinery has a direct equivalent in a modern ASP.NET Core project, and it doesn't need one: there's no separate "object model" to switch on before a Razor Page can talk to a database, because the ability to do so is simply part of what the framework and its dependency injection container already provide to every page.
A typical block of script using the Data Environment looked like this:
<%
thisPage.CreateDE()
DE.GetInventory
set rs = DE.rsGetInventory
rs.moveFirst
%>GetInventory command, which the Data Environment had been configured to execute as a query returning every row in the Inventory table. The third line points a local rs variable at the Recordset the Data Environment built automatically from that query's results. The fourth line calls the Recordset's moveFirst method to make sure iteration starts at the first row before any of the navigation or display techniques covered earlier in this course run against it.
You Already Have Direct Database Access
Set the Data Environment's own code next to any handler already built in this module and the parallel is close to exact.
DE.GetInventory runs a named, pre-configured command with no inline SQL visible at the call site. A direct SqlCommand call does the same thing, just with the SQL visible right there in the handler instead of hidden behind a name configured elsewhere:
using SqlConnection connection = new(_connectionString);
await connection.OpenAsync();
SqlCommand command = new(
"SELECT BookID, Title, QuantityOnHand FROM Inventory",
connection);
using SqlDataReader reader = await command.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
// read each row directly, the same role rs.moveFirst and
// the Recordset's own navigation played in the DE example above
}SqlCommand is that same thing, written in a different language against a different database engine, two decades later.
Giving a Command a Name
One part of the Data Environment is genuinely worth carrying forward rather than treating purely as history: the idea that a command gets a name once and is called by that name afterward, instead of being rebuilt inline every single time it's needed. Every
It's also worth being just as clear about when naming a command this way is the wrong move. The Data Environment named every command, whether or not that command was ever called from more than one place, because naming commands in advance was simply how the tool worked. A page model doesn't carry that constraint. The Update, Insert, and Delete handlers built earlier in this module each run their own statement exactly once, from exactly one place, and giving any of them a separate named method would add a layer of indirection with nothing to show for it. A named helper method earns its place when a query is genuinely shared, not as a default habit applied to every
SqlCommand built so far in this module has lived inline inside its own handler, which is the right choice for a command a page uses exactly once. A query that several different handlers or several different pages need, on the other hand, is worth naming the same way a Data Environment command was named: as a small, private helper method on the page model.
private async Task<List<InventoryItem>> GetLowStockInventoryAsync(int threshold)
{
List<InventoryItem> items = new();
using SqlConnection connection = new(_connectionString);
await connection.OpenAsync();
SqlCommand command = new(
"SELECT BookID, Title, QuantityOnHand FROM Inventory " +
"WHERE QuantityOnHand < @Threshold",
connection);
command.Parameters.AddWithValue("@Threshold", threshold);
using SqlDataReader reader = await command.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
items.Add(new InventoryItem
{
BookId = reader.GetInt32(0),
Title = reader.GetString(1),
QuantityOnHand = reader.GetInt32(2)
});
}
return items;
}await GetLowStockInventoryAsync(5) from an OnGetAsync handler reads almost as directly as DE.GetInventory did, with the SQL, the parameter, and the row-reading logic defined once and reused by name rather than retyped at every call site:
public async Task OnGetAsync()
{
LowStockItems = await GetLowStockInventoryAsync(5);
}SqlCommand, which is as far as this course's stack needs to go to recover the one genuinely useful idea the Data Environment was built around.
It's also worth being just as clear about when naming a command this way is the wrong move. The Data Environment named every command, whether or not that command was ever called from more than one place, because naming commands in advance was simply how the tool worked. A page model doesn't carry that constraint. The Update, Insert, and Delete handlers built earlier in this module each run their own statement exactly once, from exactly one place, and giving any of them a separate named method would add a layer of indirection with nothing to show for it. A named helper method earns its place when a query is genuinely shared, not as a default habit applied to every
SqlCommand in a project.
Where the Connection Information Lives Now
The Data Environment's connection details lived in
Global.asa, defined once at the application level so every page could reach the database without redefining the connection string itself. The modern equivalent is a connection string stored in appsettings.json and read through the configuration system, then handed to each page model's constructor by the framework's dependency injection container, exactly as every page model in this course has already been built. The storage location changed from a classic ASP global file to a JSON configuration file read through IConfiguration, but the underlying idea, define the connection once and let every page reach it without repeating it, carried forward unchanged.
What Actually Replaced the Data Environment Object
The Data Environment object does not exist in Visual Studio 2026, any more than it existed in the Visual Studio versions between its retirement and now. A handful of legitimate modern options exist for talking to a database from a .NET project, and it's worth naming all of them plainly rather than pretending only one exists:
- ADO.NET, using
SqlConnectionandSqlCommanddirectly, is the option this course has used throughout, and the one this lesson has just shown is the direct modern descendant of the Data Environment's own "run a command directly" philosophy. - Entity Framework Core is a full object-relational mapper that maps C# classes to database tables and can generate most of its own SQL. It's a legitimate, widely used option; this course has deliberately stayed with direct ADO.NET throughout so that the SQL being run is always visible in the code that runs it, rather than generated on your behalf.
- SQL Server Object Explorer, built into Visual Studio, lets you browse connections, tables, and views and run queries interactively, playing a role closer to a database browser than to an application's own data-access code.
Why This Course Keeps Choosing ADO.NET Over an ORM
It's worth stating the reasoning behind that choice plainly, since Entity Framework Core is a perfectly legitimate tool and plenty of production applications are built on it. Every example in this course has used direct ADO.NET specifically because the point of a course about database access is seeing the database access. A hand-written
SqlCommand shows exactly what statement runs, exactly which parameters it carries, and exactly when the connection opens and closes. An ORM's generated SQL is often just as correct, and frequently more convenient to write, but it's generated, which means a learner working through the SQL lessons earlier in this course would be looking at C# code and trusting that the right statement eventually ran, rather than seeing the statement itself. That trade is worth making differently once the fundamentals are solid; it isn't the right trade for the lessons teaching the fundamentals in the first place.
What a Custom Interface Looks Like in This Stack
The original lesson's argument for writing your own script instead of relying on a DTC was the ability to build a distinctive interface, one that looked and behaved exactly as intended rather than however the DTC happened to render it. Every Razor Pages form built across this module has already been that custom interface: hand-written markup, Tag Helpers like
asp-for and asp-page-handler wired up explicitly rather than generated by a scaffolding tool, and a layout chosen deliberately rather than inherited from a control's default rendering. A scaffolded CRUD page generated by a tool is this course's closest equivalent to a DTC: fast to produce, reasonable for a quick administrative screen, and the wrong choice whenever the page needs to look or behave like something other than a generic grid.
Frequently Asked Questions
Is there a direct ASP.NET Core replacement for the Data Environment object itself?
Not as a single named object, no. Its two jobs split apart: the connection-string storage moved to appsettings.json plus dependency injection, and the named-command idea moved to whatever shape fits the code, a private helper method for a simple case, a small data-access class for a larger one.
Why didn't this lesson use Entity Framework Core, since it's the most direct tool for mapping a Data Environment's objects to C# classes?
Because the Data Environment's core feature was running a command directly, without an abstraction layer managing it. A direct SqlCommand call preserves that same directness; introducing an ORM here would replace one layer of abstraction (the DTC and the Data Environment) with another, rather than removing the abstraction the way this lesson's objective asks for.
What was the Scripting Object Model, and does anything need to be "enabled" today the way it did then?
The SOM was a classic ASP setting that exposed a page's available scripting objects, and had to be turned on before a page could use them. Nothing comparable needs to be switched on in ASP.NET Core; a page model's constructor simply declares what it needs, and dependency injection provides it automatically.
Did classic ADO have any gotchas around running commands directly, the way this module has flagged a few ADO.NET pitfalls?
Yes. Two are worth knowing even though neither applies to a stateless SqlCommand call. A stored procedure containing more than one SELECT or UPDATE statement could cause ADO to misreport the number of affected rows unless the procedure began with SET NOCOUNT ON, because every intermediate statement sent its own row-count signal back to the client. And calling a Data Environment command a second time while its previously auto-generated Recordset was still open raised error 3705, "Operation is not allowed when the object is open," requiring an explicit check and close first. A directly built SqlCommand has no equivalent state to manage between calls, so neither situation can occur.
Is a private helper method wrapping a SqlCommand the same thing as the Repository pattern?
No. The Repository pattern is a broader architectural choice involving interfaces and typically a full abstraction over an entire table or aggregate. A single private method wrapping one query is a much smaller step, useful specifically when one query is called from more than one place, and it doesn't commit the rest of the code to any larger pattern.
Does the custom-interface argument still hold up against modern scaffolding tools, not just old DTCs?
Yes, for the same reason. A scaffolded page generates a working interface quickly, in exchange for looking and behaving like whatever the scaffolding template produces. The trade is identical to the one the original lesson described for DTCs: speed for a generic result, versus hand-written code for a specific one.
What was Visual InterDev, and why does this course keep referencing a tool from a previous version of Visual Studio?
Visual InterDev was Microsoft's web development IDE for classic ASP, the predecessor environment to the ASP.NET tooling built into Visual Studio today, and it's where the Data Environment object and its DTCs originally lived. It's referenced here only because the legacy course content being modernized in this lesson was written against it; nothing about Visual InterDev itself carries forward into a Visual Studio 2026 project.
Looking Back Across Module 5
Each lesson in this module replaced one specific piece of the drag-and-drop, DTC-driven approach to database access with a small, direct equivalent written by hand. Lesson 1 previewed all three data-changing operations and the
This lesson is where that arc closes. The Data Environment object, like the DTCs it worked alongside, existed to let a page talk to a database without a developer writing the connection, command, and result-handling code by hand. Every lesson since Module 4 has written exactly that code by hand, directly, against VirtualBookShelf's real schema, which means the objective at the top of this page was already satisfied before this lesson introduced a single new line of it. What this lesson adds is the vocabulary to recognize that, and one small refinement, naming a shared query once as a private helper method, worth keeping for the rare case where a query really is called from more than one place.
ExecuteNonQueryAsync pattern underneath them. Lesson 2 built an Update handler against a parameterized SqlCommand, replacing a Recordset's updateRecord method and its keyset-cursor requirement. Lesson 3 built an Insert handler, replacing the two-step addRecord/updateRecord sequence with a single statement and an OUTPUT INSERTED clause for the new identity value. Lesson 4 built a Delete handler, the simplest of the three statements, and closed with a side-by-side comparison of all three SQL shapes. Lesson 5 named the type-coercion concern that had been quietly sitting underneath every parameter in every one of those commands, replacing VBScript's CInt family with ASP.NET Core's model binding and TryParse methods.
This lesson is where that arc closes. The Data Environment object, like the DTCs it worked alongside, existed to let a page talk to a database without a developer writing the connection, command, and result-handling code by hand. Every lesson since Module 4 has written exactly that code by hand, directly, against VirtualBookShelf's real schema, which means the objective at the top of this page was already satisfied before this lesson introduced a single new line of it. What this lesson adds is the vocabulary to recognize that, and one small refinement, naming a shared query once as a private helper method, worth keeping for the rare case where a query really is called from more than one place.
This is the final hands-on lesson in Module 5. The next lesson wraps up the module with a quiz covering Update, Insert, Delete, type coercion, and the direct database access this lesson has just named explicitly as something you've already been doing.