MSQLite.Wrapper.Core 1.0.11

MSQLite.Wrapper

A high-performance, zero-dependency C++/CLI wrapper for SQLite, built specifically for .NET Core 3.1 .
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/x86.
It is not compatible with AnyCPU, ARM, or non-Windows platforms.

🧩 This wrapper includes Ijwhost.dll to enable mixed-mode C++/CLI support in .NET Core. Without it, native interop will fail with BadImageFormatException.

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.

🔐 Integrated Encryption & Build Configuration

• Encryption support via SQLite3 Multiple Ciphers
• Compiled with AVX instruction set for enhanced performance
• Custom build flags for optimized behavior and reduced footprint:

	SQLITE_DEFAULT_FOREIGN_KEYS=1       // Enforce foreign key constraints by default  
	HAVE_ISNAN=1                        // Enable isnan() support  
	SQLITE_OMIT_PROGRESS_CALLBACK       // Remove progress callback for leaner build  
	SQLITE_DEFAULT_MEMSTATUS=0          // Disable memory status tracking  
	SQLITE_DQS=0                        // Disable double-quoted string literals  
	SQLITE_DEFAULT_WAL_SYNCHRONOUS=1    // Safer WAL mode by default  
	SQLITE_OMIT_LOAD_EXTENSION          // Prevent runtime extension loading  
	SQLITE_OMIT_DEPRECATED              // Remove deprecated APIs  
	SQLITE_OMIT_SHARED_CACHE            // Disable shared cache mode  
	SQLITE_MAX_EXPR_DEPTH=0             // Unlimited expression depth  
	SQLITE_TEMP_STORE=2                 // Use memory for temporary storage  
	SQLITE_ENABLE_COMPRESS=1            // Enable built-in compression support  
	SQLITE_ENABLE_SQLAR=1               // Enable SQL archive format  
	SQLITE_ENABLE_ZIPFILE=1             // Enable ZIP virtual table  
	SQLITE3MC_USE_MINIZ=1               // Use miniz for encryption-compatible compression

FastLZ Compression

MSQLite includes built-in support for FastLZ compression via the MSQLite.Fastlz class.

byte[] compressed = Fastlz.Compress(data);
byte[] decompressed = Fastlz.Decompress(compressed);
byte[] compressedText = Fastlz.CompressString("Hello world");
string restored = Fastlz.DecompressString(compressedText);

⚠️ Ensure your project is targeting x64:

