MSQLite.Wrapper.Core 1.0.7

MSQLite.Wrapper

A high-performance, zero-dependency C++/CLI wrapper for SQLite, built specifically for .NET Core 3.1 x64.
It may work on .NET 5 through .NET 9, but compatibility is not guaranteed.

This library provides both low-level control and high-level ORM-style features, offering a fast and efficient bridge between your .NET application and the power of SQLite—without requiring any external dependencies or native runtime installations.

⚠️ Important: This package is built exclusively for .NET Core 3.1 x64. It is not compatible with AnyCPU, x86, or other platforms.

Features

• Zero Dependencies
  Fully self-contained. Includes the SQLite source directly—no need to ship or install additional native libraries.

• High Performance
  Built with C++/CLI to minimize overhead between managed (.NET) and native (SQLite) layers.

• ORM-like Convenience
  Includes intuitive methods like InsertReturning, UpdateReturning, and UpsertReturning for seamless object mapping.

• Low-Level Control
  Exposes prepared Statement objects for precise parameter binding, result iteration, and query execution.

• Advanced SQLite Features
  Supports transactions, savepoints, incremental BLOB I/O, and full database backup operations.

• IntelliSense-Ready
  All public APIs are documented with XML comments for rich IntelliSense support in Visual Studio.

⚠️ Ensure your project is targeting x64: '''xml <PropertyGroup> <PlatformTarget>x64</PlatformTarget> </PropertyGroup> '''

Getting Started

Below is a complete test program demonstrating the major features in a practical context.

using MSQLite;
using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Text;

// A console application to test all features of the MSQLite library.
// Each major feature is tested in a separate method for clarity.
// The application creates a temporary database, runs the tests, and deletes it upon completion.
public class Program
{
    // Define a test model to verify ORM-like features.
    // The properties represent various data types supported by the library.
    public class User
    {
        // By convention, the "Id" property will be treated as the auto-incrementing primary key.
        public long Id { get; set; }
        public string Name { get; set; }
        public string Email { get; set; }
        public double Score { get; set; }
        public DateTime CreatedAt { get; set; }
        public bool IsActive { get; set; }
        public int? NullableInt { get; set; }
        public byte[] ProfilePicture { get; set; }

        public override string ToString()
        {
            return $"Id: {Id}, Name: {Name}, Email: {Email}, IsActive: {IsActive}, CreatedAt: {CreatedAt:O}, NullableInt: {NullableInt?.ToString() ?? "NULL"}";
        }
    }

    // Paths for the database files.
    private const string DbFile = "msqlite_test.db";
    private const string BackupFile = "msqlite_backup.db";

    public static void Main(string[] args)
    {
        // Pre-cleanup in case a previous run failed.
        File.Delete(DbFile);
        File.Delete(BackupFile);

        Console.WriteLine("--- Starting Test Suite for MSQLite Library ---");
        var stopwatch = Stopwatch.StartNew();

        try
        {
            // The main method that launches all tests.
            RunAllTests();
        }
        catch (Exception ex)
        {
            // In case of failure, print detailed error information.
            Console.ForegroundColor = ConsoleColor.Red;
            Console.WriteLine($"\n!!! TEST FAILED with unhandled exception: {ex.Message}");
            Console.WriteLine(ex.StackTrace);
            Console.ResetColor();
        }
        finally
        {
            // Final cleanup after all tests are done.
            File.Delete(DbFile);
            File.Delete(BackupFile);
            stopwatch.Stop();
            Console.WriteLine($"\n--- Test Suite Finished. Execution time: {stopwatch.ElapsedMilliseconds} ms. Cleanup complete. ---");
        }
    }

    /// <summary>
    /// The main test runner. Creates a DB connection and sequentially calls all test methods.
    /// </summary>
    private static void RunAllTests()
    {
        // All tests are executed within a single connection.
        using (var db = new SQLite(DbFile))
        {
            Console.WriteLine($"Database '{DbFile}' opened successfully.");

            // Call test methods in a logical order.
            TestBasicExecutionAndCrud(db);
            TestOrmFeatures(db);
            TestIterationFeatures(db);
            TestBlobHandling(db);
            TestTransactionsAndSavepoints(db);
            TestSchemaAndGeneration(db);
            TestErrorHandling(db);
            TestBackup(db);
        }
        Console.WriteLine($"Database '{DbFile}' closed.");
    }

