Databasplanering för C# applikationer med SQL och ADO.NET

När du planerar en databas för en C#-applikation med ADO.NET finns det specifika överväganden kring hur du mappar mellan relationell data (SQL) och objektorienterad kod (C#). Detta dokument visar hur du tänker databas + C# från start med ren SQL och ADO.NET.

Introduktion

C# är ett starkt typat, objektorienterat språk. Databaser är relationella och schemabaserade. Hur får vi dessa två världar att samarbeta smidigt?

Impedance Mismatch kallas problemet där objektorienterad kod (C#) inte matchar relationell data (SQL):

// C#: Objekt med referenser
class Customer {
    public int Id { get; set; }
    public string Name { get; set; }
    public List<Order> Orders { get; set; }  // Lista av objekt
}

class Order {
    public int Id { get; set; }
    public Customer Customer { get; set; }  // Referens till objekt
}

-- SQL: Tabeller med foreign keys
CREATE TABLE Customers (
    CustomerId INTEGER PRIMARY KEY,
    Name TEXT
);

CREATE TABLE Orders (
    OrderId INTEGER PRIMARY KEY,
    CustomerId INTEGER,  -- Bara ett ID, inte hela objektet
    FOREIGN KEY (CustomerId) REFERENCES Customers(CustomerId)
);

Lösningen med ADO.NET: Du skriver SQL-queries manuellt och mappar resultaten till C#-objekt. Full kontroll, men mer kod!

C#-specifika överväganden

1. Namnkonventioner

C# konventioner:

PascalCase för klasser: Customer, OrderItem
PascalCase för properties: FirstName, IsActive
camelCase för privata fields: _customerId

SQL konventioner (varies):

PascalCase: Customers, FirstName (SQL Server standard)
snake_case: customers, first_name (PostgreSQL standard)
UPPERCASE: CUSTOMERS, FIRST_NAME (gammal skola)

Best practice för C#: Använd PascalCase i både C# och databas för konsistens!

// C# klass
public class Customer {
    public int CustomerId { get; set; }
    public string FirstName { get; set; }
}

-- Databas (samma namn!)
CREATE TABLE Customers (
    CustomerId INTEGER PRIMARY KEY AUTOINCREMENT,
    FirstName TEXT NOT NULL
);

2. Datatyp-mappning

SQL (SQLite)	C# typ	Noteringar
`INTEGER`	`int` / `long`	Använd `long` för stora ID:n
`TEXT`	`string`	Alltid nullable i databas
`REAL`	`double` / `decimal`	`decimal` för pengar!
`BLOB`	`byte[]`	Binär data (bilder, filer)
`NULL`	`T?` (nullable)	`int?`, `DateTime?`

Viktigt:

// DÅLIGT: NULL från databas → exception!
public class Product {
    public int ProductId { get; set; }
    public string Name { get; set; }  // Kraschar om NULL!
}

// BRA: Hantera NULL korrekt
public class Product {
    public int ProductId { get; set; }
    public string? Name { get; set; }  // Nullable reference type (C# 8+)
    public DateTime? DiscontinuedDate { get; set; }  // Nullable value type
}

3. NULL-hantering

I SQL:

CREATE TABLE Products (
    ProductId INTEGER PRIMARY KEY,
    Name TEXT NOT NULL,              -- Kan INTE vara NULL
    Description TEXT,                -- KAN vara NULL
    DiscontinuedDate TEXT            -- KAN vara NULL
);

I C#:

// Läsa från databas med ADO.NET
while (reader.Read()) {
    var product = new Product {
        ProductId = reader.GetInt32(0),
        Name = reader.GetString(1),
        // Hantera NULL för optional fields
        Description = reader.IsDBNull(2) ? null : reader.GetString(2),
        DiscontinuedDate = reader.IsDBNull(3) ? null : DateTime.Parse(reader.GetString(3))
    };
}

4. Array-dilemmat i C# klasser 🎯

SE OCKSÅ: Dokumentet om normalisering (4NF) för djupare förklaring.

PROBLEM: Du har en klass med flera listor:

// DÅLIG design för databas
public class Teacher {
    public int TeacherId { get; set; }
    public string Name { get; set; }
    public List<string> Courses { get; set; }        // ← Oberoende lista
    public List<string> PhoneNumbers { get; set; }   // ← Oberoende lista
}

LÖSNING: Separata tabeller och klasser:

// BRA design
public class Teacher {
    public int TeacherId { get; set; }
    public string Name { get; set; }
}

public class TeacherCourse {
    public int TeacherId { get; set; }
    public string Course { get; set; }
}

public class TeacherPhone {
    public int TeacherId { get; set; }
    public string PhoneNumber { get; set; }
}

CREATE TABLE Teachers (
    TeacherId INTEGER PRIMARY KEY,
    Name TEXT NOT NULL
);

CREATE TABLE TeacherCourses (
    TeacherId INTEGER,
    Course TEXT,
    PRIMARY KEY (TeacherId, Course),
    FOREIGN KEY (TeacherId) REFERENCES Teachers(TeacherId)
);

CREATE TABLE TeacherPhones (
    TeacherId INTEGER,
    PhoneNumber TEXT,
    PRIMARY KEY (TeacherId, PhoneNumber),
    FOREIGN KEY (TeacherId) REFERENCES Teachers(TeacherId)
);

Tumregel: Två eller fler oberoende listor → separata tabeller!

ADO.NET - Databas access från C#

ADO.NET är .NET:s lågnivå-API för databasaccess. Full kontroll över SQL, maximala prestanda.

Installation

dotnet add package System.Data.SQLite

Grundläggande CRUD

using System.Data.SQLite;

public class CustomerRepository {
    private readonly string _connectionString;

    public CustomerRepository(string dbPath) {
        _connectionString = $"Data Source={dbPath};Version=3;";
    }

    // CREATE
    public int CreateCustomer(string name, string email) {
        using var connection = new SQLiteConnection(_connectionString);
        connection.Open();

        string sql = "INSERT INTO Customers (Name, Email) VALUES (@name, @email); SELECT last_insert_rowid();";
        using var command = new SQLiteCommand(sql, connection);
        command.Parameters.AddWithValue("@name", name);
        command.Parameters.AddWithValue("@email", email);

        return Convert.ToInt32(command.ExecuteScalar());
    }

    // READ (Single)
    public Customer? GetCustomer(int customerId) {
        using var connection = new SQLiteConnection(_connectionString);
        connection.Open();

        string sql = "SELECT CustomerId, Name, Email FROM Customers WHERE CustomerId = @id";
        using var command = new SQLiteCommand(sql, connection);
        command.Parameters.AddWithValue("@id", customerId);

        using var reader = command.ExecuteReader();
        if (reader.Read()) {
            return new Customer {
                CustomerId = reader.GetInt32(0),
                Name = reader.GetString(1),
                Email = reader.GetString(2)
            };
        }
        return null;
    }

    // READ (All)
    public List<Customer> GetAllCustomers() {
        var customers = new List<Customer>();

        using var connection = new SQLiteConnection(_connectionString);
        connection.Open();

        string sql = "SELECT CustomerId, Name, Email FROM Customers";
        using var command = new SQLiteCommand(sql, connection);
        using var reader = command.ExecuteReader();

        while (reader.Read()) {
            customers.Add(new Customer {
                CustomerId = reader.GetInt32(0),
                Name = reader.GetString(1),
                Email = reader.GetString(2)
            });
        }

        return customers;
    }

    // UPDATE
    public bool UpdateCustomer(Customer customer) {
        using var connection = new SQLiteConnection(_connectionString);
        connection.Open();

        string sql = "UPDATE Customers SET Name = @name, Email = @email WHERE CustomerId = @id";
        using var command = new SQLiteCommand(sql, connection);
        command.Parameters.AddWithValue("@name", customer.Name);
        command.Parameters.AddWithValue("@email", customer.Email);
        command.Parameters.AddWithValue("@id", customer.CustomerId);

        return command.ExecuteNonQuery() > 0;
    }

    // DELETE
    public bool DeleteCustomer(int customerId) {
        using var connection = new SQLiteConnection(_connectionString);
        connection.Open();

        string sql = "DELETE FROM Customers WHERE CustomerId = @id";
        using var command = new SQLiteCommand(sql, connection);
        command.Parameters.AddWithValue("@id", customerId);

        return command.ExecuteNonQuery() > 0;
    }
}

Viktiga ADO.NET-metoder

Metod	Användning
`ExecuteScalar()`	Returnera ETT värde (t.ex. COUNT, last_insert_rowid)
`ExecuteNonQuery()`	INSERT, UPDATE, DELETE (returnerar antal rader)
`ExecuteReader()`	SELECT (returnerar DataReader för att läsa rader)

Exempel:

// ExecuteScalar - Hämta antal kunder
string sql = "SELECT COUNT(*) FROM Customers";
using var command = new SQLiteCommand(sql, connection);
int count = Convert.ToInt32(command.ExecuteScalar());

// ExecuteNonQuery - Radera alla inaktiva kunder
sql = "DELETE FROM Customers WHERE IsActive = 0";
int deletedCount = command.ExecuteNonQuery();
Console.WriteLine($"Raderade {deletedCount} kunder");

// ExecuteReader - Hämta alla kunder
sql = "SELECT CustomerId, Name FROM Customers";
using var reader = command.ExecuteReader();
while (reader.Read()) {
    Console.WriteLine($"{reader.GetInt32(0)}: {reader.GetString(1)}");
}

Mappning mellan SQL och C#

Enkel entitet

CREATE TABLE Products (
    ProductId INTEGER PRIMARY KEY AUTOINCREMENT,
    Name TEXT NOT NULL,
    Price REAL NOT NULL,
    StockQuantity INTEGER NOT NULL DEFAULT 0,
    CreatedAt TEXT NOT NULL DEFAULT (datetime('now'))
);

public class Product {
    public int ProductId { get; set; }
    public string Name { get; set; } = string.Empty;
    public decimal Price { get; set; }
    public int StockQuantity { get; set; }
    public DateTime CreatedAt { get; set; }
}

// Mappning från DataReader
public Product MapProduct(SQLiteDataReader reader) {
    return new Product {
        ProductId = reader.GetInt32(reader.GetOrdinal("ProductId")),
        Name = reader.GetString(reader.GetOrdinal("Name")),
        Price = (decimal)reader.GetDouble(reader.GetOrdinal("Price")),
        StockQuantity = reader.GetInt32(reader.GetOrdinal("StockQuantity")),
        CreatedAt = DateTime.Parse(reader.GetString(reader.GetOrdinal("CreatedAt")))
    };
}

One-to-Many relation

CREATE TABLE Customers (
    CustomerId INTEGER PRIMARY KEY AUTOINCREMENT,
    Name TEXT NOT NULL
);

CREATE TABLE Orders (
    OrderId INTEGER PRIMARY KEY AUTOINCREMENT,
    CustomerId INTEGER NOT NULL,
    OrderDate TEXT NOT NULL,
    FOREIGN KEY (CustomerId) REFERENCES Customers(CustomerId)
);

Variant 1: Customer med lista av Orders

public class Customer {
    public int CustomerId { get; set; }
    public string Name { get; set; } = string.Empty;
    public List<Order> Orders { get; set; } = new();
}

public class Order {
    public int OrderId { get; set; }
    public int CustomerId { get; set; }
    public DateTime OrderDate { get; set; }
}

// Hämta customer med alla orders
public Customer? GetCustomerWithOrders(int customerId) {
    using var connection = new SQLiteConnection(_connectionString);
    connection.Open();

    // Hämta customer
    var customer = GetCustomer(customerId);
    if (customer == null) return null;

    // Hämta orders
    string sql = "SELECT OrderId, CustomerId, OrderDate FROM Orders WHERE CustomerId = @id";
    using var command = new SQLiteCommand(sql, connection);
    command.Parameters.AddWithValue("@id", customerId);

    using var reader = command.ExecuteReader();
    while (reader.Read()) {
        customer.Orders.Add(new Order {
            OrderId = reader.GetInt32(0),
            CustomerId = reader.GetInt32(1),
            OrderDate = DateTime.Parse(reader.GetString(2))
        });
    }

    return customer;
}

Variant 2: Order med Customer-referens

public class Order {
    public int OrderId { get; set; }
    public int CustomerId { get; set; }
    public DateTime OrderDate { get; set; }
    public Customer? Customer { get; set; }  // Navigation property
}

// Hämta order med customer-info
public Order? GetOrderWithCustomer(int orderId) {
    using var connection = new SQLiteConnection(_connectionString);
    connection.Open();

    string sql = @"
        SELECT
            o.OrderId, o.CustomerId, o.OrderDate,
            c.Name AS CustomerName
        FROM Orders o
        JOIN Customers c ON o.CustomerId = c.CustomerId
        WHERE o.OrderId = @id";

    using var command = new SQLiteCommand(sql, connection);
    command.Parameters.AddWithValue("@id", orderId);

    using var reader = command.ExecuteReader();
    if (reader.Read()) {
        return new Order {
            OrderId = reader.GetInt32(0),
            CustomerId = reader.GetInt32(1),
            OrderDate = DateTime.Parse(reader.GetString(2)),
            Customer = new Customer {
                CustomerId = reader.GetInt32(1),
                Name = reader.GetString(3)
            }
        };
    }
    return null;
}

Many-to-Many relation

CREATE TABLE Students (
    StudentId INTEGER PRIMARY KEY,
    Name TEXT NOT NULL
);

CREATE TABLE Courses (
    CourseId INTEGER PRIMARY KEY,
    CourseName TEXT NOT NULL
);

CREATE TABLE StudentCourses (
    StudentId INTEGER,
    CourseId INTEGER,
    EnrollmentDate TEXT NOT NULL,
    PRIMARY KEY (StudentId, CourseId),
    FOREIGN KEY (StudentId) REFERENCES Students(StudentId),
    FOREIGN KEY (CourseId) REFERENCES Courses(CourseId)
);

public class Student {
    public int StudentId { get; set; }
    public string Name { get; set; } = string.Empty;
    public List<Course> Courses { get; set; } = new();
}

public class Course {
    public int CourseId { get; set; }
    public string CourseName { get; set; } = string.Empty;
}

// Hämta student med alla kurser
public Student? GetStudentWithCourses(int studentId) {
    using var connection = new SQLiteConnection(_connectionString);
    connection.Open();

    // Hämta student
    var student = GetStudent(studentId);
    if (student == null) return null;

    // Hämta kurser via junction table
    string sql = @"
        SELECT c.CourseId, c.CourseName
        FROM Courses c
        JOIN StudentCourses sc ON c.CourseId = sc.CourseId
        WHERE sc.StudentId = @studentId";

    using var command = new SQLiteCommand(sql, connection);
    command.Parameters.AddWithValue("@studentId", studentId);

    using var reader = command.ExecuteReader();
    while (reader.Read()) {
        student.Courses.Add(new Course {
            CourseId = reader.GetInt32(0),
            CourseName = reader.GetString(1)
        });
    }

    return student;
}

Repository Pattern med ADO.NET

Repository Pattern abstraherar databasaccess bakom ett interface. Gör koden testbar och underhållbar.

Grundläggande Repository

// 1. Interface
public interface ICustomerRepository {
    Customer? GetById(int id);
    List<Customer> GetAll();
    int Create(Customer customer);
    bool Update(Customer customer);
    bool Delete(int id);
}

// 2. Implementation
public class CustomerRepository : ICustomerRepository {
    private readonly string _connectionString;

    public CustomerRepository(string connectionString) {
        _connectionString = connectionString;
    }

    public Customer? GetById(int id) {
        using var connection = new SQLiteConnection(_connectionString);
        connection.Open();

        string sql = "SELECT CustomerId, Name, Email FROM Customers WHERE CustomerId = @id";
        using var command = new SQLiteCommand(sql, connection);
        command.Parameters.AddWithValue("@id", id);

        using var reader = command.ExecuteReader();
        if (reader.Read()) {
            return MapCustomer(reader);
        }
        return null;
    }

    public List<Customer> GetAll() {
        var customers = new List<Customer>();
        using var connection = new SQLiteConnection(_connectionString);
        connection.Open();

        string sql = "SELECT CustomerId, Name, Email FROM Customers";
        using var command = new SQLiteCommand(sql, connection);
        using var reader = command.ExecuteReader();

        while (reader.Read()) {
            customers.Add(MapCustomer(reader));
        }
        return customers;
    }

    public int Create(Customer customer) {
        using var connection = new SQLiteConnection(_connectionString);
        connection.Open();

        string sql = "INSERT INTO Customers (Name, Email) VALUES (@name, @email); SELECT last_insert_rowid();";
        using var command = new SQLiteCommand(sql, connection);
        command.Parameters.AddWithValue("@name", customer.Name);
        command.Parameters.AddWithValue("@email", customer.Email);

        return Convert.ToInt32(command.ExecuteScalar());
    }

    public bool Update(Customer customer) {
        using var connection = new SQLiteConnection(_connectionString);
        connection.Open();

        string sql = "UPDATE Customers SET Name = @name, Email = @email WHERE CustomerId = @id";
        using var command = new SQLiteCommand(sql, connection);
        command.Parameters.AddWithValue("@name", customer.Name);
        command.Parameters.AddWithValue("@email", customer.Email);
        command.Parameters.AddWithValue("@id", customer.CustomerId);

        return command.ExecuteNonQuery() > 0;
    }

    public bool Delete(int id) {
        using var connection = new SQLiteConnection(_connectionString);
        connection.Open();

        string sql = "DELETE FROM Customers WHERE CustomerId = @id";
        using var command = new SQLiteCommand(sql, connection);
        command.Parameters.AddWithValue("@id", id);

        return command.ExecuteNonQuery() > 0;
    }

    private Customer MapCustomer(SQLiteDataReader reader) {
        return new Customer {
            CustomerId = reader.GetInt32(0),
            Name = reader.GetString(1),
            Email = reader.GetString(2)
        };
    }
}

Användning

// Dependency injection-friendly
public class CustomerService {
    private readonly ICustomerRepository _customerRepo;

    public CustomerService(ICustomerRepository customerRepo) {
        _customerRepo = customerRepo;
    }

    public void ProcessCustomer(int customerId) {
        var customer = _customerRepo.GetById(customerId);
        if (customer == null) {
            Console.WriteLine("Customer not found!");
            return;
        }

        // Business logic här
        Console.WriteLine($"Processing {customer.Name}");
    }
}

// I Program.cs
string dbPath = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments), "shop.db");
ICustomerRepository customerRepo = new CustomerRepository($"Data Source={dbPath};Version=3;");
var service = new CustomerService(customerRepo);
service.ProcessCustomer(1);

