MySqlBulkLoader与列映射？ [英] MySqlBulkLoader with Column Mapping?

问题描述

我使用 SqlBulkCopy 对SQL Server数据库进行批量插入。我现在为程序提供 MySql 支持，与 SqlBulkCopy 最接近的是 MySqlBulkLoader 。但是在 MySqlBulkLoader 中，我必须先将 DataTable 转换为文件，因为 MySqlBulkLoader 仅适用于文件，不适用于 DataTable 。然后我必须在插入之前禁用外键检查。我都完成了这两个操作，但现在又有一个问题：

I use SqlBulkCopy to do bulk inserts into a SQL Server database. I am now providing MySql support for my program and the nearest thing to SqlBulkCopy is MySqlBulkLoader. But in MySqlBulkLoader, I have to first convert my DataTable to a file because MySqlBulkLoader only works with files and not DataTable. And then I have to disable foreign key checks before the insert. I have done them both but now I am left with one more problem:

我的目标表有一个标识列（自动递增和PK），而 MySqlBulkLoader 将源文件中的第一列映射到此列，因此，只有第一条记录插入了错误的列映射。如果有帮助，可以使用以下函数：

My destination table has an identity column (auto-increment and PK) and MySqlBulkLoader maps the first column in the source file to this column and therefore only the first record is inserted with wrong column mappings. Here is how I use the function if it helps:

using (var conn = new MySqlConnection(connectionString))
{
    var bl = new MySqlBulkLoader(conn);
    bl.TableName = tableName;
    bl.Timeout = 600;
    bl.FieldTerminator = ",";
    bl.LineTerminator = "\r\n";
    bl.FileName = tempFilePath;
    bl.NumberOfLinesToSkip = 1;
    numberOfInsertedRows = bl.Load();
}

这是文件的前几行：

CampaignRunId,RecipientId,IsControlGroup
27,"testrecipient_0",False
27,"testrecipient_1",False
27,"testrecipient_2",False
27,"testrecipient_3",False
27,"testrecipient_4",False
27,"testrecipient_5",False
27,"testrecipient_6",False
27,"testrecipient_7",False
27,"testrecipient_8",False
27,"testrecipient_9",False
27,"testrecipient_10",False
27,"testrecipient_11",False
27,"testrecipient_12",False
27,"testrecipient_13",False

是否可以为 MySqlBulkLoader 提供列映射？我看到它具有 Columns 属性，但它是只读的。

Is there a way to provide column mapping for MySqlBulkLoader? I see that it has a Columns property but it is read-only.

有一个写在某个地方的库 MySqlBulkCopy ，但是我在使用它时遇到了其他问题，它并非来自官方来源。 / p>

There is a library written somewhere called MySqlBulkCopy but I ran into other problems using it and it does not come from an official source.

推荐答案

以David Hartley的答案为基础：如果您不知道列名称是先验的，则只需清除列列表并重新添加项目。

Building on David Hartley's answer: if you don't know the column names a priori, you could just clear the list of column and add items to it afresh.

它将是这样的：

bl.Columns.Clear（）;
foreach（yourColumns中的字符串col）
{
bl.Columns.Add（col）;
}

这篇关于MySqlBulkLoader与列映射？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！