Create a data-bound ASP.NET Core UI using EF Core for SQL Server integration

In Visual Studio 2022, if you were to create data-bound ASP.NET Core UI using EF Core for SQL Server Integration, you would typically use Visual Basic .NET (VB.NET) or C# within the .NET Framework or .NET Core.
Preferred Higher-Level Language:

1. C# – Most commonly used for modern .NET applications, including database interactions with ADO.NET and Entity Framework.
2. VB.NET – Historically used for Recordset-based applications (especially from classic Visual Basic (VB6) and ActiveX Data Objects (ADO)). However, it is now less common in favor of C#.

Key Considerations:

• ADO.NET: Used for managing database connections and Recordsets.
• Entity Framework (EF Core): A modern ORM for working with databases in .NET.
• COM Interop: If you need to work with legacy ActiveX Data Objects (ADO) Recordsets, you may need COM Interop in C# or VB.NET.

In Visual Studio 2022, working with an ADO Recordset in C# typically involves COM Interop to interact with ActiveX Data Objects (ADO), since Recordsets are primarily a feature of classic ADO (pre-.NET).
Example: Using an ADO Recordset in C#
This example demonstrates how to create, open, and read data from an ADO Recordset using COM Interop.
Step 1: Add COM Reference Before running the code, you need to add a reference to:
- Microsoft ActiveX Data Objects 6.1 Library (`msado15.dll`).

1. In Visual Studio 2022, go to Solution Explorer.
2. Right-click on the project → Add Reference.
3. Select COM → Microsoft ActiveX Data Objects 6.1 Library.

Step 2: C# Code to Use an ADO Recordset

using System;
using System.Data;
using ADODB;  // Import ADO namespace

class Program
{
   static void Main()
   {
       // Create an ADO Connection
       Connection conn = new Connection();
       conn.ConnectionString = "Provider=SQLOLEDB;Data Source=YourServer;Initial Catalog=YourDatabase;User ID=YourUser;Password=YourPassword;";      
       try
       {
           // Open the connection
           conn.Open();

           // Create and execute the ADO Recordset
           Recordset recordset = new Recordset();
           recordset.Open("SELECT * FROM YourTable", conn, CursorTypeEnum.adOpenStatic, LockTypeEnum.adLockReadOnly, (int)CommandTypeEnum.adCmdText);

           // Display Recordset data
           while (!recordset.EOF)
           {
               Console.WriteLine($"ID: {recordset.Fields["ID"].Value}, Name: {recordset.Fields["Name"].Value}");
               recordset.MoveNext();  // Move to next record
           }

           // Close Recordset and Connection
           recordset.Close();
           conn.Close();
       }
       catch (Exception ex)
       {
           Console.WriteLine("Error: " + ex.Message);
       }
       finally
       {
           // Clean up
           if (conn.State == (int)ObjectStateEnum.adStateOpen)
               conn.Close();
       }
   }
}

Explanation of the Code

1. Connection to Database
   • Uses OLE DB Provider (SQLOLEDB) to connect to an SQL Server database.
   • Replace "YourServer", "YourDatabase", "YourUser", and "YourPassword" accordingly.
2. Creating and Opening a Recordset
   • Uses an SQL SELECT statement to retrieve data from "YourTable".
   • adOpenStatic: Static cursor that supports navigation.
   • adLockReadOnly: Read-only mode.
3. Reading and Displaying Data
   • Loops through the Recordset, accessing fields using recordset.Fields["ColumnName"].Value.
4. Closing the Recordset and Connection
   • Ensures proper cleanup to prevent memory leaks.

Alternative: Using ADO.NET (Modern Approach)
For new applications, consider using "ADO.NET with SqlClient" instead of `ADODB.Recordset`:

using System;
using System.Data.SqlClient;

class Program
{
    static void Main()
    {
        string connectionString = "Server=YourServer;Database=YourDatabase;User Id=YourUser;Password=YourPassword;";

        using (SqlConnection conn = new SqlConnection(connectionString))
        {
            conn.Open();

            using (SqlCommand cmd = new SqlCommand("SELECT * FROM YourTable", conn))
            using (SqlDataReader reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    Console.WriteLine($"ID: {reader["ID"]}, Name: {reader["Name"]}");
                }
            }
        }
    }
}

Key Takeaways

• Use ADODB (COM-based ADO) if you're working with legacy applications or MS Access, Excel, or VB6 components.
• Use ADO.NET (System.Data.SqlClient) for modern .NET applications.

Building on the previous JavaScript and Express.js solution, we will now implement a data-bound ASP.NET Core UI component using EF Core for SQL Server integration.

1. Update the Express.js API (`server.js`)
We add new CRUD routes for managing book records.
Updated `server.js`

const express = require('express');
const { Sequelize, DataTypes } = require('sequelize');

// Initialize Express app
const app = express();
const PORT = 3000;

// Setup database connection (Replace with actual credentials)
const sequelize = new Sequelize('bigbookdb', 'username', 'password', {
    host: 'localhost',
    dialect: 'mysql'
});

// Define BookRecords Model
const BookRecords = sequelize.define('BookRecords', {
    id: { type: DataTypes.INTEGER, primaryKey: true, autoIncrement: true },
    title: { type: DataTypes.STRING },
    author: { type: DataTypes.STRING }
}, { timestamps: false });

