Modern Scenario: VirtualBookShelf.com using SQL Server, .NET and EF Core

Modern, maintainable tools and development practices:
✅ 1. Define Database and Stored Procedure in SQL Server (SSMS or Azure Data Studio)
Use SQL Server Management Studio (SSMS) or Azure Data Studio to create the database schema and any stored procedures^[1] (if needed):

-- Example: Stored Procedure to get special books
CREATE PROCEDURE usp_GetSpecialBooks
AS
BEGIN
    SELECT * FROM Books WHERE IsSpecial = 1;
END

🔹 2. Connect to Database from Visual Studio (via EF Core)
Use Visual Studio 2022+ with .NET 6/7/8 and install Entity Framework Core via NuGet:

dotnet add package Microsoft.EntityFrameworkCore.SqlServer
dotnet add package Microsoft.EntityFrameworkCore.Design

🔹3. Create a DbContext and Entity Model
Use Scaffold-DbContext to auto-generate model and context from the existing VirtualBookShelf database:

Scaffold-DbContext "Server=.;Database=VirtualBookShelf;Trusted_Connection=True;" 
Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models

This generates:

• VirtualBookShelfContext.cs
• Book.cs (or other entities)

🔹 4. Execute Stored Procedure Using EF Core
Inside your `.NET` application (e.g., an ASP.NET MVC Razor page or Web API controller):

public class SpecialsController : Controller
{
   private readonly VirtualBookShelfContext _context;

   public SpecialsController(VirtualBookShelfContext context)
   {
       _context = context;
   }

   public async Task<IActionResult> Index()
   {
       var specials = await _context.Books
                                    .FromSqlRaw("EXEC usp_GetSpecialBooks")
                                    .ToListAsync();
       return View(specials);
   }
}

🔹5. Display Results in Razor View (Specials.cshtml)

@model IEnumerable<Book>

Special Books

@foreach (var book in Model) { }

Title	Author	Price
@book.Title	@book.Author	@book.Price.ToString("C")

✅ Summary of Modern Stack

Component	Legacy Equivalent	Modern Replacement
Command Object	Visual InterDev DTC	EF Core (FromSqlRaw or LINQ)
Recordset	Recordset DTC	DbSet<T> and LINQ Queries
Stored Procedure Execution	Classic ADO Command.Execute	FromSqlRaw() in EF Core
Specials.asp	ASP Page	ASP.NET Core Razor Page or Controller + View
Data Connection	OLE DB / DSN	Connection string in appsettings.json or secret

The concept of a "command object" is part of the application layer, specifically in .NET programming and not the database engine. ✅ Clarification:

• In Visual Basic / Visual InterDev (legacy), a "command object" was created in code to send SQL or stored procedure calls to the SQL Server.
• In modern .NET (e.g., ASP.NET Core with ADO.NET or EF Core), we still use command objects—but in a much more abstracted or efficient form.

🔹 Modern Practice in SQL Server 2022 Environments:

Layer	Modern Practice
SQL Server Engine	You create stored procedures, views, and functions.
.NET Application	Use SqlCommand (in ADO.NET) or LINQ + FromSqlRaw (EF Core).
Best Practice	Use parameterized commands to prevent SQL injection.

🔹 Example: Using `SqlCommand` in .NET 6/7/8

using (SqlConnection conn = new SqlConnection(connString))
{
    SqlCommand cmd = new SqlCommand("usp_GetSpecialBooks", conn);
    cmd.CommandType = CommandType.StoredProcedure;
    conn.Open();
    SqlDataReader reader = cmd.ExecuteReader();
    // process results...
}

Alternatively, using Entity Framework Core:

var specials = await _context.Books
    .FromSqlRaw("EXEC usp_GetSpecialBooks")
    .ToListAsync();

✅ Conclusion:
The command object is still a valid and modern practice in .NET applications to use command objects (via ADO.NET or EF Core) to interact with SQL Server, especially when executing parameterized queries or stored procedures.

[1] stored procedure: A database object, such as a query that can be executed.