Database.cs - main - VanDamn/cSharp_RD

Database.cs
211 строк9 KB
VanDamn
версия 2
09 июл 2026, 15:30
09 июл 2026, 15:30ea64be4
using System;
using System.Collections.Generic;
using System.Linq;
using Microsoft.Data.Sqlite;

namespace Electro_Sheet
{
    public class Row
    {
        public int Id { get; set; }
        public string Name { get; set; } = string.Empty;
        public double Voltage { get; set; } = double.NaN;
        public int Total { get; set; } = 0;
        public double Pmech { get; set; } = double.NaN;
        public double Pdvig { get; set; } = double.NaN;
        public double PdvigMin { get; set; } = double.NaN;
        public double Kpd { get; set; } = double.NaN;
        public double CosPhi { get; set; } = double.NaN;
        public string LoadType { get; set; } = string.Empty;
        public int WorkingVal { get; set; } = 0;
        public double LoadFactor { get; set; } = double.NaN;
        public double WorkHours { get; set; } = double.NaN;
        public double MathP { get; set; } = double.NaN;
        public double MathQ { get; set; } = double.NaN;
        public double Dispersion { get; set; } = double.NaN;
        public double SumP { get; set; } = double.NaN;
    }

    public class Database
    {
        private readonly string connectionString;

        public Database(string dbPath)
        {
            connectionString = $"Data Source={dbPath}";
            Initialize();
        }

        private void Initialize()
        {
            using var connection = new SqliteConnection(connectionString);
            connection.Open();
            var command = connection.CreateCommand();

            command.CommandText = @"
                CREATE TABLE IF NOT EXISTS Rows (
                    Id INTEGER PRIMARY KEY,
                    Name TEXT NOT NULL,
                    Voltage REAL,
                    Total INTEGER,
                    Pmech REAL,
                    Pdvig REAL,
                    PdvigMin REAL,
                    Kpd REAL,
                    CosPhi REAL,
                    LoadType TEXT,
                    WorkingVal INTEGER,
                    LoadFactor REAL,
                    WorkHours REAL,
                    MathP REAL,
                    MathQ REAL,
                    Dispersion REAL,
                    SumP REAL
                );";
            command.ExecuteNonQuery();
        }

        public List<Row> GetAllRows()
        {
            var rows = new List<Row>();
            using var connection = new SqliteConnection(connectionString);
            connection.Open();

            var command = connection.CreateCommand();
            command.CommandText = "SELECT Id, Name, Voltage, Total, Pmech, Pdvig, PdvigMin, Kpd, CosPhi, LoadType, WorkingVal, LoadFactor, WorkHours, MathP, MathQ, Dispersion, SumP FROM Rows ORDER BY Name";

            using var reader = command.ExecuteReader();
            while (reader.Read())
            {
                rows.Add(new Row
                {
                    Id = reader.GetInt32(0),
                    Name = reader.IsDBNull(1) ? string.Empty : reader.GetString(1),
                    Voltage = reader.IsDBNull(2) ? double.NaN : reader.GetDouble(2),
                    Total = reader.IsDBNull(3) ? 0 : reader.GetInt32(3),
                    Pmech = reader.IsDBNull(4) ? double.NaN : reader.GetDouble(4),
                    Pdvig = reader.IsDBNull(5) ? double.NaN : reader.GetDouble(5),
                    PdvigMin = reader.IsDBNull(6) ? double.NaN : reader.GetDouble(6),
                    Kpd = reader.IsDBNull(7) ? double.NaN : reader.GetDouble(7),
                    CosPhi = reader.IsDBNull(8) ? double.NaN : reader.GetDouble(8),
                    LoadType = reader.IsDBNull(9) ? string.Empty : reader.GetString(9),
                    WorkingVal = reader.IsDBNull(10) ? 0 : reader.GetInt32(10),
                    LoadFactor = reader.IsDBNull(11) ? double.NaN : reader.GetDouble(11),
                    WorkHours = reader.IsDBNull(12) ? double.NaN : reader.GetDouble(12),
                    MathP = reader.IsDBNull(13) ? double.NaN : reader.GetDouble(13),
                    MathQ = reader.IsDBNull(14) ? double.NaN : reader.GetDouble(14),
                    Dispersion = reader.IsDBNull(15) ? double.NaN : reader.GetDouble(15),
                    SumP = reader.IsDBNull(16) ? double.NaN : reader.GetDouble(16)
                });
            }
            return rows;
        }

