4.5.11 Using the MySqlBulkLoader Class

MySQL Connector/NET features a bulk loader class that wraps the MySQL statement LOAD DATA INFILE. This gives Connector/NET the ability to load a data file from a local or remote host to the server, or a stream to a database (from Connector/NET 8.0.32).

The class concerned is MySqlBulkLoader. This class has various methods, the main overloaded method being load, which permits a stream object to be loaded directly to a database (8.0.32) or the specified file to the server. Various parameters can be set to control how the data file is processed. This is achieved through setting various properties of the class. For example, the field separator used, such as comma or tab, can be specified, along with the record terminator, such as newline.

The following code shows a simple example of using the MySqlBulkLoader class. First an empty table needs to be created, in this case in the test database.

CREATE TABLE Career (
       Name VARCHAR(100) NOT NULL,
       Age INTEGER,
       Profession VARCHAR(200)
);

A simple tab-delimited data file is also created (it could use any other field delimiter such as comma).

Table Career in Test Database
Name  Age  Profession
Tony   47  Technical Writer
Ana    43  Nurse
Fred   21  IT Specialist
Simon  45  Hairy Biker

The first three lines need to be ignored with this test file, as they do not contain table data. This task is accomplished in the following C# code example by setting the NumberOfLinesToSkip property . The file can then be loaded and used to populate the Career table in the test database.

Note

As of Connector/NET 8.0.15, the Local property must be set to True explicitly to enable the local-infile capability. Previous versions set this value to True by default.

using System;
using System.Text;
using MySql.Data;
using MySql.Data.MySqlClient;
namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            string connStr = "server=localhost;user=root;database=test;port=3306;password=******";
            MySqlConnection conn = new MySqlConnection(connStr);
            MySqlBulkLoader bl = new MySqlBulkLoader(conn);
            bl.Local = true;
            bl.TableName = "Career";
            bl.FieldTerminator = "\t";
            bl.LineTerminator = "\n";
            bl.FileName = "c:/career_data.txt";
            bl.NumberOfLinesToSkip = 3;
            try
            {
                Console.WriteLine("Connecting to MySQL...");
                conn.Open();
                // Upload data from file
                int count = bl.Load();
                Console.WriteLine(count + " lines uploaded.");
                string sql = "SELECT Name, Age, Profession FROM Career";
                MySqlCommand cmd = new MySqlCommand(sql, conn);
                MySqlDataReader rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    Console.WriteLine(rdr[0] + " -- " + rdr[1] + " -- " + rdr[2]);
                }
                rdr.Close();
                conn.Close();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.ToString());
            }
            Console.WriteLine("Done.");
        }
    }
}

Further information on LOAD DATA INFILE can be found in LOAD DATA Statement. Further information on MySqlBulkLoader can be found in the reference documentation that was included with your connector.