开发者

C# SQLite 高级功能详解(推荐)

开发 作者： yz123lucky
目录
  • SQLite 基础介绍
  • 主要特点
  • 环境配置
  • 事务处理详解
  • 事务基础概念
  • 基本事务操作
  • 高级事务控制
  • 事务性业务逻辑封装
  • 连接管理与连接池
  • 批量操作优化
  • 异步操作
  • 索引优化
  • 锁机制与并发控制
  • 备份与恢复
  • 性能优化技巧
  • 实体类定义
  • 数据库表结构
  • 总结

SQLite 基础介绍

SQLite 是一个轻量级的嵌入式关系数据库，特别适合桌面应用程序和移动应用程序。它支持 ACID 特性，提供了强大的事务控制功能。

主要特点

  • 无需服务器配置
  • 跨平台支持
  • 支持标准 SQL 语法
  • 文件级数据库
  • 支持事务和 ACID 特性

环境配置

首先安装必要的 NuGet 包：

<PackageReference Include="Microsoft.Data.Sqlite" Version="7.0.0" />
<PackageReference Include="System.Data.SQLite" Version="1.0.118" />

基础连接设置：

using Microsoft.Data.Sqlite;
using System.Data;
using System.Data.SQLite;
public class DatabaseConnection
{
    private readonly string _connectionString;
    public DatabaseConnection(string databasePath)
    {
        _connectionString = $"Data Source={databasePath};";
    }
    public SqliteConnection GetConnection()
    {
        return new SqliteConnection(_connectionString);
    }
}

事务处理详解

事务基础概念

事务是数据库操作的基本单元，必须满足 ACID 特性：

  • 原子性(Atomicity)：事务中的所有操作要么全部完成，要么全部不执行
  • 一致性(Consistency)：事务前后数据库状态保持一致
  • 隔离性(Isolation)：并发事务之间相互隔离
  • 持久性(Durability)：事务提交后，数据永久保存

基本事务操作

public class TransactionExample
{
    private readonly string _connectionString;
    public TransactionExample(string connectionString)
    {
        _connectionString = connectionString;
    }
    // 基本事务操作
    public void BasicTransactionExample()
    {
        using var connection = new SqliteConnection(_connectionString);
        connection.Open();
        using var transaction = connection.BeginTransaction();
        try
        {
            // 创建命令并关联事务
            using var command = connection.CreateCommand();
            command.Transaction = transaction;
            // 执行多个相关操作
            command.CommandText = "INSERT INTO Users (Name, Email) VALUES (@name, @email)";
            command.Parameters.AddwithValue("@name", "张三");
            command.Parameters.AddWithValue("@email", "zhangsan@example.com");
            command.ExecuteNonQuery();
            command.Parameters.Clear();
            command.CommandText = "INSERT INTO UserProfiles (UserId, Age) VALUES (last_insert_rowid(), @age)";
            command.Parameters.AddWithValue("@age", 25);
            command.ExecuteNonQuery();
            // 提交事务
            transaction.Commit();
            Console.WriteLine("事务成功提交");
        }
        catch (Exception ex)
        {
            // 回滚事务
            transaction.Rollback();
            Console.WriteLine($"事务回滚: {ex.Message}");
            throw;
        }
    }
}

高级事务控制

public class AdvancedTransactionControl
{
    private readonly string _connectionString;
    public AdvancedTransactionControl(string connectionString)
    {
        _connectionString = connectionString;
    }
    // 设置事务隔离级别
    public void TransactionWithIsolationLevel()
    {
        using var connection = new SqliteConnection(_connectionString);
        connection.Open();
        // SQLite 支持的隔离级别
        using var transaction = connection.BeginTransaction(IsolationLevel.Serializable);
        try
        {
            var command = connection.CreateCommand();
            command.Transaction = transaction;
            // 执行业务逻辑
            command.CommandText = "UPDATE Accounts SET Balance = Balance - 100 WHERE Id = 1";
            int affected = command.ExecuteNonQuery();
            if (affected == 0)
            {
                throw new InvalidOperationException("账户不存在或余额不足");
            }
            command.CommandText = "UPDATE Accounts SET Balance = Balance + 100 WHERE Id = 2";
            command.ExecuteNonQuery();
            transaction.Commit();
        }
        catch
        {
            transaction.Rollback();
            throw;
        }
    }
    // 嵌套事务（保存点）
    public void SavepointExample()
    {
        using var connection = new SqliteConnection(_connectionString);
        connection.Open();
        using var mainTransaction = connection.BeginTransaction();
        try
        {
            var command = connection.CreateCommand();
            command.Transaction = mainTransaction;
            // 主要操作
   javascript         command.CommandText = "INSERT INTO Orders (CustomerId, Amount) VALUES (1, 100)";
            command.ExecuteNonQuery();
            // 创建保存点
            command.CommandText = "SAVEPOINT sp1";
            command.ExecuteNonQuery();
            try
            {
                // 可能失败的操作
                command.CommandText = "INSERT INTO OrderDetails (OrderId, ProductId) VALUES (last_insert_rowid(), 999)";
                command.ExecuteNonQuery();
            }
            catch
            {
                // 回滚到保存点
                command.CommandText = "ROLLBACK TO sp1";
                command.ExecuteNonQuery();
                // 执行替代操作
                command.CommandText = "INSERT INTO OrderDetails (OrderId, ProductId) VALUES (last_insert_rowid(), 1)";
                command.ExecuteNonQuery();
            }
            // 释放保存点
            command.CommandText = "RELEASE sp1";
            command.ExecuteNonQuery();
            mainTransaction.Commit();
        }
        catch
        {
            mainTransaction.Rollback();
            throw;
        }
    }
}

