VB.NET实现DataSet表数据插入SQLite表及代码转换求助

Got it, since you're a VB developer new to .NET and can't parse C# code, I've converted and expanded the solution to insert your DataSet data into the matching SQLite table. Here's a complete, ready-to-use VB.NET implementation:

First, make sure you have the System.Data.SQLite NuGet package installed in your project—this is required to interact with SQLite databases from .NET.

Complete VB.NET Code #

Imports System.Data.SQLite
Imports System.Data

Public Class DataSetToSQLiteImporter
    Public Sub InsertDataSetData(dataSet As DataSet, dbConnectionString As String, targetTableName As String)
        ' Validate input first
        If Not dataSet.Tables.Contains(targetTableName) OrElse dataSet.Tables(targetTableName).Rows.Count = 0 Then
            MessageBox.Show("No valid data found in the specified DataSet table!")
            Return
        End If

        Dim sourceTable As DataTable = dataSet.Tables(targetTableName)

        ' Use Using blocks to auto-dispose connections/commands (best practice)
        Using dbConn As New SQLiteConnection(dbConnectionString)
            dbConn.Open()

            ' Use a transaction to ensure all inserts succeed or fail together
            Using transaction As SQLiteTransaction = dbConn.BeginTransaction()
                Try
                    ' Build insert query dynamically based on table structure (no hardcoding columns!)
                    Dim columnList As String = String.Join(", ", sourceTable.Columns.Cast(Of DataColumn)().Select(Function(col) $"[{col.ColumnName}]"))
                    Dim paramPlaceholders As String = String.Join(", ", sourceTable.Columns.Cast(Of DataColumn)().Select(Function(col) $"@{col.ColumnName}"))
                    Dim insertQuery As String = $"INSERT INTO [{targetTableName}] ({columnList}) VALUES ({paramPlaceholders})"

                    ' Reuse the same command for all rows (faster than creating new ones each time)
                    Using insertCmd As New SQLiteCommand(insertQuery, dbConn, transaction)
                        ' Add parameters to the command
                        For Each col As DataColumn In sourceTable.Columns
                            insertCmd.Parameters.Add($"@{col.ColumnName}", MapDotNetTypeToSQLite(col.DataType))
                        Next

                        ' Loop through each row in the DataSet table
                        For Each row As DataRow In sourceTable.Rows
                            ' Skip deleted rows (adjust if you need to handle other row states)
                            If row.RowState = DataRowState.Deleted Then Continue For

                            ' Set parameter values from the current row
                            For Each col As DataColumn In sourceTable.Columns
                                insertCmd.Parameters($"@{col.ColumnName}").Value = If(row.IsNull(col), DBNull.Value, row(col))
                            Next

                            ' Execute the insert
                            insertCmd.ExecuteNonQuery()
                        Next
                    End Using

                    ' Commit the transaction if everything went smoothly
                    transaction.Commit()
                    MessageBox.Show("Data inserted into SQLite successfully!")
                Catch ex As Exception
                    ' Rollback if any error occurs
                    transaction.Rollback()
                    MessageBox.Show($"Error inserting data: {ex.Message}")
                    Throw ' Optional: rethrow if you want to handle the error elsewhere
                End Try
            End Using

            dbConn.Close()
        End Using
    End Sub

    ' Helper to map .NET data types to SQLite-compatible types
    Private Function MapDotNetTypeToSQLite(dotNetType As Type) As SQLiteType
        Select Case dotNetType.Name
            Case "String" : Return SQLiteType.Text
            Case "Int32", "Int64" : Return SQLiteType.Integer
            Case "Double", "Decimal" : Return SQLiteType.Real
            Case "DateTime" : Return SQLiteType.Text ' SQLite stores dates as strings/ints; adjust if you use epoch
            Case "Boolean" : Return SQLiteType.Integer
            Case "Byte[]" : Return SQLiteType.Blob
            Case Else : Return SQLiteType.Text ' Fallback for unknown types
        End Select
    End Function
End Class

How to Use This Code #

Call the importer method with your loaded DataSet, SQLite connection string, and target table name:

' Example usage
Dim myLoadedDataSet As DataSet = ' Your DataSet with Excel data here
Dim sqliteConnString As String = "Data Source=C:\YourDatabaseFile.db;Version=3;"
Dim importer As New DataSetToSQLiteImporter()
importer.InsertDataSetData(myLoadedDataSet, sqliteConnString, "YourSQLiteTableName")

Key Details #

• Transaction Safety: Ensures no partial data is inserted if something goes wrong.
• Dynamic Query: Automatically adapts to your table structure—no need to hardcode column names.
• Parameterized Queries: Prevents SQL injection and handles data type conversions correctly.
• Null Handling: Properly manages empty values from your Excel DataSet.

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