Windows Service中FileSystemWatcher重复插入数据库问题求助

Hey Adam, let's work through this duplicate insert issue you're hitting with FileSystemWatcher in your Windows Service. This is a super common gotcha with FileSystemWatcher—here's why it's happening and how to fix it:

Why You're Seeing Duplicates #

FileSystemWatcher's Changed event often fires multiple times for a single file operation (like saving a file). This happens because many applications write to files in stages: creating a temporary file, writing content, renaming it, updating file attributes, etc. Each of these steps triggers a separate Changed event, and your current code runs the insert every time.

Step-by-Step Fixes #

Let's modify your code to filter out these redundant events and ensure only one insert happens per file change.

1. Add a Debounce Mechanism #

We'll track when each file was last processed, and skip any events that fire within a short window (like 1 second) of the last one. This filters out rapid-fire duplicate events.

First, add these static fields to your Service1 class to track file processing times safely:

private static readonly Dictionary<string, DateTime> _lastProcessedFiles = new Dictionary<string, DateTime>();
private static readonly object _fileLock = new object(); // Thread-safe lock for the dictionary

2. Check if the File is Fully Ready #

Before processing, make sure the file is done being written to. If you try to process a file while it's still open by another application, you might get errors—and it can also trigger additional Changed events once the file is closed.

3. Updated Event Handler Code #

Replace your FileSystemWatcher_Changed method with this revised version:

public static void FileSystemWatcher_Changed(object source, FileSystemEventArgs e)
{
    // Skip duplicate events for the same file within 1 second
    lock (_fileLock)
    {
        if (_lastProcessedFiles.TryGetValue(e.FullPath, out var lastProcessed) && 
            DateTime.Now - lastProcessed < TimeSpan.FromSeconds(1))
        {
            WriteToFile($"Skipped duplicate event for: {e.FullPath}");
            return;
        }
        _lastProcessedFiles[e.FullPath] = DateTime.Now;
    }

    // Wait for the file to be fully written (retry up to 3 times)
    bool isFileAccessible = false;
    int retryAttempts = 0;
    while (!isFileAccessible && retryAttempts < 3)
    {
        try
        {
            // Try opening the file with exclusive access to confirm it's ready
            using (var stream = File.Open(e.FullPath, FileMode.Open, FileAccess.Read, FileShare.None))
            {
                isFileAccessible = true;
            }
        }
        catch (IOException)
        {
            // File is still in use—wait 500ms and try again
            retryAttempts++;
            Task.Delay(500).Wait();
        }
    }

    if (!isFileAccessible)
    {
        WriteToFile($"Could not access file after 3 attempts: {e.FullPath}");
        return;
    }

    // Proceed with database insert
    using (SqlConnection con = new SqlConnection("Data Source=localhost\\SQLEXPRESS;Database=ServiceTest;Integrated Security=True;"))
    {
        try
        {
            con.Open();
            var command = new SqlCommand("Insert into test(URL, Location, Path) values(@URL, @agendaname, @path);", con);
            command.Parameters.Add("@URL", System.Data.SqlDbType.VarChar, 100).Value = e.Name;
            command.Parameters.Add("@agendaname", System.Data.SqlDbType.VarChar, 100).Value = ConfigurationManager.AppSettings["agendaname"];
            command.Parameters.Add("@Path", System.Data.SqlDbType.VarChar, 100).Value = ConfigurationManager.AppSettings["findpath"];
            command.ExecuteNonQuery();
            WriteToFile($"Successfully inserted: {e.Name}");
        }
        catch(Exception ex)
        {
            WriteToFile($"Error inserting record: {ex.Message}");
        }
        // Note: The 'using' block automatically closes the connection—no need for con.Close()
    }
}

Key Improvements Explained #

• Debounce Lock: The dictionary and lock ensure we don't process the same file multiple times in quick succession.
• File Access Check: By trying to open the file with exclusive access, we confirm it's no longer being written to by another process.
• Cleaner Connection Handling: Removed the manual con.Close() since the using block disposes the connection automatically.

Bonus: Optional Tweaks #

• If you only care about new files (not modified ones), switch from Changed to the Created event—it fires far fewer times.
• Adjust the debounce window (1 second) or retry count/delay based on your specific file operations.

Give these changes a try—they should eliminate those duplicate inserts you're seeing!

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