Working with Local Databases in .NET Desktop Applications

Introduction to Local Databases

Local databases provide a persistent storage solution directly on the user's machine. This is ideal for applications that need to store user preferences, application state, offline data, or small datasets without relying on a remote server.

Common choices for local databases in .NET include:

• SQLite: A lightweight, file-based relational database engine. It's cross-platform and widely adopted.
• SQL Server Compact (SQL CE): A discontinued but still usable embedded database from Microsoft.
• Entity Framework Core (with a local provider): While primarily an ORM, EF Core can be configured to use local providers like SQLite or In-Memory databases for development and testing.
• LocalDB: A lightweight version of SQL Server Express, often used for local development and testing.

Using SQLite with .NET

SQLite is a popular choice due to its simplicity and excellent .NET support through libraries like System.Data.SQLite or the more modern Microsoft.Data.SQLite (part of the EF Core ecosystem).

Installation

Install the NuGet package:

dotnet add package Microsoft.Data.Sqlite

Connection String

A typical connection string for SQLite looks like this:

string connectionString = "Data Source=MyDatabase.db;";

Basic Operations (using ADO.NET)

Here's a simple example of creating a table and inserting data:

using Microsoft.Data.Sqlite;
using System;

public class DatabaseManager
{
    public static void InitializeDatabase(string dbPath)
    {
        using (var connection = new SqliteConnection($"Data Source={dbPath}"))
        {
            connection.Open();

            var command = connection.CreateCommand();
            command.CommandText = @"
                CREATE TABLE IF NOT EXISTS Items (
                    Id INTEGER PRIMARY KEY AUTOINCREMENT,
                    Name TEXT NOT NULL,
                    Value REAL
                );
            ";
            command.ExecuteNonQuery();
        }
    }

    public static void InsertItem(string dbPath, string name, double value)
    {
        using (var connection = new SqliteConnection($"Data Source={dbPath}"))
        {
            connection.Open();

            var command = connection.CreateCommand();
            command.CommandText = "INSERT INTO Items (Name, Value) VALUES (@Name, @Value)";
            command.Parameters.AddWithValue("@Name", name);
            command.Parameters.AddWithValue("@Value", value);
            command.ExecuteNonQuery();
        }
    }

Using Entity Framework Core with SQLite

Entity Framework Core (EF Core) provides an Object-Relational Mapper (ORM) that simplifies database interactions by allowing you to work with C# objects instead of raw SQL.

Installation

Install the EF Core provider for SQLite:

dotnet add package Microsoft.EntityFrameworkCore.Sqlite

Defining DbContext and Entities

using Microsoft.EntityFrameworkCore;

public class Item
{
    public int Id { get; set; }
    public string Name {  set; }
    public double Value {  set; }
}

public class AppDbContext : DbContext
{
    public DbSet<Item> Items {  set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlite("Data Source=MyEFCoreDatabase.db");
    }
}

Migrations

EF Core uses migrations to manage database schema changes. You'll need to install the EF Core tools:

dotnet tool install --global dotnet-ef

Then, create your first migration:

dotnet ef migrations add InitialCreate

And apply it to the database:

dotnet ef database update

Using the DbContext

using (var context = new AppDbContext())
{
    context.Items.Add(new Item { Name = "Example Item", Value = 123.45 });
    context.SaveChanges();

    var allItems = context.Items.ToList();
    foreach (var item in allItems)
    {
        Console.WriteLine($"ID: {item.Id}, Name: {item.Name}, Value: {item.Value}");
    }
}

Data Access Patterns

When working with local databases, consider these patterns:

• Repository Pattern: Abstract the data access logic, making your application more testable and maintainable.
• Unit of Work Pattern: Manage transactions across multiple repository operations.
• Dependency Injection: Inject your DbContext or repository instances into your application logic.

Security Considerations

While local databases are on the user's machine, consider these security aspects:

• Data Encryption: For sensitive data, consider encrypting the database file or specific sensitive columns.
• Access Control: Ensure your application's code correctly handles data access and doesn't expose sensitive information inappropriately.
• Input Validation: Always validate user input before saving it to the database to prevent data corruption and potential security issues.

Conclusion

Local databases offer a powerful way to store data directly within your .NET desktop applications. Whether you choose the simplicity of ADO.NET with SQLite or the abstraction of EF Core, understanding these concepts will help you build robust and efficient applications.