mysql bulk insert_MySql 使用MySqlBulkLoader批量插入

#region 批量插入数据

/

/ 批量插入收集库件级文书档案信息实体(批量)/

/数据表

/

public intBulkInsert(DataTable table)

{int insertCount = 0;try{

table.TableName= "tb_jj_ws_collect";//数据库中的表名

string connectionString =db.Database.Connection.ConnectionString;if (string.IsNullOrEmpty(table.TableName)) throw new Exception("请给DataTable的TableName属性附上表名称");if (table.Rows.Count == 0) return 0;string tmpPath = Directory.GetCurrentDirectory() + "\\UpTemp";if (!Directory.Exists(tmpPath))

Directory.CreateDirectory(tmpPath);

tmpPath= Path.Combine(tmpPath, "Temp.csv");//csv文件临时目录

string csv =DataTableToCsv(table);

File.WriteAllText(tmpPath, csv);var columns = table.Columns.Cast().Select(_columns =>_columns.ColumnName).ToList();using (MySqlConnection conn = newMySqlConnection(connectionString))

{try{

Stopwatch stopwatch= newStopwatch();

stopwatch.Start();

conn.Open();

MySqlBulkLoader bulk= newMySqlBulkLoader(conn)

{

FieldTerminator= ",",

FieldQuotationCharacter= '"',

EscapeCharacter= '"',

LineTerminator= "\r\n",

FileName=tmpPath,

NumberOfLinesToSkip= 0,

TableName=table.TableName,

};

bulk.Columns.AddRange(columns);//根据标题列对应插入

insertCount =bulk.Load();

stopwatch.Stop();//Console.WriteLine("耗时:{0}", stopwatch.ElapsedMilliseconds);

}catch(MySqlException ex)

{throwex;

}

}

File.Delete(tmpPath);

}catch(Exception ex)

{

OnLogError("批量插入收集库件级文书档案信息实体(批量)时异常。", ex);

}returninsertCount;

}///将DataTable转换为标准的CSV///

/// 数据表

/// 返回标准的CSV

private static stringDataTableToCsv(DataTable table)

{//以半角逗号(即,)作分隔符，列为空也要表达其存在。//列内容如存在半角逗号(即,)则用半角引号(即"")将该字段值包含起来。//列内容如存在半角引号(即")则应替换成半角双引号("")转义，并用半角引号(即"")将该字段值包含起来。

StringBuilder sb = newStringBuilder();

DataColumn colum;foreach (DataRow row intable.Rows)

{for (int i = 0; i

{

colum=table.Columns[i];if (i != 0) sb.Append(",");if (colum.DataType == typeof(string) && row[colum].ToString().Contains(","))

{

sb.Append("\"" + row[colum].ToString().Replace("\"", "\"\"") + "\"");

}elsesb.Append(row[colum].ToString());

}

sb.AppendLine();

}returnsb.ToString();

}#endregion