mysql ef批量添加数据_Mysql EF 数据录入&批量数据录入

逐条录入数据

///

/// 一条条进行数据录入

///

///

///

///

private static int InsertDataTable(string tableName, DataTable dt)

{

//循环列，循环每行，执行insert sql语句

string colStr = "";

List col = new List();

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(',');

//这里可以使用MySqlParameter

string sql = $"insert into `{tableName}`({colStr}) values({val})";

totalCount+= ExecuteNonQuery(sql, null);

}

return totalCount;

}

拼接sql录入

///

/// 批量数据录入

///

///

///

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 col = new List();

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/2039672

valueStr += $"({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 parameters = null)

{

int n = 0;

using (var cmd = new MySqlCommand(sql, connection))

{

//事务

//https://www.cnblogs.com/lhyqzx/p/6440959.html

var 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();

示例代码

参考资料