<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}, Score: {Score}, NullableInt: {NullableInt?.ToString() ?? "NULL"}";
        }
    }

    // Paths for the database files.
    private const string DbFile = "msqlite_test.db";
    private const string DbPass = "password";
    private const string DbPass2 = "2password";
    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,DbPass))
        {
            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);
            TestFastLz(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[0] == "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.
            object ocount = db.ExecuteScalar("SELECT COUNT(*) FROM Logs");
            Assert(Convert.ToInt32(ocount) == 1, "There should be one record in the Logs table.");

            // 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[0] == "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 { Id = 999, Name = "Bob", Email = "bob@example.com", CreatedAt = DateTime.UtcNow, IsActive = false };
            var upsertedBob = db.UpsertReturning("Users", bob);
            Assert(upsertedBob.Id == 3, "Bob's Id should be 3 after UpsertReturning (Insert).");
            Assert(db.Exists("Users", 3L), "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).
            int deleted = db.Delete("Users", new Dictionary<string, object> { { "Name", "Bob" } });
            Assert(!db.Exists("Users", upsertedBob.Id), "Bob should be deleted by name.");
            Assert(deleted == 1, $"We should delete only 1 record, but deleted {deleted}.");
        });
    }

    /// <summary>
    /// Tests various ways of iterating through query results.
    /// </summary>
    private static void TestIterationFeatures(SQLite db)
    {
        RunTest(nameof(TestIterationFeatures), () =>
        {
            db.ExecuteNonQuery("DELETE FROM Users");
            int deleted = db.GetLastChangeCount();
            Assert(deleted > 0 ,"Some records should be deleted !");

            // 1. Prepare data.
            var v1 = db.InsertReturning("Users", new User { Name = "User1", Email = "u1@a.com", CreatedAt = DateTime.UtcNow, IsActive = true });
            var v2 = db.InsertReturning("Users", new User { Name = "User2", Email = "u2@b.com", CreatedAt = DateTime.UtcNow, IsActive = false });

            using (var stmt = db.CreateStatement("SELECT Id, Name, IsActive FROM Users ORDER BY Id"))
            {
                // 2. Test IterateData (returns IEnumerable<object[]>).
                var rawData = stmt.IterateData().ToList();
                Assert(rawData.Count == 2, "IterateData should return 2 records.");
                Assert(rawData[0].Length == 3, "Each record should contain 3 columns.");
                Assert(rawData[0][1].ToString() == "User1", "The first user's name should be 'User1'.");

                // 3. Test IterateTyped (automatic mapping to a class).
                var typedData = stmt.IterateTyped<User>().ToList();
                Assert(typedData.Count == 2, "IterateTyped should return 2 objects.");
                Assert(typedData[0].Name == "User1" && typedData[0].IsActive, "The first user's data should be mapped correctly.");
                Assert(typedData[1].Name == "User2" && !typedData[1].IsActive, "The second user's data should be mapped correctly.");

                // 4. Test IterateMapped (custom mapping function).
                var mappedData = stmt.IterateMapped((row, colNames) => new {
                    Identifier = Convert.ToInt32(row[0]),
                    Username = Convert.ToString(row[1])
                }).ToList();
                Assert(mappedData.Count == 2, "IterateMapped should return 2 objects.");
                Assert(mappedData[1].Username == "User2", "Custom mapping should work correctly.");
            }
            db.Execute("DELETE FROM Users"); // Cleanup
            deleted = db.GetLastChangeCount();
            Assert(deleted > 0, "Some records should be deleted !");
        });
    }

    /// <summary>
    /// Tests full and incremental BLOB handling.
    /// </summary>
    private static void TestBlobHandling(SQLite db)
    {
        RunTest(nameof(TestBlobHandling), () =>
        {
            db.Execute("CREATE TABLE Files (Id INTEGER PRIMARY KEY, FileName TEXT, Data BLOB)");

            // 1. Test normal BLOB insert/read.
            var smallBlob = Encoding.UTF8.GetBytes("This is some small binary data.");
            var fileUser = new User { Name = "FileUser", ProfilePicture = smallBlob, CreatedAt = DateTime.UtcNow };
            fileUser = db.InsertReturning("Users", fileUser);

            using (var stmt = db.CreateStatement("SELECT ProfilePicture FROM Users WHERE Id = @id"))
            {
                stmt.Bind("@id", fileUser.Id);
                stmt.StepWithRetry();
                var readBlob = stmt.GetBlob(0);
                Assert(smallBlob.SequenceEqual(readBlob), "The read BLOB should match the original.");
            }

            // 2. Test incremental BLOB write/read.
            long fileSize = 1_000_000; // 1MB
            db.Execute($"INSERT INTO Files (FileName, Data) VALUES ('large.bin', zeroblob({fileSize}))");
            //long rowid = db.ExecuteScalar<long>("SELECT last_insert_rowid()");
            var rowid = db.ExecuteScalar<int>("SELECT last_insert_rowid()");
            
            // Create test data for writing.
            var originalData = new byte[fileSize];
            new Random().NextBytes(originalData); // Fill with random data

            // Incremental write.
            using (var writeStream = new MemoryStream(originalData))
            {
                Assert(db.WriteBlobIncremental("Files", "Data", rowid, writeStream), "Incremental BLOB write should succeed.");
            }

            // Incremental read.
            byte[] readData;
            using (var readStream = new MemoryStream())
            {
                Assert(db.ReadBlobIncremental("Files", "Data", rowid, readStream), "Incremental BLOB read should succeed.");
                readData = readStream.ToArray();
            }

            Assert(originalData.SequenceEqual(readData), "Incrementally read data should match the original.");
        });
    }

    /// <summary>
    /// Tests transaction and savepoint management.
    /// </summary>
    private static void TestTransactionsAndSavepoints(SQLite db)
    {
        RunTest(nameof(TestTransactionsAndSavepoints), () =>
        {
            // 1. Test transaction rollback.
            db.BeginTransaction();
            var u = db.InsertReturning("Users", new User { Id = 0, Name = "TempUser", CreatedAt = DateTime.UtcNow });
            db.RollbackTransaction();
            Assert(!db.Exists("Users", u.Id), "User should not exist after transaction rollback.");

            // 2. Test transaction commit.
            db.BeginTransaction();
            u = db.InsertReturning("Users", new User { Id = 0, Name = "PersistentUser", CreatedAt = DateTime.UtcNow });
            db.CommitTransaction();
            Assert(db.Exists("Users", u.Id), "User should exist after transaction commit.");

            // 3. Test TransactionScope with automatic rollback.
            try
            {
                using (var tx = new TransactionScope(db))
                {
                    u = db.InsertReturning("Users", new User { Id = 0, Name = "ScopeUserFail", CreatedAt = DateTime.UtcNow });
                    // Complete() is not called, a rollback should occur upon exiting the using block.
                }
            }
            catch { /* Ignore exceptions if any */ }
            Assert(!db.Exists("Users", u.Id), "User should not exist after TransactionScope rollback.");

            // 4. Test TransactionScope with explicit commit.
            using (var tx = new TransactionScope(db))
            {
                u = db.InsertReturning("Users", new User { Id = 0, Name = "ScopeUserSuccess", CreatedAt = DateTime.UtcNow });
                tx.Complete();
            }
            Assert(db.Exists("Users", u.Id), "User should exist after completing the TransactionScope.");

            // 5. Test savepoints.
            db.BeginTransaction();
            u = db.InsertReturning("Users", new User { Id = 0, Name = "BeforeSavepoint", CreatedAt = DateTime.UtcNow });

            // Create a savepoint.
            db.CreateSavepoint("MySavepoint");
            var u2 = db.InsertReturning("Users", new User { Id = 0, Name = "AfterSavepoint", CreatedAt = DateTime.UtcNow });

            // Rollback to the savepoint.
            db.RollbackToSavepoint("MySavepoint");

            db.CommitTransaction();

            Assert(db.Exists("Users", u.Id), $"User {u.Id} should exist (before the savepoint).");
            Assert(!db.Exists("Users", u2.Id), $"User {u.Id} should not exist (after savepoint rollback).");
        });
    }

    /// <summary>
    /// Tests schema retrieval and code generation features.
    /// </summary>
    private static void TestSchemaAndGeneration(SQLite db)
    {
        RunTest(nameof(TestSchemaAndGeneration), () =>
        {
            // 1. Get list of tables.
            var tables = db.GetTableList().Rows.Select(r => r[0]).ToList();
            Assert(tables.Contains("Logs") && tables.Contains("Users") && tables.Contains("Files"), "Tables Logs, Users, Files should be found.");

            // 2. Get table schema.
            var userSchema = db.GetTableSchema("Users");
            Assert(userSchema.Columns.Count == 8, "Schema should contain the correct number of columns.");
            var idCol = userSchema.Find("Id");
            var nameCol = userSchema.Find("Name");
            Assert(idCol != null && idCol.IsPrimaryKey, "The Id column should be the primary key.");
            Assert(nameCol != null && nameCol.Type == "TEXT", "The Name column should be of type TEXT.");

            // 3. Generate C# class from schema.
            string generatedClass = db.GenerateCSharpClassFromTable("Users");
            Console.WriteLine("\n  --- Generated C# Class ---");
            Console.WriteLine(generatedClass);
            Console.WriteLine("  --------------------------");
            Assert(generatedClass.Contains("public class Users") && generatedClass.Contains("public string Name { get; set; }"), "Generated code should contain expected properties.");
        });
    }

    /// <summary>
    /// Tests the database backup functionality.
    /// </summary>
    private static void TestBackup(SQLite db)
    {
        RunTest(nameof(TestBackup), () =>
        {
            int userCountBeforeBackup = db.ExecuteScalar<int>("SELECT COUNT(*) FROM Users");

            bool backupResult = db.BackupTo(BackupFile,DbPass2);
            Assert(backupResult, "Backup operation should return true.");
            Assert(File.Exists(BackupFile), "The backup file should be created.");

            // Verify the integrity of the backup.
            using (var backupDb = new SQLite(BackupFile,DbPass2))
            {
                int userCountAfterBackup = backupDb.ExecuteScalar<int>("SELECT COUNT(*) FROM Users");
                Assert(userCountBeforeBackup == userCountAfterBackup, "Backup should contain the same number of users.");
            }
        });
    }

    /// <summary>
    /// Tests error handling for invalid operations.
    /// </summary>
    private static void TestErrorHandling(SQLite db)
    {
        RunTest(nameof(TestErrorHandling), () =>
        {
            // 1. Attempt to execute invalid SQL.
            try
            {
                var result = db.Execute("SELECT * FROM NonExistentTable");
                Assert(result.IsSuccessful == false, "Executing invalid SQL should return IsSuccessful == false");
            }
            catch (SQLiteException ex)
            {
                Assert(ex.Message.Contains("no such table"), "Error message should indicate the table does not exist.");
            }

            // 2. Attempt to prepare an invalid statement.
            try
            {
                using (var stmt = db.CreateStatement("SELEC * FROM Users")) // Deliberate typo
                {
                    // Should not reach here
                }
                Assert(false, "Preparing an invalid statement should throw an exception.");
            }
            catch (SQLiteException ex)
            {
                Assert(ex.Message.Contains("Failed to prepare statement"), "Error message should indicate a preparation failure.");
            }
        });
    }


    /// <summary>
    /// Tests the database backup functionality.
    /// </summary>
    private static void TestFastLz(SQLite db)
    {
        RunTest(nameof(TestFastLz), () =>
        {
            var input_string = "This is long long input string for compress. This is long long input string for compress. This is long long input string for compress. This is long long input string for compress. This is long long input string for compress.";
            var compressed = Fastlz.CompressString(input_string);
            var decompressed = Fastlz.DecompressString(compressed);
            Assert(input_string.Equals(decompressed) , "Decompressed string must be the same as input string");

            var input = UTF8Encoding.UTF8.GetBytes(input_string);
            compressed = Fastlz.Compress(input);
            var output = Fastlz.Decompress(compressed);
            decompressed = UTF8Encoding.UTF8.GetString(output);
            Assert(input_string.Equals(decompressed), "Decompressed string must be the same as input string");
        });
    }


    /// <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}");
        }
    }

}