WindowsFormsTest/SqliteTest/Program.cs

using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.IO;
using System.Security.Cryptography;
using System.Text;

namespace SqliteTest
{
    internal class Program
    {
        static void Main(string[] args)
        {
            // 数据库路径（当前目录下的 test.db 文件）
            string dbPath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "UserMessage.db");
            var dbHelper = new UserMessSqliteDataClass(dbPath);

            string password = "123456";

            // 插入示例数据
            dbHelper.InsertUserMessage(new UserMesDataModelClass
            { 
                UserName = "admin",
                PassWord = dbHelper.HashPassword(password), 
                UserMes = "管理员账户", 
                IsDelete = 0,
                IsAdmin = 1,
                DateTime = DateTime.Now 
            });

            // 查询并打印数据
            var ErrMessage = dbHelper.GetAllUserMessage();
            foreach (var Message in ErrMessage)
            {
                //if(Message.PassWord == dbHelper.HashPassword("123456"))
                //{
                    Console.WriteLine($"ID:{Message.Id}，用户名: {Message.UserName}, 密码: {Message.PassWord}, " +
                  $"用户信息: {Message.UserMes}");
                //}
            }

            //dbHelper.ChangePassWord(1, "333", "555");

            //// 查询并打印数据
            //ErrMessage = dbHelper.GetUserMessageForName("root");
            //foreach (var Message in ErrMessage)
            //{
            //    //if (Message.PassWord == dbHelper.HashPassword("333"))
            //    //{
            //        Console.WriteLine($"ID:{Message.Id}，用户名: {Message.UserName}, 密码: {Message.PassWord}, " +
            //      $"用户信息: {Message.UserMes}");
            //    //}
            //}

            //// 数据库路径（当前目录下的 test.db 文件）
            //string dbPath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "test.db");
            //var dbHelper = new SqliteDataClass(dbPath);

            //// 插入示例数据
            //dbHelper.InsertErrMessage(new ErrMessageDataModel { Number = 1, Category = "系统", MessageType = "通知", Message = "相机启动失败", DateTime = DateTime.Now });
            //dbHelper.InsertErrMessage(new ErrMessageDataModel { Number = 2, Category = "变更", MessageType = "记录", Message = "曝光值更改", DateTime = DateTime.Now });

            //// 查询并打印数据
            //var ErrMessage = dbHelper.GetAllErrMessage();
            //foreach (var Message in ErrMessage)
            //{
            //    Console.WriteLine($"编号: {Message.Number}, 类别: {Message.Category}, " +
            //                      $"类型: {Message.MessageType}, 信息: {Message.Message}" +
            //                      $"用户：{Message.UserID}");
            //}
        }
    }
    public class ErrMessageDataModel
    {
        /// <summary>
        /// 主键
        /// </summary>
        public int ID { get; set; }
        /// <summary>
        /// 错误编号
        /// </summary>
        public int Number { get; set; }
        /// <summary>
        /// 信息类别
        /// </summary>
        public string Category { get; set; }
        /// <summary>
        /// 信息类型
        /// </summary>
        public string MessageType { get; set; }
        /// <summary>
        /// 错误信息
        /// </summary>
        public string Message { get; set; }
        /// <summary>
        /// 记录时间
        /// </summary>
        public DateTime DateTime { get; set; }
        /// <summary>
        /// 用户ID
        /// </summary>
        public int UserID { get; set; }
    }


    public class SqliteDataClass
    {
        private readonly string _dbPath;

        public SqliteDataClass(string dbPath)
        {
            _dbPath = dbPath;
            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,
                        DateTime TEXT NOT NULL,
                        UserID INTEGER NOT NULL
                )";
                new SQLiteCommand(createTableSql, conn).ExecuteNonQuery();
            }
        }

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

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

    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 void InsertUserMessage(UserMesDataModelClass Message)
        {
            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", Message.PassWord);
                cmd.Parameters.AddWithValue("@UserMes", Message.UserMes);
                cmd.Parameters.AddWithValue("@IsDelete", Message.IsDelete);
                cmd.Parameters.AddWithValue("@IsAdmin", Message.IsAdmin);
                cmd.Parameters.AddWithValue("@DateTime", Message.DateTime.ToString("yyyy-MM-dd HH:mm:ss.fff")); // 格式化为字符串
                cmd.ExecuteNonQuery();
            }
        }

        // 查询所有数据
        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
                        {
                            UserName = reader["UserName"].ToString(),
                            PassWord = reader["PassWord"].ToString(),
                            UserMes = reader["UserMes"].ToString(),
                            // 解析时间字符串
                            DateTime = DateTime.Parse(reader["DateTime"].ToString())
                        });
                    }
                }
            }
            return persons;
        }

        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();
            }
        }

        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";
                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())
                        });
                    }
                }
            }
            return persons;
        }

        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";
                var cmd = new SQLiteCommand(sql, conn);
                cmd.Parameters.AddWithValue("@PassWord", HashPassword(NewPassWord));
                cmd.Parameters.AddWithValue("@Id", Id);
                cmd.ExecuteNonQuery();
                result = true;
            }
            return result;
        }
    }
    public class UserMesDataModelClass
    {
        public int Id { get; set; }
        public string UserName { get; set; }
        public string PassWord { get; set; }
        public string UserMes { get; set; }
        public DateTime DateTime { get; set; }
        public int IsDelete { get; set; }
        public int IsAdmin { get; set; }
    }
}