0

I am working on a project about data acquisition. I get data via RasPi3b+ from machines. Data is held Mysql on Raspi server. To track data, I have made a dashborad by using C# winforms. Sometimes machines are shut down and I can't reach Mysql server. When that happens winforms freezes. I don't want it to freeze (maybe a green indicator turns red but it should not affect GUI ). How can I fix this ?

This is my timer code that is triggered every 2 second.

 private void timer1_Tick(object sender, EventArgs e)
    {
        for (int i = 0; i < (IpAndNames.Count ); i++)
        {
            string today = DateTime.Now.ToString("yyyy-MM-dd");
            _lblListOpen[i].Text = "open: " + _machineDal.SpenTime(today, IpAndNames[i].Ip, "Logs", "Machine","open").ToString();
            _lblListClose[i].Text = "close: " + _machineDal.SpendTime(today, IpAndNames[i].Ip, "Logs", "Machine","close").ToString();

            string lastState = _machineDal.LastDateAndState(IpAndNames[i].Ip, "Machine", "Logs").LastState;
            DateTime lastTime = _machineDal.LastDateAndState(IpAndNames[i].Ip, "Machine", "Logs").LastDate;

            TimeSpan spendTime= DateTime.Now - lastTime;
            _lblListWorkingTime[i].Text = lastState +" "+ spendTime.ToString("h'h 'm'm 's's'");

        }
    }

This is my data layer code

 public TimeSpan SpendTime(string date, string ip, string tableName, string db, string state)
    {
        TimeSpan openTime= new TimeSpan(0, 0, 0);
        string connString = "server=" + ip + ";user=root;database=" + db + ";port=3306;password=root;Connection Timeout=1";
        try
        {
            using (_conn = new MySqlConnection(connString))
            {
                string query = "SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(time ) ) ) AS timeSum FROM " + tableName + " " +
                "WHERE Laststate='" + state + "' " +
                "and date like \"" + date + "%\"";
                using (MySqlCommand cmd = new MySqlCommand(query, _conn))
                {
                    _conn.Open();
                    using (MySqlDataReader reader = cmd.ExecuteReader())
                    {
                        reader.Read();
                        if (!reader.IsDBNull(0))
                        {
                           openTime= reader.GetTimeSpan(0);
                        }
                    }
                }
            }
        }
        catch (Exception e)
        {
            Console.WriteLine(e);
        }
        return openTime;
    }
aydinozhan
  • 1
  • This won't help with your current problem, but please read https://stackoverflow.com/questions/14376473/what-are-good-ways-to-prevent-sql-injection as a matter of urgency. – mjwills Sep 09 '20 at 11:16
  • 2
    The reason it freezes is because it is *synchronous* (**waiting** for either a *result* or a *timeout*). If you don't want it to freeze at all, you should consider using asynchronous calls, and if you just don't want it to freeze for too long, you can set the timeout **duration** (to something like 1 or 2 seconds instead of 30 for example) – Rafalon Sep 09 '20 at 12:02
  • 2
    @Rafalon and if you want to use asynchronous calls, you need to switch to [MySqlConnector](https://www.nuget.org/packages/MySqlConnector/) (disclaimer: I'm the lead developer) because MySql.Data has a longstanding bug that its async methods are actually implemented as synchronous: https://bugs.mysql.com/bug.php?id=70111 – Bradley Grainger Sep 09 '20 at 14:12

0 Answers0