欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

C# SQLite 高級功能詳解(推薦)

 更新時間：2025年06月17日 09:40:37   作者：yz123lucky  
本文介紹C#中SQLite的高級應用,涵蓋事務處理（ACID）、連接池、批量操作、異步編程及性能優(yōu)化,強調(diào)數(shù)據(jù)安全與并發(fā)控制,適用于移動和桌面開發(fā),感興趣的朋友跟隨小編一起看看吧

SQLite 基礎介紹

SQLite 是一個輕量級的嵌入式關系數(shù)據(jù)庫，特別適合桌面應用程序和移動應用程序。它支持 ACID 特性，提供了強大的事務控制功能。

主要特點

  • 無需服務器配置
  • 跨平臺支持
  • 支持標準 SQL 語法
  • 文件級數(shù)據(jù)庫
  • 支持事務和 ACID 特性

環(huán)境配置

首先安裝必要的 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);
    }
}

事務處理詳解

事務基礎概念

事務是數(shù)據(jù)庫操作的基本單元，必須滿足 ACID 特性：

  • 原子性(Atomicity)：事務中的所有操作要么全部完成，要么全部不執(zhí)行
  • 一致性(Consistency)：事務前后數(shù)據(jù)庫狀態(tài)保持一致
  • 隔離性(Isolation)：并發(fā)事務之間相互隔離
  • 持久性(Durability)：事務提交后，數(shù)據(jù)永久保存

基本事務操作

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
        {
            // 創(chuàng)建命令并關聯(lián)事務
            using var command = connection.CreateCommand();
            command.Transaction = transaction;
            // 執(zhí)行多個相關操作
            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;
            // 執(zhí)行業(yè)務邏輯
            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;
            // 主要操作
            command.CommandText = "INSERT INTO Orders (CustomerId, Amount) VALUES (1, 100)";
            command.ExecuteNonQuery();
            // 創(chuàng)建保存點
            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();
                // 執(zhí)行替代操作
                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;
        }
    }
}

事務性業(yè)務邏輯封裝

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);
            }
        }
        // 創(chuàng)建新連接
        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();
        }
    }
}

批量操作優(yōu)化

public class BatchOperations
{
    private readonly string _connectionString;
    public BatchOperations(string connectionString)
    {
        _connectionString = connectionString;
    }
    // 批量插入優(yōu)化
    public async Task BulkInsertOptimized(List<User> users)
    {
        using var connection = new SqliteConnection(_connectionString);
        await connection.OpenAsync();
        using var transaction = await connection.BeginTransactionAsync();
        try
        {
            // 方法1：使用參數(shù)化批量插入
            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($"產(chǎn)品 {item.ProductId} 庫存更新失敗");
            }
        }
    }
}

索引優(yōu)化

public class IndexOptimization
{
    private readonly string _connectionString;
    public IndexOptimization(string connectionString)
    {
        _connectionString = connectionString;
    }
    // 創(chuàng)建和管理索引
    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("查詢執(zhí)行計劃:");
        while (await reader.ReadAsync())
        {
            Console.WriteLine($"ID: {reader[0]}, Parent: {reader[1]}, NotUsed: {reader[2]}, Detail: {reader[3]}");
        }
    }
    // 索引使用統(tǒng)計
    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("數(shù)據(jù)庫索引列表:");
        while (await reader.ReadAsync())
        {
            Console.WriteLine($"索引: {reader["name"]}, SQL: {reader["sql"]}");
        }
    }
}

鎖機制與并發(fā)控制

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);
    }
    // 悲觀鎖實現(xiàn)
    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.CommandText = "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();
                // 模擬業(yè)務處理時間
                await Task.Delay(100);
                // 更新數(shù)據(jù)
                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();
        }
    }
    // 樂觀鎖實現(xiàn)
    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 內(nèi)置備份 API
        sourceConnection.BackupDatabase(backupConnection, "main", "main");
        Console.WriteLine($"數(shù)據(jù)庫備份完成: {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;
        }
        // 序列化備份數(shù)據(jù)
        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
        {
            // 創(chuàng)建備份點
            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();
            // 執(zhí)行實際備份
            await BackupDatabase(backupPath);
            // 更新備份狀態(tài)
            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;
        }
    }
}

性能優(yōu)化技巧

public class PerformanceOptimization
{
    private readonly string _connectionString;
    public PerformanceOptimization(string connectionString)
    {
        _connectionString = connectionString;
    }
    // 數(shù)據(jù)庫配置優(yōu)化
    public async Task OptimizeDatabaseSettings()
    {
        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();
        // 設置緩存大?。摂?shù)）
        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("數(shù)據(jù)庫性能設置已優(yōu)化");
    }
    // 查詢優(yōu)化示例
    public async Task OptimizedQueries()
    {
        using var connection = new SqliteConnection(_connectionString);
        await connection.OpenAsync();
        // 使用參數(shù)化查詢和適當?shù)乃饕?
        using var command = connection.CreateCommand();
        // 優(yōu)化：使用覆蓋索引
        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")
            });
        }
    }
    // 批量數(shù)據(jù)處理優(yōu)化
    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);
    }
    // 性能監(jiān)控
    public async Task MonitorPerformance()
    {
        using var connection = new SqliteConnection(_connectionString);
        await connection.OpenAsync();
        using var command = connection.CreateCommand();
        // 獲取數(shù)據(jù)庫統(tǒng)計信息
        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("數(shù)據(jù)庫性能統(tǒng)計:");
        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; }
}

數(shù)據(jù)庫表結構

-- 創(chuàng)建示例表結構
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 NULL,
    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 數(shù)據(jù)庫的高級功能使用，重點包括：

  • 事務處理：從基本事務到嵌套事務，涵蓋了各種事務控制場景
  • 連接管理：連接池化和資源優(yōu)化
  • 批量操作：高效的數(shù)據(jù)批量處理方法
  • 異步編程：現(xiàn)代異步操作模式
  • 性能優(yōu)化：索引、查詢優(yōu)化和系統(tǒng)配置
  • 并發(fā)控制：悲觀鎖、樂觀鎖和分布式鎖
  • 備份恢復：數(shù)據(jù)安全和災難恢復

到此這篇關于C# SQLite 高級功能詳解的文章就介紹到這了,更多相關C# SQLite 功能內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！

相關文章

最新評論