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:
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();
}
And this is first few lines on my file:
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
Is there a way to provide column mapping for MySqlBulkLoader? I see that it has a Columns property but it is read-only.
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.
解决方案
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.
It'll be something like this:
bl.Columns.Clear();
foreach (string col in yourColumns)
{
bl.Columns.Add(col);
}