如何将字符串变量赋值到DataSet中并插入TestTable数据表

核心问题梳理 #

• 方法入参不匹配：SetDBfromDataset第一个参数要求为DataSet类型，原代码传入拼接的SQL字符串，直接触发类型转换报错
• 读取行逻辑错误：循环终止条件中调用objReader.ReadLine会重复读取行，导致半数数据被跳过丢失，同时indice = +1的写法错误，无法实现索引自增
• 缺少DataSet构造逻辑：没有按照目标表TestTable的结构构造对应DataSet，方法无法识别待插入的数据

修正后完整实现代码 #

文件读取与DataSet构造逻辑 #

Dim myStream As Stream = Nothing
Dim openFileDialog1 As New OpenFileDialog()

openFileDialog1.InitialDirectory = "Z:\FitalogItalia\KMaster\SPEKM"   '后续可修改为"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 = ""
            
            ' 构造与TestTable结构匹配的DataSet
            Dim ds As New DataSet()
            Dim dt As New DataTable("TestTable")
            ' 添加与表字段对应的riga列
            dt.Columns.Add("riga", GetType(String))
            ds.Tables.Add(dt)
            
            ControlLine = objReader.ReadLine()
            ' 首行校验通过后处理剩余行
            If (ControlLine.Contains("H06") And ControlLine.Contains("SPEKM")) Then
                sLine = objReader.ReadLine()
                Do While sLine IsNot Nothing
                    ' 向DataTable中新增行
                    Dim newRow = dt.NewRow()
                    newRow("riga") = sLine
                    dt.Rows.Add(newRow)
                    ' 读取下一行
                    sLine = objReader.ReadLine()
                Loop
                
                ' 传入构造完成的DataSet调用插入方法
                DB_SQL.SetDBfromDataset(ds, "TestTable")
            End If
            objReader.Close()
        End If
    Catch Ex As Exception
        MessageBox.Show(Ex.Message)
    Finally
        If (myStream IsNot Nothing) Then
            myStream.Close()
        End If
    End Try
End If

SetDBfromDataset方法补充 #

你提供的方法代码缺少最终数据提交逻辑，需在方法末尾添加以下代码才能完成数据入库：

' 新增事务保证数据一致性
Using tran As SqlTransaction = DBadapter.InsertCommand.Connection.BeginTransaction()
    DBadapter.InsertCommand.Transaction = tran
    Try
        Dim affectRows As Integer = DBadapter.Update(ds, TN)
        tran.Commit()
        Return affectRows
    Catch ex As Exception
        tran.Rollback()
        Throw ex
    End Try
End Using

内容的提问来源于stack exchange，提问作者Simone