mysqlbulkinsert_MySql使用MySqlBulkLoader批量插入

#region 批量插入数据

/

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

/数据表

/

public intBulkInsert(DataTable table)

{int insertCount &＃61; 0;try{

table.TableName&＃61; "tb_jj_ws_collect";//数据库中的表名

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

Directory.CreateDirectory(tmpPath);

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

string csv &＃61;DataTableToCsv(table);

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

{try{

Stopwatch stopwatch&＃61; newStopwatch();

stopwatch.Start();

conn.Open();

MySqlBulkLoader bulk&＃61; newMySqlBulkLoader(conn)

{

FieldTerminator&＃61; ",",

FieldQuotationCharacter&＃61; &＃39;"&＃39;,

EscapeCharacter&＃61; &＃39;"&＃39;,

LineTerminator&＃61; "\r\n",

FileName&＃61;tmpPath,

NumberOfLinesToSkip&＃61; 0,

TableName&＃61;table.TableName,

};

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

insertCount &＃61;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)

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

StringBuilder sb &＃61; newStringBuilder();

DataColumn colum;foreach (DataRow row intable.Rows)

{for (int i &＃61; 0; i

{

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

{

sb.Append("\"" &＃43; row[colum].ToString().Replace("\"", "\"\"") &＃43; "\"");

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

}

sb.AppendLine();

}returnsb.ToString();

}#endregion