Vb.net Exception help required urgent!

I have a code which process each file in a folder and send the data in the file to SQL Database and then deletes the file. But often i get this Exception "The process cannot access the file 'file.csv' because it is being used by another process." Please can anyone point me out in the right direction, will really appreciate that.

The code is shown below :

Dim dirinfo As DirectoryInfo
Dim allFiles() As FileInfo

dirinfo = New DirectoryInfo("E:\SQLUPDATE\CAC")
allFiles = dirinfo.GetFiles("*.csv")
If allFiles.Length <> 0 Then
Try
For Each fl As FileInfo In allFiles
'MsgBox(fl.FullName.ToString())
Dim con As SqlConnection = New SqlConnection(SQL_con2)
Dim sr As StreamReader = New StreamReader(fl.FullName)
Dim line As String = sr.ReadLine
Dim value() As String = line.Split(Microsoft.VisualBasic.ChrW(44))
Dim dt As DataTable = New DataTable
Dim row As DataRow
For Each dc As String In value
dt.Columns.Add(New DataColumn(dc))
Next

While Not sr.EndOfStream
value = sr.ReadLine.Split(Microsoft.VisualBasic.ChrW(44))
If (value.Length = dt.Columns.Count) Then
row = dt.NewRow
row.ItemArray = value
dt.Rows.Add(row)
End If

End While
Dim bc As SqlBulkCopy = New SqlBulkCopy(con.ConnectionString, SqlBulkCopyOptions.TableLock)
bc.DestinationTableName = "[DB].[dbo].[CAC_LData]"
bc.BatchSize = dt.Rows.Count
con.Open()
bc.WriteToServer(dt)
bc.Close()
con.Close()
sr.Close()
System.IO.File.Delete(fl.FullName)
sr.Dispose()
Next
Catch ex As Exception
MsgBox(ex.Message)
End Try
End If

What line of code generates the error...

its not all the time...its intermittent but it does it when there are several files in the folder. i dont know whether i have followed the right sequence for closing the streamwriter and deleteing the file.

Yeah...that really answered my question :rolleyes:

I'm guessing it is the delete. I removed the DB code and put in a using to eliminate the dispose. My guess is something really does have the file. When does it occur? When you are running tests over and over again? When users are running it? The basic loop seems good to me.

Code:

---

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim dirinfo As DirectoryInfo
        Dim allFiles() As FileInfo

        dirinfo = New DirectoryInfo("C:\CAC")
        allFiles = dirinfo.GetFiles("*.xlsx")
        If allFiles.Length <> 0 Then
            Try
                For Each fl As FileInfo In allFiles
                    Using sr As StreamReader = New StreamReader(fl.FullName)
                        Dim line As String = sr.ReadLine
                        Dim value() As String = line.Split(Microsoft.VisualBasic.ChrW(44))
                        sr.Close()
                        System.IO.File.Delete(fl.FullName)
                    End Using
                Next
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try
        End If
    End Sub

---

basically these files are being sent to this folder from FTP client. So i am thinking if FTP client has not finished transfering the file and it starts to process the file then this problem occurs. What do you suggest how can i kill this problem. Yes this loop goes on and on until all files in a folder are processed.

It amazes me you are just mentioning that now :o

One thing you can do is before you start the DB copy process check is to make sure you have exclusive control of the folder. I can find a link if you are not sure what to use. But, if the files are coming in a dynamic way where FTP can grab the folder anytime, I think you will need to copy it off first. A lot depends on how the FTP process is set up. For example we copy the folder off the FTP server and process that. The software we have moves the original folder off the server to a back up. If you want to elaborate about your process maybe someone can offer suggestions.

Thanks Mate for you reply...Sorry for not mentioning that before as it just came in my mind that FTP might be engaging the folder. Yes, the FTP can grab the folder anytime as you said its set in dynamic mode. Can you send me the link so i can look through what i need to do to copy the folder.Also i am thinking we will still have same issue while copying the folder as it will still be dynamically engaged by the FTP server?

