MvvmScaffoldFrame48/MvvmScaffoldFrame48.DLL/AuditTrail/ErrorMessageRecordManagement.cs

//异常信息记录管理
using MvvmScaffoldFrame48.Model.StorageModel.AuditTrail;
using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace MvvmScaffoldFrame48.DLL.AuditTrail
{
    public class ErrorMessageRecordManagement
    {
        private readonly string _dbPath;

        private string _connString;

        private object locker = new object();

        public ErrorMessageRecordManagement()
        {
            _dbPath = $"{AppDomain.CurrentDomain.BaseDirectory}DATA\\AuditTrail.db";
            // 1. 获取文件夹路径
            string directoryPath = Path.GetDirectoryName(_dbPath);

            // 2. 检查文件夹是否存在
            if (!Directory.Exists(directoryPath))
            {
                // 创建文件夹
                Directory.CreateDirectory(directoryPath);
            }
            InitializeDatabase();
        }

        public ErrorMessageRecordManagement(string dbPath)
        {
            if (dbPath == null || dbPath == "")
            {
                _dbPath = $"{AppDomain.CurrentDomain.BaseDirectory}DATA\\AuditTrail.db";
            }
            else
            {
                _dbPath = dbPath;
            }

            // 1. 获取文件夹路径
            string directoryPath = Path.GetDirectoryName(_dbPath);

            // 2. 检查文件夹是否存在
            if (!Directory.Exists(directoryPath))
            {
                // 创建文件夹
                Directory.CreateDirectory(directoryPath);
            }
            InitializeDatabase();
        }

        /// <summary>
        /// 初始化数据库和表
        /// </summary>
        private void InitializeDatabase()
        {
            if (!File.Exists(_dbPath))
            {
                SQLiteConnection.CreateFile(_dbPath);
            }

            _connString = $"Data Source={_dbPath};Version=3;" +
                "Journal Mode=WAL;" +           // 使用WAL模式提高并发性
                "Synchronous=Normal;" +         // 平衡性能和安全性
                "Cache Size=10000;" +           // 增加缓存大小
                "Pooling=true;" +               // 启用连接池
                "Max Pool Size=100;";         // 设置最大连接池大小

            using (var conn = new SQLiteConnection(_connString))
            {
                conn.Open();
                string createTableSql = @"
                    CREATE TABLE IF NOT EXISTS ErrorMessageRecord (
                        ID INTEGER PRIMARY KEY AUTOINCREMENT,
                        Category TEXT NOT NULL,
                        MessageType TEXT NOT NULL,
                        MessagePath TEXT NOT NULL,
                        Message TEXT NOT NULL,
                        DateTime TEXT NOT NULL,
                        userID INTEGER NOT NULL,
                        LogLevel INTEGER NOT NULL,
                        IsDeleted INTEGER CHECK (IsDeleted IN (0, 1))
                )";
                new SQLiteCommand(createTableSql, conn).ExecuteNonQuery();
            }
        }

        /// <summary>
        /// 插入数据
        /// </summary>
        /// <param name="Message"></param>
        public void InsertErrorMessageRecord(ErrorMessageRecordModel Message)
        {
            lock (locker)
            {
                using (var conn = new SQLiteConnection(_connString))
                {
                    conn.Open();
                    string sql = "INSERT INTO ErrorMessageRecord(" +
                        "Category,MessageType,MessagePath,Message,DateTime,userID,LogLevel,IsDeleted)" +
                        " VALUES" +
                        " (@Category, @MessageType,@MessagePath,@Message,@DateTime,@userID,@LogLevel,@IsDeleted)";
                    var cmd = new SQLiteCommand(sql, conn);
                    cmd.Parameters.AddWithValue("@Category", Message.Category);
                    cmd.Parameters.AddWithValue("@MessageType", Message.MessageType);
                    cmd.Parameters.AddWithValue("@MessagePath", Message.MessagePath);
                    cmd.Parameters.AddWithValue("@Message", Message.Message);
                    cmd.Parameters.AddWithValue("@DateTime", Message.DateTime.ToString("yyyy-MM-dd HH:mm:ss.fff"));
                    cmd.Parameters.AddWithValue("@userID", Message.userID);
                    cmd.Parameters.AddWithValue("@LogLevel", Message.LogLevel);
                    cmd.Parameters.AddWithValue("@IsDeleted", 0);
                    cmd.ExecuteNonQuery();
                }
            }
        }

        /// <summary>
        /// 查询所有数据
        /// </summary>
        /// <returns></returns>
        public List<ErrorMessageRecordModel> GetAllErrorMessageRecord()
        {
            var ErrorMessages = new List<ErrorMessageRecordModel>();
            using (var conn = new SQLiteConnection(_connString))
            {
                conn.Open();
                string sql = "SELECT * FROM ErrorMessageRecord WHERE IsDeleted=0";
                var cmd = new SQLiteCommand(sql, conn);
                using (SQLiteDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        ErrorMessages.Add(new ErrorMessageRecordModel
                        {
                            ID = Convert.ToInt32(reader["ID"]),
                            Category = reader["Category"].ToString(),
                            MessageType = reader["MessageType"].ToString(),
                            MessagePath = reader["MessagePath"].ToString(),
                            Message = reader["Message"].ToString(),
                            // 解析时间字符串
                            DateTime = DateTime.Parse(reader["DateTime"].ToString()),
                            userID = Convert.ToInt32(reader["userID"]),
                            LogLevel = Convert.ToInt32(reader["LogLevel"])
                        });
                    }
                }
            }
            return ErrorMessages;
        }

        /// <summary>
        /// 获取错误数据信息
        /// </summary>
        /// <param name="UserID">操作员ID</param>
        /// <returns></returns>
        public List<ErrorMessageRecordModel> GetErrorMessageRecordByID(int UserID)
        {
            var ErrorMessages = new List<ErrorMessageRecordModel>();
            using (var conn = new SQLiteConnection(_connString))
            {
                conn.Open();
                string sql = "SELECT * FROM ErrorMessageRecord WHERE userID=@userID AND IsDeleted=0";
                var cmd = new SQLiteCommand(sql, conn);
                cmd.Parameters.AddWithValue("@userID", UserID);
                using (SQLiteDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        ErrorMessages.Add(new ErrorMessageRecordModel
                        {
                            ID = Convert.ToInt32(reader["ID"]),
                            Category = reader["Category"].ToString(),
                            MessageType = reader["MessageType"].ToString(),
                            MessagePath = reader["MessagePath"].ToString(),
                            Message = reader["Message"].ToString(),
                            // 解析时间字符串
                            DateTime = DateTime.Parse(reader["DateTime"].ToString()),
                            userID = Convert.ToInt32(reader["userID"]),
                            LogLevel = Convert.ToInt32(reader["LogLevel"])
                        });
                    }
                }
            }
            return ErrorMessages;
        }

        /// <summary>
        /// 获取错误数据信息
        /// </summary>
        /// <param name="ErrorDateTime">错误时间</param>
        public List<ErrorMessageRecordModel> GetErrorMessageRecordByDateTime(DateTime ErrorDateTime)
        {
            var ErrorMessages = new List<ErrorMessageRecordModel>();
            using (var conn = new SQLiteConnection(_connString))
            {
                conn.Open();
                string sql = "SELECT * FROM ErrorMessageRecord WHERE date(DateTime)=date(@DateTime) AND IsDeleted=0";
                var cmd = new SQLiteCommand(sql, conn);
                cmd.Parameters.AddWithValue("@DateTime", ErrorDateTime.ToString("yyyy-MM-dd"));
                using (SQLiteDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        ErrorMessages.Add(new ErrorMessageRecordModel
                        {
                            ID = Convert.ToInt32(reader["ID"]),
                            Category = reader["Category"].ToString(),
                            MessageType = reader["MessageType"].ToString(),
                            MessagePath = reader["MessagePath"].ToString(),
                            Message = reader["Message"].ToString(),
                            // 解析时间字符串
                            DateTime = DateTime.Parse(reader["DateTime"].ToString()),
                            userID = Convert.ToInt32(reader["userID"]),
                            LogLevel = Convert.ToInt32(reader["LogLevel"])
                        });
                    }
                }
            }
            return ErrorMessages;
        }

        /// <summary>
        /// 获取错误数据信息
        /// </summary>
        /// <param name="ErrorStartDateTime">错误开始时间</param>
        /// <param name="ErrorEndDateTime">错误结束时间</param>
        public List<ErrorMessageRecordModel> GetErrorMessageRecordByDateTime(DateTime ErrorStartDateTime,DateTime ErrorEndDateTime)
        {
            var ErrorMessages = new List<ErrorMessageRecordModel>();
            using (var conn = new SQLiteConnection(_connString))
            {
                conn.Open();
                string sql = "SELECT * FROM ErrorMessageRecord WHERE date(DateTime)>=date(@ErrorStartDateTime) AND date(DateTime)<=date(@ErrorEndDateTime) AND IsDeleted=0";
                var cmd = new SQLiteCommand(sql, conn);
                cmd.Parameters.AddWithValue("@ErrorStartDateTime", ErrorStartDateTime.ToString("yyyy-MM-dd"));
                cmd.Parameters.AddWithValue("@ErrorEndDateTime", ErrorEndDateTime.ToString("yyyy-MM-dd"));
                using (SQLiteDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        ErrorMessages.Add(new ErrorMessageRecordModel
                        {
                            ID = Convert.ToInt32(reader["ID"]),
                            Category = reader["Category"].ToString(),
                            MessageType = reader["MessageType"].ToString(),
                            MessagePath = reader["MessagePath"].ToString(),
                            Message = reader["Message"].ToString(),
                            // 解析时间字符串
                            DateTime = DateTime.Parse(reader["DateTime"].ToString()),
                            userID = Convert.ToInt32(reader["userID"]),
                            LogLevel = Convert.ToInt32(reader["LogLevel"])
                        });
                    }
                }
            }
            return ErrorMessages;
        }
    }
}