Lesson 23 — Data Access in C#

Database Concepts

C# connects to databases via ADO.NET at the lowest level and Entity Framework Core at the highest. Knowing both layers makes you effective in enterprise .NET work.

CSHARP — ADO.NET SELECT 
using System.Data.SqlClient; // SQL Server
// using MySql.Data.MySqlClient; // MySQL
// using Npgsql;                  // PostgreSQL

string connStr = "Server=localhost;Database=your-it-tutor;Integrated Security=true;";
string sql     = "SELECT Name, Course, Mark FROM Learners WHERE City = @city";

using var conn = new SqlConnection(connStr);
using var cmd  = new SqlCommand(sql, conn);

cmd.Parameters.AddWithValue("@city", "Johannesburg"); // parameterised

conn.Open();

using var reader = cmd.ExecuteReader();
while (reader.Read()) {
    Console.WriteLine(
        $"{reader["Name"],-15} | {reader["Course"],-10} | {reader.GetDouble(2):F1}%"
    );
}
CSHARP — ADO.NET INSERT 
// INSERT with generated key:
string insertSQL = "INSERT INTO Learners (Name,Age,City,Course,Mark) " +
                   "VALUES (@name,@age,@city,@course,@mark);" +
                   "SELECT SCOPE_IDENTITY();";

using var insertCmd = new SqlCommand(insertSQL, conn);
insertCmd.Parameters.AddWithValue("@name",   "Thandi");
insertCmd.Parameters.AddWithValue("@age",    21);
insertCmd.Parameters.AddWithValue("@city",   "Johannesburg");
insertCmd.Parameters.AddWithValue("@course", "C#");
insertCmd.Parameters.AddWithValue("@mark",   74.5);

var newId = (decimal)insertCmd.ExecuteScalar();
Console.WriteLine($"Inserted with ID: {newId}");

Entity Framework Core

CSHARP — Entity Framework Core 
// EF Core — work with C# objects, not raw SQL:
public class AppDbContext : DbContext {
    public DbSet<Learner> Learners { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder b) =>
        b.UseSqlServer("Server=localhost;Database=your-it-tutor;...");;
}

using var db = new AppDbContext();

// LINQ queries translate to SQL:
var joburg = await db.Learners
    .Where(l => l.City == "Johannesburg")
    .OrderByDescending(l => l.Mark)
    .ToListAsync();

foreach (var l in joburg)
    Console.WriteLine($"{l.Name}: {l.Mark}%");

// Insert:
db.Learners.Add(new Learner { Name = "Sipho", Course = "EF Core" });
await db.SaveChangesAsync();

Practice Task

Your Turn

Write a LearnerDAO class with SqlConnection and methods: FindAll(), FindById(int), Insert(Learner) returning generated ID, Update(int id, double mark), Delete(int id). Use parameterised queries throughout. Test all five operations in Main.

Common Mistakes

  • String-concatenated SQL — always use @parameters to prevent SQL injection.
  • Not closing connections — use using or try-finally.
  • Hardcoding connection strings — use appsettings.json or environment variables.
  • Reading columns by index instead of name — brittle if query changes.

Professional Tip

The DAO (Data Access Object) pattern separates database logic from business logic. In real .NET projects you will use Entity Framework Core or Dapper, but understanding raw ADO.NET first makes those abstractions transparent.

Mini Quiz

Why must you use @parameters instead of string concatenation in SQL?