Stream file, file in use, FileSystemWatcher and question about threads

Question

I've been trying to create a script which takes the content of a text file which may look like this (1 line text in a .txt. file):

SomeData     SomeData     SomeData

and these files are dropped randomly into a folder. Sometimes in 10's, sometimes 1 at a time. The files are then moved from the folder using robocopy to another folder roughly every ten mins.

Anyway, I wanted to read each file's single line of text and insert into the database.

Works fine once or twice, but then I get file is in use error.

Tried switching from File.ReadAllText(textFile) to FileStream and still have the same problem.

Can anyone help?

Also, I'm a little worried about the number of threads this may use when dropped onto a server. I can see the threads in Visual Studio Code, but interested to know what I could do to improve my script and make it more efficient?

I am new to coding! This will be my first.

using System;
using System.IO;
using System.Data.SqlClient;


namespace IDGFileSystemWatcher
{
    class Program
    {
        static void Main(string[] args)
        {
            string path = "C:/Test/";
            MonitorDirectory(path);
            Console.ReadKey();
        }

        private static void MonitorDirectory(string path)
        {
            FileSystemWatcher fileSystemWatcher = new FileSystemWatcher();
            fileSystemWatcher.Path = path;
            fileSystemWatcher.Created += FileSystemWatcher_Created;
            fileSystemWatcher.EnableRaisingEvents = true;
        }

        private static void FileSystemWatcher_Created(object sender, FileSystemEventArgs e)
        {
            string txtContents;
            string dir = "C:/Test/";
            string textFile = dir + e.Name;
            //string text = File.ReadAllText(textFile);

            FileStream fs = new FileStream(textFile, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
            StreamReader sr = new StreamReader(fs);
            txtContents = sr.ReadToEnd();
            sr.Close();

            string[] splitstring = txtContents.Split(new char[0], StringSplitOptions.RemoveEmptyEntries);
            DateTime now = DateTime.Now;
                 
            SqlConnection connection = new SqlConnection();
            connection.ConnectionString =
                        "Data Source=computername;" +
                        "Initial Catalog=contents;" +
                        "User id=userid;" + 
                        "Password=password;";
                    
            string query = "INSERT INTO dbo.MB1B (Field1, Field2, Field3, TStamp) VALUES (@Field1, @Field2, @Field3, @TStamp)";

            using (SqlCommand command = new SqlCommand(query, connection))
            {
                command.Parameters.AddWithValue("@Field1", splitstring[0]);
                command.Parameters.AddWithValue("@Field2", splitstring[1]);
                command.Parameters.AddWithValue("@Field3", splitstring[2]);
                command.Parameters.AddWithValue("@TStamp", now);

                connection.Open();
                int result = command.ExecuteNonQuery();

                // Check Error
                if (result < 0) 
                {
                    Console.WriteLine("Error inserting data into database!");
                }

                // connection.Close();
            }
        }
    }
}

I tried using both File.ReadAllText(textFile) and FileStream.

I have tried to check if the files are in use but I am just copying and pasting test /txt files from one folder to another sometimes in 10's and sometimes in singles. Or dropping 50 test text files into a folder.

I've tried waiting for usage to time out (so the file isn't in use) and I have tried adding a pause to the script.

Please note that nothing is being written back to the file, I am simply reading a file and entering three items from the single line of text in the file to the database.

Answer 1

Cite the reference:

The OnCreated event is raised as soon as a file is created. If a file is being copied or transferred into a watched directory, the OnCreated event will be raised immediately, followed by one or more OnChanged events.

Simply put, OnCreated event does not guarantee that the file has been transferred completely, which is why you will receive the file-in-use exception. There are APIs to help you to detect if files are in use, but the most convenient solution should be try reading multiple times:

string txtContents;
for (int i = 0; i < 10; i++)
{
    try
    {
        txtContents = File.ReadAllText(textFile);
        break;
    }
    catch (IOException)
    {
        Thread.Sleep(10);
    }
}