Help required with File Handle while using streamreader

Hi Guys,

I have got a problem. I the below stated quote.. I often keep getting the exception "The Process couldn't access the file because it is being used by another process." when it tries to delete the file.

please can anyone tell me where i am going wrong. The program may works OK for weeks or it may throws up this exception twice a week. Suggestions please!

Code:

---

Private Sub UploaddatatoCAC_CORE()

        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
                    If String.IsNullOrEmpty(line) Then
                        sr.Close()
                        System.IO.File.Delete(fl.FullName)
                        sr.Dispose()
                        Continue For
                    End If
                    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 = "[ANDONDB].[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
    End Sub

---

Firstly, have you tried moving the stream reader dispose call to the line before trying to delete the file?

Secondly, how do you know it's not in use? Something obviously has to write to the file, so perhaps you are reading from a file, and deleting it, before the writer has finished with it?

Yes this is what I should really try putting dispose before delete although I don't know how to check for file in use..can you please help me with that?

There are a couple of ways, but it doesn't preclude the 'file in use' syndrome.

Try renaming the file before reading from it.
Try to get an exclusive lock on the file.

However, even doing this does not guarantee that you can successfully read and delete the file. You best bet is the latter to ensure the file is exclusively yours before reading. Something like:

Code:

---

                Using fs As New IO.FileStream("C:\MyFolder\MyFile.csv", IO.FileMode.Open, IO.FileAccess.ReadWrite, IO.FileShare.None)
                        Using sr As New IO.StreamReader(fs)
                                '
                        End Using
                End Using

---

Then delete as normal. You will still need to try/catch because every IO operation has a possibility of failing, and you need to handle it as appropriate.

Thread.Sleep(1000) why just why?

we had to put this delay because we don't have any handshaking signal with the devices writing the file and we need to make sure after the file is created in the folder to gives it 1 sec delay to release the handle of file. Keep in mind they are the slow devices.

Quote:

---

Originally Posted by bmf073

we had to put this delay because we don't have any handshaking signal with the devices writing the file and we need to make sure after the file is created in the folder to gives it 1 sec delay to release the handle of file. Keep in mind they are the slow devices.

---

This should be the clue why you are getting the 'file in use' error.

Basically, it's an expected error and you need to handle it correctly. As long as you are on a separate thread, the sleep is OK. But you probably want to handle the specific exception for the file in use, and set it (the file) aside to try again later, assuming the 'file in use' indicates the writer has not completed its task.

thanks guys for your help anyway.. how can i tell when file in use exception occur ignore the file this time and retry it in next scan.

Quote:

---

Originally Posted by bmf073

thanks guys for your help anyway.. how can i tell when file in use exception occur ignore the file this time and retry it in next scan.

---

Look at the actual exception thrown; instead of just handling a general Exception, catch this specific exception. I'd also try and separate out the file writing and the database work, and not catch everything in a single exception handler. There's a whole host of errors that can occur with database connectivity, also. As it stands, you will throw up a message box for pretty much everything that can go wrong, but it doesn't help you.

This is like saying "there's no forks in the silverware drawer so I'm not going to eat" if you just catch a general exception.