事务性业务逻辑封装

public class BankingService
{
    private readonly string _connectionString;
    public BankingService(string connectionString)
    {
        _connectionString = connectionString;
    }
    // 转账操作 - 事务应用实例
    public async Task<bool> TransferMoney(int froMACcountId, int toAccountId, decimal amount)
    {
        using var connection = new SqliteConnection(_connectionString);
        await connection.OpenAsync();
        using var transaction = await connection.BeginTransactionAsync();
        try
        {
            // 检查源账户余额
            decimal sourceBalance = await GetAccountBalance(connection, transaction, fromAccountId);
            if (sourceBalance < amount)
            {
                throw new InvalidOperationException("余额不足");
            }
            // 从源账户扣款
            await UpdateAccountBalance(connection, transaction, fromAccountId, -amount);
            // 向目标账户加款
            await UpdateAccountBalance(connection, transaction, toAccountId, amount);
            // 记录转账历史
            await RecordTransferHistory(connection, transaction, fromAccountId, toAccountId, amount);
            await transaction.CommitAsync();
            return true;
        }
        catch (Exception ex)
        {
            await transaction.RollbackAsync();
            Console.WriteLine($"转账失败: {ex.Message}");
            return false;
        }
    }
    private async Task<decimal> GetAccountBalance(SqliteConnection connection, SqliteTransaction transaction, int accountId)
    {
        using var command = connection.CreateCommand();
        command.Transaction = transaction;
        command.CommandText = "SELECT Balance FROM Accounts WHERE Id = @id FOR UPDATE"; // 行锁定
        command.Parameters.AddWithValue("@id", accountId);
        var result = await command.ExecuteScalarAsync();
        return result != null ? Convert.ToDecimal(result) : 0;
    }
    private async Task UpdateAccountBalance(SqliteConnection connection, SqliteTransaction transaction, int accountId, decimal amount)
    {
        using var command = connection.CreateCommand();
        command.Transaction = transaction;
        command.CommandText = "UPDATE Accounts SET Balance = Balance + @amount WHERE Id = @id";
        command.Parameters.AddWithValue("@amount", amount);
        command.Parameters.AddWithValue("@id", accountId);
        int affected = await command.ExecuteNonQueryAsync();
        if (affected == 0)
        {
            throw new InvalidOperationException($"账户 {accountId} 不存在");
        }
    }
    private async Task RecordTransferHistory(SqliteConnection connection, SqliteTransaction transaction, 
        int fromAccountId, int toAccountId, decimal amount)
    {
        using var command = connection.CreateCommand();
        command.Transaction = transaction;
        command.CommandText = @"
            INSERT INTO TransferHistory (FromAccountId, ToAccountId, Amount, TransferDate) 
            VALUES (@from, @to, @amount, @date)";
        command.Parameters.AddWithValue("@from", fromAccountId);
        command.Parameters.AddWithValue("@to", toAccountId);
        command.Parameters.AddWithValue("@amount", amount);
        command.Parameters.AddWithValue("@date", DateTime.UtcNow);
        await command.ExecuteNonQueryAsync();
    }
}

连接管理与连接池

SQLite 连接管理和池化：

