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ù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！
相關文章
c# 網(wǎng)址壓縮簡單實現(xiàn)短網(wǎng)址
短網(wǎng)址，忽然一下子就冒出來的東西，長長的一個URL，提交過去，出來就只有短短的一個URL了，看起來似乎挺神奇，其實簡單分析一下，明白其中的原理，也是一件很簡單的事情,需要的朋友可以了解下2012-12-12
本文詳細講解了C#多線程中的線程通知，文中通過示例代碼介紹的非常詳細。對大家的學習或工作具有一定的參考借鑒價值，需要的朋友可以參考下2022-02-02
這篇文章主要為大家詳細介紹了如何使用C#創(chuàng)建一個帶有Push方法和Clist類的CStack類,并如何在其中添加和遍歷堆棧數(shù)據(jù),感興趣的可以了解下2024-03-03
解析從源碼分析常見的基于Array的數(shù)據(jù)結構動態(tài)擴容機制的詳解
本篇文章是對從源碼分析常見的基于Array的數(shù)據(jù)結構動態(tài)擴容機制進行了詳細的分析介紹，需要的朋友參考下2013-05-05
這篇文章主要介紹了C#中定時器定時更新的簡單實例。需要的朋友可以過來參考下，希望對大家有所幫助2013-12-12
C#?DataSet結合FlyTreeView實現(xiàn)顯示樹狀模型數(shù)據(jù)
NineRays.WebControls.FlyTreeView?是?9rays.net?推出的一款功能強大的樹狀模型數(shù)據(jù)顯示控件,本文主要介紹了如何使用其并結合?DataSet對象進行數(shù)據(jù)顯示,感興趣的可以了解下2024-04-04
System.Data.OleDb.OleDbException: 未指定的錯誤的完美解決方法
本文給大家?guī)砣N有關System.Data.OleDb.OleDbException: 未指定的錯誤的完美解決方法,每種方法都很不錯，需要的朋友可以參考下2016-09-09
文章介紹了如何在C#中使用Tesseract進行中文識別,包括環(huán)境準備、安裝NuGet包、配置語言數(shù)據(jù)文件、基本使用代碼、核心API詳解、高級功能與優(yōu)化等步驟,需要的朋友可以參考下2026-03-03
這篇文章主要給大家介紹了關于C#機器入門學習之判斷日報是否合格的相關資料，文中通過示例代碼介紹的非常詳細，對大家的學習或者使用c#具有一定的參考學習價值，需要的朋友們下面來一起學習學習吧2019-03-03
這篇文章主要為大家詳細介紹了C#學習筆記之適配器模式的相關資料，具有一定的參考價值，感興趣的小伙伴們可以參考一下2017-11-11