MV485_v125/MV485/db/DBSlave.cs

using MV485.helper;
using MV485.model;
using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace MV485.db
{
    public class DBSlave
    {
        public static bool FreeDatabase()
        {
            // 构建删除的 SQL 语句
            string sql = "VACUUM;";

            try
            {
                // 调用 SQLiteHelper 执行删除操作
                int rowsAffected = SQLiteHelper.ExecuteSql(sql);

                // 如果删除成功，返回 true，否则返回 false
                //return rowsAffected > 0;
                return true;
            }
            catch (Exception ex)
            {
                // 处理异常（如果有的话）
                Console.WriteLine("Error deleting data: " + ex.Message);
                return false;
            }
        }

        public static bool InsertTSlave(TSlave tSlave)
        {
            // 构建插入的 SQL 语句
            string sql = "INSERT OR IGNORE INTO t_slave (slave_id, port_name, baud_rate, address, read_interval, read_image_flag,run_flag,last_read_time) " +
                         "VALUES (@SlaveId, @PortName, @BaudRate, @Address,@ReadInterval,@ReadImageFlag,@RunFlag,@LastReadTime)";

            // 创建参数数组
            SQLiteParameter[] parameters = new SQLiteParameter[]
            {
                new SQLiteParameter("@SlaveId", tSlave.SlaveId),
                new SQLiteParameter("@PortName", tSlave.PortName),
                new SQLiteParameter("@BaudRate", tSlave.BaudRate),
                new SQLiteParameter("@Address", tSlave.Address),
                new SQLiteParameter("@ReadInterval", tSlave.ReadInterval),
                new SQLiteParameter("@ReadImageFlag", tSlave.ReadImageFlag),
                new SQLiteParameter("@RunFlag", tSlave.RunFlag),
                new SQLiteParameter("@LastReadTime", tSlave.LastReadTime)
            };

            try
            {
                // 调用 SQLiteHelper 执行插入操作
                int rowsAffected = SQLiteHelper.ExecuteSql(sql, parameters);

                // 如果插入成功，返回 true，否则返回 false
                return rowsAffected > 0;
            }
            catch (Exception ex)
            {
                // 处理异常（如果有的话）
                Console.WriteLine("Error inserting data: " + ex.Message);
                return false;
            }
        }

        public static List<TSlave> GetAllTSlaves()
        {
            // 定义 SQL 查询语句，按 slave_id 排序
            string sql = @"
                SELECT slave_id, port_name, baud_rate, address, read_interval, read_image_flag, run_flag, last_read_time,detail_count
                FROM v_slave ORDER BY port_name,address ASC";

            // 执行查询并获取结果
            List<TSlave> slaves = new List<TSlave>();
            using (SQLiteDataReader reader = SQLiteHelper.ExecuteReader(sql))
            {
                while (reader.Read())
                {
                    // 将查询结果映射到 TSlave 对象
                    TSlave slave = new TSlave
                    {
                        SlaveId = reader.GetString(reader.GetOrdinal("slave_id")),
                        PortName = reader.GetString(reader.GetOrdinal("port_name")),
                        BaudRate = reader.GetInt32(reader.GetOrdinal("baud_rate")),
                        Address = reader.GetInt32(reader.GetOrdinal("address")),
                        ReadInterval = reader.GetInt32(reader.GetOrdinal("read_interval")),
                        ReadImageFlag = reader.GetInt32(reader.GetOrdinal("read_image_flag")),
                        RunFlag = reader.GetInt32(reader.GetOrdinal("run_flag")),
                        LastReadTime = reader.GetString(reader.GetOrdinal("last_read_time")),
                        DetailCount = Convert.ToInt32(reader["detail_count"])
                    };
                    slaves.Add(slave);
                }
            }

            return slaves;
        }

        public static TSlave GetVSlaveBySlaveId(string slaveId)
        {
            TSlave slave = null;
            string sql = @"
                SELECT slave_id, port_name, baud_rate, address, read_interval, read_image_flag, run_flag, last_read_time,detail_count
                FROM v_slave WHERE slave_id = @SlaveId";

            // 构造参数
            SQLiteParameter[] parameters = new SQLiteParameter[]
            {
                new SQLiteParameter("@SlaveId", slaveId)
            };

            // 执行查询
            using (SQLiteDataReader reader = SQLiteHelper.ExecuteReader(sql, parameters))
            {
                if (reader.Read())
                {
                    slave = new TSlave
                    {
                        SlaveId = reader.GetString(reader.GetOrdinal("slave_id")),
                        PortName = reader.GetString(reader.GetOrdinal("port_name")),
                        BaudRate = reader.GetInt32(reader.GetOrdinal("baud_rate")),
                        Address = reader.GetInt32(reader.GetOrdinal("address")),
                        ReadInterval = reader.GetInt32(reader.GetOrdinal("read_interval")),
                        ReadImageFlag = reader.GetInt32(reader.GetOrdinal("read_image_flag")),
                        RunFlag = reader.GetInt32(reader.GetOrdinal("run_flag")),
                        LastReadTime = reader.GetString(reader.GetOrdinal("last_read_time")),
                        DetailCount = Convert.ToInt32(reader["detail_count"])
                    };
                }
            }

            return slave;
        }


        public static bool UpdateTSlave(TSlave tSlave)
        {
            // 定义更新的 SQL 语句
            string sql = @"
                UPDATE t_slave 
                SET 
                    port_name = @PortName,
                    baud_rate = @BaudRate,
                    address = @Address,
                    read_interval = @ReadInterval,
                    read_image_flag = @ReadImageFlag,
                    run_flag = @RunFlag
                WHERE slave_id = @SlaveId";

            // 创建 SQL 参数
            SQLiteParameter[] parameters = new SQLiteParameter[]
            {
                new SQLiteParameter("@SlaveId", tSlave.SlaveId),
                new SQLiteParameter("@PortName", tSlave.PortName),
                new SQLiteParameter("@BaudRate", tSlave.BaudRate),
                new SQLiteParameter("@Address", tSlave.Address),
                new SQLiteParameter("@ReadInterval", tSlave.ReadInterval),
                new SQLiteParameter("@ReadImageFlag", tSlave.ReadImageFlag),
                new SQLiteParameter("@RunFlag", tSlave.RunFlag),
                //new SQLiteParameter("@LastReadTime", tSlave.LastReadTime)
            };

            try
            {
                // 调用 SQLiteHelper 执行更新操作
                int rowsAffected = SQLiteHelper.ExecuteSql(sql, parameters);

                // 如果更新成功，返回 true，否则返回 false
                return rowsAffected > 0;
            }
            catch (Exception ex)
            {
                // 处理异常（如果有的话）
                Console.WriteLine("Error updating data: " + ex.Message);
                return false;
            }
        }

        public static bool UpdateTSlaveLastReadTime(TSlave tSlave)
        {
            // 定义更新的 SQL 语句
            string sql = @"
                UPDATE t_slave 
                SET 
                    last_read_time = @LastReadTime
                WHERE slave_id = @SlaveId";

            // 创建 SQL 参数
            SQLiteParameter[] parameters = new SQLiteParameter[]
            {
                new SQLiteParameter("@SlaveId", tSlave.SlaveId),
                new SQLiteParameter("@LastReadTime", tSlave.LastReadTime)
            };

            try
            {
                // 调用 SQLiteHelper 执行更新操作
                int rowsAffected = SQLiteHelper.ExecuteSql(sql, parameters);

                // 如果更新成功，返回 true，否则返回 false
                return rowsAffected > 0;
            }
            catch (Exception ex)
            {
                // 处理异常（如果有的话）
                Console.WriteLine("Error updating data: " + ex.Message);
                return false;
            }
        }


        public static bool DeleteSlaveAndDetails(string slaveId)
        {
            // 定义删除的 SQL 语句，使用事务来保证一致性
            string sql = @"
                BEGIN TRANSACTION;

                -- 删除 t_slave_detail 表中与 slave_id 相关的数据
                DELETE FROM t_slave_detail WHERE slave_id = @SlaveId;

                -- 删除 t_slave 表中与 slave_id 相关的数据
                DELETE FROM t_slave WHERE slave_id = @SlaveId;

                COMMIT;";

            // 创建参数
            SQLiteParameter[] parameters = new SQLiteParameter[]
            {
                new SQLiteParameter("@SlaveId", slaveId)
            };

            try
            {
                // 执行 SQL 删除操作
                int rowsAffected = SQLiteHelper.ExecuteSql(sql, parameters);

                // 返回是否成功删除
                return rowsAffected > 0;
            }
            catch (Exception ex)
            {
                // 处理异常（如果有的话）
                Console.WriteLine("Error deleting data: " + ex.Message);
                return false;
            }
        }




        //////////////////////////////////////////////////////////////////////////////////
    }
}