Results 1 to 9 of 9

Thread: Help required with File Handle while using streamreader

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Sep 2013
    Posts
    29

    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

  2. #2
    PowerPoster SJWhiteley's Avatar
    Join Date
    Feb 2009
    Location
    South of the Mason-Dixon Line
    Posts
    2,256

    Re: Help required with File Handle while using streamreader

    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?
    "Ok, my response to that is pending a Google search" - Bucky Katt.
    "There are two types of people in the world: Those who can extrapolate from incomplete data sets." - Unk.
    "Before you can 'think outside the box' you need to understand where the box is."

  3. #3

    Thread Starter
    Junior Member
    Join Date
    Sep 2013
    Posts
    29

    Re: Help required with File Handle while using streamreader

    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?

  4. #4
    PowerPoster SJWhiteley's Avatar
    Join Date
    Feb 2009
    Location
    South of the Mason-Dixon Line
    Posts
    2,256

    Re: Help required with File Handle while using streamreader

    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.
    "Ok, my response to that is pending a Google search" - Bucky Katt.
    "There are two types of people in the world: Those who can extrapolate from incomplete data sets." - Unk.
    "Before you can 'think outside the box' you need to understand where the box is."

  5. #5
    Bad man! ident's Avatar
    Join Date
    Mar 2009
    Location
    Cambridge
    Posts
    5,388

    Re: Help required with File Handle while using streamreader

    Thread.Sleep(1000) why just why?

  6. #6

    Thread Starter
    Junior Member
    Join Date
    Sep 2013
    Posts
    29

    Re: Help required with File Handle while using streamreader

    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.

  7. #7
    PowerPoster SJWhiteley's Avatar
    Join Date
    Feb 2009
    Location
    South of the Mason-Dixon Line
    Posts
    2,256

    Re: Help required with File Handle while using streamreader

    Quote Originally Posted by bmf073 View Post
    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.
    "Ok, my response to that is pending a Google search" - Bucky Katt.
    "There are two types of people in the world: Those who can extrapolate from incomplete data sets." - Unk.
    "Before you can 'think outside the box' you need to understand where the box is."

  8. #8

    Thread Starter
    Junior Member
    Join Date
    Sep 2013
    Posts
    29

    Re: Help required with File Handle while using streamreader

    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.

  9. #9
    PowerPoster SJWhiteley's Avatar
    Join Date
    Feb 2009
    Location
    South of the Mason-Dixon Line
    Posts
    2,256

    Re: Help required with File Handle while using streamreader

    Quote Originally Posted by bmf073 View Post
    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.
    "Ok, my response to that is pending a Google search" - Bucky Katt.
    "There are two types of people in the world: Those who can extrapolate from incomplete data sets." - Unk.
    "Before you can 'think outside the box' you need to understand where the box is."

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width