Mysql 批量插入資料的方法

標籤：

使用的方式是 MySqlBulkLoader

 

方法如下:

1. 轉化datatable 為檔案

2. 使用MySqlBulkLoader 進行資料的載入

代碼：

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

 

Mysql 批量插入資料的方法