TMTT/DataService/TipsService.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 TipsService
{
    private static readonly object _lock = new object();
    public static void Log(Tips tips, User user)
    {
        LogInternal(new List<Tips> { tips }, user);
    }
    public static void LogBatch(IEnumerable<Tips> tips, User user)
    {
        LogInternal(tips, user);
    }
    // 内部统一实现
    private static void LogInternal(IEnumerable<Tips> tips, User user)
    {
        lock (_lock) // 简单线程安全
        {
            using (var conn = new SQLiteConnection(DatabaseHelper.ConnectionString))
            {
                conn.Open();
                string sql = @"INSERT INTO TipsLogs (UserId, Username, Message, Type, Timestamp)
                       VALUES (@UserId, @Username, @Message, @Type, @Timestamp)";
                using (var tran = conn.BeginTransaction()) // 👈 关键：显式事务
                {
                    foreach (var tip in tips)
                    {
                        using (var cmd = new SQLiteCommand(sql, conn))
                        {
                            cmd.Parameters.AddWithValue("@UserId", user.Id);
                            cmd.Parameters.AddWithValue("@Username", user.Username);
                            cmd.Parameters.AddWithValue("@Message", tip.Message);
                            cmd.Parameters.AddWithValue("@Type", (int)tip.Type);
                            cmd.Parameters.AddWithValue("@Timestamp", tip.Timestamp.ToString("yyyy-MM-dd HH:mm:ss"));

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

            var tips = new List<TipsDisplay>();
            string sql = @"
            SELECT Id, Timestamp, Username, Message, Type 
            FROM TipsLogs 
            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())
                    {
                        tips.Add(new TipsDisplay
                        {
                            Id = reader.GetInt32(0),
                            Timestamp = DateTime.Parse(reader.GetString(1)),
                            Username = reader.GetString(2),
                            Message = reader.GetString(3),
                            Type = ((TipsType)reader.GetInt32(4)).ToString()
                        });
                    }
                }
            }

            return tips;
        }
    }
}