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`).
- In Visual Studio 2022, go to Solution Explorer.
- Right-click on the project → Add Reference.
- 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
-
Connection to Database
- Uses OLE DB Provider (SQLOLEDB) to connect to an SQL Server database.
- Replace
"YourServer", "YourDatabase", "YourUser", and "YourPassword" accordingly.
-
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.
-
Reading and Displaying Data
- Loops through the
Recordset, accessing fields using recordset.Fields["ColumnName"].Value.
-
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.
Now that a command object is added, you can put it to use to execute a database query and display the results. You can do this easily with the Recordset DTC. The Recordset DTC properties will be set in order to associate it with the command object added in the previous lesson. This is shown in the following simulation.
Building on the previous JavaScript and Express.js solution, we will now implement a Recordset Design-Time Control (DTC) equivalent using modern JavaScript, Sequelize ORM, and an interactive front-end in Visual Studio 2022.
Key Features of the Modern Solution
- Replaces the legacy Recordset DTC with a modern JavaScript-based UI control.
- Uses Sequelize ORM with MySQL for handling recordsets dynamically.
- Provides a visual, interactive form for adding, updating, and deleting records.
- Includes dynamic JavaScript to fetch and modify records via AJAX.
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.