TMTT/DataService/AlarmService.cs

using Model;
using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.Linq;
using System.Security.Claims;
using System.Text;
using System.Threading.Tasks;

public class AlarmService
{
    private static readonly object _lock = new object();
    public static void Log(Alarm alarm, User user)
    {
        LogInternal(new List<Alarm> { alarm }, user);
    }
    public static void LogBatch(IEnumerable<Alarm> alarms, User user)
    {
        LogInternal(alarms, user);
    }

    // 内部统一实现
    private static void LogInternal(IEnumerable<Alarm> alarms, User user)
    {
        lock (_lock) // 简单线程安全
        {
            using (var conn = new SQLiteConnection(DatabaseHelper.ConnectionString))
            {
                conn.Open();
                string sql = @"INSERT INTO AlarmLogs (UserId, Username, AlarmMessage, AlarmType, Timestamp)
                       VALUES (@UserId, @Username, @AlarmMessage, @AlarmType, @Timestamp)";
                using (var tran = conn.BeginTransaction()) // 👈 关键：显式事务
                {
                    foreach (var alarm in alarms)
                    {
                        using (var cmd = new SQLiteCommand(sql, conn))
                        {
                            cmd.Parameters.AddWithValue("@UserId", user.Id);
                            cmd.Parameters.AddWithValue("@Username", user.Username);
                            cmd.Parameters.AddWithValue("@AlarmMessage", alarm.AlarmMessage);
                            cmd.Parameters.AddWithValue("@AlarmType", (int)alarm.AlarmType);
                            cmd.Parameters.AddWithValue("@Timestamp", alarm.Timestamp.ToString("yyyy-MM-dd HH:mm:ss.fff"));

                            cmd.ExecuteNonQuery();
                        }
                    }
                    tran.Commit(); // 👈 所有写入一次性提交
                }
            }
        }
    }
    public static List<AlarmDisplay> GetAlarmsByTimeRange(DateTime start, DateTime end)
    {
        using (var connection = new SQLiteConnection(DatabaseHelper.ConnectionString))
        {
            connection.Open();

            var alarms = new List<AlarmDisplay>();
            string sql = @"
            SELECT Id, Timestamp, Username, AlarmMessage, AlarmType 
            FROM AlarmLogs 
            WHERE Timestamp BETWEEN @Start AND @End 
            ORDER BY Timestamp DESC";

            using (var cmd = new SQLiteCommand(sql, connection))
            {
                // 参数化查询，防止注入
                cmd.Parameters.AddWithValue("@Start", start.ToString("yyyy-MM-dd HH:mm:ss"));
                cmd.Parameters.AddWithValue("@End", end.ToString("yyyy-MM-dd HH:mm:ss"));

                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        alarms.Add(new AlarmDisplay
                        {
                            Id = reader.GetInt32(0),
                            Timestamp = DateTime.Parse(reader.GetString(1)),
                            Username = reader.GetString(2),
                            AlarmMessage = reader.GetString(3),
                            AlarmType = ((AlarmType)reader.GetInt32(4)).ToString()
                        });
                    }
                }
            }

            return alarms;
        }
    }
}