AiQ_GUI/Microsoft/Access.cs

using System.Data.OleDb;

namespace AiQ_GUI
{
    class Access
    {
        const string connString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=G:\Shared drives\MAV Production GUI's\AiQ\GUI's\AiQ_Final_Test.accdb;Persist Security Info=False;OLE DB Services=-1;";

        // Reads camera model numbers and descriptions from the database, sorts them alphabetically by model number (except "AB12CD", which appears last), and formats each entry as "ModelNumber - Description".
        public static string[] ReadCamTypes()
        {
            List<Tuple<string, string>> modelTuples = new List<Tuple<string, string>>(30); // Preallocate list with estimated capacity to reduce internal resizing
            using OleDbConnection conn = new(connString);

            try
            {
                conn.Open();
            }
            catch
            {
                MessageBox.Show("Could not access Access in google drive. Is it running?");
                return null;
            }

            const string query = "SELECT ModelNumber, Description FROM AiQ WHERE MarkNumber > 1";

            using OleDbCommand cmd = new(query, conn);
            using OleDbDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                // Extract each model number and description, using empty string if null
                string modelNumber = reader["ModelNumber"] as string ?? string.Empty;
                string description = reader["Description"] as string ?? string.Empty;
                modelTuples.Add(Tuple.Create(modelNumber.Trim(), description.Trim()));
            }

            // Sort: push "AB12CD" to the bottom, then sort remaining items alphabetically
            IOrderedEnumerable<Tuple<string, string>> sorted = modelTuples.OrderBy(t => t.Item1.Equals("AB12CD", StringComparison.OrdinalIgnoreCase) ? 1 : 0)
                                    .ThenBy(t => t.Item1, StringComparer.OrdinalIgnoreCase);

            // Format the sorted tuples as "ModelNumber - Description" strings and return as array
            return sorted.Select(t => $"{t.Item1} - {t.Item2}").ToArray();
        }

        // Read the universal data table from the database and populate the UniversalData class with the values.
        public static void ReadUniData()
        {
            using OleDbConnection conn = new(connString);
            try
            {
                conn.Open();
            }
            catch
            {
                MessageBox.Show("Could not access Access in google drive. Is it running?");
                return;
            }

            const string query = "SELECT FlexiVersion, FlexiRevision, WonwooFirmware, AiQGUIVersion, PowerConsumption, LicencingServerURL FROM UniversalData"; // Grab the universal data

            using OleDbCommand cmd = new(query, conn);
            using OleDbDataReader reader = cmd.ExecuteReader();
            reader.Read();
            UniversalData.ExpFlexiVer = Convert.ToString(reader["FlexiVersion"]);
            UniversalData.ExpFlexiRev = Convert.ToString(reader["FlexiRevision"]);
            UniversalData.WonwooFirmware = Convert.ToString(reader["WonwooFirmware"]);
            UniversalData.LatestVersion = Convert.ToString(reader["AiQGUIVersion"]);
            UniversalData.PowerConsumption = Convert.ToInt16(reader["PowerConsumption"]);
            UniversalData.LicencingServerURL = Convert.ToString(reader["LicencingServerURL"]);
        }

        // Knowing the model number on test, this function reads the database and populates the Camera class with the values.
        public static void ReadModelRow(string ModelOnTest)
        {
            using OleDbConnection conn = new(connString);
            try
            {
                conn.Open();
            }
            catch
            {
                MessageBox.Show("Could not access Access in google drive. Is it running?");
                return;
            }

            string query = $"SELECT * FROM AiQ WHERE ModelNumber = '{ModelOnTest}';"; // Grab all the info for specified model

            using OleDbCommand cmd = new(query, conn);
            using OleDbDataReader reader = cmd.ExecuteReader();
            reader.Read();

            // Populate the CameraAccessInfo class with the values from the database
            CameraAccessInfo.Processor = Convert.ToString(reader["Processor"]);
            CameraAccessInfo.VaxtorLic = Convert.ToBoolean(reader["Vaxtor"]);
            CameraAccessInfo.HardwareExtras = Convert.ToString(reader["HardwareExtras"]);
            CameraAccessInfo.PowerType = Convert.ToString(reader["PowerType"]);
            CameraAccessInfo.LED_V = Convert.ToDouble(reader["LEDVoltage"]);
            CameraAccessInfo.LED_I = Convert.ToInt32(reader["LEDCurrent"]);
            CameraAccessInfo.SpreadsheetID = Convert.ToString(reader["SSID"]);
        }
    }

    // Expected universal data for the GUI, read from the database
    public class UniversalData
    {
        public static string ExpFlexiVer { get; set; } = string.Empty;
        public static string ExpFlexiRev { get; set; } = string.Empty;
        public static string WonwooFirmware { get; set; } = string.Empty;
        public static string LatestVersion { get; set; } = string.Empty;
        public static int PowerConsumption { get; set; } = 0;
        public static string LicencingServerURL { get; set; } = string.Empty;
    }

    // One object to contain all the camera info from the model info access database
    public class CameraAccessInfo
    {
        public static string Processor { get; set; } = string.Empty;
        public static bool VaxtorLic { get; set; } = false;
        public static string HardwareExtras { get; set; } = string.Empty;
        public static string PowerType { get; set; } = string.Empty;
        public static double LED_V { get; set; } = 0;
        public static int LED_I { get; set; } = 0;
        public static string SpreadsheetID { get; set; } = string.Empty;
    }
}