help with clearing a list in c#
Tags: csharp, beginners
Published: 2024-05-08 18:19:53

Not sure if this is the best way to accomplish what I'm trying to do. This populates a list from an sql query. Then does stuff and sets a flag. Then when it goes through again it's supposed pull from SQL again ignoring the flagged items from earlier. The flag is getting set. But it doesn't ever pull the new list. It just keeps processing the same items over and over and over again.

I'm sure I'm violating some best practices. I am wide open for input on how to do this better. 

`public sealed class WindowsBackgroundService
(
    CheckIdScan idScanService, 
    ILogger<WindowsBackgroundService> logger
) : BackgroundService
{
    bool debug = false;
    string sqlHost = "localhost";
	protected override async Task ExecuteAsync(CancellationToken stoppingToken)
	{  
		try
        {
            while(!stoppingToken.IsCancellationRequested)
            {
                var MySQLconn = MySQLDbConn.Instance();
                MySQLconn.Host = "xxx.xxx.xxx.xxx";
                MySQLconn.Database = "xxxx";
                MySQLconn.UserName = "xxxx";
                MySQLconn.Password = "xxxx"; 
                List<ScanModel> scans = idScanService.GetScans();
                foreach (ScanModel scan in scans)
                {
                    try
                    {
                        if (MySQLconn.IsConnect())
                        {
                            string insert = "insert into scans(siteCode,gender,age,hash,timeStamp) values(@siteCode,@gender,@age,@hash,@timeStamp)";
                           
                            MySqlCommand MySQLcmd = new(insert, MySQLconn.connection);
                            MySQLcmd.Parameters.AddWithValue("@siteCode", scan.siteCode);
                            MySQLcmd.Parameters.AddWithValue("@gender", scan.gender);
                            MySQLcmd.Parameters.AddWithValue("@age", scan.age);
                            MySQLcmd.Parameters.AddWithValue("@hash", scan.hash);
                            MySQLcmd.Parameters.AddWithValue("@timeStamp", scan.timeStamp);
                            MySQLcmd.Prepare();
                            try
                            {
                                MySQLcmd.ExecuteNonQuery();
                            }
                            catch(Exception ex)
                            {
                                MessageBox.Show(ex.ToString()); 
                            }
                        }
                    } 
                    catch(Exception ex)
                    {
                        logger.LogWarning("Error:{error}", ex.ToString());
                    }
                    if (!debug)
                    {
                        string sqlConnString = $"Server=tcp:{sqlHost};Database=xxxx;User Id=xxxx;Password=xxxx;Connection Timeout=60;TrustServerCertificate=True";
                        string updateSentQuery = "update scans set sent=1 where scanId=@scanId";
                        using (var sqlConn = new SqlConnection(sqlConnString))
                        {
                            using (SqlCommand sqlCmd = sqlConn.CreateCommand())
                            {
                                sqlCmd.CommandText = updateSentQuery; 
                                sqlCmd.Parameters.AddWithValue("@scanId", scan.scanId);
                                sqlConn.Open();
                                sqlCmd.ExecuteNonQuery();
                                sqlConn.Close();
                            }
                        }
                    }
                }
                scans = new List<ScanModel>();
                
            }
            
        }
        catch(Exception ex)
        {
            logger.LogWarning("Error:{error}", ex.ToString());
        }
        await Task.Delay(TimeSpan.FromSeconds(5), stoppingToken);
	}
}`