File.Move no longer working

[kriscs1]

Hi,
I have a program that checks a folder for Excel files, imports their contents via SQL to a database table, then moves the Excel file to a 'Completed' folder.
This worked flawlessly last year. Unfortunatley, I am now getting the following error:

System.IO.IOException: The process cannot access the file because it is being used by another process.
at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)
at System.IO.__Error.WinIOError()
at System.IO.File.InternalMove(String sourceFileName, String destFileName, Boolean checkHost)
at System.IO.File.Move(String sourceFileName, String destFileName)
at UploadAttendanceLogtoSQLTaible.Program.InsertExcelRecords(String filePath, String destinationPath) in C:\Users\**Redacted**\Google Drive\Teaching\Software\UploadAttendanceLog\UploadAttendanceLogtoSQLTable\Program.cs:line 159

The data uploads just fine. It is just moving the file afterwards that is the problem.

Here is the relevant code:

        private static void InsertExcelRecords(string filePath, string destinationPath)
        {
            LogWriter Log = new LogWriter("");
            try
            {
                //  ExcelConn(_path); 
                string constr = string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0 Xml;HDR=YES;""", filePath);
                OleDbConnection Econ = new OleDbConnection(constr);
                string Query = string.Format("Select [Student ID],[Activity],[Session Name],[Date],[Time],[Status],[Sub-Session Name] FROM [{0}]", arg0: "data_RawAttendanceLog$");
                OleDbCommand Ecom = new OleDbCommand(Query, Econ);
                Econ.Open();

                //Create one dataset and fill this data set with this selected items, using oledbdataadpter
                DataSet ds = new DataSet();
                OleDbDataAdapter oda = new OleDbDataAdapter(Query, Econ);
                Econ.Close();
                oda.Fill(ds);
                DataTable Exceldt = ds.Tables[0];

                for (int i = Exceldt.Rows.Count - 1; i >= 0; i--)
                {
                    if (Exceldt.Rows[i]["Student ID"] == DBNull.Value)
                    {
                        Exceldt.Rows[i].Delete();
                    }
                }
                Exceldt.AcceptChanges();

                //creating object of SqlBulkCopy     
                string csDestination = "server = " + GetValueFromFile("server") + "; database = " + GetValueFromFile("database") + "; Trusted_Connection=True"; // User ID = *redacted*; Password = ; Trusted_Connection=True";

                using (SqlConnection con = new SqlConnection(csDestination))
                {

                    SqlBulkCopy objbulk = new SqlBulkCopy(con);
                    //assigning Destination table name     
                    objbulk.DestinationTableName =  GetValueFromFile("DestinationTableName");
                    //Mapping Table column   

                    objbulk.ColumnMappings.Add("[Student ID]", "StudentID");
                    objbulk.ColumnMappings.Add("[Activity]", "Activity");
                    objbulk.ColumnMappings.Add("[Session Name]", "SessionName");
                    objbulk.ColumnMappings.Add("[Date]", "SessionDate");
                    objbulk.ColumnMappings.Add("[Time]", "SessionTime");
                    objbulk.ColumnMappings.Add("[Status]", "SessionStatus");
                    objbulk.ColumnMappings.Add("[Sub-Session Name]", "SubSessionName");

                    //inserting Datatable Records to DataBase   
                    SqlConnection sqlConnection = new SqlConnection();

                    con.Open();
                    objbulk.WriteToServer(Exceldt);

                    Console.WriteLine("Upload successful.");
                    Log.LogWrite("Upload successful.");
                    Econ.Close();
                    Log.LogWrite("Moving " + filePath + " to " + destinationPath);
                    File.Move(filePath, destinationPath);

                }

            }
            catch (Exception ex)
            {
                if (ex.Message.Contains("network-related"))
                {
                    Console.WriteLine("Cancelled. No Network access.");
                    return;
                }
                else
                {
                    Console.WriteLine(string.Format("Upload has not been imported due to: {0}", ex.Message));
                    Log.LogWrite(string.Format("Upload has not been imported due to: {0}", ex.Message));
                    Console.WriteLine("Press any key to close...");
                    ShowWindow(GetConsoleWindow(), SW_SHOW);
                    Console.ReadKey();
                }

            }

        }

    }

Let me know if you need any more information.
Thanks so much.

 

[jmcilhinney]

The Excel file is clearly still open somewhere when you call Move. At a glance, I can't see specifically where that would be in your code, although your code is somewhat more convoluted than it needs to be. ADO.NET does use connection pooling but I'm not sure whether that is the case for Excel files. In the case of "proper" databases, the actual database connection is at a lower level and remains open for some time after you close your ADO.NET connection, to speed up access in case it needs to be used again. I'm not sure whether that happens with Excel files but I would have thought not. You can turn off connection pooling in a connection string in some cases but it wouldn't be supported if pooling itself is not supported.

As I was writing this, it occurred to me that I was assuming the issue was with the source file but perhaps it is the destination file that is the problem. You don't have another file with the same name in the destination folder already open, do you?

 

[NoUserHere]

There is nothing wrong with how your code moves the file. Problem is painfully obvious and tells you in the error. What do you think this means :
The process cannot access the file because it is being used by another process. ?

For any code that opens the file for any reason, must be forced to release the file when its finished reading or opening it. Best way to do this is to use using blocks since they are self disposing of the resources they use.

What is the filePath being passed in? And why do you have that path at the end of your constr?

 

[jmcilhinney]

And why do you have that path at the end of your constr?

It isn't. It's an argument to String.Format.

 

[NoUserHere]

I didn't see the string format. Long day on the road John.

I didn't see the path was being fed to the datasource. Half asleep here

 

[jmcilhinney]

Half asleep here

Based on the time here is Aust, I think that you probably ought to be fully asleep.

 

[NoUserHere]

you probably ought to be fully asleep.

I should be. The joys of being self employed and working for a international tech company.

Shouldn't you get a different error if the destination file already exists? I'll check it out before bed, and post back.

 

[jmcilhinney]

Shouldn't you get a different error if the destination file already exists? I'll check it out before bed, and post back.

Not sure. If an existing file should be overwritten then possibly not.

 

[Skydiver]

Another thing to look at is your choice of Antivirus. Some AV software keeps files open while it performs it's scans. To make matters worse, some of them do DLL injection into applications (including your own app), so they are running as that process.

Use ProcMon from SysInternals to see who has file handles open on the file if disabling AV (temporarily) still results in the same error.

 

[NoUserHere]

Right.

So if the file already exists in the destination, and you try moving the file you will get : System.IO.IOException: 'Cannot create a file when that file already exists.
If the file is open in your application by way of not disposing of any used resources where you've opened or read the file, you will get: System.IO.IOException: The process cannot access the file because it is being used by another process.

See this ref for using blocks or the one in my signature : OleDbCommand Class (System.Data.OleDb) It should eradicate the problem. Wrap as much of your code in using blocks unless you want to handle all your disposing manually.

@Skydiver wouldn't what you said re the AV be the same as opening a file manually? I can open files manually (external not with code) and still be able to move them without exception.

 

[jmcilhinney]

I can open files manually (external not with code) and still be able to move them without exception.

It depends on the app and how it handles the file. Some apps open a file, read its contents and then close the file. I believe that Notepad works that way. Office apps, on the other hand, open a file and keep it open until you explicitly close it. I have found that i can't read Office files using ADO.NET if an Office app has them open.

 

[Skydiver]

Jim beat me to it.

 

[NoUserHere]

I assume they read the contents into memory, so they can dispose of the actual resources used by opening the file. Smart, and upon closing applications such as Notepad++, I assume they do it as you mentioned. Upon closing the application, the files contents are retained in the application regardless if you save or not. I guess open non saved files are wrote to a internal database when the program exits. Interesting some apps will give me an error.

Bed time for me, see you guys tomorrow.

Edit : Some of which I was playing with to replicate the error. Notice how if the file is openread, it will throw a woobler with the exception :

        private void Form1_Load(object sender, EventArgs e)
        {
            File.OpenRead(Path.Combine(Application.StartupPath, "ddoc.txt"));
            DoFile(Path.Combine(Application.StartupPath, "ddoc.txt"), Path.Combine(Application.StartupPath, "ddoc2.txt"));
        }

        private void DoFile(string In, string Out)
        {
            File.Move(In, Out);
        }

 

[kriscs1]

Thank you for responding.

The strange thing is this same program worked for over a year just fine. I think it stopped working since I uninstalled and reinstalled Microsoft Office, so maybe there was a change there.
The file definitely doesn't already exist and the only thing opening it is my program. I have tried changing the directories to no avail.

I am unable to disable the AV as it is locked by my company, however it is the same AV that was installed last year.

Are saying if I somehow try to wrap more of the function in a using block that should fix the issue? I tried it like this (SQL struff removed) but it still has the same issue:

 using (OleDbConnection Econ = new OleDbConnection(constr))
                {
                string Query = string.Format("Select [Student ID],[Activity],[Session Name],[Date],[Time],[Status],[Sub-Session Name] FROM [{0}]", arg0: "data_RawAttendanceLog$");
                    OleDbCommand Ecom = new OleDbCommand(Query, Econ);
               
                    Econ.Open();

                    //Create one dataset and fill this data set with this selected items, using oledbdataadpter
                    DataSet ds = new DataSet();
                    OleDbDataAdapter oda = new OleDbDataAdapter(Query, Econ);
                    Econ.Close();
                    oda.Fill(ds);
                    DataTable Exceldt = ds.Tables[0];

                    for (int i = Exceldt.Rows.Count - 1; i >= 0; i--)
                    {
                        if (Exceldt.Rows[i]["Student ID"] == DBNull.Value)
                        {
                            Exceldt.Rows[i].Delete();
                        }
                    }
                    Exceldt.AcceptChanges();
                }
             
                    File.Move(filePath, destinationPath);

I assume they read the contents into memory, so they can dispose of the actual resources used by opening the file. Smart, and upon closing applications such as Notepad++, I assume they do it as you mentioned. Upon closing the application, the files contents are retained in the application regardless if you save or not. I guess open non saved files are wrote to a internal database when the program exits. Interesting some apps will give me an error.

Bed time for me, see you guys tomorrow.

Edit : Some of which I was playing with to replicate the error. Notice how if the file is openread, it will throw a woobler with the exception :

        private void Form1_Load(object sender, EventArgs e)
        {
            File.OpenRead(Path.Combine(Application.StartupPath, "ddoc.txt"));
            DoFile(Path.Combine(Application.StartupPath, "ddoc.txt"), Path.Combine(Application.StartupPath, "ddoc2.txt"));
        }

        private void DoFile(string In, string Out)
        {
            File.Move(In, Out);
        }

 

[Skydiver]

Sorry, Process Explorer, not ProcMon. (My team uses ProcMon extensively to troubleshoot or monitoring what applications are trying to open or read). If you are just interested in open file handles, use Process Explorer or Handle. All three are available on SysInternals.com.