MySql 使用MySqlBulkLoader批量插入
数据库批量插入Oracle中有 OracleBulkCopy,SQL当然也有个SqlBulkCopy .这里有介绍就不说,网上有非常详细的例子,大家可去搜索下,可是MySql确没有MySqlBulkCopy这个,网上找了很久也没找到。找到了一个 MySqlBulkLoader
不废话了,直接上代码
#region 批量插入数据 ///// <summary> ///// 批量插入收集库件级文书档案信息实体(批量) ///// </summary> ///// <param name="dataTable">数据表</param> ///// <returns></returns> public int BulkInsert(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<DataColumn>().Select(_columns => _columns.ColumnName).ToList(); using (MySqlConnection conn = new MySqlConnection(connectionString)) { try { Stopwatch stopwatch = new Stopwatch(); stopwatch.Start(); conn.Open(); MySqlBulkLoader bulk = new MySqlBulkLoader(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) { throw ex; } } File.Delete(tmpPath); } catch (Exception ex) { OnLogError("批量插入收集库件级文书档案信息实体(批量)时异常。", ex); } return insertCount; } ///将DataTable转换为标准的CSV /// </summary> /// <param name="table">数据表</param> /// <returns>返回标准的CSV</returns> private static string DataTableToCsv(DataTable table) { //以半角逗号(即,)作分隔符,列为空也要表达其存在。 //列内容如存在半角逗号(即,)则用半角引号(即"")将该字段值包含起来。 //列内容如存在半角引号(即")则应替换成半角双引号("")转义,并用半角引号(即"")将该字段值包含起来。 StringBuilder sb = new StringBuilder(); DataColumn colum; foreach (DataRow row in table.Rows) { for (int i = 0; i<table.Columns.Count; 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("\"", "\"\"") + "\""); } else sb.Append(row[colum].ToString()); } sb.AppendLine(); } return sb.ToString(); } #endregion