C#后台服务循环中列表未更新问题求助

开发了一个Windows后台服务，逻辑为：从SQL获取所有未标记（sent=0）的ScanModel列表，处理列表项后设置sent=1标记，等待5秒后重新拉取列表。但服务反复处理旧列表而非新数据，已确认sent标记设置正常，重启服务后可拉取未发送项，但循环内无法更新列表。刚接触C#，寻求优化方案或列表更新解决方法。

WindowsBackgroundService 代码 #

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);
    }
}

GetScans 方法代码 #

public List<ScanModel> GetScans()
{
    if (!debug)
    {
        #region ReleaseScanList
        string connString = $"Server=tcp:{host};Database=piscan;User Id=xxxx;Password=xxxx;Connection Timeout=60;TrustServerCertificate=True;";
        string query = $"select scanID, gender, age, hash, sent, timeStamp from scans where sent=0";
     

        try
        {
            using (SqlConnection conn = new SqlConnection(connString))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand(query, conn))
                {
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            #region GetVariables
                            try
                            {
                                scanId = (short)reader.GetInt16(reader.GetOrdinal("scanId"));
                            }
                            catch (InvalidCastException ex)
                            {
                                throw new InvalidCastException($"ScanID:  {ex.Message}");
                            }

                            try
                            {
                                gender = reader.GetString(reader.GetOrdinal("gender"));
                            }
                            catch (InvalidCastException ex)
                            {
                                throw new InvalidCastException($"Gender:  {ex.Message}");
                            }

                            try
                            {
                                age = (short)reader.GetInt16(reader.GetOrdinal("age"));
                            }
                            catch (InvalidCastException ex)
                            {
                                throw new InvalidCastException($"Age:  {ex.Message}");
                            }

                            try
                            {
                                hash = reader.GetString(reader.GetOrdinal("hash"));
                            }
                            catch (InvalidCastException ex)
                            {
                                throw new InvalidCastException($"Hash:  {ex.Message}");
                            }

                            try
                            {
                                timeStamp = reader.GetDateTime(reader.GetOrdinal("timeStamp"));
                            }
                            catch (InvalidCastException ex)
                            {
                                throw new InvalidCastException($"Time Stamp:  {ex.Message}");
                            }

                            try
                            {
                                sentInt = reader.GetInt16(reader.GetOrdinal("sent"));
                            }
                            catch(InvalidCastException ex)
                            {
                                throw new InvalidCastException($"Sent: Unable to get Int16 from database {ex.ToString()}"); 
                            }
                            try
                            { 
                                sent = Convert.ToBoolean(sentInt); 
                            }
                            catch (InvalidCastException ex)
                            {
                                throw new InvalidCastException($"Sent: Unable to cast as boolean {ex.Message}");
                            }

                            try
                            {
                                siteCode = GetSiteCode();
                            }
                            catch (InvalidCastException ex)
                            {
                                throw new InvalidCastException($"Site Code: {ex.Message}");
                            }
                            #endregion
                            scans.Add
                            (
                                new ScanModel
                                (
                                        scanId,
                                        gender,
                                        age,
                                        hash,
                                        sent,
                                        timeStamp,
                                        siteCode
                                )
                            );
                        }
                        reader.Close();
                    }
                }
            }
        }
        catch (Exception ex)
        {
            throw new Exception($"Could not connect to piscan {ex.Message} {ex.ToString()}");
        }
       
        #endregion
    }

    else
    {
        #region DebugScanList
        scans.Add
        (
            new ScanModel
            (
                1234,
                "M",
                48,
                "ABCDEFGHIJKLMNOP",
                false,
                DateTime.Now,
                siteCode
            )
        );
        
        #endregion
    }
    return scans;
}

核心问题定位 #

GetScans方法复用了类成员变量scans，每次调用时仅向列表中添加新数据，未清空原有已处理的旧数据，导致循环中反复处理历史项。重启服务后scans被重新初始化，首次拉取正常，但后续调用只会累加数据。

具体修复与优化方案 #

1. 修复列表重复问题 #

每次调用GetScans时初始化新列表，删除类成员变量scans：

public List<ScanModel> GetScans()
{
    var scans = new List<ScanModel>(); // 每次调用创建新列表
    if (!debug)
    {
        // 原有查询逻辑...
    }
    else
    {
        // debug逻辑...
    }
    return scans;
}

2. 修正循环等待逻辑 #

将Task.Delay移至while循环内部，确保处理完一次后等待5秒再拉取新数据：

protected override async Task ExecuteAsync(CancellationToken stoppingToken)
{  
    try
    {
        while(!stoppingToken.IsCancellationRequested)
        {
            // 原有处理逻辑...
            
            await Task.Delay(TimeSpan.FromSeconds(5), stoppingToken); // 移到循环内
        }
    }
    catch(Exception ex)
    {
        logger.LogWarning("Error:{error}", ex.ToString());
    }
}

3. 数据库操作优化 #

• 移除后台服务中的MessageBox：后台无UI界面，改用日志记录错误：

  catch(Exception ex)
  {
      logger.LogError(ex, "MySQL插入操作失败");
  }
  

• 使用异步数据库方法：避免阻塞线程，提升服务性能：

  using (SqlDataReader reader = await cmd.ExecuteReaderAsync(stoppingToken))
  {
      while (await reader.ReadAsync(stoppingToken))
      {
          // 读取数据逻辑...
      }
  }
  

• 避免单例数据库连接：改用using块创建新连接，避免连接泄漏：

  using (var mySqlConn = new MySqlConnection("你的MySQL连接字符串"))
  {
      await mySqlConn.OpenAsync(stoppingToken);
      // 执行插入操作...
  }
  

• 连接字符串配置化：将连接字符串存入appsettings.json，通过IConfiguration读取，避免硬编码：

  // appsettings.json
  {
    "ConnectionStrings": {
      "SqlServer": "Server=tcp:xxx;Database=xxx;User Id=xxx;Password=xxx;TrustServerCertificate=True",
      "MySQL": "Server=xxx;Database=xxx;Uid=xxx;Pwd=xxx;"
    }
  }
  

• 局部变量替代类成员：GetScans中的scanId、gender等变量改为局部变量，避免多线程数据竞争：

  while (await reader.ReadAsync(stoppingToken))
  {
      short scanId;
      string gender;
      short age;
      // ... 其他变量声明
      // 读取数据逻辑...
  }
  

4. 原子性处理优化（可选） #

使用UPDATE ... OUTPUT原子性获取并标记数据，避免多实例重复处理：

UPDATE scans 
SET sent = 1 
OUTPUT inserted.scanID, inserted.gender, inserted.age, inserted.hash, inserted.timeStamp
WHERE sent = 0;

内容的提问来源于stack exchange，提问作者Tom Bauserman