Mysql 批量插入数据的方法

时间:2022-12-10 00:42:55

使用的方式是 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;
     }
     }
}