Skip to content
Deadline to Die

The Mechanic: ADO.NET & Raw SQL

🏗️ The Mechanic: ADO.NET & Raw SQL

Before we use Entity Framework Core (EF Core) to “automagically” map our objects, we must understand the raw mechanism that communicates with the database: ADO.NET.

1. The Core Components

To talk to a database in .NET without an ORM, you use three main classes:

  • SqlConnection: The physical pipe to the database.
  • SqlCommand: The SQL query you want to execute.
  • SqlDataReader: The forward-only, high-speed stream of rows from the database.

🛠️ Lab: Manual Object Mapping

In this lab, we don’t have an ORM. We must manually map the columns to our C# properties.

using Microsoft.Data.SqlClient;

public class Product {
    public int Id { get; set; }
    public string Name { get; set; }
}

public async Task<List<Product>> GetAllProducts(string connectionString) {
    var products = new List<Product>();
    
    using var connection = new SqlConnection(connectionString);
    await connection.OpenAsync();
    
    using var command = new SqlCommand("SELECT Id, Name FROM Products", connection);
    using var reader = await command.ExecuteReaderAsync();
    
    while (await reader.ReadAsync()) {
        products.Add(new Product {
            Id = reader.GetInt32(0),
            Name = reader.GetString(1)
        });
    }
    
    return products;
}

2. Why Learn This?

  • Performance: ADO.NET is the fastest way to read data. Every ORM (EF Core, Dapper) uses this under the hood.
  • Control: You have absolute control over the SQL being executed.
  • Debugging: When your ORM generates a 500-line SQL query that is slow, you need to know how to run it manually and analyze it.

🧪 Professor’s Challenge: The Manual Mapper

Task: Build a generic method MapReader<T>(SqlDataReader reader) that uses Reflection to automatically map columns to properties.

  1. Get all properties of type T.
  2. Loop through the columns in the reader.
  3. Match the column name to the property name and set the value.