从DataTable批量插入到Access数据库 [英] Bulk Insert From DataTable to Access Database

问题描述

已经填充了DataTable，但是需要将其整个表内容转储到Access数据库中.

Already have the DataTable populated but need to dump its entire table contents into an Access database.

Using (conn)
    If conn.State <> ConnectionState.Open Then conn.Open()
    Dim adapter As OleDbDataAdapter = New OleDbDataAdapter("SELECT * FROM " & sMdbTableName, conn)
    adapter.InsertCommand = New OleDbCommand("INSERT INTO " & sMdbTableName & " SELECT * FROM " & sMdbTableNameSource & ";", conn)
    adapter.TableMappings.Add(sMdbTableName, ds.Tables.Item(sMdbTableNameSource).ToString)
    adapter.Update(ds.Tables.Item(sMdbTableNameSource))
End Using

我正在尝试执行类似操作，但没有错误，也没有添加数据.需要表现最好的方式.

Im trying to do something like this but no errors and no data added. Need the best performing way.

谢谢

更新后的代码速度慢，需要大量的访问导入类型:

Updated code is slow as heck and need a bulk type of access import:

        Using (conn)
            If conn.State <> ConnectionState.Open Then conn.Open()
            Dim adapter As OleDbDataAdapter = New OleDbDataAdapter()
            adapter.InsertCommand = New OleDbCommand("INSERT INTO " & sMdbTableName & "(Field1, Field2, Field3, Field4, Field5) VALUES(@Field1, @Field2, @Field3, @Field4, @Field5);", conn)
            adapter.InsertCommand.Parameters.Add("Field1", OleDbType.VarChar, 16, "Field1")
            adapter.InsertCommand.Parameters.Add("Field2", OleDbType.VarChar, 16, "Field2")
            adapter.InsertCommand.Parameters.Add("Field3", OleDbType.VarChar, 16, "Field3")
            adapter.InsertCommand.Parameters.Add("Field4", OleDbType.VarChar, 16, "Field4")
            adapter.InsertCommand.Parameters.Add("Field5", OleDbType.VarChar, 16, "Field5")
            adapter.TableMappings.Add(sMdbTableName, ds.Tables.Item(sMdbTableNameSource).ToString)
            adapter.Update(ds.Tables.Item(sMdbTableNameSource))
        End Using

推荐答案

Private Sub CopyDatatableToAccess(dt As DataTable, AccsessAddress As String, TableName As String)
    Try
        Dim connString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & AccsessAddress & ";Persist Security Info=False;"
        Dim accConnection As New OleDb.OleDbConnection(connString)
        Dim selectCommand As String = "SELECT <field1>, <field2> and so on FROM <table>" & TableName
        Dim accDataAdapter As New OleDb.OleDbDataAdapter(selectCommand, accConnection)
        Dim accCommandBuilder As New OleDb.OleDbCommandBuilder()
        accDataAdapter.InsertCommand = accCommandBuilder.GetInsertCommand()
        accDataAdapter.UpdateCommand = accCommandBuilder.GetUpdateCommand()
        Dim accDataTable As DataTable = dt.Copy()
        ''Just to make sure, set the RowState to added to make sure an Insert is performed'
        For Each row As DataRow In accDataTable.Rows '
            If row.RowState = DataRowState.Added Or DataRowState.Unchanged Then '
                row.SetAdded() '
            End If '
        Next '
        accDataAdapter.Update(accDataTable)
    Catch ex As Exception
    End Try
End Sub

这篇关于从DataTable批量插入到Access数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！