-
Apr 27th, 2015, 09:34 AM
#1
Thread Starter
Junior Member
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
-
Apr 27th, 2015, 12:25 PM
#2
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."
-
Apr 27th, 2015, 12:51 PM
#3
Thread Starter
Junior Member
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?
-
Apr 27th, 2015, 02:39 PM
#4
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."
-
Apr 27th, 2015, 04:20 PM
#5
Re: Help required with File Handle while using streamreader
Thread.Sleep(1000) why just why?
-
Apr 28th, 2015, 03:12 AM
#6
Thread Starter
Junior Member
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.
-
Apr 28th, 2015, 06:30 AM
#7
Re: Help required with File Handle while using streamreader
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.
"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."
-
Apr 28th, 2015, 06:42 AM
#8
Thread Starter
Junior Member
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.
-
Apr 28th, 2015, 06:49 AM
#9
Re: Help required with File Handle while using streamreader
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.
"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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|