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);
}