如何分配一个字符串变量的数据集?

Question

我从文件中读取行，对第一行进行检查，然后我必须将文件的下一行写入名为" testtable "的表中;用一个在DataSet上工作的方法。它告诉我不能在DataSet中插入字符串类型。

Dim myStream As Stream = Nothing
Dim openFileDialog1 As New OpenFileDialog()
openFileDialog1.InitialDirectory = "Z:FitalogItaliaKMasterSPEKM"   'ATTENZIONE CAMBIARE IN "C:"
openFileDialog1.Filter = "txt files (*.txt)|*.txt|All files (*.*)|*.*"
openFileDialog1.FilterIndex = 2
openFileDialog1.RestoreDirectory = True

If openFileDialog1.ShowDialog() = System.Windows.Forms.DialogResult.OK Then
Try
myStream = openFileDialog1.OpenFile()
If (myStream IsNot Nothing) Then
Dim objReader As New StreamReader(openFileDialog1.FileName)
Dim ControlLine As String = ""
Dim sLine As String = ""
Dim arrText As New ArrayList()
ControlLine = objReader.ReadLine
If (ControlLine.Contains("H06") And ControlLine.Contains("SPEKM")) Then
sLine = objReader.ReadLine
Dim indice As Integer = 0
Do
If Not sLine Is Nothing Then
arrText.Add(sLine)
DB_SQL.SetDBfromDataset("INSERT INTO TestTable (riga) VALUES (" + arrText.Item(indice) + ");", "TestTable")
indice = +1
End If
Loop Until objReader.ReadLine.Count - 1
End If
objReader.Close()

End If
Catch Ex As Exception
MessageBox.Show(Ex.Message)
Finally
' Check this again, since we need to make sure we didn't throw an exception on open.
If (myStream IsNot Nothing) Then
myStream.Close()
End If
End Try
End If

我添加了在数据库上加载数据的函数。问题是数组列表，因为我需要传递一个数据集

Public Function SetDBfromDataset(ByVal ds As Data.DataSet, ByVal TN As String) As Integer
DBadapter.InsertCommand = New SqlCommand
TmpSQLstring = "INSERT INTO " & TN
' ottengo la lista dei campi
ListFields = " ("

For Each myCol In ds.Tables(0).Columns
If (Not IsPrimaryCol(myCol, PKcols)) And (NormalizeDataTypeToDBtype(myCol) <> SqlDbType.Timestamp) Then
ListFields = ListFields & Trim(myCol.ColumnName)
ListFields = ListFields & ","
End If
Next
ListFields = Mid$(ListFields, 1, Len(ListFields) - 1) & ")"
'ottengo la lista dei parametri
ListParam = " VALUES ("
For Each myCol In ds.Tables(0).Columns
If (Not IsPrimaryCol(myCol, PKcols)) And (NormalizeDataTypeToDBtype(myCol) <> SqlDbType.Timestamp) Then
ListParam = ListParam & "@" & Trim(myCol.ColumnName)
ListParam = ListParam & ","
DBadapter.InsertCommand.Parameters.Add(New SqlParameter("@" & Trim(myCol.ColumnName), NormalizeDataTypeToDBtype(myCol)))
DBadapter.InsertCommand.Parameters("@" & Trim(myCol.ColumnName)).SourceColumn = Trim(myCol.ColumnName)
End If
Next
ListParam = Mid$(ListParam, 1, Len(ListParam) - 1) & ")"
DBadapter.InsertCommand.CommandText = TmpSQLstring & ListFields & ListParam
DBadapter.InsertCommand.Connection = CType(DBadapter.SelectCommand.Connection, SqlConnection)

End Function

Answer 1

Stream需要通过调用Dispose或放置在Using块来处理。这是一个文本文件，所以你甚至不需要流。

ArrayList大约是为了向后兼容,但你不应该用于新代码。参考https://learn.microsoft.com/en-us/dotnet/api/system.collections.arraylist?view=net-5.0#remarksList(Of T)

我已经把代码分成3个方法。您在一个方法中执行了太多独立的任务。我使用System.IO.File类来读取文件。ReadAllLines返回文件中的行数组。然后使用一个简单的For Each循环来标识要添加到数据库中的行。我用List(Of String)来收集线条。然后将列表传递给InsertText中的数据库代码。

在循环中只改变参数的值

Private Sub OPCode()
Dim FilePath = GetFilePath()
If FilePath Is Nothing Then
MessageBox.Show("No file selected.")
End If
Dim lst As New List(Of String)
Dim lines = File.ReadAllLines(FilePath)
For Each line In lines
If line.Contains("H06") AndAlso line.Contains("SPEKM")) Then
lst.Add(line)
End If
Next
InsertText(lst)
End Sub
Private Function GetFilePath() As String
Dim openFileDialog1 As New OpenFileDialog()
openFileDialog1.InitialDirectory = "Z:FitalogItaliaKMasterSPEKM"   'ATTENZIONE CAMBIARE IN "C:"
openFileDialog1.Filter = "txt files (*.txt)|*.txt|All files (*.*)|*.*"
openFileDialog1.FilterIndex = 2
openFileDialog1.RestoreDirectory = True
If openFileDialog1.ShowDialog() = System.Windows.Forms.DialogResult.OK Then
Return openFileDialog1.FileName
Else
Return Nothing
End If
End Function
Private Sub InsertText(lst As List(Of String))
Using cn As New SqlConnection("Your connection string"),
cmd As New SqlCommand("INSERT INTO TestTable (riga) VALUES (@riga);", cn)
cmd.Parameters.Add("@riga", SqlDbType.NVarChar)
cn.Open()
For Each line In lst
cmd.Parameters("@riga").Value = line
cmd.ExecuteNonQuery()
Next
End Using 'closes the connection and disposes the command and the connection
End Sub