CCDCount/TestWork.DLL/SqlDataClass/UserMessSqliteDataClass.cs

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

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

        //构建方法，根绝地址初始化数据库
        public UserMessSqliteDataClass(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 UserMessage (
                        Id INTEGER PRIMARY KEY AUTOINCREMENT,
                        UserName TEXT NOT NULL,
                        PassWord TEXT NOT NULL,
                        UserMes TEXT NOT NULL,
                        DateTime TEXT NOT NULL,
                        IsDelete INTEGER CHECK (IsDelete IN (0, 1)),
                        IsAdmin INTEGER CHECK (IsAdmin IN (0, 1))
                )";
                new SQLiteCommand(createTableSql, conn).ExecuteNonQuery();
            }
        }

        // 插入数据
        public int InsertUserMessage(UserMesDataModelClass Message)
        {
            int result = -1;
            if(GetUserMessageForName(Message.UserName).Count>0)
            {
                result = 2;
                return result;
            }
            using (var conn = new SQLiteConnection($"Data Source={_dbPath};Version=3;"))
            {
                conn.Open();
                string sql = "INSERT INTO UserMessage (UserName, PassWord,UserMes,IsDelete,IsAdmin,DateTime) VALUES (@UserName, @PassWord,@UserMes,@IsDelete,@IsAdmin,@DateTime)";
                var cmd = new SQLiteCommand(sql, conn);
                cmd.Parameters.AddWithValue("@UserName", Message.UserName);
                cmd.Parameters.AddWithValue("@PassWord", HashPassword(Message.PassWord));
                cmd.Parameters.AddWithValue("@UserMes", Message.UserMes);
                cmd.Parameters.AddWithValue("@IsDelete", Message.IsDelete == true ? 1 : 0);
                cmd.Parameters.AddWithValue("@IsAdmin", Message.IsAdmin == true ? 1 : 0);
                cmd.Parameters.AddWithValue("@DateTime", Message.DateTime.ToString("yyyy-MM-dd HH:mm:ss.fff")); // 格式化为字符串
                cmd.ExecuteNonQuery();
                result = 0;
            }
            return result;
        }

        // 查询所有数据
        public List<UserMesDataModelClass> GetAllUserMessage()
        {
            var persons = new List<UserMesDataModelClass>();
            using (var conn = new SQLiteConnection($"Data Source={_dbPath};Version=3;"))
            {
                conn.Open();
                string sql = "SELECT * FROM UserMessage WHERE IsDelete=0";
                var cmd = new SQLiteCommand(sql, conn);
                using (SQLiteDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        persons.Add(new UserMesDataModelClass
                        {
                            Id = Convert.ToInt32(reader["Id"]),
                            UserName = reader["UserName"].ToString(),
                            PassWord = reader["PassWord"].ToString(),
                            UserMes = reader["UserMes"].ToString(),
                            // 解析时间字符串
                            DateTime = DateTime.Parse(reader["DateTime"].ToString()),
                            IsAdmin = Convert.ToInt32(reader["IsAdmin"]) == 1 ? true : false,
                            IsDelete = Convert.ToInt32(reader["IsDelete"]) == 1 ? true : false
                        });
                    }
                }
            }
            return persons;
        }

        // 查询对应用户名的所有信息
        public List<UserMesDataModelClass> GetUserMessageForName(string UserName)
        {
            var persons = new List<UserMesDataModelClass>();
            using (var conn = new SQLiteConnection($"Data Source={_dbPath};Version=3;"))
            {
                conn.Open();
                string sql = "SELECT * FROM UserMessage WHERE UserName = @UserName AND IsDelete = 0";
                var cmd = new SQLiteCommand(sql, conn);
                cmd.Parameters.AddWithValue("@UserName", UserName);
                using (SQLiteDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        persons.Add(new UserMesDataModelClass
                        {
                            Id = Convert.ToInt32(reader["Id"]),
                            UserName = reader["UserName"].ToString(),
                            PassWord = reader["PassWord"].ToString(),
                            UserMes = reader["UserMes"].ToString(),
                            // 解析时间字符串
                            DateTime = DateTime.Parse(reader["DateTime"].ToString()),
                            IsAdmin = Convert.ToInt32(reader["IsAdmin"]) == 1 ? true : false,
                            IsDelete = Convert.ToInt32(reader["IsDelete"]) == 1 ? true : false
                        });
                    }
                }
            }
            return persons;
        }

        // 查询对应用户ID的所有信息
        public List<UserMesDataModelClass> GetUserMessageForId(int Id)
        {
            var persons = new List<UserMesDataModelClass>();
            using (var conn = new SQLiteConnection($"Data Source={_dbPath};Version=3;"))
            {
                conn.Open();
                string sql = "SELECT * FROM UserMessage WHERE Id = @Id AND IsDelete = 0";
                var cmd = new SQLiteCommand(sql, conn);
                cmd.Parameters.AddWithValue("@Id", Id);
                using (SQLiteDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        persons.Add(new UserMesDataModelClass
                        {
                            Id = Convert.ToInt32(reader["Id"]),
                            UserName = reader["UserName"].ToString(),
                            PassWord = reader["PassWord"].ToString(),
                            UserMes = reader["UserMes"].ToString(),
                            // 解析时间字符串
                            DateTime = DateTime.Parse(reader["DateTime"].ToString()),
                            IsAdmin = Convert.ToInt32(reader["IsAdmin"]) == 1 ? true : false,
                            IsDelete = Convert.ToInt32(reader["IsDelete"]) == 1 ? true : false
                        });
                    }
                }
            }
            return persons;
        }

        //查询用户名的登录信息是否正确
        public bool CheckUserLogin(string UserName, string PassWord)
        {
            return GetAllUserMessage().Any(x => x.UserName == UserName && x.PassWord == HashPassword(PassWord));
        }

        //修改密码(使用ID)
        public bool ChangePassWord(int Id, string OldPassWord, string NewPassWord)
        {
            bool result = false;
            using (var conn = new SQLiteConnection($"Data Source={_dbPath};Version=3;"))
            {
                conn.Open();
                string sql = "UPDATE UserMessage SET PassWord = @PassWord WHERE Id = @Id AND IsDelete = 0";
                var cmd = new SQLiteCommand(sql, conn);
                cmd.Parameters.AddWithValue("@PassWord", HashPassword(NewPassWord));
                cmd.Parameters.AddWithValue("@Id", Id);
                cmd.ExecuteNonQuery();
                result = true;
            }
            return result;
        }

        public bool ChangeUserMessage(int Id,UserMesDataModelClass Message)
        {
            bool result = false;
            using (var conn = new SQLiteConnection($"Data Source={_dbPath};Version=3;"))
            {
                conn.Open();
                string sql = "UPDATE UserMessage SET UserName = @UserName,PassWord = @PassWord,UserMes = @UserMes,IsAdmin = @IsAdmin WHERE Id = @Id AND IsDelete = 0";
                var cmd = new SQLiteCommand(sql, conn);
                cmd.Parameters.AddWithValue("@UserName", Message.UserName);
                cmd.Parameters.AddWithValue("@PassWord", HashPassword(Message.PassWord));
                cmd.Parameters.AddWithValue("@UserMes", Message.UserMes);
                cmd.Parameters.AddWithValue("@Id", Id);
                cmd.Parameters.AddWithValue("@IsAdmin", Message.IsAdmin);
                cmd.ExecuteNonQuery();
                result = true;
            }
            return result;
        }
        
        // 重置密码
        public bool ResetPassWord(int Id)
        {
            bool result = false;
            using (var conn = new SQLiteConnection($"Data Source={_dbPath};Version=3;"))
            {
                conn.Open();
                string sql = "UPDATE UserMessage SET PassWord = @PassWord WHERE Id = @Id AND IsDelete = 0";
                var cmd = new SQLiteCommand(sql, conn);
                cmd.Parameters.AddWithValue("@PassWord", HashPassword("123456"));
                cmd.Parameters.AddWithValue("@Id", Id);
                cmd.ExecuteNonQuery();
                result = true;
            }
            return result;
        }

        // 删除用户信息
        public bool DeleteUserMessage(int Id)
        {
            bool result = false;
            using (var conn = new SQLiteConnection($"Data Source={_dbPath};Version=3;"))
            {
                conn.Open();
                string sql = "UPDATE UserMessage SET IsDelete = 1 WHERE Id = @Id";
                var cmd = new SQLiteCommand(sql, conn);
                cmd.Parameters.AddWithValue("@Id", Id);
                cmd.ExecuteNonQuery();
                result = true;
            }
            return result;
        }


        // Hash密码加密
        public string HashPassword(string password)
        {
            using (SHA256 sha256 = SHA256.Create())
            {
                byte[] bytes = sha256.ComputeHash(Encoding.UTF8.GetBytes(password));
                StringBuilder builder = new StringBuilder();
                for (int i = 0; i < bytes.Length; i++)
                {
                    builder.Append(bytes[i].ToString("x2"));
                }
                return builder.ToString();
            }
        }
    }
}