Transaktioner i C#

Transaktioner säkerställer att flera operationer antingen alla lyckas eller alla rullas tillbaka.

Grundläggande transaktion

public bool TransferMoney(int fromAccountId, int toAccountId, decimal amount) {
    using var connection = new SQLiteConnection(_connectionString);
    connection.Open();

    using var transaction = connection.BeginTransaction();
    try {
        // Dra från första kontot
        string sql1 = "UPDATE Accounts SET Balance = Balance - @amount WHERE AccountId = @id";
        using (var command = new SQLiteCommand(sql1, connection, transaction)) {
            command.Parameters.AddWithValue("@amount", (double)amount);
            command.Parameters.AddWithValue("@id", fromAccountId);
            command.ExecuteNonQuery();
        }

        // Lägg till på andra kontot
        string sql2 = "UPDATE Accounts SET Balance = Balance + @amount WHERE AccountId = @id";
        using (var command = new SQLiteCommand(sql2, connection, transaction)) {
            command.Parameters.AddWithValue("@amount", (double)amount);
            command.Parameters.AddWithValue("@id", toAccountId);
            command.ExecuteNonQuery();
        }

        // Allt OK - commit!
        transaction.Commit();
        return true;
    }
    catch (Exception ex) {
        // Något gick fel - rollback!
        transaction.Rollback();
        Console.WriteLine($"Transaction failed: {ex.Message}");
        return false;
    }
}