public class ConnectionPoolManager
{
    private readonly ConcurrentQueue<SqliteConnection> _connectionPool;
    private readonly string _connectionString;
    private readonly int _maxPoolSize;
    private int _currentPoolSize;
    public ConnectionPoolManager(string connectionString, int maxPoolSize = 10)
    {
        _connectionString = connectionString;
        _maxPoolSize = maxPoolSize;
        _connectionPool = new ConcurrentQueue<SqliteConnection>();
        _currentPoolSize = 0;
    }
    public async Task<SqliteConnection> GetConnectionAsync()
    {
        if (_connectionPool.TryDequeue(out var connection))
        {
            if (connection.State == ConnectionState.Open)
            {
                return connection;
            }
            else
            {
                connection.Dispose();
                Interlocked.Decrement(ref _currentPoolSize);
            }
        }
        // 创建新连接
        connection = new SqliteConnection(_connectionString);
        await connection.OpenAsync();
        Interlocked.Increment(ref _currentPoolSize);
        return connection;
    }
    public void ReturnConnection(SqliteConnection connection)
    {
        if (connection.State == ConnectionState.Open && _currentPoolSize <= _maxPoolSize)
        {
            _connectionPool.Enqueue(connection);
        }
        else
        {
            connection.Dispose();
            Interlocked.Decrement(ref _currentPoolSize);
        }
    }
    public void Dispose()
    {
        while (_connectionPool.TryDequeue(out var connection))
        {
            connection.Dispose();
        }
    }
}

批量操作优化

public class BATchOperations
{
    private readonly string _connectionString;
    public BatchOperations(string connectionString)
    {
        _connectionString = connectionString;
    }
    // 批量插入优化
    public async Task BulkInsertOptimized(List<User> users)
    {
        using var connection = new SqliteConnection(_connectionString);
        await connection.OpenAsync();
        using var transaction = await connection.BeginTransactionAsync();
        try
        {
            // 方法1：使用参数化批量插入
            using var command = connection.CreateCommand();
            command.Transaction = transaction;
            command.CommandText = "INSERT INTO Users (Name, Email, Age) VALUES (@name, @email, @age)";
            var nameParam = command.Parameters.Add("@name", SqliteType.Text);
            var emailParam = command.Parameters.Add("@email", SqliteType.Text);
            var ageParam = command.Parameters.Add("@age", SqliteType.Integer);
            foreach (var user in users)
            {
                nameParam.Value = user.Name;
                emailParam.Value = user.Email;
                ageParam.Value = user.Age;
                await command.ExecuteNonQueryAsync();
            }
            await transaction.CommitAsync();
        }
        catch
        {
            await transaction.RollbackAsync();
            throw;
        }
    }
    // 超大批量插入（分批处理）
    public async Task BulkInsertLargeDataset(IEnumerable<User> users, int batchSize = 1000)
    {
        var userList = users.ToList();
        int totalBatches = (int)Math.Ceiling((double)userList.Count / batchSize);
        for (int batch = 0; batch < totalBatches; batch++)
        {
            var batchUsers = userList.Skip(batch * batchSize).Take(batchSize);
            await BulkInsertOptimized(batchUsers.ToList());
            // 可选：报告进度
            Console.WriteLine($"处理批次 {batch + 1}/{totalBatches}");
        }
    }
    // 使用 VALUES 子句批量插入
    public async Task BulkInsertWithValues(List<User> users)
    {
        using var connection = new SqliteConnection(_connectionString);
        await connection.OpenAsync();
        using var transaction = await connection.BeginTransactionAsync();
        try
        {
            const int batchSize = 500;
            for (int i = 0; i < users.Count; i += batchSize)
            {
                var batch = users.Skip(i).Take(batchSize);
                var values = new List<string>();
                var parameters = new List<SqliteParameter>();
                int paramIndex = 0;
                foreach (var user in batch)
                {
                    values.Add($"(@name{paramIndex}, @email{paramIndex}, @age{paramIndex})");
                    parameters.Add(new SqliteParameter($"@name{paramIndex}", user.Name));
                    parameters.Add(new SqliteParameter($"@email{paramIndex}", user.Email));
                    parameters.Add(new SqliteParameter($"@age{paramIndex}", user.Age));
                    paramIndex++;
                }
                using var command = connection.CreateCommand();
                command.Transaction = transaction;
                command.CommandText = $"INSERT INTO Users (Name, Email, Age) VALUES {string.Join(", ", values)}";
                command.Parameters.AddRange(parameters.ToArray());
                await command.ExecuteNonQueryAsync();
            }
            await transaction.CommitAsync();
        }
        catch
        {
            await transaction.RollbackAsync();
            throw;
        }
    }
}

