CCDCount/TestWork.DLL/SqlDataClass/ErrorMesSqliteDataClass.cs

using CCDCount.MODEL.SqlDataModel;
using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CCDCount.DLL.SqlDataClass
{
    public class ErrorMesSqliteDataClass
    {
        private readonly string _dbPath;

        private object locker = new object();

        public ErrorMesSqliteDataClass(string dbPath)
        {
            _dbPath = dbPath;

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

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

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

            using (var conn = new SQLiteConnection($"Data Source={_dbPath};Version=3;"))
            {
                conn.Open();
                string createTableSql = @"
                    CREATE TABLE IF NOT EXISTS ErrMessage (
                        Id INTEGER PRIMARY KEY AUTOINCREMENT,
                        Number INTEGER NOT NULL,
                        Category TEXT NOT NULL,
                        MessageType TEXT NOT NULL,
                        Message TEXT NOT NULL,
                        Path TEXT ,
                        DateTime TEXT NOT NULL,
                        UserID INTEGER NOT NULL
                )";
                new SQLiteCommand(createTableSql, conn).ExecuteNonQuery();
            }
        }

        // 插入数据
        public void InsertErrMessage(ErroeMesDataModelClass Message)
        {
            lock (locker)
            {
                using (var conn = new SQLiteConnection($"Data Source={_dbPath};Version=3;"))
                {
                    conn.Open();
                    string sql = "INSERT INTO ErrMessage (Number, Category, MessageType,Message,Path,DateTime,UserID) VALUES (@Number, @Category, @MessageType,@Message,@Path,@DateTime, @UserID)";
                    var cmd = new SQLiteCommand(sql, conn);
                    cmd.Parameters.AddWithValue("@Number", Message.Number);
                    cmd.Parameters.AddWithValue("@Category", Message.Category);
                    cmd.Parameters.AddWithValue("@MessageType", Message.MessageType);
                    cmd.Parameters.AddWithValue("@Message", Message.Message);
                    cmd.Parameters.AddWithValue("@Path", Message.Path);
                    cmd.Parameters.AddWithValue("@DateTime", Message.DateTime.ToString("yyyy-MM-dd HH:mm:ss.fff")); // 格式化为字符串
                    cmd.Parameters.AddWithValue("@UserID", Message.UserID);
                    cmd.ExecuteNonQuery();
                }
            }
        }

        // 查询所有数据
        public List<ErroeMesDataModelClass> GetAllErrMessage()
        {
            var persons = new List<ErroeMesDataModelClass>();
            using (var conn = new SQLiteConnection($"Data Source={_dbPath};Version=3;"))
            {
                conn.Open();
                string sql = "SELECT * FROM ErrMessage ORDER BY DateTime DESC LIMIT 1000";
                var cmd = new SQLiteCommand(sql, conn);
                using (SQLiteDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        persons.Add(new ErroeMesDataModelClass
                        {
                            Number = Convert.ToInt32(reader["Number"]),
                            Category = reader["Category"].ToString(),
                            MessageType = reader["MessageType"].ToString(),
                            Message = reader["Message"].ToString(),
                            Path = reader["Path"].ToString(),
                            UserID = Convert.ToInt32(reader["UserID"]),
                            // 解析时间字符串
                            DateTime = DateTime.Parse(reader["DateTime"].ToString())
                        });
                    }
                }
            }
            return persons;
        }

        /// <summary>
        /// 获取值改变信息
        /// </summary>
        /// <returns></returns>
        public List<ErroeMesDataModelClass> GetValueChangeMessage()
        {
            var persons = new List<ErroeMesDataModelClass>();
            using (var conn = new SQLiteConnection($"Data Source={_dbPath};Version=3;"))
            {
                conn.Open();
                string sql = "SELECT * FROM ErrMessage WHERE MessageType = 'value change'";
                var cmd = new SQLiteCommand(sql, conn);
                using (SQLiteDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        persons.Add(new ErroeMesDataModelClass
                        {
                            Number = Convert.ToInt32(reader["Number"]),
                            Category = reader["Category"].ToString(),
                            MessageType = reader["MessageType"].ToString(),
                            Message = reader["Message"].ToString(),
                            Path = reader["Path"].ToString(),
                            UserID = Convert.ToInt32(reader["UserID"]),
                            // 解析时间字符串
                            DateTime = DateTime.Parse(reader["DateTime"].ToString())
                        });
                    }
                }
            }
            return persons;
        }

        /// <summary>
        /// 获取值改变信息
        /// </summary>
        /// <param name="UserID">操作员ID</param>
        /// <returns></returns>
        public List<ErroeMesDataModelClass> GetValueChangeMessage(int UserID)
        {
            var persons = new List<ErroeMesDataModelClass>();
            using (var conn = new SQLiteConnection($"Data Source={_dbPath};Version=3;"))
            {
                conn.Open();
                string sql = "SELECT * FROM ErrMessage WHERE MessageType = @MessageType AND UserID = @UserID";
                var cmd = new SQLiteCommand(sql, conn);
                cmd.Parameters.AddWithValue("@MessageType", "value change");
                cmd.Parameters.AddWithValue("@UserID", UserID);
                using (SQLiteDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        persons.Add(new ErroeMesDataModelClass
                        {
                            Number = Convert.ToInt32(reader["Number"]),
                            Category = reader["Category"].ToString(),
                            MessageType = reader["MessageType"].ToString(),
                            Message = reader["Message"].ToString(),
                            Path = reader["Path"].ToString(),
                            UserID = Convert.ToInt32(reader["UserID"]),
                            // 解析时间字符串
                            DateTime = DateTime.Parse(reader["DateTime"].ToString())
                        });
                    }
                }
            }
            return persons;
        }

        /// <summary>
        /// 获取异常信息
        /// </summary>
        /// <returns></returns>
        public List<ErroeMesDataModelClass> GetErrorMessage()
        {
            var persons = new List<ErroeMesDataModelClass>();
            using (var conn = new SQLiteConnection($"Data Source={_dbPath};Version=3;"))
            {
                conn.Open();
                string sql = "SELECT * FROM ErrMessage WHERE MessageType = @MessageType";
                var cmd = new SQLiteCommand(sql, conn);
                cmd.Parameters.AddWithValue("@MessageType", "Exceptional");
                using (SQLiteDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        persons.Add(new ErroeMesDataModelClass
                        {
                            Number = Convert.ToInt32(reader["Number"]),
                            Category = reader["Category"].ToString(),
                            MessageType = reader["MessageType"].ToString(),
                            Message = reader["Message"].ToString(),
                            Path = reader["Path"].ToString(),
                            UserID = Convert.ToInt32(reader["UserID"]),
                            // 解析时间字符串
                            DateTime = DateTime.Parse(reader["DateTime"].ToString())
                        });
                    }
                }
            }
            return persons;
        }
    }
}