// Middleware
app.use(express.static('public'));
app.use(express.json());
app.set('view engine', 'ejs');

// Route to fetch all book records
app.get('/books', async (req, res) => {
    try {
        const books = await BookRecords.findAll();
        res.json(books);
    } catch (error) {
        res.status(500).json({ error: "Database error" });
    }
});

// Route to add a new book
app.post('/books', async (req, res) => {
    try {
        const { title, author } = req.body;
        const newBook = await BookRecords.create({ title, author });
        res.json(newBook);
    } catch (error) {
        res.status(500).json({ error: "Failed to add book" });
    }
});

// Route to update a book
app.put('/books/:id', async (req, res) => {
    try {
        const { id } = req.params;
        const { title, author } = req.body;
        await BookRecords.update({ title, author }, { where: { id } });
        res.json({ message: "Book updated successfully" });
    } catch (error) {
        res.status(500).json({ error: "Failed to update book" });
    }
});

// Route to delete a book
app.delete('/books/:id', async (req, res) => {
    try {
        const { id } = req.params;
        await BookRecords.destroy({ where: { id } });
        res.json({ message: "Book deleted successfully" });
    } catch (error) {
        res.status(500).json({ error: "Failed to delete book" });
    }
});

// Serve the HTML page
app.get('/', (req, res) => {
    res.render('index');
});

// Start the server
app.listen(PORT, () => console.log(`Server running on http://localhost:${PORT}`));

2. Update the Front-End UI (`views/index.ejs`)
We add an interactive Recordset-like form with buttons for adding, updating, and deleting records.
Updated `views/index.ejs`

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Book Records</title>
    <script defer src="/script.js"></script>
    <style>
        body { font-family: Arial, sans-serif; margin: 20px; }
        table { width: 100%; border-collapse: collapse; margin-top: 20px; }
        th, td { border: 1px solid black; padding: 8px; text-align: left; }
        th { background-color: #f2f2f2; }
        .form-container { margin-top: 20px; }
    </style>
</head>
<body>
    <h1>Book Records</h1>

    <div class="form-container">
        <input type="hidden" id="bookId">
        <label>Title: <input type="text" id="title"></label>
        <label>Author: <input type="text" id="author"></label>
        <button onclick="addOrUpdateBook()">Save</button>
        <button onclick="clearForm()">Clear</button>
    </div>

    <table>
        <thead>
            <tr>
                <th>ID</th>
                <th>Title</th>
                <th>Author</th>
                <th>Actions</th>
            </tr>
        </thead>
        <tbody id="bookTableBody"></tbody>
    </table>
</body>
</html>

3. Update JavaScript for Interactivity (`public/script.js`)
This script dynamically loads records, adds, updates, and deletes books.
Updated `public/script.js`

document.addEventListener('DOMContentLoaded', loadBooks);

function loadBooks() {
    fetch('/books')
        .then(response => response.json())
        .then(data => {
            const tableBody = document.getElementById('bookTableBody');
            tableBody.innerHTML = ''; // Clear existing content
            data.forEach(book => {
                let row = document.createElement('tr');
                row.innerHTML = `
                    <td>${book.id}</td>
                    <td>${book.title}</td>
                    <td>${book.author}</td>
                    <td>
                        <button onclick="editBook(${book.id}, '${book.title}', '${book.author}')">Edit</button>
                        <button onclick="deleteBook(${book.id})">Delete</button>
                    </td>
                `;
                tableBody.appendChild(row);
            });
        })
        .catch(error => console.error('Error fetching books:', error));
}

function addOrUpdateBook() {
    const id = document.getElementById('bookId').value;
    const title = document.getElementById('title').value;
    const author = document.getElementById('author').value;

    if (!title || !author) {
        alert("Please fill in all fields.");
        return;
    }

    const bookData = { title, author };
    const url = id ? `/books/${id}` : '/books';
    const method = id ? 'PUT' : 'POST';

    fetch(url, {
        method: method,
        headers: { 'Content-Type': 'application/json' },
        body: JSON.stringify(bookData)
    })
    .then(() => {
        clearForm();
        loadBooks();
    })
    .catch(error => console.error('Error saving book:', error));
}

function editBook(id, title, author) {
    document.getElementById('bookId').value = id;
    document.getElementById('title').value = title;
    document.getElementById('author').value = author;
}

function deleteBook(id) {
    if (confirm("Are you sure you want to delete this book?")) {
        fetch(`/books/${id}`, { method: 'DELETE' })
        .then(() => loadBooks())
        .catch(error => console.error('Error deleting book:', error));
    }
}

function clearForm() {
    document.getElementById('bookId').value = '';
    document.getElementById('title').value = '';
    document.getElementById('author').value = '';
}

Summary of Enhancements

• Interactive Recordset-like DTC
  • Users can add, update, and delete records dynamically.
  • Uses AJAX to update data without refreshing the page.
• Modern ORM-based Database Management
  • Uses Sequelize with MySQL to replace ADO Recordsets.
• Cleaner UI and UX
  • Simple, responsive HTML form with real-time database updates.

Next Steps:

• Extend pagination and filtering in the UI.
• Enhance form validation to prevent duplicate entries.
• Add user authentication to restrict access.

In the next lesson, you will learn how the command and recordset objects will display BookTable using bound controls.