        public int AddRow(Row row)
        {
            using var connection = new SqliteConnection(connectionString);
            connection.Open();

            using var insertCmd = connection.CreateCommand();
            insertCmd.CommandText = @"
                INSERT INTO Rows 
                    (Id, Name, Voltage, Total, Pmech, Pdvig, PdvigMin, Kpd, CosPhi, LoadType, WorkingVal, LoadFactor, WorkHours, MathP, MathQ, Dispersion, SumP) 
                VALUES 
                    (@id, @name, @voltage, @total, @pmech, @pdvig, @pdvigMin, @kpd, @cosPhi, @loadType, @workingVal, @loadFactor, @workHours, @mathP, @mathQ, @dispersion, @sumP)";

            insertCmd.Parameters.AddWithValue("@id", row.Id);
            insertCmd.Parameters.AddWithValue("@name", row.Name ?? string.Empty);
            insertCmd.Parameters.AddWithValue("@voltage", row.Voltage);
            insertCmd.Parameters.AddWithValue("@total", row.Total);
            insertCmd.Parameters.AddWithValue("@pmech", row.Pmech);
            insertCmd.Parameters.AddWithValue("@pdvig", row.Pdvig);
            insertCmd.Parameters.AddWithValue("@pdvigMin", row.PdvigMin);
            insertCmd.Parameters.AddWithValue("@kpd", row.Kpd);
            insertCmd.Parameters.AddWithValue("@cosPhi", row.CosPhi);
            insertCmd.Parameters.AddWithValue("@loadType", row.LoadType ?? string.Empty);
            insertCmd.Parameters.AddWithValue("@workingVal", row.WorkingVal);
            insertCmd.Parameters.AddWithValue("@loadFactor", row.LoadFactor);
            insertCmd.Parameters.AddWithValue("@workHours", row.WorkHours);

            insertCmd.Parameters.AddWithValue("@mathP", row.MathP);
            insertCmd.Parameters.AddWithValue("@mathQ", row.MathQ);
            insertCmd.Parameters.AddWithValue("@dispersion", row.Dispersion);
            insertCmd.Parameters.AddWithValue("@sumP", row.SumP);

            insertCmd.ExecuteNonQuery();

            return row.Id;
        }

        public void UpdateRow(Row row)
        {
            using var connection = new SqliteConnection(connectionString);
            connection.Open();

            var command = connection.CreateCommand();
            command.CommandText = @"
                UPDATE Rows
                SET Name=@name, Voltage=@voltage, Total=@total, Pmech=@pmech, Pdvig=@pdvig, PdvigMin=@pdvigMin, Kpd=@kpd, CosPhi=@cosPhi, 
                    LoadType=@loadType, WorkingVal=@workingVal, LoadFactor=@loadFactor, WorkHours=@workHours,
                    MathP=@mathP, MathQ=@mathQ, Dispersion=@dispersion, SumP=@sumP
                WHERE Id=@id";

            command.Parameters.AddWithValue("@id", row.Id);
            command.Parameters.AddWithValue("@name", row.Name ?? string.Empty);
            command.Parameters.AddWithValue("@voltage", row.Voltage);
            command.Parameters.AddWithValue("@total", row.Total);
            command.Parameters.AddWithValue("@pmech", row.Pmech);
            command.Parameters.AddWithValue("@pdvig", row.Pdvig);
            command.Parameters.AddWithValue("@pdvigMin", row.PdvigMin);
            command.Parameters.AddWithValue("@kpd", row.Kpd);
            command.Parameters.AddWithValue("@cosPhi", row.CosPhi);
            command.Parameters.AddWithValue("@loadType", row.LoadType ?? string.Empty);
            command.Parameters.AddWithValue("@workingVal", row.WorkingVal);
            command.Parameters.AddWithValue("@loadFactor", row.LoadFactor);
            command.Parameters.AddWithValue("@workHours", row.WorkHours);

            command.Parameters.AddWithValue("@mathP", row.MathP);
            command.Parameters.AddWithValue("@mathQ", row.MathQ);
            command.Parameters.AddWithValue("@dispersion", row.Dispersion);
            command.Parameters.AddWithValue("@sumP", row.SumP);

            command.ExecuteNonQuery();
        }

        public void DeleteRow(int id)
        {
            using var connection = new SqliteConnection(connectionString);
            connection.Open();

            var command = connection.CreateCommand();
            command.CommandText = "DELETE FROM Rows WHERE Id=@id";

            command.Parameters.AddWithValue("@id", id);
            command.ExecuteNonQuery();
        }

        public int GetNextAvailableId()
        {
            var sortedIds = GetAllRows()
                .Select(r => r.Id)
                .OrderBy(id => id)
                .ToList();

            int nextId = 1;

            foreach (var id in sortedIds)
            {
                if (id == nextId)
                {
                    nextId++;
                }
                else if (id > nextId)
                {
                    break;
                }
            }

            return nextId;
        }
    }
}