Bulk Insert into MySQL Database using C#

Background

In my project I have to insert 1000 rows at any instance of time, and this process is very time consuming and will take lot of time insert row one bye. Also there are chances of losing the connection. In order to insert huge number of we are using Bulk Insert of MySQL.

In MySQL there are 2 ways where we can insert multiple numbers of rows.

Solution 1:-

insert into table(column1, column2, column3) values(“abc”,”bcd”,”dfg”),(“ “abc”,”bcd”,”dfg”), (“abc”,”bcd”,”dfg”);

But in the above solution there is some limitation, we can send only few number of row.

For example: - 100 row at one time.

Solution 2:-

We have to use MySqlBulkLoader Class refer the below link

http://dev.mysql.com/doc/refman/5.1/en/connector-net-programming-bulk-loader.html

In MySql Bulk insert you need to load all the data into the .csv.

Using the code

A brief description of how to use the article or code.
1)  I have first converted the dictionary to .csv:

//

// Converting Dictionary to .csv

//

string pathToCsv = @"mytest.csv";

String csv = String.Join(Environment.NewLine, dict.Select(d => d.Key + ";" + d.Value[0] + ";" + d.Value[1].ToString()));

System.IO.File.WriteAllText(pathToCsv, csv);

Below is the code which will do the BulkInsert Functionality.

You need to add the one Reference dll.

Click on Add Reference, under Assemblies you will find the 1 dllwhich is provided by microsoft.

MySqlData.dll

//

// Bulk Insert

//

using MySql.Data.MySqlClient;

string strFile = @"mytest.csv";

if (this.OpenConnection() == true)

{

MySqlBulkLoader bcp1 = new MySqlBulkLoader(this.connection);

bcp1.TableName = "testTableName";

bcp1.FieldTerminator = ";";

bcp1.LineTerminator = "\r\n";

bcp1.FileName = strFile;

bcp1.NumberOfLinesToSkip = 0;

bcp1.Load();

this.CloseConnection();

}

In the above code you need to give the csv file path and then the table Name

With the help of“fieldTerminator” it will put the data into different column. For eg, colm1, colm2

“LineTerminator” will keep on adding the new row in the table.

Points of Interest

. BulkInsert help to improve the performance of Application.

. Also it help to reduce the risk of losing the connection.