Skapa order med orderrader (transaktion)

public int CreateOrderWithItems(int customerId, List<OrderItem> items) {
    using var connection = new SQLiteConnection(_connectionString);
    connection.Open();

    using var transaction = connection.BeginTransaction();
    try {
        // 1. Skapa order
        string sql1 = "INSERT INTO Orders (CustomerId, OrderDate) VALUES (@customerId, @date); SELECT last_insert_rowid();";
        int orderId;
        using (var command = new SQLiteCommand(sql1, connection, transaction)) {
            command.Parameters.AddWithValue("@customerId", customerId);
            command.Parameters.AddWithValue("@date", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
            orderId = Convert.ToInt32(command.ExecuteScalar());
        }

        // 2. Lägg till alla orderrader
        string sql2 = "INSERT INTO OrderLines (OrderId, ProductId, Quantity, Price) VALUES (@orderId, @productId, @qty, @price)";
        foreach (var item in items) {
            using var command = new SQLiteCommand(sql2, connection, transaction);
            command.Parameters.AddWithValue("@orderId", orderId);
            command.Parameters.AddWithValue("@productId", item.ProductId);
            command.Parameters.AddWithValue("@qty", item.Quantity);
            command.Parameters.AddWithValue("@price", (double)item.Price);
            command.ExecuteNonQuery();
        }

        // 3. Uppdatera lagersaldo
        string sql3 = "UPDATE Products SET StockQuantity = StockQuantity - @qty WHERE ProductId = @id";
        foreach (var item in items) {
            using var command = new SQLiteCommand(sql3, connection, transaction);
            command.Parameters.AddWithValue("@qty", item.Quantity);
            command.Parameters.AddWithValue("@id", item.ProductId);
            command.ExecuteNonQuery();
        }

        transaction.Commit();
        return orderId;
    }
    catch {
        transaction.Rollback();
        throw;
    }
}

Praktiskt exempel: E-handel

Databasschema

CREATE TABLE Customers (
    CustomerId INTEGER PRIMARY KEY AUTOINCREMENT,
    Name TEXT NOT NULL,
    Email TEXT UNIQUE NOT NULL,
    CreatedAt TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE Products (
    ProductId INTEGER PRIMARY KEY AUTOINCREMENT,
    Name TEXT NOT NULL,
    Price REAL NOT NULL,
    StockQuantity INTEGER NOT NULL DEFAULT 0
);

CREATE TABLE Orders (
    OrderId INTEGER PRIMARY KEY AUTOINCREMENT,
    CustomerId INTEGER NOT NULL,
    OrderDate TEXT NOT NULL,
    TotalAmount REAL NOT NULL,
    Status TEXT NOT NULL DEFAULT 'Pending',
    FOREIGN KEY (CustomerId) REFERENCES Customers(CustomerId)
);

CREATE TABLE OrderLines (
    OrderLineId INTEGER PRIMARY KEY AUTOINCREMENT,
    OrderId INTEGER NOT NULL,
    ProductId INTEGER NOT NULL,
    Quantity INTEGER NOT NULL,
    PriceAtOrderTime REAL NOT NULL,
    LineTotal REAL NOT NULL,
    FOREIGN KEY (OrderId) REFERENCES Orders(OrderId),
    FOREIGN KEY (ProductId) REFERENCES Products(ProductId)
);

CREATE INDEX idx_orders_customer ON Orders(CustomerId);
CREATE INDEX idx_orderlines_order ON OrderLines(OrderId);

C# Klasser

public class Customer {
    public int CustomerId { get; set; }
    public string Name { get; set; } = string.Empty;
    public string Email { get; set; } = string.Empty;
    public DateTime CreatedAt { get; set; }
}

public class Product {
    public int ProductId { get; set; }
    public string Name { get; set; } = string.Empty;
    public decimal Price { get; set; }
    public int StockQuantity { get; set; }
}

public class Order {
    public int OrderId { get; set; }
    public int CustomerId { get; set; }
    public DateTime OrderDate { get; set; }
    public decimal TotalAmount { get; set; }
    public string Status { get; set; } = "Pending";

    // Navigation properties
    public Customer? Customer { get; set; }
    public List<OrderLine> Lines { get; set; } = new();
}

public class OrderLine {
    public int OrderLineId { get; set; }
    public int OrderId { get; set; }
    public int ProductId { get; set; }
    public int Quantity { get; set; }
    public decimal PriceAtOrderTime { get; set; }
    public decimal LineTotal { get; set; }

    // Navigation property
    public Product? Product { get; set; }
}

Komplett OrderRepository

public class OrderRepository {
    private readonly string _connectionString;

    public OrderRepository(string connectionString) {
        _connectionString = connectionString;
    }

    public int CreateOrder(Order order) {
        using var connection = new SQLiteConnection(_connectionString);
        connection.Open();

        using var transaction = connection.BeginTransaction();
        try {
            // 1. Skapa order
            string sql = @"
                INSERT INTO Orders (CustomerId, OrderDate, TotalAmount, Status)
                VALUES (@customerId, @date, @total, @status);
                SELECT last_insert_rowid();";

            int orderId;
            using (var command = new SQLiteCommand(sql, connection, transaction)) {
                command.Parameters.AddWithValue("@customerId", order.CustomerId);
                command.Parameters.AddWithValue("@date", order.OrderDate.ToString("yyyy-MM-dd HH:mm:ss"));
                command.Parameters.AddWithValue("@total", (double)order.TotalAmount);
                command.Parameters.AddWithValue("@status", order.Status);
                orderId = Convert.ToInt32(command.ExecuteScalar());
            }

            // 2. Lägg till orderrader
            sql = @"
                INSERT INTO OrderLines (OrderId, ProductId, Quantity, PriceAtOrderTime, LineTotal)
                VALUES (@orderId, @productId, @qty, @price, @lineTotal)";

            foreach (var line in order.Lines) {
                using var command = new SQLiteCommand(sql, connection, transaction);
                command.Parameters.AddWithValue("@orderId", orderId);
                command.Parameters.AddWithValue("@productId", line.ProductId);
                command.Parameters.AddWithValue("@qty", line.Quantity);
                command.Parameters.AddWithValue("@price", (double)line.PriceAtOrderTime);
                command.Parameters.AddWithValue("@lineTotal", (double)line.LineTotal);
                command.ExecuteNonQuery();
            }

            transaction.Commit();
            return orderId;
        }
        catch {
            transaction.Rollback();
            throw;
        }
    }

    public Order? GetOrderWithDetails(int orderId) {
        using var connection = new SQLiteConnection(_connectionString);
        connection.Open();

        // Hämta order med customer
        string sql = @"
            SELECT
                o.OrderId, o.CustomerId, o.OrderDate, o.TotalAmount, o.Status,
                c.Name AS CustomerName, c.Email AS CustomerEmail
            FROM Orders o
            JOIN Customers c ON o.CustomerId = c.CustomerId
            WHERE o.OrderId = @id";

        Order? order = null;
        using (var command = new SQLiteCommand(sql, connection)) {
            command.Parameters.AddWithValue("@id", orderId);
            using var reader = command.ExecuteReader();

            if (reader.Read()) {
                order = new Order {
                    OrderId = reader.GetInt32(0),
                    CustomerId = reader.GetInt32(1),
                    OrderDate = DateTime.Parse(reader.GetString(2)),
                    TotalAmount = (decimal)reader.GetDouble(3),
                    Status = reader.GetString(4),
                    Customer = new Customer {
                        CustomerId = reader.GetInt32(1),
                        Name = reader.GetString(5),
                        Email = reader.GetString(6)
                    }
                };
            }
        }

        if (order == null) return null;

        // Hämta orderrader med produktinfo
        sql = @"
            SELECT
                ol.OrderLineId, ol.ProductId, ol.Quantity, ol.PriceAtOrderTime, ol.LineTotal,
                p.Name AS ProductName
            FROM OrderLines ol
            JOIN Products p ON ol.ProductId = p.ProductId
            WHERE ol.OrderId = @orderId";

        using (var command = new SQLiteCommand(sql, connection)) {
            command.Parameters.AddWithValue("@orderId", orderId);
            using var reader = command.ExecuteReader();

            while (reader.Read()) {
                order.Lines.Add(new OrderLine {
                    OrderLineId = reader.GetInt32(0),
                    ProductId = reader.GetInt32(1),
                    Quantity = reader.GetInt32(2),
                    PriceAtOrderTime = (decimal)reader.GetDouble(3),
                    LineTotal = (decimal)reader.GetDouble(4),
                    Product = new Product {
                        ProductId = reader.GetInt32(1),
                        Name = reader.GetString(5)
                    }
                });
            }
        }

        return order;
    }
}

Best practices

1. Använd using för connections och commands

// BRA ✓
using var connection = new SQLiteConnection(_connectionString);
connection.Open();
// Connection stängs automatiskt när using-blocket slutar

// DÅLIGT ❌
var connection = new SQLiteConnection(_connectionString);
connection.Open();
// ... kod ...
connection.Close();  // Glöms lätt! Memory leak!

2. Alltid använda parametrar (SQL injection!)

// DÅLIGT ❌ - SQL Injection!
string sql = $"SELECT * FROM Users WHERE Username = '{username}'";
// Om username = "admin' OR '1'='1" → alla users!

// BRA ✓
string sql = "SELECT * FROM Users WHERE Username = @username";
command.Parameters.AddWithValue("@username", username);

3. Hantera NULL korrekt

// BRA ✓
string? description = reader.IsDBNull(reader.GetOrdinal("Description"))
    ? null
    : reader.GetString(reader.GetOrdinal("Description"));

// Eller ännu bättre - extension method
public static class DataReaderExtensions {
    public static string? GetNullableString(this SQLiteDataReader reader, string columnName) {
        int ordinal = reader.GetOrdinal(columnName);
        return reader.IsDBNull(ordinal) ? null : reader.GetString(ordinal);
    }
}

// Användning
string? description = reader.GetNullableString("Description");

4. Använd transaktioner för flera operationer

// BRA ✓
using var transaction = connection.BeginTransaction();
try {
    // Flera operationer
    transaction.Commit();
}
catch {
    transaction.Rollback();
    throw;
}

5. Separera data access från business logic

// DÅLIGT ❌ - Business logic blandat med data access
public void ProcessOrder(int orderId) {
    using var connection = new SQLiteConnection(_connectionString);
    connection.Open();

    // SQL queries här
    // Business logic här
    // Mer SQL här
}

// BRA ✓ - Separation of concerns
public class OrderService {
    private readonly IOrderRepository _orderRepo;

    public void ProcessOrder(int orderId) {
        var order = _orderRepo.GetById(orderId);  // Data access via repository

        // Business logic här (inga SQL queries!)
        if (order.Status == "Pending") {
            order.Status = "Processing";
            _orderRepo.Update(order);
        }
    }
}

Vanliga fallgropar

1. Glömma stänga connections

// PROBLEM: Connection leak!
var connection = new SQLiteConnection(_connectionString);
connection.Open();
// ... kod ...
// Glömt .Close()!

// LÖSNING: Använd using
using var connection = new SQLiteConnection(_connectionString);

2. N+1 query-problemet

// DÅLIGT ❌ - N+1 queries!
var customers = GetAllCustomers();  // 1 query
foreach (var customer in customers) {
    customer.Orders = GetOrdersForCustomer(customer.CustomerId);  // N queries!
}
// Total: 1 + N queries (om 100 kunder = 101 queries!)

// BRA ✓ - En query med JOIN
string sql = @"
    SELECT c.CustomerId, c.Name, o.OrderId, o.OrderDate
    FROM Customers c
    LEFT JOIN Orders o ON c.CustomerId = o.CustomerId";
// Gruppera i C# kod

3. Inte använda parametrar

// DÅLIGT ❌
string sql = $"SELECT * FROM Users WHERE Id = {userId}";
// SQL injection risk + datatype problem

// BRA ✓
string sql = "SELECT * FROM Users WHERE Id = @id";
command.Parameters.AddWithValue("@id", userId);

4. Ignorera NULL-värden

// KRASCHAR om Description är NULL!
string description = reader.GetString(reader.GetOrdinal("Description"));

// BRA ✓
string? description = reader.IsDBNull(reader.GetOrdinal("Description"))
    ? null
    : reader.GetString(reader.GetOrdinal("Description"));

5. Lagra pengar som REAL/double

// DÅLIGT ❌ - Avrundningsfel!
public decimal Price { get; set; }
// Sparas som REAL i SQLite → 19.99 kan bli 19.989999...

// BRA ✓ - Spara som INTEGER (ören/cents)
CREATE TABLE Products (
    PriceInCents INTEGER NOT NULL  -- 1999 = 19.99 kr
);

public decimal Price {
    get => PriceInCents / 100m;
    set => PriceInCents = (int)(value * 100);
}
private int PriceInCents { get; set; }

Slutsats

Att arbeta med SQL och ADO.NET i C# ger dig:

✅ Full kontroll - Du skriver exakt den SQL du vill ✅ Maximum prestanda - Inga ORM overhead ✅ Förståelse - Du vet exakt vad som händer ✅ Flexibilitet - Kan optimera varje query

Men kräver:

⚠️ Mer kod - Manuell mappning mellan SQL och C# ⚠️ Mer ansvar - Du måste hantera NULL, connections, parametrar själv ⚠️ Mer underhåll - Ändra schema = ändra kod på flera ställen

Best practices:

Använd Repository Pattern för testbarhet
Alltid parametrar i SQL (aldrig string interpolation!)
Använd using för connections och commands
Hantera NULL korrekt
Transaktioner för flera relaterade operationer
Separera data access från business logic

Kom ihåg array-dilemmat: Två oberoende listor i en klass → separata tabeller i databasen!

TL;DR

ADO.NET i korthet:

// 1. Connection
using var connection = new SQLiteConnection(connectionString);
connection.Open();

// 2. Command med parametrar
string sql = "SELECT * FROM Products WHERE Price < @maxPrice";
using var command = new SQLiteCommand(sql, connection);
command.Parameters.AddWithValue("@maxPrice", 100);

// 3. Läs resultat
using var reader = command.ExecuteReader();
while (reader.Read()) {
    var product = new Product {
        ProductId = reader.GetInt32(0),
        Name = reader.GetString(1),
        Price = (decimal)reader.GetDouble(2)
    };
}

Datatyp-mappning:

SQL INTEGER → C# int/long
SQL TEXT → C# string
SQL REAL → C# decimal (för pengar!)
SQL NULL → C# T? (nullable)

Best practices:

✓ Använd using för connections
✓ Alltid parametrar (SQL injection!)
✓ Hantera NULL med IsDBNull()
✓ Repository Pattern för separation
✓ Transaktioner för flera operationer
✓ Array-dilemma: Oberoende listor → separata tabeller!

Undvik:

✗ String interpolation i SQL
✗ Glömma stänga connections
✗ Ignorera NULL-värden
✗ N+1 queries (använd JOIN!)
✗ Lagra pengar som double

ADO.NET = Full kontroll + Max prestanda, men mer kod! 🚀