异步操作

public class AsyncDatabaseoperations
{
    private readonly string _connectionString;
    public AsyncDatabaseOperations(string connectionString)
    {
        _connectionString = connectionString;
    }
    // 异步事务操作
    public async Task<bool> ProcessOrderAsync(Order order, List<OrderItem> items)
    {
        using var connection = new SqliteConnection(_connectionString);
        await connection.OpenAsync();
        using var transaction = await connection.BeginTransactionAsync();
        try
        {
            // 插入订单
            long orderId = await InsertOrderAsync(connection, transaction, order);
            // 并行处理订单项
            var tasks = items.Select(item => InsertOrderItemAsync(connection, transaction, orderId, item));
            await Task.WhenAll(tasks);
            // 更新库存
            await UpdateInventoryAsync(connection, transaction, items);
            await transaction.CommitAsync();
            return true;
        }
        catch (Exception ex)
        {
            await transaction.RollbackAsync();
            Console.WriteLine($"订单处理失败: {ex.Message}");
            return false;
        }
    }
    private async Task<long> InsertOrderAsync(SqliteConnection connection, SqliteTransaction transaction, Order order)
    {
        using var command = connection.CreateCommand();
        command.Transaction = transaction;
        command.CommandText = @"
            INSERT INTO Orders (CustomerId, OrderDate, TotalAmount) 
            VALUES (@customerId, @orderDate, @totalAmount);
            SELECT last_insert_rowid();";
        command.Parameters.AddWithValue("@customerId", order.CustomerId);
        command.Parameters.AddWithValue("@orderDate", order.OrderDate);
        command.Parameters.AddWithValue("@totalAmount", order.TotalAmount);
        var result = await command.ExecuteScalarAsync();
        return Convert.ToInt64(result);
    }
    private async Task InsertOrderItemAsync(SqliteConnection connection, SqliteTransaction transaction, 
        long orderId, OrderItem item)
    {
        using var command = connection.CreateCommand();
        command.Transaction = transaction;
        command.CommandText = @"
            INSERT INTO OrderItems (OrderId, ProductId, Quantity, UnitPrice) 
            VALUES (@orderId, @productId, @quantity, @unitPrice)";
        command.Parameters.AddWithValue("@orderId", orderId);
        command.Parameters.AddWithValue("@productId", item.ProductId);
        command.Parameters.AddWithValue("@quantity", item.Quantity);
        command.Parameters.AddWithValue("@unitPrice", item.UnitPrice);
        await command.ExecuteNonQueryAsync();
    }
    private async Task UpdateInventoryAsync(SqliteConnection connection, SqliteTransaction transaction, 
        List<OrderItem> items)
    {
        using var command = connection.CreateCommand();
        command.Transaction = transaction;
        foreach (var item in items)
        {
            command.CommandText = "UPDATE Products SET Stock = Stock - @quantity WHERE Id = @productId";
            command.Parameters.Clear();
            command.Parameters.AddWithValue("@quantity", item.Quantity);
            command.Parameters.AddWithValue("@productId", item.ProductId);
            int affected = await command.ExecuteNonQueryAsync();
            if (affected == 0)
            {
                throw new InvalidOperationException($"产品 {item.ProductId} 库存更新失败");
            }
        }
    }
}

索引优化

