MV485_v125/MV485/db/DBUpgradeHis.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 DBUpgradeHis
    {
        public static bool InsertUpgradeHis(TUpgradeHis his)
        {
            string sql = @"
            INSERT OR IGNORE INTO t_upgrade_his (
                his_id, device_sn, port_name, baud_rate,
                address, old_fireware, upgrade_time,
                new_fireware, upgrade_result
            )
            VALUES (
                @HisId, @DeviceSn, @PortName, @BaudRate,
                @Address, @OldFireware, @UpgradeTime,
                @NewFireware, @UpgradeResult
            )";

            SQLiteParameter[] parameters = new SQLiteParameter[]
            {
            new SQLiteParameter("@HisId", his.HisId),
            new SQLiteParameter("@DeviceSn", his.DeviceSn),
            new SQLiteParameter("@PortName", his.PortName),
            new SQLiteParameter("@BaudRate", his.BaudRate),
            new SQLiteParameter("@Address", his.Address),
            new SQLiteParameter("@OldFireware", his.OldFireware),
            new SQLiteParameter("@UpgradeTime", his.UpgradeTime),
            new SQLiteParameter("@NewFireware", his.NewFireware),
            new SQLiteParameter("@UpgradeResult", his.UpgradeResult),
            };

            try
            {
                int rows = SQLiteHelper.ExecuteSql(sql, parameters);
                return rows > 0;
            }
            catch (Exception ex)
            {
                Console.WriteLine("InsertUpgradeHis Error: " + ex.Message);
                return false;
            }
        }

        public static List<TUpgradeHis> GetUpgradeHisByDeviceSn(string deviceSn)
        {
            string sql = @"
            SELECT * FROM t_upgrade_his
            WHERE device_sn = @DeviceSn
            ORDER BY upgrade_time DESC";

            SQLiteParameter[] parameters = new SQLiteParameter[]
            {
            new SQLiteParameter("@DeviceSn", deviceSn)
            };

            List<TUpgradeHis> result = new List<TUpgradeHis>();

            using (var reader = SQLiteHelper.ExecuteReader(sql, parameters))
            {
                while (reader.Read())
                {
                    result.Add(new TUpgradeHis
                    {
                        HisId = reader["his_id"].ToString(),
                        DeviceSn = reader["device_sn"].ToString(),
                        PortName = reader["port_name"].ToString(),
                        BaudRate = Convert.ToInt32(reader["baud_rate"]),
                        Address = Convert.ToInt32(reader["address"]),
                        OldFireware = reader["old_fireware"].ToString(),
                        UpgradeTime = reader["upgrade_time"].ToString(),
                        NewFireware = reader["new_fireware"].ToString(),
                        UpgradeResult = Convert.ToInt32(reader["upgrade_result"]),
                    });
                }
            }

            return result;
        }

        public static Tuple<int, int, List<TUpgradeHis>> GetPagedUpgradeHis(int pageNumber, int pageSize, string deviceSn)
        {
            int offset = (pageNumber - 1) * pageSize;

            string sql = @"
            SELECT * FROM t_upgrade_his
            WHERE device_sn = @DeviceSn
            ORDER BY upgrade_time DESC
            LIMIT @PageSize OFFSET @Offset";

            List<SQLiteParameter> parameters = new List<SQLiteParameter>
        {
            new SQLiteParameter("@DeviceSn", deviceSn),
            new SQLiteParameter("@PageSize", pageSize),
            new SQLiteParameter("@Offset", offset),
        };

            List<TUpgradeHis> result = new List<TUpgradeHis>();
            using (var reader = SQLiteHelper.ExecuteReader(sql, parameters.ToArray()))
            {
                while (reader.Read())
                {
                    result.Add(new TUpgradeHis
                    {
                        HisId = reader["his_id"].ToString(),
                        DeviceSn = reader["device_sn"].ToString(),
                        PortName = reader["port_name"].ToString(),
                        BaudRate = Convert.ToInt32(reader["baud_rate"]),
                        Address = Convert.ToInt32(reader["address"]),
                        OldFireware = reader["old_fireware"].ToString(),
                        UpgradeTime = reader["upgrade_time"].ToString(),
                        NewFireware = reader["new_fireware"].ToString(),
                        UpgradeResult = Convert.ToInt32(reader["upgrade_result"]),
                    });
                }
            }

            string countSql = "SELECT COUNT(*) FROM t_upgrade_his WHERE device_sn = @DeviceSn";
            int total = Convert.ToInt32(SQLiteHelper.GetSingle(countSql, new SQLiteParameter("@DeviceSn", deviceSn)));

            int totalPages = (int)Math.Ceiling(total / (double)pageSize);
            return Tuple.Create(total, totalPages, result);
        }

        public static Tuple<int, int, List<TUpgradeHis>> GetPagedUpgradeHis(int pageNumber, int pageSize)
        {
            int offset = (pageNumber - 1) * pageSize;

            string sql = @"
            SELECT * FROM t_upgrade_his            
            ORDER BY upgrade_time DESC
            LIMIT @PageSize OFFSET @Offset";

            List<SQLiteParameter> parameters = new List<SQLiteParameter>
            {
                new SQLiteParameter("@PageSize", pageSize),
                new SQLiteParameter("@Offset", offset),
            };

            List<TUpgradeHis> result = new List<TUpgradeHis>();
            using (var reader = SQLiteHelper.ExecuteReader(sql, parameters.ToArray()))
            {
                while (reader.Read())
                {
                    result.Add(new TUpgradeHis
                    {
                        HisId = reader["his_id"].ToString(),
                        DeviceSn = reader["device_sn"].ToString(),
                        PortName = reader["port_name"].ToString(),
                        BaudRate = Convert.ToInt32(reader["baud_rate"]),
                        Address = Convert.ToInt32(reader["address"]),
                        OldFireware = reader["old_fireware"].ToString(),
                        UpgradeTime = reader["upgrade_time"].ToString(),
                        NewFireware = reader["new_fireware"].ToString(),
                        UpgradeResult = Convert.ToInt32(reader["upgrade_result"]),
                    });
                }
            }

            string countSql = "SELECT COUNT(*) FROM t_upgrade_his";
            int total = Convert.ToInt32(SQLiteHelper.GetSingle(countSql));

            int totalPages = (int)Math.Ceiling(total / (double)pageSize);
            return Tuple.Create(total, totalPages, result);
        }

        public static bool DeleteUpgradeHisByHisId(string hisId)
        {
            string sql = "DELETE FROM t_upgrade_his WHERE his_id = @HisId";
            SQLiteParameter[] parameters = { new SQLiteParameter("@HisId", hisId) };

            try
            {
                return SQLiteHelper.ExecuteSql(sql, parameters) > 0;
            }
            catch (Exception ex)
            {
                Console.WriteLine("DeleteUpgradeHisByHisId Error: " + ex.Message);
                return false;
            }
        }

        public static bool DeleteUpgradeHisByDeviceSn(string deviceSn)
        {
            string sql = "DELETE FROM t_upgrade_his WHERE device_sn = @DeviceSn";
            SQLiteParameter[] parameters = { new SQLiteParameter("@DeviceSn", deviceSn) };

            try
            {
                return SQLiteHelper.ExecuteSql(sql, parameters) > 0;
            }
            catch (Exception ex)
            {
                Console.WriteLine("DeleteUpgradeHisByDeviceSn Error: " + ex.Message);
                return false;
            }
        }

        public static bool DeleteUpgradeAllHis()
        {
            string sql = "DELETE FROM t_upgrade_his";
            //SQLiteParameter[] parameters = { new SQLiteParameter("@DeviceSn", deviceSn) };

            try
            {
                //return SQLiteHelper.ExecuteSql(sql, parameters) > 0;
                return SQLiteHelper.ExecuteSql(sql) >= 0;
            }
            catch (Exception ex)
            {
                Console.WriteLine("DeleteUpgradeHisByDeviceSn Error: " + ex.Message);
                return false;
            }
        }

        public static bool UpdateUpgradeResult(TUpgradeHis his)
        {
            // 定义更新的 SQL 语句
            string sql = @"
                UPDATE t_upgrade_his 
                SET 
                    upgrade_result = @UpgradeResult
                WHERE his_id = @HisId";

            // 创建 SQL 参数
            SQLiteParameter[] parameters = new SQLiteParameter[]
            {
                new SQLiteParameter("@HisId", his.HisId),
                new SQLiteParameter("@UpgradeResult", his.UpgradeResult)
            };

            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 UpdateUpgradeResultAndNewFrieware(TUpgradeHis his)
        {
            // 定义更新的 SQL 语句
            string sql = @"
                UPDATE t_upgrade_his 
                SET 
                    new_fireware = @NewFireware,
                    upgrade_result = @UpgradeResult
                WHERE his_id = @HisId";

            // 创建 SQL 参数
            SQLiteParameter[] parameters = new SQLiteParameter[]
            {
                new SQLiteParameter("@HisId", his.HisId),
                new SQLiteParameter("@NewFireware", his.NewFireware),
                new SQLiteParameter("@UpgradeResult", his.UpgradeResult)
            };

            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;
            }
        }
        //----------------------------------------------------------------------------------
    }

}