How to bulk insert into MySQL using C#

Question

I have previously used the SQLBulkCopy class to load data into a MS SQL Server db. The results were very good, and worked exactly as I intended it to.

Now, I'm trying to use a script task in SSIS to bulk load data into a MySQL (5.5.8) database using either an ODBC or ADO.NET connection (recommend?).

The columns in my dataset correspond with the columns of the MySQL table. What is the best way to do a bulk insert of a dataset into a MySQL database?

If your data set is in a text format, you don't need to write a program, just write a LOAD DATA INFILE query and MySQL will load the file itself. — Dan Grossman
– Dan Grossman, Commented Feb 3, 2011 at 7:19
Dan, thanks for the suggestion I will be scheduling the SSIS to load a series of text files, and need to check some file content before I decide to insert the data. This is why I'm doing this programatically. — yamn2
– yamn2, Commented Feb 3, 2011 at 8:26

Erwin Mayer · Accepted Answer · 2013-09-04 08:42:34Z

14

You can use the MySqlBulkLoader shipped with the MySQL Connector for .NET:

var bl = new MySqlBulkLoader(connection);
bl.TableName = "mytable";
bl.FieldTerminator = ",";
bl.LineTerminator = "\r\n";
bl.FileName = "myfileformytable.csv";
bl.NumberOfLinesToSkip = 1;
var inserted = bl.Load();
Debug.Print(inserted + " rows inserted.");

edited Sep 4, 2013 at 8:42

answered Sep 20, 2011 at 20:26

Erwin Mayer

18.8k9 gold badges96 silver badges134 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

rahularyansharma Over a year ago

I have tried same code but its not working inserted always remains zero .

Erwin Mayer Over a year ago

@rahularyansharma make sure your installation of MySQL supports LOAD_DATA_INFILE (dev.mysql.com/doc/refman/5.1/en/…) as MySQLBulkLoader is just a wrapper around it.

Erwin Mayer Over a year ago

ok, then make sure all parameters are specified correctly, as LOAD_DATA_INFILE may assume parameters which must be explicitly specified with MySqlBulkLoader.

rahularyansharma Over a year ago

now its running OK but only uploading a single record, May be issue due to my csv becuase when i open csv in notepad then there is no column .

Erwin Mayer Over a year ago

You should try different CSV files with different data (including the simplest possible). You can also use Notepad++ to show all characters to make sure your file is properly formatted (notepad.exe is very basic).

|

Collectives™ on Stack Overflow

How to bulk insert into MySQL using C#

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related