    /// <summary>
    /// Tests basic operations: SQL execution, CRUD via Statement, and scalar queries.
    /// </summary>
    private static void TestBasicExecutionAndCrud(SQLite db)
    {
        RunTest(nameof(TestBasicExecutionAndCrud), () =>
        {
            // 1. Create a table using a simple Execute call.
            db.Execute("CREATE TABLE Logs (Id INTEGER PRIMARY KEY, Message TEXT, Timestamp INTEGER)");
            Assert(db.GetTableList().Rows.Any(r => r == "Logs"), "The 'Logs' table should be created.");

            // 2. Use CreateStatement to insert data with parameters.
            using (var stmt = db.CreateStatement("INSERT INTO Logs (Message, Timestamp) VALUES (@msg, @ts)"))
            {
                stmt.Bind("@msg", "System started");
                stmt.Bind("@ts", DateTimeOffset.UtcNow.ToUnixTimeSeconds());
                stmt.StepWithRetry(); // Execute a step to perform the insertion.
            }

            // 3. Use ExecuteScalar to check the record count.
            int count = db.ExecuteScalar<int>("SELECT COUNT(*) FROM Logs");
            Assert(count == 1, "There should be one record in the Logs table.");

            // 4. Read and verify the inserted data.
            using (var stmt = db.CreateStatement("SELECT Message FROM Logs WHERE Id = 1"))
            {
                Assert(stmt.StepWithRetry(), "A record with Id=1 should be found.");
                string message = (string)stmt.GetColumnValue(0);
                Assert(message == "System started", "The log message should match.");
            }

            // 5. Update data.
            db.Execute("UPDATE Logs SET Message = 'System updated' WHERE Id = 1");
            string updatedMessage = db.ExecuteScalar<string>("SELECT Message FROM Logs WHERE Id = 1");
            Assert(updatedMessage == "System updated", "The message should be updated.");

            // 6. Delete data.
            db.Execute("DELETE FROM Logs WHERE Id = 1");
            count = db.ExecuteScalar<int>("SELECT COUNT(*) FROM Logs");
            Assert(count == 0, "The record should be deleted.");
        });
    }

    /// <summary>
    /// Tests high-level ORM-like features: Insert, Update, Delete, Upsert, Exists.
    /// </summary>
    private static void TestOrmFeatures(SQLite db)
    {
        RunTest(nameof(TestOrmFeatures), () =>
        {
            // 1. Generate SQL to create a table based on a C# class and then execute it.
            string createSql = db.GenerateCreateTableSql<User>("Users");
            db.Execute(createSql);
            Assert(db.GetTableList().Rows.Any(r => r == "Users"), "The 'Users' table should be created.");

            // 2. Create a test object.
            var alice = new User { Name = "Alice", Email = "alice@example.com", Score = 95.5, CreatedAt = DateTime.UtcNow, IsActive = true, NullableInt = 100 };

            // 3. Test InsertReturning, which inserts an object and returns it with the populated ID.
            var insertedAlice = db.InsertReturning("Users", alice);
            Assert(insertedAlice.Id > 0, "The returned object should have a generated Id.");
            Assert(insertedAlice.Name == alice.Name, "Name should match after InsertReturning.");
            Console.WriteLine($"  - InsertReturning returned user: {insertedAlice}");

            // 4. Test Exists to check for the record's presence.
            Assert(db.Exists("Users", insertedAlice.Id), "User with the Id should exist.");

            // 5. Test UpdateReturning.
            insertedAlice.Name = "Alice Smith";
            var updatedAlice = db.UpdateReturning("Users", insertedAlice);
            Assert(updatedAlice.Name == "Alice Smith", "Name should be updated after UpdateReturning.");

            // 6. Test UpsertReturning (in update mode).
            updatedAlice.Score = 99.0;
            var upsertedAlice = db.UpsertReturning("Users", updatedAlice);
            Assert(upsertedAlice.Score == 99.0, "Score should be updated after UpsertReturning (Update).");

            // 7. Test UpsertReturning (in insert mode).
            var bob = new User { Name = "Bob", Email = "bob@example.com", CreatedAt = DateTime.UtcNow, IsActive = false };
            var upsertedBob = db.UpsertReturning("Users", bob);
            Assert(upsertedBob.Id > 0, "Bob should have a generated Id after UpsertReturning (Insert).");
            Assert(db.Exists("Users", upsertedBob.Id), "Bob should exist in the DB.");

            // 8. Test Delete by primary key.
            db.Delete("Users", insertedAlice.Id);
            Assert(!db.Exists("Users", insertedAlice.Id), "Alice should be deleted.");

            // 9. Test Delete by dictionary (WHERE clause).
            db.Delete("Users", new Dictionary<string, object> { { "Name", "Bob" } });
            Assert(!db.Exists("Users", upsertedBob.Id), "Bob should be deleted by name.");
        });
    }

    /// <summary>
    /// A helper method to run tests, print status, and catch specific test exceptions.
    /// </summary>
    private static void RunTest(string testName, Action testAction)
    {
        Console.Write($"\nRunning test: {testName}... ");
        try
        {
            testAction();
            Console.ForegroundColor = ConsoleColor.Green;
            Console.WriteLine("PASSED");
            Console.ResetColor();
        }
        catch (Exception ex)
        {
            Console.ForegroundColor = ConsoleColor.Red;
            Console.WriteLine($"FAILED\n  - Reason: {ex.Message}");
            Console.ResetColor();
            throw; // Re-throw to stop the entire test suite on failure.
        }
    }

    /// <summary>
    /// A simple assertion method. Throws an exception if the condition is false.
    /// </summary>
    private static void Assert(bool condition, string message)
    {
        if (!condition)
        {
            throw new Exception($"Assertion failed: {message}");
        }
    }
}
'''