Shaunebu.Data.SQLite 1.0.0

Shaunebu.Data.SQLite 🗄️✨

Shaunebu.Data.SQLite provides a lightweight, thread-safe SQLite manager for .NET / .NET MAUI apps.

It supports:

• CRUD operations (single & batch)

• Table management (EnsureTableExistsAsync, ResetTableAsync)

• Fluent query building via TableContext<T>

🚀 Installation

Install via NuGet:

PM> Install-Package Shaunebu.Data.SQLite

NuGet Link: Shaunebu.Data.SQLite
Compatible with .NET 8+ / .NET MAUI 8+ projects.

⚙ Setup

using Shaunebu.Data.SQLite;
using Microsoft.Extensions.Logging;

// Optional: configure logging
var loggerFactory = LoggerFactory.Create(builder => builder.AddConsole());
var logger = loggerFactory.CreateLogger<SQLiteManagerService>();

// Get a database instance (singleton per file)
var db = SQLiteManagerService.GetInstance("mydatabase.db", logger);

Multiple calls to GetInstance("mydatabase.db") return the same object (thread-safe singleton).

🧩 Architecture Overview

+--------------------------+
| SQLiteManagerService     |
|--------------------------|
| - _connection            |
| - _logger                |
|--------------------------|
| + Insert<T>(...)         |
| + Update<T>(...)         |
| + Delete<T>(...)         |
| + EnsureTableExistsAsync |
| + ResetTableAsync        |
| + For<T>()               |
+-----------+--------------+
            |
            v
+--------------------------+
| TableContext<T>          |
|--------------------------|
| - _connection            |
| - _logger                |
|--------------------------|
| + InsertAsync(T entity)  |
| + InsertAsync(List<T>)   |
| + UpdateAsync(...)       |
| + DeleteAsync(...)       |
| + Where(...)             |
| + OrderBy(...)           |
| + OrderByDescending(...) |
| + ToListAsync()          |
| + FirstOrDefaultAsync()  |
+--------------------------+

• SQLiteManagerService → main entry point per database file.

• TableContext<T> → fluent query and CRUD API per table.

• Both support logging via Microsoft.Extensions.Logging.ILogger.

🛠 Table Management

Example:

await db.EnsureTableExistsAsync<User>();
await db.ResetTableAsync<Product>();
await db.For<Product>().DeleteAllAsync();

⚡ CRUD Operations

Insert

// Single
await db.Insert(new User { Name = "Alice" });

// Batch
await db.Insert(new List<User>
{
    new User { Name = "Bob" },
    new User { Name = "Charlie" }
});

Update

// Single
user.Name = "Alice Updated";
await db.Update(user);

// Batch
foreach (var u in users)
    u.Name += " Batch";
await db.Update(users);

Delete

// Single
await db.Delete(user);

// Batch
await db.Delete(usersToDelete);

🔎 Querying & Fluent API

TableContext<T> exposes a fluent query API:

var users = await db.For<User>()
                    .Where(u => u.Name.Contains("Alice"))
                    .OrderBy(u => u.Name)
                    .ToListAsync();

var firstUser = await db.For<User>()
                        .Where(u => u.Name == "Alice")
                        .FirstOrDefaultAsync();

var sortedProducts = await db.For<Product>()
                             .OrderByDescending(p => p.Price)
                             .ToListAsync();

Supported Methods in TableContext<T>:

🌐 TableContext Helper

You can create a table-specific context from the manager:

var usersTable = db.For<User>();
await usersTable.InsertAsync(new User { Name = "Diana" });

var allUsers = await usersTable.ToListAsync();

💡 Best Practices

1. Use GetInstance(dbPath) for each database; singleton per file ensures connection safety.

2. ResetTableAsync<T>() is ideal for testing or clearing tables.

3. Use TableContext<T> for fluent queries with LINQ expressions.

4. Always catch exceptions if you want to handle logging yourself; the manager logs errors automatically if a logger is provided.

🔍 Comparison & Advantages

💡 Key Advantages of Shaunebu.Data.SQLite :

1. Simplicity: easy to set up and use, perfect for MAUI or .NET projects without EF Core dependency.

2. Batch-friendly: insert, update, and delete lists of entities efficiently using transactions.

3. Built-in Logging: errors are automatically logged if you provide an ILogger.

4. Fluent API: query tables using LINQ with clear and expressive methods (Where, OrderBy, OrderByDescending).

5. Flexibility: use either the manager (SQLiteManagerService) or table-specific contexts (TableContext<T>).

🛠 Example: Full Flow

var userDb = SQLiteManagerService.GetInstance("users.db");
var productDb = SQLiteManagerService.GetInstance("products.db");

await userDb.EnsureTableExistsAsync<User>();
await productDb.EnsureTableExistsAsync<Product>();
await productDb.ResetTableAsync<Product>();

await userDb.Insert(new User { Name = "Alice" });
await productDb.Insert(new Product { Name = "Laptop", Price = 1200 });

var moreUsers = new List<User>
{
    new User { Name = "Bob" },
    new User { Name = "Charlie" }
};
await userDb.Insert(moreUsers);

var users = await userDb.For<User>().ToListAsync();
Console.WriteLine($"Users count: {users.Count}");

var userAlice = await userDb.For<User>()
                            .Where(u => u.Name == "Alice")
                            .FirstOrDefaultAsync();

userAlice!.Name = "Alice Updated";
await userDb.Update(userAlice);

// Delete example
await userDb.Delete(userAlice);

// Query with ordering
var sortedProducts = await productDb.For<Product>()
                                    .OrderByDescending(p => p.Price)
                                    .ToListAsync();

foreach (var p in sortedProducts)
    Console.WriteLine($"{p.Name} - {p.Price}");

🔗 References

• SQLite-net Async Documentation

• .NET MAUI + SQLite Guide