C# SQLite 高级功能详解

事务处理详解

基本事务操作和ACID特性
事务隔离级别设置
嵌套事务（保存点）
实际业务场景应用（银行转账示例）
连接池管理和资源优化
批量操作性能优化
异步编程模式
索引策略和查询优化
并发控制（悲观锁/乐观锁）
备份恢复机制

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;// 主要操作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 NULLORDER 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.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();// 模拟业务处理时间await Task.Delay(100);// 更新数据using var updateCommand = connection.CreateCommand();updateCommand.Transaction = transaction;updateCommand.CommandText = @"UPDATE Users SET Email = @email, Version = Version + 1, UpdatedAt = @updatedAtWHERE 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 = @updatedAtWHERE 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 内置备份 APIsourceConnection.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 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();// 设置缓存大小（页数）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 TotalSpentFROM Orders WHERE OrderDate BETWEEN @startDate AND @endDateGROUP BY UserIdHAVING COUNT(*) > 5ORDER BY TotalSpent DESCLIMIT 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 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
);