Can you make your program do other things while waiting for the filesystemwatcher to find newly changed files?

Question

I'm building a program that keeps track of a directory and adds an entry to an mysql database whenever a new file has been created in the directory.

I'll show the code for the FileSystemWatcher, the storage instance is an mysql class:

FileSystemWatcher watcher = new FileSystemWatcher
{
    Path = directoryToWatch,
    IncludeSubdirectories = true,
    NotifyFilter = NotifyFilters.Attributes |
                   NotifyFilters.DirectoryName |
                   NotifyFilters.FileName,
    EnableRaisingEvents = true,
    Filter = "*.*"
};

watcher.Created += (OnDirectoryChange);

public void OnDirectoryChange(object sender, FileSystemEventArgs e)
{
    storage.Insert(Settings.Default.added_files, e.Name);
}

So that's clear. Here's the code for the mysql database. CloseConnection is almost the same as 'OpenConnection' so I didn't copy that over.

public bool OpenConnection()
{
    try
    {
        connection.Open();
        return true;
    }
    catch (Exception ex)
    {
        throw ex;
    }
}

public void Insert(string tablename, string filename, string attractionCode, int number=0)
{
   string query = "INSERT INTO " + tablename + " (FILE_NAME) VALUES('" + filename + "')";

   MySqlCommand cmd = new MySqlCommand(query, connection);

   OpenConnection();
   cmd.ExecuteNonQuery();
   CloseConnection();
}

Now the thing is, when I paste 20 files at once in the directory the filesystemwatcher checks, the sql will only process 18 of them. It throws errors like 'Connection was open already'. I also use an select statement somewhere in the sql code, which contains this part for example:

MySqlCommand cmd = new MySqlCommand(query, connection);

MySqlDataReader dataReader = cmd.ExecuteReader();

while (dataReader.Read())
{
    list[0].Add(dataReader["id"] + "");
    list[1].Add(dataReader["code"] + "");
    list[2].Add(dataReader["name"] + "");
}

dataReader.Close();

This also sometimes throws errors like 'can only use one datareader'.

I think the solution for me would be creating some kind of queue for all files the filesystemwatcher handles and then iterating through this queue one by one. But how would I handle this, because the filesystemwatcher has to keep watching the directory. I'm afraid it might miss some files while processing the queue. What could work?

Your conceptual approach (using a queue) is a good approach. The filesystemwatcher will run on its own thread so if the queue runs on its own thread, the `OnDirectoryChange` need only poke the new value into the queue and the queue will get to it eventually. — Forty3, Mar 22 '18 at 13:43
So the queue should be another class, instantiated with the filesystemwatcher? — Superleggera, Mar 22 '18 at 13:45
Yes - and given the multithreaded nature of the filesystemwatcher, have a look at the Producer/Consumer pattern [Microsoft Docs](https://learn.microsoft.com/en-us/dotnet/standard/parallel-programming/how-to-implement-a-producer-consumer-dataflow-pattern). At first it may seem overwhelming, but once you realize your filesystemwatcher is the Producer, wiring up your Consumer should be straightforward. — Forty3, Mar 22 '18 at 13:48
BTW: here is a pretty good read on a situation somewhat similar to yours: [SO Question](https://stackoverflow.com/q/2308195/3485669) and here is a nifty BackgroundQueue solution: [SO Answer](https://stackoverflow.com/a/15120092/3485669) — Forty3, Mar 22 '18 at 14:01

score 2 · Accepted Answer · answered Mar 22 '18 at 15:44

Borrowing from this excellent solution add this class somewhere in your project:

public class BackgroundQueue
{
    private Task previousTask = Task.FromResult(true);
    private object key = new object();
    public Task QueueTask(Action action)
    {
        lock (key)
        {
            previousTask = previousTask.ContinueWith(t => action()
                , CancellationToken.None
                , TaskContinuationOptions.None
                , TaskScheduler.Default);
            return previousTask;
        }
    }

    public Task<T> QueueTask<T>(Func<T> work)
    {
        lock (key)
        {
            var task = previousTask.ContinueWith(t => work()
                , CancellationToken.None
                , TaskContinuationOptions.None
                , TaskScheduler.Default);
            previousTask = task;
            return task;
        }
    }
}

I propose the following change to your main module:

// Place this as a module level variable.. so it doesn't go out of scope as long
// as the FileSystemWatcher is running
BackgroundQueue _bq = new BackgroundQueue();

Then the following change to invoke the queue:

FileSystemWatcher watcher = new FileSystemWatcher
{
    Path = directoryToWatch,
    IncludeSubdirectories = true,
    NotifyFilter = NotifyFilters.Attributes |
                   NotifyFilters.DirectoryName |
                   NotifyFilters.FileName,
    EnableRaisingEvents = true,
    Filter = "*.*"
};

watcher.Created += (OnDirectoryChange);

public void OnDirectoryChange(object sender, FileSystemEventArgs e)
{
     // Using the shorthand lambda syntax
    _bq.QueueTask(() => storage.Insert(Settings.Default.added_files, e.Name));
}

This should enqueue each change the FileSystemWatcher throws your way. Keep in mind the comments in this SO Question about the FileSystemWatcher not catching everything.

Maybe I should create a new question for this, but how would i go with a disconnected database with this? — Superleggera, Mar 23 '18 at 14:06
Ooo... definitely open/research another question. TBH - I know very little about MySql so I certainly wouldn't be of much use. Also, it would clutter up the cleanliness of your original question. — Forty3, Mar 24 '18 at 15:53

Can you make your program do other things while waiting for the filesystemwatcher to find newly changed files?

1 Answers1

Linked