public class IndexOptimization
{
    private readonly string _connectionString;
    public IndexOptimization(string connectionString)
    {
        _connectionString = connectionString;
    }
    // 创建和管理索引
    public async Task CreateOptimalIndexes()
    {
        using var connection = new SqliteConnection(_connectionString);
        await connection.OpenAsync();
        using var command = connection.CreateCommand();
        // 单列索引
        command.CommandText = "CREATE INDEX IF NOT EXISTS idx_users_email ON Users(Email)";
        await command.ExecuteNonQueryAsync();
        // 复合索引
        command.CommandText = "CREATE INDEX IF NOT EXISTS idx_orders_customer_date ON Orders(CustomerId, OrderDate)";
        await command.ExecuteNonQueryAsync();
        // 部分索引（条件索引）
        command.CommandText = "CREATE INDEX IF NOT EXISTS idx_orders_pending ON Orders(OrderDate) WHERE Status = 'Pending'";
        await command.ExecuteNonQueryAsync();
        // 唯一索引
        command.CommandText = "CREATE UNIQUE INDEX IF NOT EXISTS idx_users_username ON Users(Username)";
        await command.ExecuteNonQueryAsync();
    }
    // 分析查询性能
    public async Task AnalyzeQueryPerformance(string query)
    {
        using var connection = new SqliteConnection(_connectionString);
        await connection.OpenAsync();
        using var command = connection.CreateCommand();
        command.CommandText = $"EXPLAIN QUERY PLAN {query}";
        using var reader = await command.ExecuteReaderAsync();
        Console.WriteLine("查询执行计划:");
        while (await reader.ReadAsync())
        {
            Console.WriteLine($"ID: {reader[0]}, Parent: {reader[1]}, NotUsed: {reader[2]}, Detail: {reader[3]}");
        }
    }
    // 索引使用统计
    public async Task GetIndexStatistics()
    {
        using var connection = new SqliteConnection(_connectionString);
        await connection.OpenAsync();
        using var command = connection.CreateCommand();
        command.CommandText = @"
            SELECT name, sql 
            FROM sqlite_master 
            WHERE type = 'index' 
            AND sql IS NOT NULL
            ORDER BY name";
        using var reader = await command.ExecuteReaderAsync();
        Console.WriteLine("数据库索引列表:");
        while (await reader.ReadAsync())
        {
            Console.WriteLine($"索引: {reader["name"]}, SQL: {reader["sql"]}");
        }
    }
}

锁机制与并发控制

public class ConcurrencyControl
{
    private readonly string _connectionString;
    private readonly SemaphoreSlim _semaphore;
    public ConcurrencyControl(string connectionString, int maxConcurrentConnections = 5)
    {
        _connectionString = connectionString;
        _semaphore = new SemaphoreSlim(maxConcurrentConnections, maxConcurrentConnections);
    }
    // 悲观锁实现
    public async Task<bool> UpdateWithPessimisticLock(int userId, string newEmail)
    {
        await _semaphore.WaitAsync();
        try
        {
            using var connection = new SqliteConnection(_connectionString);
            await connection.OpenAsync();
            using var transaction = await connection.BeginTransactionAsync(IsolationLevel.Serializable);
            try
            {
                // 查询并锁定行
                using var selectCommand = connection.CreateCommand();
                selectCommand.Transaction = transaction;
                selectCommand.CommandTextjavascript = "SELECT Email, Version FROM Users WHERE Id = @id";
                selectCommand.Parameters.AddWithValue("@id", userId);
                using var reader = await selectCommand.ExecuteReaderAsync();
                if (!await reader.ReadAsync())
                {
                    return false;
                }
                string currentEmail = reader["Email"].ToString();
                int currentVersion = Convert.ToInt32(reader["Version"]);
                reader.Close();
                // 模拟业务处理时间
                await Task.Delay(100);
                // 更新数据
                using var updateCommand = connection.CreateCommand();
                updateCommand.Transaction = transaction;
                updateCommand.CommandText = @"
                    UPDATE Users 
                    SET Email = @email, Version = Version + 1, UpdatedAt = @updatedAt
                    WHERE Id = @id AND Version = @version";
                updateCommand.Parameters.AddWithValue("@email", newEmail);
                updateCommand.Parameters.AddWithValue("@updatedAt", DateTime.UtcNow);
                updateCommand.Parameters.AddWithValue("@id", userId);
                updateCommand.Parameters.AddWithValue("@version", currentVersion);
                int affected = await updateCommand.ExecuteNonQueryAsync();
                if (affected > 0)
                {
                    await transaction.CommitAsync();
                    return true;
                }
                else
                {
                    await transaction.RollbackAsync();
                    return false;
                }
            }
            catch
            {
                await transaction.RollbackAsync();
                throw;
            }
        }
        finally
        {
            _semaphore.Release();
        }
    }
    // 乐观锁实现
    public async Task<bool> UpdateWithOptimisticLock(int userId, string newEmail, int expectedVersion)
    {
        using var connection = new SqliteConnection(_connectionString);
        await connection.OpenAsync();
        using var command = connection.CreateCommand();
        command.CommandText = @"
            UPDATE Users 
            SET Email = @email, Version = Version + 1, UpdatedAt = @updatedAt
            WHERE Id = @id AND Version = @expectedVersion";
        command.Parameters.AddWithValue("@email", newEmail);
        command.Parameters.AddWithValue("@updatedAt", DateTime.UtcNow);
        command.Parameters.AddWithValue("@id", userId);
        command.Parameters.AddWithValue("@expectedVersion", expectedVersion);
        int affected = await command.ExecuteNonQueryAsync();
        return affected > 0;
    }
    // 分布式锁模拟
    public async Task<bool> TryAcquiRedistributedLock(string lockName, TimeSpan expiration)
    {
        using var connection = new SqliteConnection(_connectionString);
        await connection.OpenAsync();
        using var command = connection.CreateCommand();
        command.CommandText = @"
            INSERT OR IGNORE INTO DistributedLocks (LockName, ExpiresAt, CreatedAt)
            VALUES (@lockName, @expiresAt, @createdAt)";
        command.Parameters.AddWithValue("@lockName", lockName);
        command.Parameters.AddWithValue("@expiresAt", DateTime.UtcNow.Add(expiration));
        command.Parameters.AddWithValue("@createdAt", DateTime.UtcNow);
        int affected = await command.ExecuteNonQueryAsync();
        return affected > 0;
    }
    public async Task ReleaseLock(string lockName)
    {
        using var connection = new SqliteConnection(_connectionString);
        await connection.OpenAsync();
        using var command = connection.CreateCommand();
        command.CommandText = "DELETE FROM DistributedLocks WHERE LockName = @lockName";
        command.Parameters.AddWithValue("@lockName", lockName);
        await command.ExecuteNonQueryAsync();
    }
}

