How to BULK INSERT data in Mysql

The way to use it is Mysqlbulkloader

Here's how:

1. Converting a DataTable to a file

2. Loading data using Mysqlbulkloader

Code:

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(); } 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;      }      } }

How to BULK INSERT data in Mysql