(VB.NET)System.IO.IOException:进程无法访问该文件

Question

我(通过SQL(将大约60万行提取到DataGridView中，并按日期将它们全部拆分为文件。

我尝试了几种方法来做到这一点，虽然我找到了一些方法来避免它崩溃，并安全可靠地完成它，但它非常慢。

然而，我发现最快的方法是只从"日期"列中删除日期，例如将2020/06/01 13:50:43改为0601，并将其用作文件名，然后将文本附加到该文件中。(与在临时对象中创建所有行并一次写入所有行等相反(。

它很快，一切都很好，但是写了大约30？在大约150个文件中，它崩溃了。它非常随机，我试着运行了几次。它可能在几秒钟或半分钟后坠毁。因此，我相信这是由于它在尝试如此快速地写这么多次时绊倒了自己。

我已经尝试过多次更改写入方法，确保使用Using块，以及尝试文件共享。

这是我的代码：

Private Sub btnExec_Click(sender As Object, e As EventArgs) Handles btnExec.Click
Static start_time As DateTime
Static stop_time As DateTime
Dim elapsed_time As TimeSpan
start_time = Now
If IsNumeric(TextBox1.Text) Then columnNumber = CInt(TextBox1.Text) - 1
Dim dgw = Form1.DataGridView1
Dim curcell As String = ""
Dim ToWrite As String = ""
Dim Written As Integer = 0
Try
ProgressBar1.Maximum = dgw.Rows.Count
For i = 0 To dgw.Rows.Count - 1
'For each row
If IsDBNull(dgw.Rows(i).Cells(columnNumber).Value.ToString()) Then
curcell = "" 'if null replace with a blank value
Else
curcell = CStr(dgw.Rows(i).Cells(columnNumber).Value)
End If
'RB FORMATTING
If RadioButton1.Checked = True Then '2020/01/01 00:00:00
'2020/06/16 19:38:33
curcell = curcell.Remove(0, 5)
curcell = curcell.Remove(curcell.Length - 9, 9)
curcell = curcell.Replace("/", "")
'0616
End If
'Now we have 0616 in a string which is the date of the current row
Dim currow As String = ""
For Each c As DataGridViewCell In dgw.Rows(i).Cells
If IsDBNull(c.Value) Then
currow += Chr(34) + "" + Chr(34) + ","
Else
currow += Chr(34) + CStr(c.Value) + Chr(34) + ","
End If
Next
currow = currow.Remove(currow.Length - 1, 1) 'Remove last comma
'Now we have the full row to print as well as the date in a string
Dim FILENAME As String = TextBox2.Text + "" + curcell + ".csv"
' Using fs As FileStream = File.OpenWrite(FILENAME)
'  AddText(fs, currow + vbCrLf)
' End Using
Dim fs As FileStream = New FileStream(FILENAME, FileMode.OpenOrCreate, FileAccess.Write)
Dim w As StreamWriter = New StreamWriter(fs)
w.BaseStream.Seek(0, SeekOrigin.[End])
w.Write(currow + vbCrLf)
w.Close()
'Using writer As StreamWriter = File.AppendText(FILENAME)
'writer.WriteLine(currow)
' End Using

'My.Computer.FileSystem.WriteAllText(FILENAME, currow + vbCrLf, 1)    'Gets used by its own process error some times


Written += 1
Next 'Next row
stop_time = Now
elapsed_time = stop_time.Subtract(start_time)
Log("Wrote " + CStr(Written) + " lines in " + elapsed_time.TotalSeconds.ToString("0.00" & " seconds."))
Catch ex As Exception
Log(ex.ToString())
End Try

End Sub

错误如下：

System.IO.IOException: The process cannot access the file 'C:UsersusernameDesktopTEST2416.csv' because it is being used by another process.
at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)
at System.IO.FileStream.Init(String path, FileMode mode, FileAccess access, Int32 rights, Boolean useRights, FileShare share, Int32 bufferSize, FileOptions options, SECURITY_ATTRIBUTES secAttrs, String msgPath, Boolean bFromProxy, Boolean useLongPath, Boolean checkHost)
at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access)
at TicketsSQL.frmSplitFiles.btnExec_Click(Object sender, EventArgs e) in C:UsersusernamesourcereposTicketsSQLTicketsSQLfrmSplitFiles.vb:line 63

我注释掉了另外两种方式，而不是我在代码中尝试的当前方式。我尝试了以下三种方法：

Dim fs As FileStream = New FileStream(FILENAME, FileMode.OpenOrCreate, FileAccess.Write)
Dim w As StreamWriter = New StreamWriter(fs)
w.BaseStream.Seek(0, SeekOrigin.[End])
w.Write(currow + vbCrLf)
w.Close()
Using writer As StreamWriter = File.AppendText(FILENAME)
writer.WriteLine(currow)
End Using
My.Computer.FileSystem.WriteAllText(FILENAME, currow + vbCrLf, 1) 

似乎无论我以何种方式阻止它使用已经打开的文件，甚至允许它这样做……它都会绊倒。

有人有解决这个问题的办法吗？

Answer 1

试试这个，让我们知道发生了什么错误

Static start_time As DateTime
Static stop_time As DateTime
Dim elapsed_time As TimeSpan
start_time = Now
If IsNumeric(TextBox1.Text) Then columnNumber = CInt(TextBox1.Text) - 1
Dim dgw = Form1.DataGridView1
Dim curcell As String = ""
Dim ToWrite As String = ""
Dim Written As Integer = 0
Try
ProgressBar1.Maximum = dgw.Rows.Count
For i = 0 To dgw.Rows.Count - 1
'For each row
If IsDBNull(dgw.Rows(i).Cells(columnNumber).Value.ToString()) Then
curcell = "" 'if null replace with a blank value
Else
curcell = CStr(dgw.Rows(i).Cells(columnNumber).Value)
End If
'RB FORMATTING
If RadioButton1.Checked = True Then '2020/01/01 00:00:00
'2020/06/16 19:38:33
curcell = curcell.Remove(0, 5)
curcell = curcell.Remove(curcell.Length - 9, 9)
curcell = curcell.Replace("/", "")
'0616
End If
'Now we have 0616 in a string which is the date of the current row
Dim currow As String = ""
For Each c As DataGridViewCell In dgw.Rows(i).Cells
If IsDBNull(c.Value) Then
currow += Chr(34) + "" + Chr(34) + ","
Else
currow += Chr(34) + CStr(c.Value) + Chr(34) + ","
End If
Next
currow = currow.Remove(currow.Length - 1, 1) 'Remove last comma
'>>>>>>>>>>>>>>>>>>>>>>>>>
'Now we have the full row to print as well as the date in a string
Dim FILENAME As String
FILENAME = IO.Path.Combine(TextBox2.Text, curcell)
FILENAME = IO.Path.ChangeExtension(FILENAME, ".csv")
Try
IO.File.WriteAllText(FILENAME, currow & ControlChars.CrLf)
Catch ex As Exception
Stop ' what is the error <<<<<<<<<<<<<<<<<<<<<<
End Try
'<<<<<<<<<<<<<<<<<<<<<<<<<
Written += 1
Next 'Next row
stop_time = Now
elapsed_time = stop_time.Subtract(start_time)
Log("Wrote " + CStr(Written) + " lines in " + elapsed_time.TotalSeconds.ToString("0.00" & " seconds."))
Catch ex As Exception
Log(ex.ToString())
End Try