Introduction to Databases in CSharp

Introduction to Relational Databases in C#

Why Use a Database?

While saving data to files is simple and effective for small projects, it becomes less practical as applications grow.

Use a database, especially if …

• multiple users need access
• you must ensure data integrity
• you want to query data efficiently

A relational database stores data in tables that have relationships (for example: students enrolled in courses).

Each table consists of rows and columns. The language used to interact with relational databases is SQL (Structured Query Language).

---

Ways to Work with Databases in C#

C# provides several mechanisms to interact with databases, each with its own abstraction level and trade-offs.

1. ADO.NET (Low-level API)

• What it is: The fundamental data access technology in .NET.
• How it works: You manually write SQL commands and use classes like SqlConnection, SqlCommand, and SqlDataReader.
• Advantages:
  • Full control over SQL and performance.
  • No external dependencies beyond .NET.
• Disadvantages:
  • Verbose code.
  • Easy to introduce SQL injection if not careful.
  • Requires more boilerplate and manual mapping of results to objects.

2. Dapper (Micro ORM)

• What it is: A lightweight abstraction built on top of ADO.NET.
• How it works: You still write SQL queries, but Dapper maps query results directly to C# objects.
• Advantages:
  • Extremely fast and simple.
  • Minimal setup.
  • Keeps SQL visibility.
• Disadvantages:
  • Still requires SQL writing.
  • No automatic schema management or tracking of relationships.

3. Entity Framework Core (Full ORM)

• What it is: A high-level Object-Relational Mapper (ORM).
• How it works: You work with C# classes (entities), and EF Core handles the SQL generation and mapping automatically.
• Advantages:
  • Very productive and easy to use.
  • Supports migrations, LINQ queries, and complex relationships.
  • Tracks changes to objects automatically.
• Disadvantages:
  • More complex under the hood.
  • Can lead to slower queries.
  • Less control over SQL.
  • Harder to debug for beginners.

---

Starting with ADO.NET

We’ll begin with ADO.NET because it helps you understand what’s really happening when an application interacts with a database.

Basic Components

• SqlConnection - manages the connection to the database.
• SqlCommand - represents a SQL statement or stored procedure.
• SqlDataReader - reads rows returned by a query.
• SqlParameter - prevents SQL injection by safely passing parameters.

To use these classes, install the MySQL Connector/NET NuGet package:

dotnet add package MySql.Data

Note: Depending on the database you need a different connector.

---

Example: Connecting to a Database

using System;
using MySql.Data.MySqlClient;
 
class Program
{
    static void Main()
    {
        string connectionString = "Server=localhost;Database=SchoolDb;User ID=root;Password=yourpassword;";
 
        using (var connection = new MySqlConnection(connectionString))
        {
            connection.Open();
            Console.WriteLine("Connection established successfully!");
        }
    }
}

This example connects to a local MySql database on localhost. If you’re using SQLite, you can use Microsoft.Data.Sqlite and a file-based connection string such as: Data Source=school.db

---

CRUD Operations in ADO.NET

The following samples show how to perform simple CRUD (Create, Read, Update, Delete) operations.

Create (Insert)

public void AddStudent(Student student)
{
    using var connection = new MySqlConnection(_connectionString);
    connection.Open();
 
    string sql = "INSERT INTO Students (Id, Name, Email) VALUES (@Id, @Name, @Email)";
    using var command = new MySqlCommand(sql, connection);
    command.Parameters.AddWithValue("@Id", student.Id);
    command.Parameters.AddWithValue("@Name", student.Name);
    command.Parameters.AddWithValue("@Email", student.Email);
 
    command.ExecuteNonQuery();
}

Read (Select by Id)

public Student? GetById(string id)
{
    using var connection = new MySqlConnection(_connectionString);
    connection.Open();
 
    string sql = "SELECT Id, Name, Email FROM Students WHERE Id = @Id";
    using var command = new MySqlCommand(sql, connection);
    command.Parameters.AddWithValue("@Id", id);
 
    using var reader = command.ExecuteReader();
    if (reader.Read())
    {
        return new Student
        {
            Id = reader.GetString("Id"),
            Name = reader.GetString("Name"),
            Email = reader.GetString("Email")
        };
    }
    return null;
}

Update

public void Update(Student student)
{
    using var connection = new MySqlConnection(_connectionString);
    connection.Open();
 
    string sql = "UPDATE Students SET Name = @Name, Email = @Email WHERE Id = @Id";
    using var command = new MySqlCommand(sql, connection);
    command.Parameters.AddWithValue("@Id", student.Id);
    command.Parameters.AddWithValue("@Name", student.Name);
    command.Parameters.AddWithValue("@Email", student.Email);
 
    command.ExecuteNonQuery();
}

Delete

public void Delete(string id)
{
    using var connection = new MySqlConnection(_connectionString);
    connection.Open();
 
    string sql = "DELETE FROM Students WHERE Id = @Id";
    using var command = new MySqlCommand(sql, connection);
    command.Parameters.AddWithValue("@Id", id);
 
    command.ExecuteNonQuery();
}

Read All

public IEnumerable<Student> GetAll()
{
    var students = new List<Student>();
    using var connection = new MySqlConnection(_connectionString);
    connection.Open();
 
    string sql = "SELECT Id, Name, Email FROM Students";
    using var command = new MySqlCommand(sql, connection);
    using var reader = command.ExecuteReader();
 
    while (reader.Read())
    {
        students.Add(new Student
        {
            Id = reader.GetString("Id"),
            Name = reader.GetString("Name"),
            Email = reader.GetString("Email")
        });
    }
 
    return students;
}

---

Summary

Technology	Abstraction Level	Manual SQL	Auto Mapping	Best For
ADO.NET	Low	✅ Yes	❌ No	Full control
Dapper	Medium	✅ Yes	✅ Yes	Fast, simple apps. Using SQL directly
Entity Framework Core	High	❌ No	✅ Yes	Productivity, larger projects