备份与恢复

public class BackupAndRestore
{
    private readonly string _connectionString;
    public BackupAndRestore(string connectionString)
    {
        _connectionString = connectionString;
    }
    // 在线备份
    public async Task BackupDatabase(string backupPath)
    {
        using var sourceConnection = new SqliteConnection(_connectionString);
        using var backupConnection = new SqliteConnection($"Data Source={backupPath}");
        await sourceConnection.OpenAsync();
        await backupConnection.OpenAsync();
        // 使用 SQLite 内置备份 API
        sourceConnection.BackupDatabase(backupConnection, "main", "main");
        Console.WriteLine($"数据库备份完成: {backupPath}");
    }
    // 增量备份（通过时间戳）
    public async Task IncrementalBackup(string backupPath, DateTime lastBackupTime)
    {
        using var connection = new SqliteConnection(_connectionString);
        await connection.OpenAsync();
        // 导出自上次备份以来的更改
        var tables = new[] { "Users", "Orders", "OrderItems" };
        var backupData = new Dictionary<string, List<Dictionary<string, object>>>();
        foreach (var table in tables)
        {
            using var command = connection.CreateCommand();
            command.CommandText = $"SELECT * FROM {table} WHERE UpdatedAt > @lastBackup";
            command.Parameters.AddWithValue("@lastBackup", lastBackupTime);
            using var reader = await command.ExecuteReaderAsync();
            var tableData = new List<Dictionary<string, object>>();
            while (await reader.ReadAsync())
            {
                var row = new Dictionary<string, object>();
                for (int i = 0; i < reader.FieldCount; i++)
                {
                    row[reader.GetName(i)] = reader.GetValue(i);
                }
                tableData.Add(row);
            }
            backupData[table] = tableData;
        }
        // 序列化备份数据
        var json = System.Text.Json.JsonSerializer.Serialize(backupData, new JsonSerializerOptions { WriteIndented = true });
        await File.WriteAllTextAsync(backupPath, json);
        Console.WriteLine($"增量备份完成: {backupPath}");
    }
    // 事务日志备份
    public async Task BackupWithTransactionLog(string backupPath)
    {
        using var connection = new SqliteConnection(_connectionString);
        await connection.OpenAsync();
        using var transaction = await connection.BeginTransactionAsync();
        try
        {
            // 创建备份点
            using var command = connection.CreateCommand();
            command.Transaction = transaction;
            command.CommandText = "INSERT INTO BackupLog (BackupTime, BackupPath, Status) VALUES (@time, @path, 'Started')";
            command.Parameters.AddWithValue("@time", DateTime.UtcNow);
            command.Parameters.AddWithValue("@path", backupPath);
            await command.ExecuteNonQueryAsync();
            // 执行实际备份
            await BackupDatabase(backupPath);
            // 更新备份状态
            command.CommandText = "UPDATE BackupLog SET Status = 'Completed' WHERE BackupPath = @path";
            await command.ExecuteNonQueryAsync();
            await transaction.CommitAsync();
        }
        catch
        {
            await transaction.RollbackAsync();
            throw;
        }
    }
    // 验证备份完整性
    public async Task<bool> ValidateBackup(string backupPath)
    {
        try
        {
            using var connection = new SqliteConnection($"Data Source={backupPath}");
            await connection.OpenAsync();
            using var command = connection.CreateCommand();
            command.CommandText = "PRAGMA integrity_check";
            var result = await command.ExecuteScalarAsync();
            return result?.ToString() == "ok";
        }
        catch
        {
            return false;
        }
    }
}

