ADO.NET as the foundational Data Access Technology within the .NET ecosystem

Microsoft has evolved its technologies significantly since the days of Visual InterDev and the dotcom era. Today, when interacting with SQL Server 2022, Microsoft primarily relies on ADO.NET as the foundational data access technology within the .NET ecosystem, alongside modern frameworks and tools that build upon it. Here’s a breakdown of the current approach:
Primary Technology: ADO.NET ADO.NET is the core data access technology used in .NET applications to connect to databases like SQL Server 2022. It provides a robust, object-oriented framework for interacting with data sources through:

• Connection Objects: Establish a connection to SQL Server (e.g., SqlConnection).
• Command Objects: Execute SQL queries or stored procedures (e.g., SqlCommand), similar to the command object you mentioned from the Visual InterDev era.
• Data Readers: Retrieve data efficiently in a forward-only, read-only stream (e.g., SqlDataReader).
• DataAdapters: Bridge the gap between the database and in-memory datasets (e.g., SqlDataAdapter for filling a DataSet or DataTable).
• Datasets: In-memory representations of data that can be bound to controls.

ADO.NET supports both connected and disconnected data access models, making it versatile for various application scenarios. Modern Frameworks: Entity Framework Core While ADO.NET is the low-level foundation, Microsoft heavily promotes Entity Framework Core (EF Core) as the primary high-level technology for interacting with SQL Server 2022 in modern .NET applications. EF Core is an Object-Relational Mapping (ORM) framework that simplifies database interactions by:

• Mapping database tables to .NET objects (entities).
• Automatically generating SQL queries based on LINQ (Language Integrated Query) expressions.
• Handling relationships between entities (similar to the relationships established in Visual InterDev’s data-bound controls).
• Supporting data binding to UI controls in frameworks like ASP.NET Core, Blazor, or Windows Forms/WPF.

EF Core abstracts much of the manual work that was required in earlier technologies, such as writing raw SQL or managing command objects directly, while still allowing fine-grained control when needed.
UI Integration and Data Binding
For binding data to UI controls in modern applications:

• ASP.NET Core: Uses Razor views or Blazor components with data binding, often leveraging EF Core to fetch data from SQL Server 2022 and display it in web applications.
• Windows Presentation Foundation (WPF) and WinForms: Support data binding through DataTable, DataSet, or collections (e.g., List<T>) populated via ADO.NET or EF Core.
• Blazor: A newer framework that supports both server-side and client-side (WebAssembly) data binding, typically using EF Core to interact with SQL Server.

Connection to SQL Server 2022
The interaction with SQL Server 2022 specifically is facilitated by the Microsoft.Data.SqlClient library, which is the modern evolution of the SQL Server Native Client. This library provides the underlying connectivity for ADO.NET and EF Core, supporting features like:

• Secure connections with encryption.
• Integration with Azure Active Directory (Microsoft Entra ID) authentication.
• Compatibility with SQL Server 2022’s advanced features, such as Azure Synapse Link and ledger capabilities.

Evolution from Visual InterDev
In the Visual InterDev era, data-bound controls relied on technologies like ActiveX Data Objects (ADO) and direct HTML binding, often requiring a tight coupling between the UI and database at design time. Today, Microsoft’s approach is more modular and flexible:

• Design-time binding has largely been replaced by runtime data binding in frameworks like ASP.NET Core or Blazor, with tools like Visual Studio providing drag-and-drop support for prototyping.
• Command objects have evolved into SqlCommand in ADO.NET or are abstracted away entirely in EF Core.
• The focus has shifted to scalable, cross-platform solutions with .NET Core/.NET 8, supporting deployment on Windows, Linux, and containers, aligning with SQL Server 2022’s hybrid and cloud-enabled capabilities.

Summary For interacting with SQL Server 2022, Microsoft uses ADO.NET as the foundational technology, with Entity Framework Core as the preferred high-level framework for most modern applications. These are complemented by the Microsoft.Data.SqlClient library for connectivity and modern UI frameworks (e.g., ASP.NET Core, Blazor) for data binding, offering a more powerful, flexible, and maintainable successor to the Visual InterDev approach.

In the earlier lesson, Binding data-bound Design Time Controls, you learned how to display records by connecting to a database and binding fields directly to HTML text box controls. You had to establish a relationship between the control and the data. A command object takes care of this detail for you, making the process easier. To display data using bound controls and the command object, all you have to do is drag the desired fields from the command object to the Web page. Now, it doesn't get much easier than that. The command object (BookRecordsCmd) created in Lesson 3, will be used to display BookTable data by using bound controls. The following simulation illustrates how the command object can be used to easily display information in a Web page.

1. The project and file Specials.asp are open. To begin, expand the BookRecordsCmd object to display the column tables. Expand the object by clicking the + symbol in front of its name.
2. Now select the columns you want to display and drag them onto the page. Highlight the fields ItemNo, Title, Author, and Retail. You can drag all four fields at once by holding down the Ctrl key and selecting each field.
3. Now, drag the fields to the page. For the purpose of this simulation, just click ItemNo and we will drag them for you.
4. Notice that not only are the Textbox controls inserted into the page, but that HTML table tags and the field names are also inserted. View it in the browser by right-clicking the Source Editor.
5. Select “View in Browser” from the menu.
6. Now the record is formatted nicely in an HTML table. This is the end of the simulation.

Dragging fields from the command object and dropping them into the page is simpler and faster than binding fields to controls. In addition, a little more work is done for you because the HTML table tags and field names are automatically inserted for you.
Up to this point, we have only been able to view a single record. In the next lesson, you will add the RecordsetNavBar DTC to the Web page.