Mysql EF 数据录入批量数据录入

本文主要是介绍Mysql EF 数据录入批量数据录入，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

逐条录入数据

/// <summary>
/// 一条条进行数据录入
/// </summary>
/// <param name="tableName"></param>
/// <param name="dt"></param>
/// <returns></returns>
private static int InsertDataTable(string tableName, DataTable dt)
{//循环列，循环每行，执行insert sql语句string colStr = "";List<string> col = new List<string>();foreach (DataColumn column in dt.Columns){col.Add(column.ColumnName);colStr += $"`{column.ColumnName}`,";}colStr = colStr.TrimEnd(',');var totalCount = 0;foreach (DataRow dr in dt.Rows){string val = "";foreach (var columnName in col){val += $"\"{dr[columnName]}\",";}val = val.TrimEnd(',');//这里可以使用MySqlParameterstring sql = $"insert into `{tableName}`({colStr}) values({val})";totalCount+= ExecuteNonQuery(sql, null);}return totalCount;
}

拼接sql录入

/// <summary>
/// 批量数据录入
/// </summary>
/// <param name="tableName"></param>
/// <param name="dt"></param>
public static void SqlBulkCopyByDatatable(string tableName, DataTable dt)
{try{//循环列，循环每行，执行insert sql语句//一次提交500条记录var maxSingleCount = 500;//本次录入的数据条数var singleCount = 0;var totalCount = 0;//总共需要录入的数据条数var recordCount = dt.Rows.Count;string colStr = "";List<string> col = new List<string>();foreach (DataColumn column in dt.Columns){col.Add(column.ColumnName);colStr += $"`{column.ColumnName}`,";}colStr = colStr.TrimEnd(',');var valueStr = "";foreach (DataRow dr in dt.Rows){string val = "";foreach (var columnName in col){val += $"\"{dr[columnName]}\",";}val = val.TrimEnd(',');//https://blog.csdn.net/atgc/article/details/2039672valueStr += $"({val}),";singleCount++;totalCount++;//满足maxSingleCount条数据，或者到达最后一条数据，则录入if (singleCount >= maxSingleCount || totalCount >= recordCount){singleCount = 0;valueStr = valueStr.TrimEnd(',');//insert into table(columns) values(value1),(value2),.....(valuen);string sql = $"insert into `{tableName}`({colStr}) values{valueStr}";ExecuteNonQuery(sql, null);valueStr = "";}}}catch (Exception ex){throw ex;}
}

公共方法

private static int ExecuteNonQuery(string sql, IEnumerable<MySqlParameter> parameters = null)
{int n = 0;using (var cmd = new MySqlCommand(sql, connection)){//事务//https://www.cnblogs.com/lhyqzx/p/6440959.htmlvar sqlTransaction = connection.BeginTransaction();cmd.Transaction = sqlTransaction;if (parameters != null){foreach(var p in parameters){cmd.Parameters.Add(p);}}try{n = cmd.ExecuteNonQuery();sqlTransaction.Commit();}catch(Exception ex){sqlTransaction.Rollback();throw ex;}}return n;
}

通过生成文件导入数据库

mysql支持把csv文件倒入到数据库
生成csv文件的时候，不需要特别生成一行头文件，每行生成的数据直接用 ‘,’ 分隔就行，一行代表数据库的一条记录，所以生成csv文件时候，注意数据的顺序

MySqlBulkLoader bulk = new MySqlBulkLoader(connection)
{FieldTerminator = ",",//这个地方字段间的间隔方式，为逗号FieldQuotationCharacter = '"',EscapeCharacter = '"',LineTerminator = "\r\n",//每行FileName = filePath,//文件地址NumberOfLinesToSkip = 0,TableName = tableName,
};
bulk.Load();