SQLXML导入/导出

Question

我有一个SQL DB，我使用VB.Net代码将数据导出为XML。代码相对简单，运行速度快，并且XML的格式很漂亮。代码是：

    Dim connetionString As String
    Dim connection As SqlConnection
    Dim adapter As SqlDataAdapter
    Dim ds As New DataSet
    Dim sql As String

    connetionString = "**connectionstring**"
    connection = New SqlConnection(connetionString)
    sql = "select * from costdata"
    Try
        connection.Open()
        adapter = New SqlDataAdapter(sql, connection)
        adapter.Fill(ds)
        connection.Close()
        ds.WriteXml("**PATH**")
        MsgBox("Done")
    Catch ex As Exception
        MsgBox(ex.ToString)
    End Try

我遇到的问题是重新加载这些数据。看起来应该和上面的一样简单，但是我似乎不能得到一个简单的方法来做它。

据我所知，我可以将XMLReader与ADO.NET结合使用，但在这种情况下，在将数据全部导入数据库之前，我需要为DataTable定义插入数据的列。

有没有什么方法可以避免在DataTable中硬编码列值，并以类似的方式导入导出的数据？

Answer 1

尽管它不是通过列名自动执行的，但我认为对映射进行硬编码并不是一个太大的麻烦。然而，我全神贯注于自动化的方式。我的解决方案是：

    Dim connectionString As String = "Data Source=(localdb)\v11.0;Initial Catalog=localACETest;Integrated Security=True"
    Try
        Using sqlconn As New SqlConnection(connectionString)
            Dim ds As New DataSet()
            Dim sourcedata As New DataTable()
            ds.ReadXml("C:\Users\coopere.COOPERE-PC\Desktop\Test.xml")
            sourcedata = ds.Tables(0)
            sqlconn.Open()
            Using bulkcopy As New SqlBulkCopy(sqlconn)
                bulkcopy.DestinationTableName = "ScheduleData"
                bulkcopy.ColumnMappings.Add("Id", "Id")
                bulkcopy.ColumnMappings.Add("Period", "Period")
                ...

                bulkcopy.WriteToServer(sourcedata)
            End Using
            sqlconn.Close()
        End Using
        MsgBox("Done")
    Catch ex As Exception
        MsgBox(ex.ToString)
    End Try

Answer 2

下面是一种自动执行列映射的方法...它假设目标数据库中存在具有相同结构的表。干杯:-)

Public Shared Function BulkCopyXML( _
            path_ As String, _
            connection_string_ As String, _
            messages_ As List(Of String), _
            exceptions_ As List(Of Exception) _
    ) As Boolean
    Dim result_ As Boolean = False
    Try

        Dim dataset_ As New DataSet()
        dataset_.ReadXml(path_)
        Dim datatable_ As DataTable = Nothing

        Using connection_ As SqlClient.SqlConnection = New SqlClient.SqlConnection(connection_string_)
            connection_.Open()
            Using bulkCopy_ As SqlClient.SqlBulkCopy = New SqlClient.SqlBulkCopy(connection_)
                For Each datatable_ In dataset_.Tables()
                    messages_.Add(datatable_.TableName)
                    bulkCopy_.DestinationTableName = datatable_.TableName
                    bulkCopy_.ColumnMappings.Clear()
                    For Each dataColumn_ As DataColumn In datatable_.Columns
                        bulkCopy_.ColumnMappings.Add(dataColumn_.ColumnName, dataColumn_.ColumnName)
                    Next
                    bulkCopy_.WriteToServer(datatable_)
                Next
            End Using
        End Using

        result_ = True
    Catch exception_ As Exception
        If exceptions_ Is Nothing Then
            Throw exception_
        Else
            exceptions_.Add(exception_)
        End If
    Finally

    End Try
    Return result_
End Function