using System;
|
using System.Data.SQLite;
|
using System.IO;
|
|
namespace HMI.Simple.Util
|
{
|
public class SerialDataHelper
|
{
|
private static string FileName = "SerialData";
|
/// <summary>
|
/// 防止多线程创建文件（但是不知道teststand会不会多进程调用）
|
/// </summary>
|
private static object fileLock = new object();
|
|
private static string createTableSql = @"CREATE TABLE IF NOT EXISTS serialData (
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
sn TEXT NOT NULL,
|
station TEXT NOT NULL,
|
step TEXT NOT NULL,
|
limitDown TEXT,
|
limitUp TEXT,
|
unit TEXT,
|
offset TEXT,
|
value TEXT,
|
status TEXT,
|
finalStatus TEXT,
|
consumeTime BIGINT,
|
totalConsumeTime BIGINT,
|
time DATETIME NOT NULL);";
|
|
private static string GetConnectionStr(string dbFile)
|
{
|
return $"DataSource={dbFile};Version=3;Pooling=False;Max Pool Size=100";
|
}
|
|
private static void CreateDbFile(string dbFile)
|
{
|
lock (fileLock)
|
{
|
if (!File.Exists(dbFile))
|
{
|
string dir = Path.GetDirectoryName(dbFile);
|
if (!Directory.Exists(dir))
|
{
|
Directory.CreateDirectory(dir);
|
}
|
SQLiteConnection.CreateFile(dbFile);
|
|
using (SQLiteConnection conn = new SQLiteConnection())
|
using (SQLiteCommand cmd = new SQLiteCommand(conn))
|
{
|
conn.ConnectionString = GetConnectionStr(dbFile);
|
conn.Open();
|
cmd.CommandText = createTableSql;
|
cmd.ExecuteNonQuery();
|
}
|
}
|
}
|
}
|
|
private static string GetDbFile()
|
{
|
string value = RegUtil.GetLocaltion();
|
string path = Path.Combine(value, "Config", "DataBoard", $"{FileName}_{DateTime.Now:yyyy-MM}.db");
|
if (!File.Exists(path))
|
{
|
CreateDbFile(path);
|
}
|
return path;
|
}
|
|
public static bool InsertSerialData(string sn, string station, string step, string status, out string errMsg)
|
{
|
errMsg = string.Empty;
|
try
|
{
|
string dbFile = GetDbFile();
|
using (SQLiteConnection conn = new SQLiteConnection())
|
using (SQLiteCommand cmd = new SQLiteCommand(conn))
|
{
|
conn.ConnectionString = GetConnectionStr(dbFile);
|
conn.Open();
|
cmd.CommandText =
|
$@"INSERT INTO serialData (sn, station, step, status, time) VALUES ('{sn}', '{station}', '{step}', '{status}', '{DateTime.Now:yyyy-MM-dd HH:mm:ss}');";
|
if (cmd.ExecuteNonQuery() > 0)
|
{
|
return true;
|
}
|
errMsg = "插入失败";
|
return false;
|
}
|
}
|
catch (Exception ex)
|
{
|
errMsg = ex.Message;
|
return false;
|
}
|
}
|
|
public static bool UpdateStatusBySn(string dbFile, string sn, string status, out string errMsg)
|
{
|
errMsg = string.Empty;
|
try
|
{
|
if (!File.Exists(dbFile))
|
{
|
errMsg = "数据库文件不存在";
|
return false;
|
}
|
using (SQLiteConnection conn = new SQLiteConnection())
|
using (SQLiteCommand cmd = new SQLiteCommand(conn))
|
{
|
conn.ConnectionString = GetConnectionStr(dbFile);
|
conn.Open();
|
cmd.CommandText =
|
$@"UPDATE serialData SET status='{status}', time='{DateTime.Now:yyyy-MM-dd HH:mm:ss}' WHERE sn='{sn}';";
|
if (cmd.ExecuteNonQuery() > 0)
|
{
|
return true;
|
}
|
errMsg = "更新失败";
|
return false;
|
}
|
}
|
catch (Exception ex)
|
{
|
errMsg = ex.Message;
|
return false;
|
}
|
}
|
}
|
}
|
