Building an Offline-First Desktop Invoicing Application Using VB.NET / C# and SQLite

build 1. Enterprise Desktop Engineering: .NET & SQLite Prerequisites & Environment Setup

Install Visual Studio 2022 (.NET Desktop Development workload) and .NET 8 SDK on Windows 10/11.

Initialize your project workspace and install the verified production dependencies:

dotnet new wpf -n EnterpriseInvoicing
cd EnterpriseInvoicing
dotnet add package Microsoft.Data.Sqlite
dotnet add package Dapper
dotnet add package QuestPDF

account_tree 2. Enterprise Desktop Engineering: .NET & SQLite Execution Lifecycle

code 3. Step-by-Step Implementation of Enterprise Desktop Engineering: .NET & SQLite

1. SQLite Connection Manager with PRAGMA Settings

using Microsoft.Data.Sqlite;
using System.IO;

namespace EnterpriseInvoicing.Data
{
    public static class DatabaseManager
    {
        private static readonly string DbPath = Path.Combine(
            Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData),
            "JoelXOrbit", "invoicing.db"
        );

        public static string ConnectionString => $"Data Source={DbPath};";

        public static void InitializeDatabase()
        {
            Directory.CreateDirectory(Path.GetDirectoryName(DbPath)!);

            using var connection = new SqliteConnection(ConnectionString);
            connection.Open();

            // Enable WAL mode and foreign key constraints for high concurrency
            using var command = connection.CreateCommand();
            command.CommandText = @"
                PRAGMA journal_mode = WAL;
                PRAGMA foreign_keys = ON;
                PRAGMA synchronous = NORMAL;

                CREATE TABLE IF NOT EXISTS Invoices (
                    Id INTEGER PRIMARY KEY AUTOINCREMENT,
                    InvoiceNumber TEXT UNIQUE NOT NULL,
                    CustomerName TEXT NOT NULL,
                    TotalAmount REAL NOT NULL,
                    CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP
                );

                CREATE TABLE IF NOT EXISTS InvoiceItems (
                    Id INTEGER PRIMARY KEY AUTOINCREMENT,
                    InvoiceId INTEGER NOT NULL,
                    Description TEXT NOT NULL,
                    Quantity INTEGER NOT NULL,
                    UnitPrice REAL NOT NULL,
                    FOREIGN KEY (InvoiceId) REFERENCES Invoices(Id) ON DELETE CASCADE
                );
            ";
            command.ExecuteNonQuery();
        }
    }
}

2. Transactional Invoicing Service with Dapper

using Dapper;
using Microsoft.Data.Sqlite;

namespace EnterpriseInvoicing.Services
{
    public class InvoicingService
    {
        public async Task SaveInvoiceAsync(string invoiceNumber, string customer, decimal total, List<(string desc, int qty, decimal price)> items)
        {
            using var conn = new SqliteConnection(DatabaseManager.ConnectionString);
            await conn.OpenAsync();
            using var transaction = conn.BeginTransaction();

            try
            {
                var insertInvoiceSql = @"
                    INSERT INTO Invoices (InvoiceNumber, CustomerName, TotalAmount) 
                    VALUES (@invoiceNumber, @customer, @total);
                    SELECT last_insert_rowid();
                ";
                var invoiceId = await conn.ExecuteScalarAsync(insertInvoiceSql, new { invoiceNumber, customer, total }, transaction);

                var insertItemSql = @"
                    INSERT INTO InvoiceItems (InvoiceId, Description, Quantity, UnitPrice) 
                    VALUES (@invoiceId, @desc, @qty, @price);
                ";
                foreach (var item in items)
                {
                    await conn.ExecuteAsync(insertItemSql, new { invoiceId, item.desc, item.qty, item.price }, transaction);
                }

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

3. Automated SQLite Unit Test

// Automated in-memory SQLite verification
using Microsoft.Data.Sqlite;
using Xunit;

public class SqliteTests {
  [Fact]
  public void TestDatabaseOpen() {
    using var conn = new SqliteConnection("Data Source=:memory:");
    conn.Open();
    Assert.Equal(System.Data.ConnectionState.Open, conn.State);
  }
}

speed 4. Enterprise Desktop Engineering: .NET & SQLite Performance Benchmarks & Efficiency Analysis

security 5. Enterprise Desktop Engineering: .NET & SQLite Enterprise Security Hardening Checklist

• Encrypt sensitive customer invoices on disk using SQLCipher AES-256 database encryption.
• Store application data inside %AppData% or user profile paths rather than Program Files to avoid Windows UAC permission errors.
• Validate numeric quantities and decimal prices prior to initiating SQL transactions.
• Digitally sign desktop EXE binaries with a verified code-signing certificate to eliminate Windows SmartScreen warnings.

bug_report 6. Enterprise Desktop Engineering: .NET & SQLite Diagnostics & Production Troubleshooting Matrix

quiz 7. Frequently Asked Questions Regarding Enterprise Desktop Engineering: .NET & SQLite