性能优化技巧

public class PerformanceOptimization
{
    private readonly string _connectionString;
    public PerformanceOptimization(string connectionString)
    {
        _connectionString = connectionString;
    }
    // 数据库配置优化
    public async Task OptimizeDatabasjavascripteSettings()
    {
        using var connection = new SqliteConnection(_connectionString);
        await connection.OpenAsync();
        using var command = connection.CreateCommand();
        // 设置 WAL 模式（写前日志）
        command.CommandText = "PRAGMA journal_mode = WAL";
        await command.ExecuteNonQueryAsync();
        // 设置同步模式
        command.CommandText = "PRAGMA synchronous = NORMAL";
        await command.ExecuteNonQueryAsync();
        // 设置缓存大小（页数）
        command.CommandText = "PRAGMA cache_size = 10000";
        await command.ExecuteNonQueryAsync();
        // 设置页面大小
        command.CommandText = "PRAGMA page_size = 4096";
        await command.ExecuteNonQueryAsync();
        // 启用外键约束
        command.CommandText = "PRAGMA foreign_keys = ON";
        await command.ExecuteNonQueryAsync();
        // 设置临时存储
        command.CommandText = "PRAGMA temp_store = MEMORY";
        await command.ExecuteNonQueryAsync();
        Console.WriteLine("数据库性能设置已优化");
    }
    // 查询优化示例
    public async Task OptimizedQueries()
    {
        using var connection = new SqliteConnection(_connectionString);
        await connection.OpenAsync();
        // 使用参数化查询和适当的索引
        using var command = connection.CreateCommand();
        // 优化：使用覆盖索引
        command.CommandText = @"
            SELECT UserId, COUNT(*) as OrderCount, SUM(TotalAmount) as TotalSpent
            FROM Orders 
            WHERE OrderDate BETWEEN @startDate AND @endDate
            GROUP BY UserId
            HAVING COUNT(*) > 5
            ORDER BY TotalSpent DESC
            LIMIT 100";
        command.Parameters.AddWithValue("@startDate", DateTime.Now.AddMonths(-3));
        command.Parameters.AddWithValue("@endDate", DateTime.Now);
        using var reader = await command.ExecuteReaderAsync();
        var results = new List<CustomerSummary>();
        while (await reader.ReadAsync())
        {
            results.Add(new CustomerSummary
            {
                UserId = reader.GetInt32("编程UserId"),
                OrderCount = reader.GetInt32("OrderCount"),
                TotalSpent = reader.GetDecimal("TotalSpent")
            });
        }
    }
    // 批量数据处理优化
    public async Task OptimizedBatchProcessing<T>(IEnumerable<T> items, Func<T, SqliteCommand, Task> processItem)
    {
        const int batchSize = 1000;
        const int maxConcurrency = Environment.ProcessorCount;
        var semaphore = new SemaphoreSlim(maxConcurrency);
        var batches = items.Chunk(batchSize);
        var tasks = batches.Select(async batch =>
        {
            await semaphore.WaitAsync();
            try
            {
                using var connection = new SqliteConnection(_connectionString);
                await connection.OpenAsync();
                using var transaction = await connection.BeginTransactionAsync();
                try
                {
                    using var command = connection.CreateCommand();
                    command.Transaction = transaction;
                    foreach (var item in batch)
                    {
                        await processItem(item, command);
                    }
                    await transaction.CommitAsync();
                }
                catch
                {
                    await transaction.RollbackAsync();
                    throw;
                }
            }
            finally
            {
                semaphore.Release();
            }
        });
        await Task.WhenAll(tasks);
    }
    // 性能监控
    public async Task MonitorPerformance()
    {
        using var connection = new SqliteConnection(_connectionString);
        await connection.OpenAsync();
        using var command = connection.CreateCommand();
        // 获取数据库统计信息
        var stats = new Dictionary<string, object>();
        command.CommandText = "PRAGMA page_count";
        stats["PageCount"] = await command.ExecuteScalarAsync();
        command.CommandText = "PRAGMA page_size";
        stats["PageSize"] = await command.ExecuteScalarAsync();
        command.CommandText = "PRAGMA cache_size";
        stats["CacheSize"] = await command.ExecuteScalarAsync();
        command.CommandText = "PRAGMA journal_mode";
        stats["JournalMode"] = await command.ExecuteScalarAsync();
        Console.WriteLine("数据库性能统计:");
        foreach (var stat in stats)
        {
            Console.WriteLine($"{stat.Key}: {stat.Value}");
        }
    }
}

