MySQL-Bulk data import using .Net connector MySqlBulkLoader Class

Lets see how do we import bulk data in MySQL database? To deal with MySQL database from .Net, we need .Net connector. Download MySQL .Net Connector (ADO.NET driver for MySQL) from MySQL developer website. When you install this connector, it will give you DLLs required to deal with MySQL database from .Net code.

Reference MySql.Data.dll in your .Net project, we'll be using MySqlBulkLoader class to import bulk data. However this class doesn't provide any direct way to load DataTable into database, but it provides way to bulk load .csv file. This MySqlBulkLoader Example will help you to solve your query.

1. Function to create .csv file from DataTable (you can skip this, if you already have csv file)

public static void CreateCSVfile(DataTable dtable, string strFilePath)
{
    StreamWriter sw = new StreamWriter(strFilePath, false);
    int icolcount = dtable.Columns.Count;
    foreach (DataRow drow in dtable.Rows)
    {
	for (int i = 0; i < icolcount; i++)
	{
	    if (!Convert.IsDBNull(drow[i]))
	    {
		sw.Write(drow[i].ToString());
	    }
	    if (i < icolcount - 1)
	    {
		sw.Write(",");
	    }
	}
	sw.Write(sw.NewLine);
    }
    sw.Close();
    sw.Dispose();
}

2. Import data into MySQL database

private void ImportMySQL()
{
    DataTable orderDetail = new DataTable("ItemDetail");
    DataColumn c = new DataColumn();        // always
    orderDetail.Columns.Add(new DataColumn("ID", Type.GetType("System.Int32")));
    orderDetail.Columns.Add(new DataColumn("value", Type.GetType("System.Int32")));
    orderDetail.Columns.Add(new DataColumn("length", Type.GetType("System.Int32")));
    orderDetail.Columns.Add(new DataColumn("breadth", Type.GetType("System.Int32")));
    orderDetail.Columns.Add(new DataColumn("total", Type.GetType("System.Decimal")));
    orderDetail.Columns["total"].Expression = "value/(length*breadth)";

    //Adding dummy entries
    DataRow dr = orderDetail.NewRow();
    dr["ID"] = 1;
    dr["value"] = 50;
    dr["length"] = 5;
    dr["breadth"] = 8;
    orderDetail.Rows.Add(dr);

    dr = orderDetail.NewRow();
    dr["ID"] = 2;
    dr["value"] = 60;
    dr["length"] = 15;
    dr["breadth"] = 18;
    orderDetail.Rows.Add(dr);
    //Adding dummy entries

    string connectMySQL = "Server=localhost;Database=test;Uid=username;Pwd=password;";
    string strFile = "/TempFolder/MySQL" + DateTime.Now.Ticks.ToString() + ".csv";

    //Create directory if not exist... Make sure directory has required rights..
    if (!Directory.Exists(Server.MapPath("~/TempFolder/")))
	Directory.CreateDirectory(Server.MapPath("~/TempFolder/"));

    //If file does not exist then create it and right data into it..
    if (!File.Exists(Server.MapPath(strFile)))
    {
	FileStream fs = new FileStream(Server.MapPath(strFile), FileMode.Create, FileAccess.Write);
	fs.Close();
	fs.Dispose();
    }

    //Generate csv file from where data read
    CreateCSVfile(orderDetail, Server.MapPath(strFile));
    using (MySqlConnection cn1 = new MySqlConnection(connectMySQL))
    {
	cn1.Open();
	MySqlBulkLoader bcp1 = new MySqlBulkLoader(cn1);
	bcp1.TableName = "productorder"; //Create ProductOrder table into MYSQL database...
	bcp1.FieldTerminator = ",";

	bcp1.LineTerminator = "\r\n";
	bcp1.FileName = Server.MapPath(strFile);
	bcp1.NumberOfLinesToSkip = 0;
	bcp1.Load();

	//Once data write into db then delete file..
	try
	{
	    File.Delete(Server.MapPath(strFile));
	}
	catch (Exception ex)
	{
	    string str = ex.Message;
	}
    }
}

To load DataTable into a database, you need to use MySqlBulkCopy class. This class will help you load the MySQL server table with another data source. You can quickly bulk load the data using MySqlBulkLoader Class, but you must know technical stuff for the process. You can always consult an IT outsourcing company in India with your technical issues.