I think you are always going to have access issues as long as the file can come in at any time and FTP has exclusive locks. I may not be able to help you. We have software where we set up an "FTP job" that is constantly looking for files. At set times they are copied off. Then we process the copies. Probably because of the issues you are having. There are plenty of links to check if you can get exclusive control, I'll post one shortly, but if FTP grabs it during the copy the processes will fail unless the shares are compatible. Deleting then will always be an issue. Sit tight and see if someone with more experience than I chimes in.

Yes you are absolutely Right. By the way thanks a lot for your help. Lolz...yes i am sitting very tight until i kill this issue or hopefully someone will prove himself as a star :)

Does the FTP lock the folder or just the file? I would assume just the file.
And is ftp creating a new file each time? Meaning that a locked file is a file that's still in the process of being created? I would assume so.
And finally, if you run this process while a file is being created, is it ok to defer the import and delete of that file to the DB until the next time the process is run? Again, I would assume so.

I've made three fairly large assumptions there but if they're all correct the solution is actually quite easy I think:-
1. Open the file in exclusive mode (use FileShare.None) - that will force the error to occur at the point of opening rather than deleting.
2. Put a try catch block inside the loop and check for that specific exception. If you get that exception, simply move onto the next file in the loop.

That way you will only progress with the import if the file is not being locked by the FTP process and at that point you also know you're going to be able to delete it. If you fail to get the lock it's because the FTP process is creating the file in which case just defer importing it until the next time this runs.

yes, your three big assumptions are absolutely correct. I think you have hit the spot with that one. Its a really good solution but i am confused about how am i going to implement this in my code as i am using the streamreader to read the array of files.

Quote:

---

Originally Posted by bmf073

yes, your three big assumptions are absolutely correct. I think you have hit the spot with that one. Its a really good solution but i am confused about how am i going to implement this in my code as i am using the streamreader to read the array of files.

---

FunkyDextor posted a viable solution but I personally shy away for handling errors like they are "technically" OK. In this case if you can't get exclusive control, assume FTP has it, you will pick it up later, and just move on. That seems like asking for trouble. There must be some control surrounding the FTP files coming in and giving you a chance to get exclusive control. I would try and go down that path first. Find out how to set up a maintenance window and process withing that if possible. On the flip side I work in bi shops that have tools and processes like that set up. I don't know your situation.

Quote:

---

That seems like asking for trouble

---

Good point. There are certainly risks and you should be aware of them. The most obvious one is that you can't truly distinguish between a file that's locked by the FTP process and one that's locked by, for example, a user who opened it, didn't bother to close it and just left on a three week vacation. With that in mind my solution should be viewed as exactly what it is: a way of processing all free files in a directory and ignoring those which are not free. If that doesn't suffice then it's not the right solution.

Of course, you could also add logging, warnings etc to help identify problem files but you'll only be dealing with the problems you've thought of, not the ones you haven't

If you still think it's the right solution then here's how to open a file with an exclusive lock

I don't really want to go that route then because it can cause more problems for me.I am also thinking about instead of deleting that file may be if we can move it to different folder and delete it afterwards? But i don't know whether we will have the same issue of file being engaged?

Quote:

---

i don't know whether we will have the same issue of file being engaged?

---

You almost certainly will.

Personally I wouldn't bother moving the file if you're going to delete it afterwards anyway, it won't give you any benefit. There might be some mileage in moving it if you plan on keeping it for a while. Having a "quarantine" area like this is quite common because it allows you to recover (probably with some manual effort) if something goes wrong.



Basically you've got two ways of going:-
You can try to pre-empt and avoid the situation where a file is locked. Tyson's suggestion to use a maintenance window is an example of this. In that world, any time you encounter a locked file you know there's a problem, you can send out emails to adminstrators, set off warning sirens etc and get someone to deal with it.
Or you can accept that there are valid reason for a file to be locked and accept them. My suggestion of deferring the import of locked files is an example of this. A slightly more sophisticated example might be to reattempt the import after an apropriate time delay and set off the sirens if it's still locked.

