22.2.5.13. Using the MySqlBulkLoader Class

MySQL Connector/Net features a bulk loader class that wraps the MySQL statement LOAD DATA INFILE. This gives MySQL Connector/Net the ability to load a data file from a local or remote host to the server. The class concerned is MySqlBulkLoader. This class has various methods, the main one being load to cause the specified file to be loaded 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 DatabaseName  Age  ProfessionTony  47  Technical WriterAna  43  NurseFred  21  IT SpecialistSimon  45  Hairy Biker

Note that with this test file the first three lines will need to be ignored, as they do not contain table data. This can be achieved using the NumberOfLinesToSkip property. This file can then be loaded and used to populate the Career table in the test database:

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.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 Section 13.2.6, "LOAD DATA INFILE Syntax". Further information on MySqlBulkLoader can be found in the reference documentation that was included with your connector.