AVS/MySqlConnectivity.cs

using MySql.Data.MySqlClient;
using System;
using System.Net.NetworkInformation;

namespace AVS
{
    class MySqlConnectivity
    {
        private readonly string connectionString = "server=utopia-2.c5qech8o9lgg.us-east-1.rds.amazonaws.com;user=itemVerification;password=itemVerification01;database=item_verification_system";

        public void UpdateClientActivity(string pcName)
        {
            if (!IsInternetAvailable())
                return;

            using (var conn = new MySqlConnection(connectionString))
            {
                conn.Open();

                long count = 0;

                // First check if line_name exists
                string checkQuery = "SELECT COUNT(*) FROM avs_line WHERE line_name = @pcName";
                using (var cmd = new MySqlCommand(checkQuery, conn))
                {
                    cmd.Parameters.AddWithValue("@pcName", pcName);
                    count = Convert.ToInt64(cmd.ExecuteScalar());
                }

                string dateTimeNow = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");

                if (count > 0)
                {
                    string updateQuery = "UPDATE avs_line SET last_client_activity = @date WHERE line_name = @pcName";
                    using (var updateCmd = new MySqlCommand(updateQuery, conn))
                    {
                        updateCmd.Parameters.AddWithValue("@pcName", pcName);
                        updateCmd.Parameters.AddWithValue("@date", dateTimeNow); // ✅ correct assignment
                        updateCmd.ExecuteNonQuery();
                    }
                }
                else
                {
                    string insertQuery = @"
                INSERT INTO avs_line 
                (line_name, site, department, floor, device_type, status, last_client_activity, created_at, department_id) 
                VALUES (@pcName, '', '', '', 'PC', 1, @date, @date, 0)";
                    using (var insertCmd = new MySqlCommand(insertQuery, conn))
                    {
                        insertCmd.Parameters.AddWithValue("@pcName", pcName);
                        insertCmd.Parameters.AddWithValue("@date", dateTimeNow); // ✅ correct assignment
                        insertCmd.ExecuteNonQuery();
                    }
                }
            }
        }
        private bool IsInternetAvailable()
        {
            try
            {
                using (var ping = new Ping())
                {
                    var reply = ping.Send("8.8.8.8", 3000);
                    return reply.Status == IPStatus.Success;
                }
            }
            catch
            {
                return false;
            }
        }
    }
}