Either way, though, you have to accept that files are going to be locked and you need to work with that.

Hi guys,

How about if i put the thread to sleep for 1 sec lets say after getting all file names in the array and after 1 sec delay i start the while loop. By that time the FTP server should have finished writing to the file. Any suggestions about this?



Dim dirinfo As DirectoryInfo
Dim allFiles() As FileInfo

dirinfo = New DirectoryInfo("E:\SQLUPDATE\CAC")
allFiles = dirinfo.GetFiles("*.csv")

Thread.Sleep(1000)

If allFiles.Length <> 0 Then
Try
For Each fl As FileInfo In allFiles
'MsgBox(fl.FullName.ToString())
Dim con As SqlConnection = New SqlConnection(SQL_con2)
Dim sr As StreamReader = New StreamReader(fl.FullName)
Dim line As String = sr.ReadLine
Dim value() As String = line.Split(Microsoft.VisualBasic.ChrW(44))
Dim dt As DataTable = New DataTable
Dim row As DataRow
For Each dc As String In value
dt.Columns.Add(New DataColumn(dc))
Next

While Not sr.EndOfStream
value = sr.ReadLine.Split(Microsoft.VisualBasic.ChrW(44))
If (value.Length = dt.Columns.Count) Then
row = dt.NewRow
row.ItemArray = value
dt.Rows.Add(row)
End If

End While
Dim bc As SqlBulkCopy = New SqlBulkCopy(con.ConnectionString, SqlBulkCopyOptions.TableLock)
bc.DestinationTableName = "[DB].[dbo].[CAC_LData]"
bc.BatchSize = dt.Rows.Count
con.Open()
bc.WriteToServer(dt)
bc.Close()
con.Close()
sr.Close()
System.IO.File.Delete(fl.FullName)
sr.Dispose()
Next
Catch ex As Exception
MsgBox(ex.Message)
End Try
End If

The operative words are "server should have" :o

Can you really count on that? I don't know what kind of environment you are in but I prefer copying off the data regardless if it is going to be deleted or not. As was mentioned you never know when may need to look at the data, reprocess it, prove it arrived, and all that kind of jazz.

Here is briefly how one I set up worked:

1. The files arrived via FTP and I never knew how many would be coming or when.
2. I set up a job that ran once a day. The FTP software copied the data from the FTP server to a production share. There were no sharing issues.
3. I would process the file and write a “job started” and “job ended” log to a DB table.
4. If any error were encountered the program sent emails based on table entries to support personnel and logged what happened and where.
5. About twenty minutes after the job was scheduled another job ran and read the log file. If it didn’t detect via the log the job ran successfully it also sent out emails. That was in case something happened like a severe error or the like.
6. The same job in step 5 read the backup folders and deleted anything past a certain that was stored in a table.

Seems like a lot of work but up front work coding and notifying for errors and the like will be much less painful than coding for expected errors, hoping the timing is right, and crossing your fingers. Unless you want tied to that process you should make it hands off and proactive. Our environments might be completely different though.

somewhere around post #10 I stopped reading and decided to offer a possible solution that I've used in the past when dealing files received via FTP.
We use a FileSystemWatcher to watch the inbound folder on our server. It has a FileCreated event that will let you know when a file is created. It does not let you know when it's done writing to the file though. So what we would do is take the full file name (including path) and put it into a List(Of String) ... then we start a timer (or re-start it if it's already running). In the Tick event of the timer (through experimentation in this case we found that the amount of time it takes to write a file of the largest expected size was about 37 seconds, so we used a timer set for 45 seconds) we would then loop through the List(Of String) and process each file. When done, we'd then move the file off to an archive folder based on the client.

I've used that technique a couple times since, it seems to work reasonable well.


-tg