实体类定义

// 支持类定义
public class User
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Email { get; set; }
    public string Username { get; set; }
    public int Age { get; set; }
    public int Version { get; set; }
    public DateTime UpdatedAt { get; set; }
}
public class Order
{
    public int Id { get; set; }
    public int CustomerId { get; set; }
    public DateTime OrderDate { get; set; }
    public decimal TotalAmount { get; set; }
    public string Status { get; set; }
}
public class OrderItem
{
    public int Id { get; set; }
    public int OrderId { get; set; }
    public int ProductId { get; set; }
    public int Quantity { get; set; }
    public decimal UnitPrice { get; set; }
}
public class CustomerSummary
{
    public int UserId { get; set; }
    public int OrderCount { get; set; }
    public decimal TotalSpent { get; set; }
}

数据库表结构

-- 创建示例表结构
CREATE TABLE Users (
    Id INTEGER PRIMARY KEY AUTOINCREMENT,
    Name TEXT NOT NULL,
    Email TEXT UNIQUE NOT NULL,
    Username TEXT UNIQUE,
    Age INTEGER,
    Version INTEGER DEFAULT 0,
    UpdatedAt DATETIME DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE Orders (
    Id INTEGER PRIMARY KEY AUTOINCREMENT,
    CustomerId INTEGER NOT NULL,
    OrderDate DATETIME NOT NULL,
    TotalAmount DECIMAL(10,2) NOT NULL,
    Status TEXT DEFAULT 'Pending',
    UpdatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (CustomerId) REFERENCES Users(Id)
);
CREATE TABLE OrderItems (
    Id INTEGER PRIMARY KEY AUTOINCREMENT,
    OrderId INTEGER NOT NULL,
    ProductId INTEGER NOT NULL,
    Quantity INTEGER NOT NULL,
    UnitPrice DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (OrderId) REFERENCES Orders(Id)
);
CREATE TABLE Accounts (
    Id INTEGER PRIMARY KEY AUTOINCREMENT,
    AccountNumber TEXT UNIQUE NOT NULL,
    Balance DECIMAL(15,2) NOT NULL DEFAULT 0
);
CREATE TABLE TransferHistory (
    Id INTEGER PRIMARY KEY AUTOINCREMENT,
    FromAccountId INTEGER NOT NULL,
    ToAccountId INTEGER NOT NULL,
    Amount DECIMAL(15,2) NOT NULL,
    TransferDate DATETIME NOT NULL,
    FOREIGN KEY (FromAccountId) REFERENCES Accounts(Id),
    FOREIGN KEY (ToAccountId) REFERENCES Accounts(Id)
);
CREATE TABLE DistributedLocks (
    LockName TEXT PRIMARY KEY,
    ExpiresAt DATETIME NOT NUwww.devze.comLL,
    CreatedAt DATETIME NOT NULL
);
CREATE TABLE BackupLog (
    Id INTEGER PRIMARY KEY AUTOINCREMENT,
    BackupTime DATETIME NOT NULL,
    BackupPath TEXT NOT NULL,
    Status TEXT NOT NULL
);

总结

本文档详细介绍了 C# 与 SQLite 数据库的高级功能使用，重点包括：

  • 事务处理：从基本事务到嵌套事务，涵盖了各种事务控制场景
  • 连接管理：连接池化和资源优化
  • 批量操作：高效的数据批量处理方法
  • 异步编程：现代异步操作模式
  • 性能优化：索引、查询优化和系统配置
  • 并发控制：悲观锁、乐观锁和分布式锁
  • 备份恢复：数据安全和灾难恢复

到此这篇关于C# SQLite 高级功能详解的文章就介绍到这了,更多相关C# SQLite 功能内容请搜索编程客栈(www.devze.com)以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程客栈(www.devze.com)！

继续阅读：C# SQLite 功能

更多精彩内容

0

上一篇:

下一篇:

精彩评论

暂无评论...
登录 注册
Qedev.com
验证码 换一张
取 消

最新开发

开发排行榜

法律声明：本站内容均为网友上传，网站举办方负责审核和监督，

如存在版权或非法内容，欢迎举报，我们将尽快予以删除。

Copyright © 2018-2020 开发者. All rights reserved.

Powered by 开发者

ICP备案号： 京ICP备10032868号-9