I have a service which retrieve historical data and saves it in MySQL table

    protected override async Task ExecuteAsync(CancellationToken stoppingToken)
    {
        while (!stoppingToken.IsCancellationRequested)
        {
            _logger.LogInformation("Worker running at: {time}", DateTimeOffset.Now);

            //Get Relevant Gateways
            string GetRouters = "SELECT * FROM RouterModel;";
            var Routers = await dataAccess.LoadData<RouterModel, dynamic>(GetRouters, new { }, _config.GetConnectionString("MainDB"));

            string GetMeters = "SELECT * FROM PowerMeterModel;";
            var Meters = await dataAccess.LoadData<PowerMeterModel, dynamic>(GetMeters, new { }, _config.GetConnectionString("MainDB"));


            foreach (RouterModel Router in Routers)
            {
                if (Router.IsHavePowerMeters)
                {
                    foreach (PowerMeterModel Meter in Meters.Where(x => x.IdGateway == Router.Id).ToList())
                    {
                        if (Meter.IsActive)
                        {
                            actionList.Add(new Action(() =>
                            {
                                GetHistory(Router, Meter);
                                _logger.LogInformation("Synced History meter:"   Meter.SerialNumber.ToString());

                                
                            }));

                            
                        }
                    }
                }

            }

            Parallel.Invoke(actionList.ToArray());
            actionList.Clear();

            _logger.LogWarning("*************** Sync loop is finished ***************");

            await Task.Delay(60*1000, stoppingToken);
        }
    }


    
    public async void GetHistory(RouterModel Router, PowerMeterModel Meter)
    {
        Web device = new() { IpAddress = Router.IpAddress, UserName = Meter.Username, Password = Meter.Password, Port = Meter.TcpPort };
        StringBuilder SqlQuery = new();
        StringBuilder TimeString = new();
        ElnetMcMapping elnetMcMapping = new();
        ElnetMcModel mcReadings = new();

        if (Meter.ModelClass == nameof(ElnetMcModel).ToString())
        {
            for (int day = 1; day < 4; day  )
            {
                List<List<double>> responeData = new();
                DateTime date = todayDate.AddDays(-day);

                for (int j = 0; j < elnetMcMapping.DataItems.Count; j  = 8)
                {
                    var items = elnetMcMapping.DataItems.Skip(j).Take(8);
                    device.Items = items.Select(x => x.Address.ToString()).ToList();

                    var responseString = device.GetElnetReadings(httpClient, 2, date);
                    if (responseString != null)
                    {
                        int check = responeData.Count;
                        responeData.AddRange(JsonConvert.DeserializeObject<List<List<double>>>(responseString));

                    }
                    else { return; }
                }

                for (int j = 0; j < elnetMcMapping.DataItems.Count; j  ) { elnetMcMapping.DataItems[j].Value = responeData[j]; }

                foreach (MetaData metaData in elnetMcMapping.DataItems)
                {
                    Type type = mcReadings.GetType();
                    PropertyInfo prop = type.GetProperty(metaData.Label);

                    if (prop.PropertyType == typeof(decimal)) { prop.SetValue(mcReadings, Convert.ToDecimal(metaData.Value[0]) * metaData.Multiplier, null); }
                    else if (prop.PropertyType == typeof(string)) { prop.SetValue(mcReadings, metaData.Value[0].ToString(), null); }
                    else if (prop.PropertyType == typeof(int)) { prop.SetValue(mcReadings, int.Parse(metaData.Value[0].ToString()), null); }
                    else if (prop.PropertyType == typeof(DateTime)) { prop.SetValue(mcReadings, DateTime.Now, null); }
                    else { return; }
                }


                var propList = mcReadings.GetType().GetProperties().ToList();
                SqlQuery.Append("INSERT INTO "   Meter.DataTableName   " (");
                foreach (PropertyInfo prop in propList) { SqlQuery.Append(prop.Name   ","); }
                SqlQuery.Remove(SqlQuery.Length - 1, 1);
                SqlQuery.Append(") VALUES (");
                foreach (PropertyInfo prop in propList)
                {
                    if (prop.PropertyType == typeof(DateTime))
                    {
                        DateTime dateTime = (DateTime)prop.GetValue(mcReadings);
                        SqlQuery.Append("'"   dateTime.ToString("yyyy-MM-dd HH:mm:ss")   "'"   ",");
                    }
                    else { SqlQuery.Append(prop.GetValue(mcReadings).ToString()   ","); }
                }
                SqlQuery.Remove(SqlQuery.Length - 1, 1);
                SqlQuery.Append(");");

                await dataAccess.SaveData(SqlQuery.ToString(), mcReadings, _config.GetConnectionString("PwrMeterDb"));

            }
        }
    }

}

}

The above code should add only 4 MySql rows, instead I get 6, it seems like the whole method is starting all over again for each time the loop for (int day = 1; day < 4; day ) cahnges its value.

Instead getting 4 rows (one for each day) I get 6

Instead getting 5 rows (one for each day) I get 10

Instead getting 6 rows (one for each day) I get 15

Instead getting 7 rows (one for each day) I get 21

Any ideas what could be wrong ?

CodePudding user response：

You're not clearing the SqlQuery StringBuilder object at the beginning of each loop.

So, in the first iteration, it has one INSERT query. In the second iteration, it has two, for a total of three INSERTs. In the third iteration, it has three, for a total of six. In the fourth iteration, it has four INSERTs, for a total of ten. This produces the 1, 3, 6, 10, 15, 21 sequence you're observing.

Call SqlQuery.Clear() at the beginning of each loop.