|
namespace DapperExtensions.MySql
{
public static class MySqlBulkLoaderHelper
{
///
///将DataTable转换为标准的CSV
///
/// 数据表
/// 返回标准的CSV
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 if (colum.DataType == typeof(DateTime) || colum.DataType == typeof(DateTime?))
{
sb.Append(string.IsNullOrEmpty(row[colum].ToString()) ? "1970-01-01 00:00:00" : row[colum].ToString());
}
else sb.Append(row[colum].ToString());
}
sb.AppendLine();
}
return sb.ToString();
}
///
///大批量数据插入,返回成功插入行数
///
/// 数据库连接字符串
/// 数据表
/// 返回成功插入行数
public static int BulkInsert(string connectionString, DataTable table)
{
if (string.IsNullOrEmpty(table.TableName)) throw new Exception("请给DataTable的TableName属性附上表名称");
if (table.Rows.Count == 0) return 0;
int insertCount = 0;
string tmpPath = Path.GetTempFileName();
string csv = DataTableToCsv(table);
File.WriteAllText(tmpPath, csv);
// MySqlTransaction tran = null;
using (MySqlConnection conn = new MySqlConnection(connectionString))
{
try
{
conn.Open();
//tran = conn.BeginTransaction();
MySqlBulkLoader bulk = new MySqlBulkLoader(conn)
{
FieldTerminator = ",",
FieldQuotationCharacter = '"',
EscapeCharacter = '"',
LineTerminator = "\r\n",
FileName = tmpPath,
NumberOfLinesToSkip = 0,
TableName = table.TableName,
};
//bulk.Columns.AddRange(table.Columns.Cast().Select(colum => colum.ColumnName).ToArray());
insertCount = bulk.Load();
// tran.Commit();
}
catch (MySqlException ex)
{
// if (tran != null) tran.Rollback();
File.Delete(tmpPath);
throw ex;
}
}
File.Delete(tmpPath);
return insertCount;
}
}
}
MSSQL有SqlBulkCopy，MYSQL有MySqlBulkLoader，Oracle有OracleBulkCopy，对应各自的驱动。
但是core没有批量插入数据的方法，所以只能创建临时CSV，导入CSV，批量插入后再删除CSV
|