工业环境离线数据缓存解决方案：C# SQLite 实践指南
你是不是也遇到过类似的问题？工业物联网项目都面临网络不稳定导致的数据丢失风险。今天就来分享一套完整的C#离线数据缓存解决方案，让你的工业应用在断网情况下也能稳如泰山！
问题分析：工业环境的数据挑战
典型痛点网络不稳定工厂环境信号差，经常断网数据量大设备24小时采集，数据量惊人实时性要求高生产数据不能丢失存储成本云端存储费用昂贵核心需求离线时本地存储数据网络恢复后自动同步数据完整性保证高性能读写操作解决方案：SQLite + C# 完美组合
技术选型理由为什么选择SQLite？
✅ 零配置，单文件数据库✅ 高并发读写性能✅ 事务支持，保证数据一致性✅ 跨平台，适合嵌入式环境🔧 核心实现：分步骤详解
步骤1：数据模型设计复制
// 工业数据模型 - 简洁而全面
public class IndustrialDataModel
{
public int Id { get; set; } // 主键ID
public DateTime Timestamp { get; set; } // 采集时间戳
public string DeviceId { get; set; } // 设备编号
public double Temperature { get; set; } // 温度值
public double Pressure { get; set; } // 压力值
public bool IsSynced { get; set; } // 同步状态标记
}1.2.3.4.5.6.7.8.9.10.
设计亮点：
IsSynced字段是关键，标记数据是否已同步时间戳精确到毫秒，保证数据时序性字段类型选择兼顾性能和精度步骤2：本地缓存仓储实现复制
using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace AppSqliteLocalCache
{
publicclass LocalCacheRepository
{
privatestring _connectionString;
public LocalCacheRepository(string dbPath)
{
// 构造SQLite连接字符串
_connectionString = $"Data Source={dbPath};Version=3;";
InitializeDatabase();
}
/// <summary>
/// 初始化数据库表结构 - 首次运行自动创建
/// </summary>
private void InitializeDatabase()
{
using (var connection = new SQLiteConnection(_connectionString))
{
connection.Open();
using (var command = new SQLiteCommand(connection))
{
// 创建工业数据表，支持自增主键
command.CommandText = @"
CREATE TABLE IF NOT EXISTS IndustrialData (
Id INTEGER PRIMARY KEY AUTOINCREMENT,
Timestamp DATETIME,
DeviceId TEXT,
Temperature REAL,
Pressure REAL,
IsSynced INTEGER
)";
command.ExecuteNonQuery();
}
}
}
/// <summary>
/// 插入采集数据 - 高性能参数化查询
/// </summary>
public void InsertData(IndustrialDataModel data)
{
using (var connection = new SQLiteConnection(_connectionString))
{
connection.Open();
using (var command = new SQLiteCommand(connection))
{
// 使用参数化查询防止SQL注入
command.CommandText = @"
INSERT INTO IndustrialData
(Timestamp, DeviceId, Temperature, Pressure, IsSynced)
VALUES (@Timestamp, @DeviceId, @Temperature, @Pressure, @IsSynced)";
command.Parameters.AddWithValue("@Timestamp", data.Timestamp);
command.Parameters.AddWithValue("@DeviceId", data.DeviceId);
command.Parameters.AddWithValue("@Temperature", data.Temperature);
command.Parameters.AddWithValue("@Pressure", data.Pressure);
command.Parameters.AddWithValue("@IsSynced", data.IsSynced ? 1 : 0);
command.ExecuteNonQuery();
}
}
}
/// <summary>
/// 获取未同步数据 - 网络恢复后批量上传
/// </summary>
public List<IndustrialDataModel> GetUnsyncedData()
{
var unsyncedData = new List<IndustrialDataModel>();
using (var connection = new SQLiteConnection(_connectionString))
{
connection.Open();
using (var command = new SQLiteCommand("SELECT * FROM IndustrialData WHERE IsSynced = 0", connection))
{
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
// 安全的数据类型转换
unsyncedData.Add(new IndustrialDataModel
{
Id = Convert.ToInt32(reader["Id"]),
Timestamp = Convert.ToDateTime(reader["Timestamp"]),
DeviceId = reader["DeviceId"].ToString(),
Temperature = Convert.ToDouble(reader["Temperature"]),
Pressure = Convert.ToDouble(reader["Pressure"]),
IsSynced = Convert.ToBoolean(reader["IsSynced"])
});
}
}
}
}
return unsyncedData;
}
/// <summary>
/// 批量标记为已同步 - 事务保证数据一致性
/// </summary>
public void MarkAsSynced(List<int> ids)
{
using (var connection = new SQLiteConnection(_connectionString))
{
connection.Open();
// 使用事务确保批量操作的原子性
using (var transaction = connection.BeginTransaction())
{
foreach (var id in ids)
{
using (var command = new SQLiteCommand(connection))
{
command.CommandText = "UPDATE IndustrialData SET IsSynced = 1 WHERE Id = @Id";
command.Parameters.AddWithValue("@Id", id);
command.ExecuteNonQuery();
}
}
transaction.Commit(); // 批量提交，提高性能
}
}
}
}
}1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.25.26.27.28.29.30.31.32.33.34.35.36.37.38.39.40.41.42.43.44.45.46.47.48.49.50.51.52.53.54.55.56.57.58.59.60.61.62.63.64.65.66.67.68.69.70.71.72.73.74.75.76.77.78.79.80.81.82.83.84.85.86.87.88.89.90.91.92.93.94.95.96.97.98.99.100.101.102.103.104.105.106.107.108.109.110.111.112.113.114.115.116.117.118.119.120.121.122.123.124.125.126.127.128.129.130.131.132.
我这里是用的http post，实际用mq效果会更好。
复制
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Text.Json;
using System.Threading.Tasks;
namespace AppSqliteLocalCache
{
publicclass DataSyncService
{
private readonly LocalCacheRepository _repository;
private readonly HttpClient _httpClient;
private readonly string _apiEndpoint;
public DataSyncService(LocalCacheRepository repository, string apiEndpoint)
{
_repository = repository;
_apiEndpoint = apiEndpoint;
_httpClient = new HttpClient();
}
/// <summary>
/// 智能同步：检测网络状态并自动同步数据
/// </summary>
public async Task<bool> TrySyncData()
{
try
{
// 获取待同步数据
var unsyncedData = _repository.GetUnsyncedData();
if (unsyncedData.Count == 0)
{
Console.WriteLine("✅ 无待同步数据");
returntrue;
}
// 分批上传，避免单次请求过大
constint batchSize = 100;
var syncedIds = new List<int>();
for (int i = 0; i < unsyncedData.Count; i += batchSize)
{
var batch = unsyncedData.GetRange(i, Math.Min(batchSize, unsyncedData.Count - i));
if (await UploadBatch(batch))
{
// 记录成功同步的ID
foreach (var item in batch)
{
syncedIds.Add(item.Id);
}
}
else
{
Console.WriteLine($"❌ 批次 {i / batchSize + 1} 同步失败");
break;
}
}
// 更新同步状态
if (syncedIds.Count > 0)
{
_repository.MarkAsSynced(syncedIds);
Console.WriteLine($"✅ 成功同步 {syncedIds.Count} 条数据");
}
return syncedIds.Count == unsyncedData.Count;
}
catch (Exception ex)
{
Console.WriteLine($"❌ 同步异常: {ex.Message}");
returnfalse;
}
}
/// <summary>
/// 批量上传数据到云端API
/// </summary>
private async Task<bool> UploadBatch(List<IndustrialDataModel> batch)
{
try
{
var json = JsonSerializer.Serialize(batch);
var content = new StringContent(json, System.Text.Encoding.UTF8, "application/json");
var response = await _httpClient.PostAsync(_apiEndpoint, content);
return response.IsSuccessStatusCode;
}
catch
{
returnfalse; // 网络异常返回失败
}
}
public void Dispose()
{
_httpClient?.Dispose();
}
}
}1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.25.26.27.28.29.30.31.32.33.34.35.36.37.38.39.40.41.42.43.44.45.46.47.48.49.50.51.52.53.54.55.56.57.58.59.60.61.62.63.64.65.66.67.68.69.70.71.72.73.74.75.76.77.78.79.80.81.82.83.84.85.86.87.88.89.90.91.92.93.94.95.96.97.98.99.100.101.102.103.
复制
using System.Text;
namespace AppSqliteLocalCache
{
internal class Program
{
static async Task Main(string[] args)
{
Console.OutputEncoding = Encoding.UTF8;
// 初始化本地缓存
var repository = new LocalCacheRepository("industrial_data.db");
var syncService = new DataSyncService(repository, "https://localhost:7284/api/Industrial");
Console.WriteLine("🏭 工业数据采集系统启动");
// 模拟数据采集和同步
var cts = new CancellationTokenSource();
// 启动数据采集任务
var dataCollectionTask = StartDataCollection(repository, cts.Token);
// 启动定时同步任务
var syncTask = StartPeriodicSync(syncService, cts.Token);
Console.WriteLine("按任意键停止系统...");
Console.ReadKey();
cts.Cancel();
await Task.WhenAll(dataCollectionTask, syncTask);
Console.WriteLine("👋 系统已停止");
}
/// <summary>
/// 模拟设备数据采集
/// </summary>
static async Task StartDataCollection(LocalCacheRepository repository, CancellationToken token)
{
var random = new Random();
while (!token.IsCancellationRequested)
{
try
{
// 模拟多设备数据采集
for (int deviceId = 1; deviceId <= 5; deviceId++)
{
var data = new IndustrialDataModel
{
Timestamp = DateTime.Now,
DeviceId = $"DEVICE_{deviceId:D3}",
Temperature = 20 + random.NextDouble() * 60, // 20-80°C
Pressure = 1 + random.NextDouble() * 9, // 1-10 bar
IsSynced = false
};
repository.InsertData(data);
}
Console.WriteLine($"📊 {DateTime.Now:HH:mm:ss} 采集5个设备数据");
await Task.Delay(5000, token); // 每5秒采集一次
}
catch (OperationCanceledException)
{
break;
}
}
}
/// <summary>
/// 定时同步任务
/// </summary>
static async Task StartPeriodicSync(DataSyncService syncService, CancellationToken token)
{
while (!token.IsCancellationRequested)
{
try
{
await syncService.TrySyncData();
await Task.Delay(30000, token); // 每30秒尝试同步
}
catch (OperationCanceledException)
{
break;
}
}
}
}
}1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.25.26.27.28.29.30.31.32.33.34.35.36.37.38.39.40.41.42.43.44.45.46.47.48.49.50.51.52.53.54.55.56.57.58.59.60.61.62.63.64.65.66.67.68.69.70.71.72.73.74.75.76.77.78.79.80.81.82.83.84.85.86.87.88.89.
图片
性能优化技巧
数据库优化复制
// 1. 创建索引提升查询性能
private void CreateIndexes()
{
using (var connection = new SQLiteConnection(_connectionString))
{
connection.Open();
using (var command = new SQLiteCommand(connection))
{
// 为常用查询字段创建索引
command.CommandText = "CREATE INDEX IF NOT EXISTS idx_sync_status ON IndustrialData(IsSynced)";
command.ExecuteNonQuery();
command.CommandText = "CREATE INDEX IF NOT EXISTS idx_timestamp ON IndustrialData(Timestamp)";
command.ExecuteNonQuery();
}
}
}
// 2. 批量插入优化
public void BatchInsert(List<IndustrialDataModel> dataList)
{
using (var connection = new SQLiteConnection(_connectionString))
{
connection.Open();
using (var transaction = connection.BeginTransaction())
{
using (var command = new SQLiteCommand(connection))
{
command.CommandText = @"
INSERT INTO IndustrialData
(Timestamp, DeviceId, Temperature, Pressure, IsSynced)
VALUES (@Timestamp, @DeviceId, @Temperature, @Pressure, @IsSynced)";
foreach (var data in dataList)
{
command.Parameters.Clear();
command.Parameters.AddWithValue("@Timestamp", data.Timestamp);
command.Parameters.AddWithValue("@DeviceId", data.DeviceId);
command.Parameters.AddWithValue("@Temperature", data.Temperature);
command.Parameters.AddWithValue("@Pressure", data.Pressure);
command.Parameters.AddWithValue("@IsSynced", data.IsSynced ? 1 : 0);
command.ExecuteNonQuery();
}
transaction.Commit(); // 批量提交大幅提升性能
}
}
}
}1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.25.26.27.28.29.30.31.32.33.34.35.36.37.38.39.40.41.42.43.44.45.46.47.48.49.50.
生产环境注意事项
数据安全定期备份设置自动备份机制数据加密敏感数据需要加密存储访问控制限制数据库文件访问权限监控告警复制
// 监控数据库大小，及时清理历史数据
public long GetDatabaseSize(string dbPath)
{
var fileInfo = new FileInfo(dbPath);
return fileInfo.Length;
}
// 清理已同步的历史数据
public void CleanupSyncedData(DateTime olderThan)
{
using (var connection = new SQLiteConnection(_connectionString))
{
connection.Open();
using (var command = new SQLiteCommand(connection))
{
command.CommandText = "DELETE FROM IndustrialData WHERE IsSynced = 1 AND Timestamp < @OlderThan";
command.Parameters.AddWithValue("@OlderThan", olderThan);
var deletedRows = command.ExecuteNonQuery();
Console.WriteLine($"🗑️ 清理了 {deletedRows} 条历史数据");
}
}
}1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.
总结：三个核心要点
架构设计SQLite轻量级数据库 + 参数化查询 + 事务保证数据一致性同步策略离线缓存 + 定时同步 + 分批上传提升效率性能优化索引优化 + 批量操作 + 定期清理保持高性能这套解决方案已在多个工业项目中验证，数据零丢失率达到99.9%以上！无论是制造业、能源行业还是智慧农业，都能轻松适配。
阅读剩余
THE END
