public void BulkLoadColumnOrder()
{
execSQL(@"CREATE TABLE Test (id INT NOT NULL, n1 VARCHAR(250), n2 VARCHAR(250),
n3 VARCHAR(250), PRIMARY KEY(id))");
// first create the external file
string path = Path.GetTempFileName();
StreamWriter sw = new StreamWriter(path);
for (int i = 0; i < 20; i++)
sw.WriteLine(i + ",col3,col2,col1");
sw.Flush();
sw.Close();
MySqlBulkLoader loader = new MySqlBulkLoader(conn);
loader.TableName = "Test";
loader.FileName = path;
loader.Timeout = 0;
loader.FieldTerminator = ",";
loader.LineTerminator = Environment.NewLine;
loader.Columns.Add("id");
loader.Columns.Add("n3");
loader.Columns.Add("n2");
loader.Columns.Add("n1");
int count = loader.Load();
Assert.AreEqual(20, count);
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", conn);
DataTable dt = new DataTable();
da.Fill(dt);
Assert.AreEqual(20, dt.Rows.Count);
Assert.AreEqual("col1", dt.Rows[0][1]);
Assert.AreEqual("col2", dt.Rows[0][2]);
Assert.AreEqual("col3", dt.Rows[0][3].ToString().Trim());
}
public int BulkCopy(Guid taskId, string tableFullName, MySqlConnection connection, DataTable dataTable, IEnumerable <string> columnFields)
{
_logger.LogInformation($"taskId={taskId}, bulk copy start.");
int effectRows = 0;
if (dataTable.Rows.Count == 0)
{
return(effectRows);
}
Func <DataTable, string> DataTableToCsv = (data) => {
StringBuilder builder = new StringBuilder();
DataColumn colum;
foreach (DataRow row in data.Rows)
{
for (int i = 0; i < data.Columns.Count; i++)
{
colum = data.Columns[i];
if (i != 0)
{
builder.Append(",");
}
string cellValue = row[colum].ToString();
if (colum.DataType == typeof(string) && cellValue.Contains(","))
{
builder.Append("\"" + cellValue.Replace("\"", "\"\"") + "\"");
}
else if (colum.DataType == typeof(DateTime) && row[colum] != null && !string.IsNullOrEmpty(cellValue))
{
try
{
builder.Append($"{(DateTime)row[colum]:yyyy-MM-dd HH:mm:ss}");
}
catch
{
builder.Append("0001-01-01");
}
}
else
{
builder.Append(cellValue);
}
}
builder.AppendLine();
}
return(builder.ToString());
};
string tempPath = Path.GetTempFileName();
try
{
File.WriteAllText(tempPath, DataTableToCsv(dataTable), Encoding.UTF8);
MySqlBulkLoader bulkLoader = new MySqlBulkLoader(connection)
{
FieldTerminator = ",",
FieldQuotationCharacter = '"',
EscapeCharacter = '"',
LineTerminator = Environment.NewLine,
FileName = tempPath,
Local = true,
NumberOfLinesToSkip = 0,
TableName = tableFullName,
};
bulkLoader.Columns.AddRange(columnFields);
effectRows = bulkLoader.Load();
_logger.LogInformation($"taskId={taskId}, bulk copy end, effectRows={effectRows}.");
}
catch (Exception ex)
{
_logger.LogError(ex, $"taskId={taskId}, bulk copy occured an error: {ex.Message}");
throw ex;
}
finally
{
if (File.Exists(tempPath))
{
File.Delete(tempPath);
}
}
return(effectRows);
}
public void BulkLoadColumnOrder()
{
execSQL(@"CREATE TABLE Test (id INT NOT NULL, n1 VARCHAR(250), n2 VARCHAR(250),
n3 VARCHAR(250), PRIMARY KEY(id))");
// first create the external file
string path = Path.GetTempFileName();
StreamWriter sw = new StreamWriter(File.OpenWrite(path));
for (int i = 0; i < 20; i++)
sw.WriteLine(i + ",col3,col2,col1");
sw.Flush();
sw.Dispose();
MySqlBulkLoader loader = new MySqlBulkLoader(conn);
loader.TableName = "Test";
loader.FileName = path;
loader.Timeout = 0;
loader.FieldTerminator = ",";
loader.LineTerminator = Environment.NewLine;
loader.Columns.Add("id");
loader.Columns.Add("n3");
loader.Columns.Add("n2");
loader.Columns.Add("n1");
int count = loader.Load();
Assert.AreEqual(20, count);
}
public void BulkLoadConflictOptionIgnore()
{
execSQL("CREATE TABLE Test (id INT NOT NULL, name VARCHAR(250), PRIMARY KEY(id))");
// first create the external file
string path = Path.GetTempFileName();
StreamWriter sw = new StreamWriter(File.OpenWrite(path));
for (int i = 0; i < 20; i++)
sw.WriteLine(i + ",col1");
sw.Flush();
sw.Dispose();
MySqlBulkLoader loader = new MySqlBulkLoader(conn);
loader.TableName = "Test";
loader.FileName = path;
loader.Timeout = 0;
loader.FieldTerminator = ",";
int count = loader.Load();
Assert.AreEqual(20, count);
path = Path.GetTempFileName();
sw = new StreamWriter(File.OpenWrite(path));
for (int i = 0; i < 20; i++)
sw.WriteLine(i + ",col2");
sw.Flush();
sw.Dispose();
loader = new MySqlBulkLoader(conn);
loader.TableName = "Test";
loader.FileName = path;
loader.Timeout = 0;
loader.FieldTerminator = ",";
loader.ConflictOption = MySqlBulkLoaderConflictOption.Ignore;
loader.Load();
}
public void BulkLoadConflictOptionIgnore()
{
execSQL("CREATE TABLE Test (id INT NOT NULL, name VARCHAR(250), PRIMARY KEY(id))");
// first create the external file
string path = Path.GetTempFileName();
StreamWriter sw = new StreamWriter(path);
for (int i = 0; i < 20; i++)
sw.WriteLine(i + ",col1");
sw.Flush();
sw.Close();
MySqlBulkLoader loader = new MySqlBulkLoader(conn);
loader.TableName = "Test";
loader.FileName = path;
loader.Timeout = 0;
loader.FieldTerminator = ",";
int count = loader.Load();
Assert.AreEqual(20, count);
path = Path.GetTempFileName();
sw = new StreamWriter(path);
for (int i = 0; i < 20; i++)
sw.WriteLine(i + ",col2");
sw.Flush();
sw.Close();
loader = new MySqlBulkLoader(conn);
loader.TableName = "Test";
loader.FileName = path;
loader.Timeout = 0;
loader.FieldTerminator = ",";
loader.ConflictOption = MySqlBulkLoaderConflictOption.Ignore;
loader.Load();
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", conn);
DataTable dt = new DataTable();
da.Fill(dt);
Assert.AreEqual(20, dt.Rows.Count);
Assert.AreEqual("col1", dt.Rows[0][1].ToString().Trim());
}
public async Task <ResultSet> ReadResultSetHeaderAsync(IOBehavior ioBehavior)
{
// ResultSet can be re-used, so initialize everything
BufferState = ResultSetState.None;
ColumnDefinitions = null;
ColumnTypes = null;
LastInsertId = 0;
RecordsAffected = null;
State = ResultSetState.None;
m_columnDefinitionPayloadUsedBytes = 0;
m_dataLengths = null;
m_dataOffsets = null;
m_readBuffer.Clear();
m_row = null;
m_rowBuffered = null;
m_hasRows = false;
try
{
while (true)
{
var payload = await Session.ReceiveReplyAsync(ioBehavior, CancellationToken.None).ConfigureAwait(false);
var firstByte = payload.HeaderByte;
if (firstByte == OkPayload.Signature)
{
var ok = OkPayload.Create(payload);
RecordsAffected = (RecordsAffected ?? 0) + ok.AffectedRowCount;
LastInsertId = ok.LastInsertId;
ColumnDefinitions = null;
ColumnTypes = null;
State = (ok.ServerStatus & ServerStatus.MoreResultsExist) == 0
? ResultSetState.NoMoreData
: ResultSetState.HasMoreData;
if (State == ResultSetState.NoMoreData)
{
break;
}
}
else if (firstByte == LocalInfilePayload.Signature)
{
try
{
var localInfile = LocalInfilePayload.Create(payload);
if (!IsHostVerified(Connection) &&
!localInfile.FileName.StartsWith(MySqlBulkLoader.StreamPrefix, StringComparison.Ordinal))
{
throw new NotSupportedException("Use SourceStream or SslMode >= VerifyCA for LOAD DATA LOCAL INFILE");
}
using (var stream = localInfile.FileName.StartsWith(MySqlBulkLoader.StreamPrefix, StringComparison.Ordinal) ?
MySqlBulkLoader.GetAndRemoveStream(localInfile.FileName) :
File.OpenRead(localInfile.FileName))
{
byte[] readBuffer = new byte[65536];
int byteCount;
while ((byteCount = await stream.ReadAsync(readBuffer, 0, readBuffer.Length).ConfigureAwait(false)) > 0)
{
payload = new PayloadData(new ArraySegment <byte>(readBuffer, 0, byteCount));
await Session.SendReplyAsync(payload, ioBehavior, CancellationToken.None).ConfigureAwait(false);
}
}
}
catch (Exception ex)
{
// store the exception, to be thrown after reading the response packet from the server
ReadResultSetHeaderException = new MySqlException("Error during LOAD DATA LOCAL INFILE", ex);
}
await Session.SendReplyAsync(EmptyPayload.Create(), ioBehavior, CancellationToken.None).ConfigureAwait(false);
}
else
{
var reader = new ByteArrayReader(payload.ArraySegment);
var columnCount = (int)reader.ReadLengthEncodedInteger();
if (reader.BytesRemaining != 0)
{
throw new MySqlException("Unexpected data at end of column_count packet; see https://github.com/mysql-net/MySqlConnector/issues/324");
}
// reserve adequate space to hold a copy of all column definitions (but note that this can be resized below if we guess too small)
Array.Resize(ref m_columnDefinitionPayloads, columnCount * 96);
ColumnDefinitions = new ColumnDefinitionPayload[columnCount];
ColumnTypes = new MySqlDbType[columnCount];
m_dataOffsets = new int[columnCount];
m_dataLengths = new int[columnCount];
for (var column = 0; column < ColumnDefinitions.Length; column++)
{
payload = await Session.ReceiveReplyAsync(ioBehavior, CancellationToken.None).ConfigureAwait(false);
var arraySegment = payload.ArraySegment;
// 'Session.ReceiveReplyAsync' reuses a shared buffer; make a copy so that the column definitions can always be safely read at any future point
if (m_columnDefinitionPayloadUsedBytes + arraySegment.Count > m_columnDefinitionPayloads.Length)
{
Array.Resize(ref m_columnDefinitionPayloads, Math.Max(m_columnDefinitionPayloadUsedBytes + arraySegment.Count, m_columnDefinitionPayloadUsedBytes * 2));
}
Buffer.BlockCopy(arraySegment.Array, arraySegment.Offset, m_columnDefinitionPayloads, m_columnDefinitionPayloadUsedBytes, arraySegment.Count);
var columnDefinition = ColumnDefinitionPayload.Create(new ArraySegment <byte>(m_columnDefinitionPayloads, m_columnDefinitionPayloadUsedBytes, arraySegment.Count));
ColumnDefinitions[column] = columnDefinition;
ColumnTypes[column] = TypeMapper.ConvertToMySqlDbType(columnDefinition, treatTinyAsBoolean: Connection.TreatTinyAsBoolean, oldGuids: Connection.OldGuids);
m_columnDefinitionPayloadUsedBytes += arraySegment.Count;
}
if (!Session.SupportsDeprecateEof)
{
payload = await Session.ReceiveReplyAsync(ioBehavior, CancellationToken.None).ConfigureAwait(false);
EofPayload.Create(payload);
}
LastInsertId = -1;
State = ResultSetState.ReadResultSetHeader;
break;
}
}
}
catch (Exception ex)
{
ReadResultSetHeaderException = ex;
}
finally
{
BufferState = State;
}
return(this);
}
private async Task <int> LoaderAsync(MySqlBulkLoader loader, DataTable dataTable)
{
if (loader == null)
{
return(0);
}
var path = Path.GetTempFileName();
try
{
loader.FileName = path;
using (var writer = new StreamWriter(path))
{
var configuration = new Configuration
{
HasHeaderRecord = false,
};
configuration.TypeConverterCache.AddConverter <DateTime?>(new NullDateTimeConverter());
using (var csv = new CsvWriter(writer, configuration))
{
using (var dt = dataTable.Copy())
{
foreach (DataColumn column in dt.Columns)
{
var columnName = column.ColumnName;
if (columnName != RowNumberColumnName)
{
loader.Columns.Add(columnName);
}
}
foreach (DataRow row in dt.Rows)
{
for (var i = 0; i < dt.Columns.Count; i++)
{
var column = dt.Columns[i];
var columnName = column.ColumnName;
if (columnName != RowNumberColumnName)
{
csv.WriteField(row[i]);
}
}
csv.NextRecord();
}
}
}
}
return(await loader.LoadAsync());
}
catch (Exception e)
{
throw e;
}
finally
{
File.Delete(path);
}
}
public void BulkLoadReadOnlyFile()
{
execSQL("CREATE TABLE Test (id INT NOT NULL, name VARCHAR(250), PRIMARY KEY(id))");
// first create the external file
string path = Path.GetTempFileName();
StreamWriter sw = new StreamWriter(File.OpenWrite(path));
for (int i = 0; i < 200; i++)
sw.WriteLine(i + "\t'Test'");
sw.Flush();
sw.Dispose();
FileInfo fi = new FileInfo(path);
FileAttributes oldAttr = fi.Attributes;
fi.Attributes = fi.Attributes | FileAttributes.ReadOnly;
try
{
MySqlBulkLoader loader = new MySqlBulkLoader(conn);
loader.TableName = "Test";
loader.FileName = path;
loader.Timeout = 0;
int count = loader.Load();
Assert.AreEqual(200, count);
}
finally
{
fi.Attributes = oldAttr;
fi.Delete();
}
}
public void BulkLoadSimple4()
{
execSQL("CREATE TABLE Test (id INT NOT NULL, name VARCHAR(250), PRIMARY KEY(id))");
// first create the external file
string path = Path.GetTempFileName();
StreamWriter sw = new StreamWriter(File.OpenWrite(path));
for (int i = 0; i < 100; i++)
sw.Write("aaa" + i + ",'Test' xxx");
for (int i = 100; i < 200; i++)
sw.Write("bbb" + i + ",'Test' xxx");
for (int i = 200; i < 300; i++)
sw.Write("aaa" + i + ",'Test' xxx");
for (int i = 300; i < 400; i++)
sw.Write("bbb" + i + ",'Test' xxx");
sw.Flush();
sw.Dispose();
MySqlBulkLoader loader = new MySqlBulkLoader(conn);
loader.TableName = "Test";
loader.FileName = path;
loader.Timeout = 0;
loader.FieldTerminator = ",";
loader.LineTerminator = "xxx";
loader.LinePrefix = "bbb";
int count = loader.Load();
Assert.AreEqual(200, count);
MySqlCommand cmd = new MySqlCommand("SELECT COUNT(*) FROM Test", conn);
Assert.AreEqual(200, cmd.ExecuteScalar());
}
public ReturnValue AddEncounterDeaths(List <EncounterDeath> encounterDeaths)
{
var returnValue = new ReturnValue();
try
{
// Create a CSV of the data, and then dump it into the database
string randomFilename = AuthEncryption.RandomFilename() + ".csv";
while (true)
{
if (!File.Exists("databaseImport\\" + randomFilename))
{
break;
}
randomFilename = AuthEncryption.RandomFilename() + ".csv";
}
string filePath = "databaseImport\\" + randomFilename;
using (var outFile = File.CreateText(filePath))
{
outFile.WriteLine("SourcePlayerId,SourceNpcName,SourceNpcId,SourcePetName,TargetPlayerId,TargetNpcName,TargetNpcId,TargetPetName," +
"EncounterId,AbilityId,TotalDamage,OverkillValue,SecondsElapsed,OrderWithinSecond");
foreach (var death in encounterDeaths)
{
List <object> lineList = new List <object>()
{
death.SourcePlayerId, death.SourceNpcName, death.SourceNpcId, death.SourcePetName,
death.TargetPlayerId, death.TargetNpcName, death.TargetNpcId, death.TargetPetName,
death.EncounterId, death.AbilityId, death.TotalDamage, death.OverkillValue,
death.SecondsElapsed, death.OrderWithinSecond
};
outFile.WriteLine(EncapLine(lineList));
}
}
using (var connection = new MySqlConnection(_connectionString))
{
MySqlBulkLoader bulkLoader = new MySqlBulkLoader(connection)
{
TableName = "EncounterDeath",
FieldTerminator = ",",
LineTerminator = "\r\n",
FieldQuotationCharacter = '"',
FieldQuotationOptional = false,
FileName = filePath,
NumberOfLinesToSkip = 1,
Columns = { "SourcePlayerId", "SourceNpcName", "SourceNpcId", "SourcePetName", "TargetPlayerId", "TargetNpcName", "TargetNpcId", "TargetPetName",
"EncounterId", "AbilityId", "TotalDamage", "OverkillValue", "SecondsElapsed", "OrderWithinSecond" }
};
int count = bulkLoader.Load();
returnValue.Success = true;
returnValue.Message = count.ToString();
}
try
{
File.Delete(filePath);
}
catch (Exception ex)
{
// Catch this?
}
return(returnValue);
}
catch (Exception ex)
{
returnValue.Message = ex.Message;
return(returnValue);
}
}
/// <summary>
/// 批量执行
/// </summary>
/// <param name="batchSize"></param>
/// <param name="timeout"></param>
public override void Execute(int batchSize = 10000, int timeout = 10 * 1000)
{
MySqlConnection newConnection = (MySqlConnection)_database.CreateConnection();
try
{
_dataTable = ToDataTable(_list);
if (_dataTable == null || _dataTable.Rows.Count == 0)
{
return;
}
string tmpPath = Path.Combine(Path.GetTempPath(), _dataTable.TableName + ".csv");
DBContext.WriteToCSV(_dataTable, tmpPath, false);
newConnection.Open();
using (MySqlTransaction tran = newConnection.BeginTransaction())
{
MySqlBulkLoader bulk = new MySqlBulkLoader(newConnection)
{
FieldTerminator = ",",
FieldQuotationCharacter = '"',
EscapeCharacter = '"',
LineTerminator = "\r\n",
FileName = tmpPath,
Local = true,
NumberOfLinesToSkip = 0,
TableName = _dataTable.TableName,
CharacterSet = "utf8"
};
try
{
bulk.Columns.AddRange(_dataTable.Columns.Cast <DataColumn>().Select(colum => colum.ColumnName).ToList());
var size = bulk.Load();
tran.Commit();
}
catch (MySqlException ex)
{
if (tran != null)
{
tran.Rollback();
}
throw ex;
}
finally
{
File.Delete(tmpPath);
}
}
}
finally
{
if (newConnection.State == ConnectionState.Open)
{
newConnection.Close();
}
_list.Clear();
}
}
public ReturnValue AddBuffAction(List <EncounterBuffAction> buffActions)
{
var returnValue = new ReturnValue();
try
{
// Create a CSV of the data, and then dump it into the database
string randomFilename = AuthEncryption.RandomFilename() + ".csv";
while (true)
{
if (!File.Exists("databaseImport\\" + randomFilename))
{
break;
}
randomFilename = AuthEncryption.RandomFilename() + ".csv";
}
string filePath = "databaseImport\\" + randomFilename;
using (var outFile = File.CreateText(filePath))
{
outFile.WriteLine("EncounterId,AbilityId,BuffName,SourceId,SourceName,SourceType,TargetId,TargetName,TargetType," +
"SecondBuffWentUp,SecondBuffWentDown");
foreach (var action in buffActions)
{
List <object> lineList = new List <object>()
{
action.EncounterId, action.AbilityId, action.BuffName, action.SourceId,
action.SourceName, action.SourceType, action.TargetId, action.TargetName,
action.TargetType, action.SecondBuffWentUp, action.SecondBuffWentDown
};
if (action.AbilityId == 0)
{
string something = "blah";
}
outFile.WriteLine(EncapLine(lineList));
}
}
using (var connection = new MySqlConnection(_connectionString))
{
MySqlBulkLoader bulkLoader = new MySqlBulkLoader(connection)
{
TableName = "EncounterBuffAction",
FieldTerminator = ",",
LineTerminator = "\r\n",
FieldQuotationCharacter = '"',
FieldQuotationOptional = false,
FileName = filePath,
NumberOfLinesToSkip = 1,
Columns = { "EncounterId", "AbilityId", "BuffName", "SourceId", "SourceName", "SourceType", "TargetId", "TargetName",
"TargetType", "SecondBuffWentUp", "SecondBuffWentDown" }
};
int count = bulkLoader.Load();
returnValue.Success = true;
returnValue.Message = count.ToString();
}
try
{
File.Delete(filePath);
}
catch (Exception ex)
{
// Catch this?
}
return(returnValue);
}
catch (Exception ex)
{
returnValue.Message = ex.Message;
return(returnValue);
}
}
public async Task <ResultSet> ReadResultSetHeaderAsync(IOBehavior ioBehavior)
{
// ResultSet can be re-used, so initialize everything
BufferState = ResultSetState.None;
ColumnDefinitions = null;
LastInsertId = 0;
RecordsAffected = 0;
State = ResultSetState.None;
m_dataLengths = null;
m_dataOffsets = null;
m_readBuffer.Clear();
m_row = null;
m_rowBuffered = null;
try
{
while (true)
{
var payload = await Session.ReceiveReplyAsync(ioBehavior, CancellationToken.None).ConfigureAwait(false);
var firstByte = payload.HeaderByte;
if (firstByte == OkPayload.Signature)
{
var ok = OkPayload.Create(payload);
RecordsAffected += ok.AffectedRowCount;
LastInsertId = ok.LastInsertId;
ColumnDefinitions = null;
State = (ok.ServerStatus & ServerStatus.MoreResultsExist) == 0
? ResultSetState.NoMoreData
: ResultSetState.HasMoreData;
if (State == ResultSetState.NoMoreData)
{
break;
}
}
else if (firstByte == LocalInfilePayload.Signature)
{
try
{
var localInfile = LocalInfilePayload.Create(payload);
using (var stream = localInfile.FileName.StartsWith(MySqlBulkLoader.StreamPrefix, StringComparison.Ordinal) ?
MySqlBulkLoader.GetAndRemoveStream(localInfile.FileName) :
File.OpenRead(localInfile.FileName))
{
byte[] readBuffer = new byte[65536];
int byteCount;
while ((byteCount = await stream.ReadAsync(readBuffer, 0, readBuffer.Length).ConfigureAwait(false)) > 0)
{
payload = new PayloadData(new ArraySegment <byte>(readBuffer, 0, byteCount));
await Session.SendReplyAsync(payload, ioBehavior, CancellationToken.None).ConfigureAwait(false);
}
}
}
catch (Exception ex)
{
// store the exception, to be thrown after reading the response packet from the server
ReadResultSetHeaderException = new MySqlException("Error during LOAD DATA LOCAL INFILE", ex);
}
await Session.SendReplyAsync(EmptyPayload.Create(), ioBehavior, CancellationToken.None).ConfigureAwait(false);
}
else
{
var reader = new ByteArrayReader(payload.ArraySegment);
var columnCount = (int)reader.ReadLengthEncodedInteger();
ColumnDefinitions = new ColumnDefinitionPayload[columnCount];
m_dataOffsets = new int[columnCount];
m_dataLengths = new int[columnCount];
for (var column = 0; column < ColumnDefinitions.Length; column++)
{
payload = await Session.ReceiveReplyAsync(ioBehavior, CancellationToken.None).ConfigureAwait(false);
ColumnDefinitions[column] = ColumnDefinitionPayload.Create(payload);
}
payload = await Session.ReceiveReplyAsync(ioBehavior, CancellationToken.None).ConfigureAwait(false);
EofPayload.Create(payload);
LastInsertId = -1;
State = ResultSetState.ReadResultSetHeader;
break;
}
}
}
catch (Exception ex)
{
ReadResultSetHeaderException = ex;
}
finally
{
BufferState = State;
}
return(this);
}
public void BulkLoadSimple3()
{
execSQL("CREATE TABLE Test (id INT NOT NULL, name VARCHAR(250), PRIMARY KEY(id))");
// first create the external file
string path = Path.GetTempFileName();
StreamWriter sw = new StreamWriter(path);
for (int i = 0; i < 200; i++)
sw.Write(i + ",'Test' xxx");
sw.Flush();
sw.Close();
MySqlBulkLoader loader = new MySqlBulkLoader(conn);
loader.TableName = "Test";
loader.FileName = path;
loader.Timeout = 0;
loader.FieldTerminator = ",";
loader.LineTerminator = "xxx";
loader.NumberOfLinesToSkip = 50;
int count = loader.Load();
Assert.AreEqual(150, count);
MySqlCommand cmd = new MySqlCommand("SELECT COUNT(*) FROM Test", conn);
Assert.AreEqual(150, cmd.ExecuteScalar());
}
public void BulkLoadReadOnlyFile()
{
execSQL("CREATE TABLE Test (id INT NOT NULL, name VARCHAR(250), PRIMARY KEY(id))");
// first create the external file
string path = Path.GetTempFileName();
StreamWriter sw = new StreamWriter(path);
for (int i = 0; i < 200; i++)
sw.WriteLine(i + "\t'Test'");
sw.Flush();
sw.Close();
FileInfo fi = new FileInfo(path);
FileAttributes oldAttr = fi.Attributes;
fi.Attributes = fi.Attributes | FileAttributes.ReadOnly;
try
{
MySqlBulkLoader loader = new MySqlBulkLoader(conn);
loader.TableName = "Test";
loader.FileName = path;
loader.Timeout = 0;
int count = loader.Load();
Assert.AreEqual(200, count);
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", conn);
DataTable dt = new DataTable();
da.Fill(dt);
Assert.AreEqual(200, dt.Rows.Count);
Assert.AreEqual("'Test'", dt.Rows[0][1].ToString().Trim());
}
finally
{
fi.Attributes = oldAttr;
fi.Delete();
}
}
public void BulkLoadFieldQuoting()
{
execSQL("CREATE TABLE Test (id INT NOT NULL, name VARCHAR(250), name2 VARCHAR(250), PRIMARY KEY(id))");
// first create the external file
string path = Path.GetTempFileName();
StreamWriter sw = new StreamWriter(path);
for (int i = 0; i < 200; i++)
sw.WriteLine(i + "\t`col1`\tcol2");
sw.Flush();
sw.Close();
MySqlBulkLoader loader = new MySqlBulkLoader(conn);
loader.TableName = "Test";
loader.FileName = path;
loader.Timeout = 0;
loader.FieldQuotationCharacter = '`';
loader.FieldQuotationOptional = true;
int count = loader.Load();
Assert.AreEqual(200, count);
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", conn);
DataTable dt = new DataTable();
da.Fill(dt);
Assert.AreEqual(200, dt.Rows.Count);
Assert.AreEqual("col1", dt.Rows[0][1]);
Assert.AreEqual("col2", dt.Rows[0][2].ToString().Trim());
}
private static async Task DoBulkInsert(MyDataTable dt, IDbConnection connection, string tableName, bool isDataMigration = false, string schema = "dbo")
{
if (connection.State == ConnectionState.Open)
{
connection.Close();
}
connection.Open();
var con = connection as MySqlConnection;
if (con != null)
{
string strFile = string.Format("{0}_{1}.csv", tableName, DateTime.UtcNow.ToString("yyyyMMMddhhmmss"));
//Create directory if not exist... Make sure directory has required rights..
var baseDir = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "MySQLTemp");
if (!Directory.Exists(baseDir))
{
Directory.CreateDirectory(baseDir);
}
var theFile = Path.Combine(baseDir, strFile);
//If file does not exist then create it and right data into it..
if (!File.Exists(theFile))
{
using (File.Create(theFile))
{
}
}
string fieldTerminator = "\t";
string lineTerminator = "\r\n";
//Generate csv file from where data read
await IO.CsvWriter.CreateCSVfile(dt, theFile, true, fieldTerminator);
try
{
RunMySqlCommand(con, "SET FOREIGN_KEY_CHECKS=0");
MySqlBulkLoader bl = new MySqlBulkLoader(con)
{
TableName = tableName,
FieldTerminator = fieldTerminator,
LineTerminator = lineTerminator,
FileName = theFile,
NumberOfLinesToSkip = 0
};
bl.Columns.Clear();
foreach (var col in dt.Columns)
{
bl.Columns.Add(col.Key);
}
int count = bl.Load();
RunMySqlCommand(con, "SET FOREIGN_KEY_CHECKS=1");
}
finally
{
try
{
File.Delete(theFile);
}
catch { }
}
}
else // SQL Server
{
#region Deal with later - or never
//dt.TableName = string.Format("[{0}].[{1}]", schema, tableName);
//var sqlConn = connection as SqlConnection;
//SqlBulkCopyOptions sqlBulkCopyOptions;
//if (isDataMigration)
//{
// //NB: The Bitwise OR (|) is NOT the same as OR. What this particular ORing does, in English, is actually AND; i.e., we want to
// //Keep Identity and Keep nulls also.
// sqlBulkCopyOptions = SqlBulkCopyOptions.KeepIdentity | SqlBulkCopyOptions.KeepNulls;
//}
//else
//{
// sqlBulkCopyOptions = SqlBulkCopyOptions.KeepNulls;
//}
//using (var copy = new SqlBulkCopy(sqlConn, sqlBulkCopyOptions, null)
//{
// BulkCopyTimeout = 10000,
// DestinationTableName = dt.TableName,
// NotifyAfter = 5000,
//})
//{
// foreach (DataColumn column in dt.Columns)
// {
// copy.ColumnMappings.Add(column.ColumnName, column.ColumnName);
// }
// copy.WriteToServer(dt);
//}
#endregion
}
}
public ReturnValue AddNpcCasts(List <EncounterNpcCast> npcCasts)
{
var returnValue = new ReturnValue();
try
{
// Create a CSV of the data, and then dump it into the database
string randomFilename = AuthEncryption.RandomFilename() + ".csv";
while (true)
{
if (!File.Exists("databaseImport\\" + randomFilename))
{
break;
}
randomFilename = AuthEncryption.RandomFilename() + ".csv";
}
string filePath = "databaseImport\\" + randomFilename;
using (var outFile = File.CreateText(filePath))
{
outFile.WriteLine("EncounterId,AbilityName,NpcId,NpcName");
foreach (var cast in npcCasts)
{
List <object> lineList = new List <object>()
{
cast.EncounterId, cast.AbilityName, cast.NpcId, cast.NpcName
};
outFile.WriteLine(EncapLine(lineList));
}
}
using (var connection = new MySqlConnection(_connectionString))
{
MySqlBulkLoader bulkLoader = new MySqlBulkLoader(connection)
{
TableName = "EncounterNpcCast",
FieldTerminator = ",",
LineTerminator = "\r\n",
FieldQuotationCharacter = '"',
FieldQuotationOptional = false,
FileName = filePath,
NumberOfLinesToSkip = 1,
Columns = { "EncounterId", "AbilityName", "NpcId", "NpcName" }
};
int count = bulkLoader.Load();
returnValue.Success = true;
returnValue.Message = count.ToString();
}
try
{
File.Delete(filePath);
}
catch (Exception ex)
{
// Catch this?
}
return(returnValue);
}
catch (Exception ex)
{
returnValue.Message = ex.Message;
return(returnValue);
}
}
public async Task ReadResultSetHeaderAsync(IOBehavior ioBehavior)
{
Reset();
try
{
while (true)
{
var payload = await Session.ReceiveReplyAsync(ioBehavior, CancellationToken.None).ConfigureAwait(false);
var firstByte = payload.HeaderByte;
if (firstByte == OkPayload.Signature)
{
var ok = OkPayload.Create(payload.Span, Session.SupportsDeprecateEof, Session.SupportsSessionTrack);
RecordsAffected = (RecordsAffected ?? 0) + ok.AffectedRowCount;
LastInsertId = unchecked ((long)ok.LastInsertId);
WarningCount = ok.WarningCount;
if (ok.NewSchema is object)
{
Connection.Session.DatabaseOverride = ok.NewSchema;
}
ColumnDefinitions = null;
ColumnTypes = null;
State = (ok.ServerStatus & ServerStatus.MoreResultsExist) == 0
? ResultSetState.NoMoreData
: ResultSetState.HasMoreData;
if (State == ResultSetState.NoMoreData)
{
break;
}
}
else if (firstByte == LocalInfilePayload.Signature)
{
try
{
if (!Connection.AllowLoadLocalInfile)
{
throw new NotSupportedException("To use LOAD DATA LOCAL INFILE, set AllowLoadLocalInfile=true in the connection string. See https://fl.vu/mysql-load-data");
}
var localInfile = LocalInfilePayload.Create(payload.Span);
if (!IsHostVerified(Connection) &&
!localInfile.FileName.StartsWith(MySqlBulkLoader.StreamPrefix, StringComparison.Ordinal))
{
throw new NotSupportedException("Use SourceStream or SslMode >= VerifyCA for LOAD DATA LOCAL INFILE. See https://fl.vu/mysql-load-data");
}
using var stream = localInfile.FileName.StartsWith(MySqlBulkLoader.StreamPrefix, StringComparison.Ordinal) ?
MySqlBulkLoader.GetAndRemoveStream(localInfile.FileName) :
File.OpenRead(localInfile.FileName);
var readBuffer = new byte[65536];
int byteCount;
while ((byteCount = await stream.ReadAsync(readBuffer, 0, readBuffer.Length).ConfigureAwait(false)) > 0)
{
payload = new PayloadData(new ArraySegment <byte>(readBuffer, 0, byteCount));
await Session.SendReplyAsync(payload, ioBehavior, CancellationToken.None).ConfigureAwait(false);
}
}
catch (Exception ex)
{
// store the exception, to be thrown after reading the response packet from the server
ReadResultSetHeaderException = new MySqlException("Error during LOAD DATA LOCAL INFILE", ex);
}
await Session.SendReplyAsync(EmptyPayload.Instance, ioBehavior, CancellationToken.None).ConfigureAwait(false);
}
else
{
int ReadColumnCount(ReadOnlySpan <byte> span)
{
var reader = new ByteArrayReader(span);
var columnCount_ = (int)reader.ReadLengthEncodedInteger();
if (reader.BytesRemaining != 0)
{
throw new MySqlException("Unexpected data at end of column_count packet; see https://github.com/mysql-net/MySqlConnector/issues/324");
}
return(columnCount_);
}
var columnCount = ReadColumnCount(payload.Span);
// reserve adequate space to hold a copy of all column definitions (but note that this can be resized below if we guess too small)
Utility.Resize(ref m_columnDefinitionPayloads, columnCount * 96);
ColumnDefinitions = new ColumnDefinitionPayload[columnCount];
ColumnTypes = new MySqlDbType[columnCount];
for (var column = 0; column < ColumnDefinitions.Length; column++)
{
payload = await Session.ReceiveReplyAsync(ioBehavior, CancellationToken.None).ConfigureAwait(false);
var payloadLength = payload.Span.Length;
// 'Session.ReceiveReplyAsync' reuses a shared buffer; make a copy so that the column definitions can always be safely read at any future point
if (m_columnDefinitionPayloadUsedBytes + payloadLength > m_columnDefinitionPayloads.Count)
{
Utility.Resize(ref m_columnDefinitionPayloads, m_columnDefinitionPayloadUsedBytes + payloadLength);
}
payload.Span.CopyTo(m_columnDefinitionPayloads.Array.AsSpan().Slice(m_columnDefinitionPayloadUsedBytes));
var columnDefinition = ColumnDefinitionPayload.Create(new ResizableArraySegment <byte>(m_columnDefinitionPayloads, m_columnDefinitionPayloadUsedBytes, payloadLength));
ColumnDefinitions[column] = columnDefinition;
ColumnTypes[column] = TypeMapper.ConvertToMySqlDbType(columnDefinition, treatTinyAsBoolean: Connection.TreatTinyAsBoolean, guidFormat: Connection.GuidFormat);
m_columnDefinitionPayloadUsedBytes += payloadLength;
}
if (!Session.SupportsDeprecateEof)
{
payload = await Session.ReceiveReplyAsync(ioBehavior, CancellationToken.None).ConfigureAwait(false);
EofPayload.Create(payload.Span);
}
if (ColumnDefinitions.Length == (Command?.OutParameters?.Count + 1) && ColumnDefinitions[0].Name == SingleCommandPayloadCreator.OutParameterSentinelColumnName)
{
ContainsCommandParameters = true;
}
LastInsertId = -1;
WarningCount = 0;
State = ResultSetState.ReadResultSetHeader;
break;
}
}
}
catch (Exception ex)
{
ReadResultSetHeaderException = ex;
}
finally
{
BufferState = State;
}
}
public override void Run <T>(IEnumerable <T> entities, MySqlTransaction transaction, BulkInsertOptions options)
{
var keepIdentity = (SqlBulkCopyOptions.KeepIdentity & options.SqlBulkCopyOptions) > 0;
using (var reader = new MappedDataReader <T>(entities, this))
{
var csvPath = AppDomain.CurrentDomain.BaseDirectory + System.Guid.NewGuid().ToString() + ".csv";
Dictionary <int, IPropertyMap> propertyMaps = reader.Cols
.Where(x => !x.Value.IsIdentity || keepIdentity)
.ToDictionary(x => x.Key, x => x.Value);
var csv = new StringBuilder();
while (reader.Read())
{
foreach (var kvp in propertyMaps)
{
var value = reader.GetValue(kvp.Key);
if (value == null)
{
csv
.Append("null")
.Append(FieldTerminator);
}
else
{
// todo - escape "'"
csv
.AppendFormat("{0}", value)
.Append(FieldTerminator);
}
}
csv.Append(LineTerminator);
}
// todo - save csv
File.WriteAllText(csvPath, csv.ToString());
// upload csv
var mySqlBulkLoader = new MySqlBulkLoader(transaction.Connection);
//mySqlBulkLoader.TableName = string.Format("[{0}].[{1}]", reader.SchemaName, reader.TableName);
mySqlBulkLoader.TableName = reader.TableName;
mySqlBulkLoader.FieldTerminator = FieldTerminator;
mySqlBulkLoader.LineTerminator = LineTerminator;
mySqlBulkLoader.FileName = csvPath;
foreach (var kvp in propertyMaps)
{
mySqlBulkLoader.Columns.Add(kvp.Value.ColumnName);
}
int count = mySqlBulkLoader.Load();
try
{
File.Delete(csvPath);
}
catch (Exception exception)
{
}
}
}
/// <summary>
/// BulkToDB 批量导入
/// 注意： 日期字符串格式应该是 yyyy-MM-dd HH:mm:ss
/// 对应mysql的默认格式 %Y-%m-%d %H-%i-%s
/// 否则的话，导入的日期有可能是0000-00-00
/// </summary>
/// <param name="constring"></param>
/// <param name="dt"></param>
/// <param name="tarTble"></param>
/// <param name="sErr"></param>
/// <returns></returns>
public static int BulkToDB(string constring, DataTable dt, string tarTble, out string sErr)
{
sErr = string.Empty;
if (DataTableHelper.IsEmptyDataTable(dt))
{
return(0);
}
int nIns = 0;
string tmpPath = Path.GetTempFileName();
var sRes = DataTableHelper.DataTableToCSV(dt);
try
{
File.WriteAllText(tmpPath, sRes);
}
catch (Exception ex)
{
sErr = ex.Message;
return(0);
}
using (var conn = new MySqlConnection(constring))
{
MySqlTransaction tran = null;
try
{
conn.Open();
tran = conn.BeginTransaction();
EnableImport(conn, true);
MySqlBulkLoader bulk = new MySqlBulkLoader(conn)
{
FieldTerminator = ",",
EscapeCharacter = '"',
LineTerminator = "\r\n",
FileName = tmpPath,
NumberOfLinesToSkip = 0,
TableName = tarTble,
Local = true
};
var cols = dt.Columns.Cast <DataColumn>().Select(colum => colum.ColumnName).ToList();
bulk.Columns.AddRange(cols);
nIns = bulk.Load();
tran.Commit();
EnableImport(conn, false);
}
catch (Exception ex)
{
if (tran != null)
{
tran.Rollback();
}
sErr = ex.Message;
}
finally
{
try
{
File.Delete(tmpPath);
}
catch { }
}
}
if (0 == nIns)
{
sErr = "没有导入数据，可能不符合数据库约束";
}
return(nIns);
}
/// <summary>
/// Implementation the specified operation.
/// </summary>
/// <param name="connection">The connection.</param>
/// <param name="transaction">The transaction.</param>
/// <returns>System.Nullable<System.Int32>.</returns>
protected override int?Implementation(MySqlConnection connection, MySqlTransaction?transaction)
{
var bl = new MySqlBulkLoader(connection);
var mappedColumns = SetupBulkCopy(bl);
var lastNotification = 0;
var totalCount = 0;
var rowCount = 0;
var output = new StringBuilder();
while (m_Source.Read())
{
rowCount += 1;
WriteRow(mappedColumns, output);
if (rowCount == m_BatchSize)
{
using (var ms = CreateMemoryStream(output))
{
bl.FileName = null;
bl.SourceStream = ms;
totalCount += bl.Load();
output.Clear();
}
//We only notify after a batch has been posted to the server.
if (m_NotifyAfter.HasValue && m_EventHandler != null)
{
var notificationCount = totalCount % m_NotifyAfter.Value;
if ((totalCount % m_NotifyAfter) > notificationCount)
{
lastNotification = notificationCount;
var e = new AbortableOperationEventArgs(totalCount);
m_EventHandler?.Invoke(this, e);
if (e.Abort)
{
throw new TaskCanceledException("Bulk insert operation aborted.");
}
}
}
rowCount = 0;
}
}
if (rowCount > 0) //final batch
{
using (var ms = CreateMemoryStream(output))
{
bl.FileName = null;
bl.SourceStream = ms;
totalCount += bl.Load();
}
if (m_EventHandler != null)
{
var e = new AbortableOperationEventArgs(totalCount);
m_EventHandler?.Invoke(this, e);
//can't abort at this point;
}
}
return(totalCount);
}
private void button1_Click(object sender, EventArgs e)
{
OpenFileDialog openFile1 = new OpenFileDialog();
openFile1.Filter = "Comma Seperated Files|*.csv";
if (openFile1.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
richTextBox1.LoadFile(openFile1.FileName,
RichTextBoxStreamType.PlainText);
}
int firstVisibleChar = richTextBox1.GetCharIndexFromPosition(new Point(0, 0));
int lineIndex = richTextBox1.GetLineFromCharIndex(firstVisibleChar);
string firstVisibleLine = richTextBox1.Lines[lineIndex];
String[] ColoumnName = firstVisibleLine.Split(',');
string last = ColoumnName[ColoumnName.Length - 1];
using (MySqlConnection dbConnection = new MySqlConnection("server=localhost;uid=root;pwd=root;database=nullvalues;"))
{
dbConnection.Open();
string tabledrop = "drop table if exists table1";
MySqlCommand cmd3 = new MySqlCommand(tabledrop, dbConnection);
cmd3.ExecuteNonQuery();
string sql = "CREATE TABLE table1(abc varchar(40))";
MySqlCommand cmd = new MySqlCommand(sql, dbConnection);
cmd.ExecuteNonQuery();
string drop = "ALTER TABLE table1 CHANGE COLUMN abc " + ColoumnName[0].Replace(" ", "_") + " varchar(40)";
MySqlCommand cmd1 = new MySqlCommand(drop, dbConnection);
cmd1.ExecuteNonQuery();
for (int i = 1; i < ColoumnName.Length; i++)
{
string alter = "ALTER TABLE table1 ADD COLUMN " + ColoumnName[i].Replace(" ", "_") + " varchar(40)";
MySqlCommand cmd2 = new MySqlCommand(alter, dbConnection);
cmd2.ExecuteNonQuery();
}
MySqlBulkLoader s = new MySqlBulkLoader(dbConnection);
s.TableName = "table1";
s.FieldTerminator = ",";
s.LineTerminator = "\n";
s.FileName = openFile1.FileName;
s.NumberOfLinesToSkip = 1;
try
{
int count = s.Load();
Console.WriteLine(count + " lines uploaded.");
dbConnection.Close();
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}
}
//dataGridView1.Dock = DockStyle.Fill;
dataGridView1.AutoGenerateColumns = true;
dataGridView1.DataSource = GetData("Select * From table1");
dataGridView1.AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.DisplayedCellsExceptHeaders;
// this.table1TableAdapter.Fill(this.nullvaluesDataSet.table1);
}
public void InsertItems <T>(IEnumerable <T> items, string schema, string tableName, IList <ColumnMapping> properties, DbConnection storeConnection, int?batchSize, bool isUpdate = false)
{
var itemsToInsert = items as T[] ?? items.ToArray();
var con = storeConnection as MySqlConnection;
const int buffer = 65536;
int batchTimes = batchSize.HasValue ? itemsToInsert.Length / batchSize.Value + 1 : 1;
batchSize = batchTimes == 1 ? itemsToInsert.Length : batchSize;
for (int i = 0; i < batchTimes; i++)
{
//1. write to temp csv file.
var dbColumns = !isUpdate?properties.Where(x => !x.IsPrimaryKey).ToList() : properties.ToList();
string path = $"{Path.GetTempPath()}{Guid.NewGuid()}.csv";
string columns = string.Join(",", dbColumns.Select(x => x.NameInDatabase));
File.WriteAllText(path, $"{columns}{Environment.NewLine}");
var reflectedObjs = new object[dbColumns.Count];
using (var stream = new StreamWriter(path, true, Encoding.UTF8, buffer))
{
foreach (var x in itemsToInsert
.Skip(batchSize.GetValueOrDefault() * i) // Arbitrary
.Take(batchSize.GetValueOrDefault())) // Batching
{
for (var index = 0; index < dbColumns.Count; index++)
{
var columnMapping = dbColumns[index];
reflectedObjs[index] = PropertyCallAdapterProvider <T>
.GetInstance(columnMapping.NameOnObject)
.InvokeGet(x);
var val = reflectedObjs[index]?.ToString();
if (val != null && columnMapping.DataType.Equals("bool", StringComparison.OrdinalIgnoreCase) &&
(val.Equals("False") || val.Equals("True")))
{
reflectedObjs[index] = val.Equals("False") ? 0 : 1;
}
if (columnMapping.DataType.Equals("varchar", StringComparison.OrdinalIgnoreCase) || columnMapping.DataType.Equals("text", StringComparison.OrdinalIgnoreCase))
{
reflectedObjs[index] = $@"""{reflectedObjs[index]}""";
}
if (!columnMapping.DataType.Equals("datetime", StringComparison.OrdinalIgnoreCase))
{
continue;
}
var reflectedObj = reflectedObjs[index];
if (reflectedObj != null)
{
reflectedObjs[index] = ((DateTime)reflectedObj).ToString("yyyy-MM-dd HH:mm:ss.fff");
}
}
stream.WriteLine(string.Join(",", reflectedObjs));
}
}
if (con != null && con.State != ConnectionState.Open)
{
con.Open();
}
//2. bulk import mysql from file
MySqlBulkLoader loader = new MySqlBulkLoader(con)
{
TableName = tableName,
FieldTerminator = ",",
LineTerminator = Environment.NewLine,
FileName = path,
NumberOfLinesToSkip = 1,
FieldQuotationCharacter = '"'
};
// we do not know columns a priori
// and Columns is readonly - so this.
loader.Columns.AddRange(dbColumns.Select(x => x.NameInDatabase).ToList());
loader.Load();
//3. remove temporary file after usage
File.Delete(path);
}
}
public void BulkLoadSimple()
{
execSQL("CREATE TABLE Test (id INT NOT NULL, name VARCHAR(250), PRIMARY KEY(id))");
// first create the external file
string path = Path.GetTempFileName();
StreamWriter sw = new StreamWriter(File.OpenWrite(path));
for (int i = 0; i < 200; i++)
sw.WriteLine(i + "\t'Test'");
sw.Flush();
sw.Dispose();
MySqlBulkLoader loader = new MySqlBulkLoader(conn);
loader.TableName = "Test";
loader.FileName = path;
loader.Timeout = 0;
int count = loader.Load();
Assert.AreEqual(200, count);
}
/// <summary>
///大批量数据插入,返回成功插入行数
public /*static*/ int BulkInsert(string connectionString, DataTable table)
{
//if (table.TableName!="Activity")
//{
// return 0;
//}
StringBuilder sb = new StringBuilder();
sb.AppendFormat("truncate TABLE `{0}`;", table.TableName);
MySqlCommand command = mycon.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = sb.ToString();
command.ExecuteNonQuery();
if (string.IsNullOrEmpty(table.TableName))
{
throw new Exception("请给DataTable的TableName属性附上表名称");
}
if (table.Rows.Count == 0)
{
return(0);
}
int insertCount = 0;
string tmpPath = string.Format("{0}{1}.csv", CSVPATH, table.TableName);
//string csv = DataTableToCsv(table);
//File.WriteAllText(tmpPath, csv);
if (!File.Exists(tmpPath))
{
textBoxSql.AppendText(string.Format("@@@@@@@@@@@@@ error:{0}不存在", tmpPath));
return(-1);
}
using (MySqlConnection conn = new MySqlConnection(connectionString))
{
MySqlTransaction tran = null;
try
{
conn.Open();
tran = conn.BeginTransaction();
MySqlBulkLoader bulk = new MySqlBulkLoader(conn)
{
FieldTerminator = ",",
FieldQuotationCharacter = '"',
EscapeCharacter = '"',
LineTerminator = "\r\n",
FileName = tmpPath,
NumberOfLinesToSkip = 2,
TableName = table.TableName,
};
bulk.Columns.Clear();
List <TBName> tblist = table.Columns.Cast <DataColumn>().Select(colum => colum.ColumnName).ToList();
bulk.Columns.AddRange(table.Columns.Cast <DataColumn>().Select(colum => colum.ColumnName).ToList());
insertCount = bulk.Load();
tran.Commit();
}
catch (MySqlException ex)
{
string ss = "解析数据表出错！tableName: " + table.TableName + " field: " + ex.ToString();
MessageBox.Show(ss);
//if (tran != null) tran.Rollback();
//throw ex;
}
}
//File.Delete(tmpPath);
return(insertCount);
}
/// <summary>
/// MySqlBulkLoader 文件读取数据
/// 要开启数据库 secure_file_priv 允许上传文件 修改mysql.ini文件
/// </summary>
/// <returns></returns>
public async Task TestBulk()
{
//开始时间
var startTime = DateTime.Now;
Console.WriteLine("10w条数据插入开始：");
try
{
await using var conn = GetMySqlConnection();
if (conn.State == ConnectionState.Closed)
{
await conn.OpenAsync();
}
var table = new DataTable();
table.Columns.Add("id", typeof(string));
table.Columns.Add("trade_no", typeof(string));
//生成10万数据
for (var i = 0; i < 100000; i++)
{
if (i % 500000 == 0)
{
table.Rows.Clear();
}
//记录
var row = table.NewRow();
row[0] = Guid.NewGuid().ToString();
row[1] = "trade_" + (i + 1);
table.Rows.Add(row);
//50万条一批次插入
if (i % 500000 != 499999 && i < (100000 - 1))
{
continue;
}
Console.WriteLine("开始插入:" + i);
//数据转换为csv格式
var tradeCsv = DataTableToCsv(table);
var tradeFilePath = System.AppDomain.CurrentDomain.BaseDirectory + "trade.csv";
File.WriteAllText(tradeFilePath, tradeCsv);
#region 保存至数据库
var bulkCopy = new MySqlBulkLoader(conn)
{
FieldTerminator = ",",
FieldQuotationCharacter = '"',
EscapeCharacter = '"',
LineTerminator = "\r\n",
FileName = tradeFilePath,
NumberOfLinesToSkip = 0,
TableName = "trade"
};
bulkCopy.Columns.AddRange(table.Columns.Cast <DataColumn>().Select(colum => colum.ColumnName).ToList());
bulkCopy.Load();
#endregion
}
}
catch (Exception ex)
{
throw;
}
//完成时间
var endTime = DateTime.Now;
//耗时
var spanTime = endTime - startTime;
Console.WriteLine("10w条数据插入结束，方式耗时：" + spanTime.Minutes + "分" + spanTime.Seconds + "秒" + spanTime.Milliseconds + "毫秒");
}
public void Readexceel(string path)
{
//read the exceel before upload the file to db
string conn = string.Empty;
string filePath = path;
DataTable dtExcel = new DataTable();
conn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=Excel 12.0;";
//conn = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='Excel 4.0;HDR=YES';";
OleDbConnection con = new OleDbConnection(conn);
OleDbDataAdapter oleAdpt = new OleDbDataAdapter("select * from [Sheet1$]", con);
oleAdpt.Fill(dtExcel);
if (dtExcel != null && dtExcel.Rows.Count > 0)
{
Displaygrid_Div.Style["display"] = "block";
loadGridView(dtExcel);
}
Session["Rowount"] = dtExcel.Rows.Count;
MySqlConnection sqlcon = new MySqlConnection(MyConnection2);
sqlcon.Open();
MySqlBulkLoader sq = new MySqlBulkLoader(sqlcon);
sq.TableName = "testtable";// to create testtable into hans database
sq.Load();
sqlcon.Close();
#region
// Session["MailType"] = "Multiple";
// working in local
//StringBuilder sb = new StringBuilder();
//string s;
//Excel.Application excelApp = new Excel.Application();
//if (excelApp != null)
//{//@"C:\Users\sanch\source\repos\TestApplication\TestApplication\testfolder\Book1.xlsx"
// Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(path, 0, true, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
// Excel.Worksheet excelWorksheet = (Excel.Worksheet)excelWorkbook.Sheets[1];
// Excel.Range excelRange = excelWorksheet.UsedRange;
// int rowCount = excelRange.Rows.Count;
// int colCount = excelRange.Columns.Count;
// Session["Rowount"] = rowCount;
// Session["MailType"] = "Multiple";
// for (int i = 1; i <= rowCount; i++)
// {
// for (int j = 1; j <= colCount; j++)
// {
// Excel.Range range = (excelWorksheet.Cells[i, 1] as Excel.Range);
// string cellValue = range.Value.ToString();
// sb.Append(cellValue);
// sb.Append(",");
// }
// }
// s = sb.ToString();
// string g = s.TrimEnd(',');
// Session["MultipleMailId"] = g;
// string[] sTwo = g.Split(',');
// DataTable dt = new DataTable();
// dt.Columns.Add("PersonMailID");
// DataRow dr = null;
// for (int i = 0; i < sTwo.Length; i++)
// {
// dr = dt.NewRow();
// dr["PersonMailID"] = sTwo[i];
// dt.Rows.Add(dr);
// }
// loadGridView(dt);
// excelWorkbook.Close();
// excelApp.Quit();
// return s;
//}
//return "";
#endregion
}
private bool InternalExecScript(string table, object script, out uint count)
{
count = 0;
if (script is string sql)
{
return(Execute(sql, null, out count));
}
else if (script is CSVScript obj)
{
try
{
MySqlBulkLoader bulk = new MySqlBulkLoader(conn)
{
Local = true,
FieldTerminator = ",",
FieldQuotationCharacter = '"',
EscapeCharacter = '\\',
LineTerminator = "\n",
FileName = obj.CSVFile,
NumberOfLinesToSkip = 0,
TableName = table,
CharacterSet = "utf8"
};
bulk.Columns.AddRange(obj.Fields);
count = (uint)bulk.Load();
if (count != obj.Count)
{
LastError = $"{table}：写入记录数错误！应写入 {obj.Count}，实际写入 {count}。";
}
return(count == obj.Count);
}
catch (Exception ex)
{
LastError = $"{table}：{ex.Message}";
Logger.WriteLogExcept(LogTitle, ex);
}
}
else if (script is MergeScript ms)
{
if (Execute(ms.PrepareSQL, null, out _))
{
try
{
if (InternalExecScript(ms.TableName, ms.Data, out count))
{
if (Execute(ms.UpdateSQL, null, out _))
{
if (string.IsNullOrEmpty(ms.InsertSQL) || Execute(ms.InsertSQL, null, out _))
{
return(true);
}
}
}
}
finally
{
Execute(ms.CleanSQL, null, out _);
}
}
}
return(false);
}
protected void ImportDataTable(DataTable dt, string tableName, int timeoutAttempts)
{
string tmpFile = System.IO.Path.GetTempFileName();
try
{
System.Text.StringBuilder sb = new System.Text.StringBuilder();
foreach (DataRow row in dt.Rows)
{
IEnumerable <string> fields = row.ItemArray.Select(i => i.GetType() == typeof(DateTime) ? ((DateTime)i).ToString("yyyy-MM-dd HH:mm:ss") : i.ToString());
sb.AppendLine(string.Join("\",\"", fields));
}
System.IO.File.WriteAllText(tmpFile, sb.ToString());
using (MySqlConnection connection = new MySqlConnection(Authentication.ConnectionString))
{
connection.Open();
int retry = timeoutAttempts;
while (retry-- >= 0)
{
try
{
MySqlBulkLoader s = new MySqlBulkLoader(connection);
s.TableName = tableName;
s.FileName = tmpFile;
s.FieldTerminator = ",";
s.FieldQuotationCharacter = '"';
s.Local = true;
s.Load();
break;
}
catch (Exception ex)
{
if (retry <= 0)
{
throw ex;
}
if (ex.ToString().ToLower().Contains("deadlocked") || ex.ToString().ToLower().Contains("timeout"))
{
System.Threading.Thread.Sleep(1000);
}
}
}
}
}
finally
{
try
{
if (File.Exists(tmpFile))
{
File.Delete(tmpFile);
}
}
catch { }
}
}
public MySqlBulkCopyWrapper(MySqlBulkLoader copyTool)
{
_sbc = copyTool;
}
public int BulkInsert <T>(string tableName, IEnumerable <T> values)
{
Type type = typeof(T);
List <TableFieldAttribute> attrs;
if (!_bulkCaches.TryGetValue(type, out attrs))
{
attrs = BuildBulkCache(type);
_bulkCaches[type] = attrs;
}
string seed = Guid.NewGuid().ToString("N");
string path = Environment.CurrentDirectory + "\\" + seed + ".txt";
using (StreamWriter writer = new StreamWriter(path, false)) {
foreach (T value in values)
{
int n = attrs.Count;
for (int i = 0; i < n; i++)
{
var attr = attrs[i];
object obj = attr.Field.GetValue(value);
writer.Write(FormatData(obj, attr.Field.FieldType));
if (i < n - 1)
{
writer.Write(TabSplitter);
}
}
writer.Write(LineSplitter);
}
}
int ret;
MySqlConnection cnn = null;
try {
cnn = GetConnection();
MySqlBulkLoader bulkLoader = new MySqlBulkLoader(cnn);
bulkLoader.TableName = tableName;
bulkLoader.FileName = path;
bulkLoader.ConflictOption = MySqlBulkLoaderConflictOption.Replace;
bulkLoader.FieldTerminator = TabSplitter;
bulkLoader.LineTerminator = LineSplitter;
bulkLoader.EscapeCharacter = '\\';
bulkLoader.Priority = MySqlBulkLoaderPriority.Concurrent;
//
// UTF-8会导致第一行数据丢失, 暂时没有解决办法
// bulkLoader.CharacterSet = "UTF8";
ret = bulkLoader.Load();
} catch (Exception) {
throw;
} finally {
if (cnn != null)
{
cnn.Close();
}
try {
if (File.Exists(path))
{
File.Delete(path);
}
} catch (Exception ex) {
Trace.Write(ex.ToString());
}
}
return(ret);
}
public bool ExecuteBlueCopy()
{
var IsBulkLoad = false;
if (Entitys == null || Entitys.Length <= 0)
{
return(IsBulkLoad);
}
if (Entitys.First() == null && Entitys.Length == 1)
{
return(IsBulkLoad);
}
DataTable dt = new DataTable();
Type type = typeof(T);
var entity = this.Context.EntityMaintenance.GetEntityInfo <T>();
dt.TableName = this.Builder.GetTranslationColumnName(entity.DbTableName);
//创建属性的集合
List <PropertyInfo> pList = new List <PropertyInfo>();
//把所有的public属性加入到集合 并添加DataTable的列
Array.ForEach(entity.Columns.ToArray(), p => {
if (!p.IsIgnore && !p.IsOnlyIgnoreInsert)
{
pList.Add(p.PropertyInfo); dt.Columns.Add(p.PropertyName);
}
});
DataRow row = null;
foreach (T item in Entitys)
{
row = dt.NewRow();
pList.ForEach(p =>
{
var name = p.Name;
if (entity.Columns.Any(it => it.PropertyName == name))
{
name = entity.Columns.First(it => it.PropertyName == name).DbColumnName;
}
row[name] = p.GetValue(item, null);
});
dt.Rows.Add(row);
}
var dllPath = AppDomain.CurrentDomain.BaseDirectory + "failFiles";
DirectoryInfo dir = new DirectoryInfo(dllPath);
if (!dir.Exists)
{
dir.Create();
}
var fileName = dllPath + "\\" + Guid.NewGuid().ToString() + ".csv";
var dataTableToCsv = DataTableToCsvString(dt);
File.WriteAllText(fileName, dataTableToCsv, Encoding.UTF8);
MySqlConnection conn = this.Context.Ado.Connection as MySqlConnection;
try
{
this.Context.Ado.Open();
// IsolationLevel.Parse
MySqlBulkLoader bulk = new MySqlBulkLoader(conn)
{
CharacterSet = GetChara(),
FieldTerminator = ",",
FieldQuotationCharacter = '"',
EscapeCharacter = '"',
LineTerminator = "\r\n",
FileName = fileName,
NumberOfLinesToSkip = 0,
TableName = dt.TableName,
Local = true,
};
bulk.Columns.AddRange(dt.Columns.Cast <DataColumn>().Select(colum => colum.ColumnName).Distinct().ToArray());
IsBulkLoad = bulk.Load() > 0;
//执行成功才删除文件
if (IsBulkLoad && File.Exists(fileName))
{
File.Delete(fileName);
}
}
catch (MySqlException ex)
{
throw ex;
}
finally
{
CloseDb();
}
return(IsBulkLoad);;
}
/// <summary>
/// 根据指定的字段顺序插入表
/// </summary>
/// <param name="tableName"></param>
/// <param name="table"></param>
/// <param name="colNames">字段顺序列</param>
/// <returns></returns>
public int BulkInsert(string tableName, DataTable table, string[] colNames)
{
//比对表字段数否一致
if (table.Columns.Count != colNames.Length)
{
throw new Exception(string.Format("Columns count is not equal,源:{0};目的:{1}", table.TableName, tableName));
}
for (int i = 0; i < colNames.Length; i++)
{
if (!table.Columns.Contains(colNames[i]))
{
throw new Exception(string.Format("DataTable:{0} does not countains Colume:{1}", table.TableName, colNames[i]));
}
}
string seed = Guid.NewGuid().ToString("N");
string path = Environment.CurrentDirectory + "\\" + seed + ".txt";
using (StreamWriter writer = new StreamWriter(path, false))
{
foreach (DataRow row in table.Rows)
{
int n = table.Columns.Count;
for (int i = 0; i < n; i++)
{
string colName = colNames[i];
object obj = row[colName];
writer.Write(FormatData(obj, obj.GetType()));
if (i < n - 1)
{
writer.Write(TabSplitter);
}
}
writer.Write(LineSplitter);
}
}
int ret;
MySqlConnection cnn = null;
try
{
cnn = GetConnection();
MySqlBulkLoader bulkLoader = new MySqlBulkLoader(cnn);
bulkLoader.TableName = tableName;
bulkLoader.FileName = path;
bulkLoader.ConflictOption = MySqlBulkLoaderConflictOption.Replace;
bulkLoader.FieldTerminator = TabSplitter;
bulkLoader.LineTerminator = LineSplitter;
bulkLoader.EscapeCharacter = '\\';
bulkLoader.Priority = MySqlBulkLoaderPriority.Concurrent;
//
// UTF-8会导致第一行数据丢失, 暂时没有解决办法
// bulkLoader.CharacterSet = "UTF8";
ret = bulkLoader.Load();
}
catch (Exception)
{
throw;
}
finally
{
if (cnn != null)
{
cnn.Close();
}
try
{
if (File.Exists(path))
{
File.Delete(path);
}
}
catch (Exception ex)
{
Trace.Write(ex.ToString());
}
}
return(ret);
}
void DoInsertForMySql(DbSource runtimeDatabase, SqlBulkCopyOptions currentOptions, IWarewolfListIterator parametersIteratorCollection, IWarewolfIterator batchItr, IWarewolfIterator timeoutItr, IDSFDataObject dataObject, ErrorResultTO errorResultTo, ErrorResultTO allErrors, ref bool addExceptionToErrorList, int update)
{
MySqlBulkLoader sqlBulkCopy = new MySqlBulkLoader(new MySqlConnection(runtimeDatabase.ConnectionString));
TableName = TableName.Replace("[", "").Replace("]", "");
if (TableName.Contains("."))
{
TableName = TableName.Substring(TableName.IndexOf(".", StringComparison.Ordinal) + 1);
}
if (String.IsNullOrEmpty(BatchSize) && String.IsNullOrEmpty(Timeout))
{
sqlBulkCopy = new MySqlBulkLoader(new MySqlConnection(runtimeDatabase.ConnectionString))
{
TableName = TableName, FieldTerminator = ",", LineTerminator = "\n"
};
}
else
{
while (parametersIteratorCollection.HasMoreData())
{
sqlBulkCopy = SetupMySqlBulkCopy(batchItr, parametersIteratorCollection, timeoutItr, runtimeDatabase, currentOptions);
}
}
if (sqlBulkCopy != null)
{
var dataTableToInsert = BuildDataTableToInsertMySql();
if (InputMappings != null && InputMappings.Count > 0)
{
var iteratorCollection = new WarewolfListIterator();
var listOfIterators = GetIteratorsFromInputMappings(dataObject, iteratorCollection, out errorResultTo, update);
allErrors.MergeErrors(errorResultTo);
// oh no, we have an issue, bubble it out ;)
if (allErrors.HasErrors())
{
addExceptionToErrorList = false;
throw new Exception("Problems with Iterators for SQLBulkInsert");
}
// emit options to debug as per acceptance test ;)
if (dataObject.IsDebugMode())
{
AddBatchSizeAndTimeOutToDebug(dataObject.Environment, update);
AddOptionsDebugItems();
}
FillDataTableWithDataFromDataList(iteratorCollection, dataTableToInsert, listOfIterators);
foreach (var dataColumnMapping in InputMappings)
{
if (!String.IsNullOrEmpty(dataColumnMapping.InputColumn))
{
sqlBulkCopy.Columns.Add(dataColumnMapping.OutputColumn.ColumnName);
}
}
}
// Pass in wrapper now ;)
var wrapper = new MySqlBulkCopyWrapper(sqlBulkCopy);
var inserted = SqlBulkInserter.Insert(wrapper, dataTableToInsert);
var resultString = inserted ? "Success" : "Failure";
dataObject.Environment.Assign(Result, resultString, update);
if (dataObject.IsDebugMode())
{
AddDebugOutputItem(new DebugItemWarewolfAtomResult(resultString, Result, ""));
}
allErrors.MergeErrors(errorResultTo);
if (dataTableToInsert != null)
{
dataTableToInsert.Dispose();
}
}
}
// add item
public int Add(string AutomailFile)
{
var count = 0;
try
{
// connecting
if (_conn.State != ConnectionState.Open)
{
_conn.Open();
}
Console.Write("Connected to database... "); // 3
var check = true;
// Kiểm tra nếu có dữ liệu thì xóa hết
if (CountAll() > 0)
{
//Console.Write("Deleting old data... ");
// delete all data in vnso talbe before This be insert
check = Truncate();
//Console.Write(check+"...");
}
// check is true, get data and insert
if (check)
{
Console.Write("Updating..."); // 4
// Insert data ...
MySqlBulkLoader Loader = new MySqlBulkLoader(_conn);
Loader.CharacterSet = "utf8";
Loader.TableName = _Table; // Vị trí bảng cần lưu
Loader.FieldTerminator = "\t"; // tách cột bằng ký tự "\t" (ký tự tab)
Loader.LineTerminator = "\n"; // tách dòng bằng ký tự "\n" (ký tự xuống dòng)
Loader.FileName = AutomailFile;
Loader.NumberOfLinesToSkip = 0;
Loader.Local = true;
count = Loader.Load();
return(count);
// xóa file sau khi hoàn thành
// File.Delete(DataFile);
}
}
catch (Exception ex)
{
Console.WriteLine("Error: ");
Console.WriteLine(ex.ToString()); // xử lý ngoại lệ
}
finally
{
if (_conn.State == ConnectionState.Open)
{
_conn.Close(); // Close connect
}
}
return(count);
}
/// <summary>
/// 插入mysql
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="tableName"></param>
/// <param name="list"></param>
/// <returns></returns>
public static async Task <bool> InsertAsync <T>(string tableName, IList <T> list) where T : class, new()
{
using (MySqlConnection connection = new MySqlConnection(MY_SQL_CONNECTION))
{
var table = new DataTable()
{
TableName = tableName
};
MySqlTransaction sqlTransaction = null;
try
{
connection.Open();
sqlTransaction = connection.BeginTransaction();
var props = TypeDescriptor.GetProperties(typeof(T))
.Cast <PropertyDescriptor>()
.Where(item => item.PropertyType.Namespace.Equals("System"))
.ToArray();
foreach (var item in props)
{
table.Columns.Add(item.Name, Nullable.GetUnderlyingType(item.PropertyType) ?? item.PropertyType);
}
var values = new object[props.Length];
foreach (var item in list)
{
for (var i = 0; i < values.Length; i++)
{
values[i] = props[i].GetValue(item);
}
table.Rows.Add(values);
}
var fileurl = Path.Combine(Directory.GetCurrentDirectory(), $"Resources/Csv/{DateTime.Now.ToString("D")}/{table.TableName}-{DateTime.Now.ToFileTimeUtc().ToString()}.csv");
table.ToCsv(fileurl);
var columns = table.Columns.Cast <DataColumn>().Select(colum => colum.ColumnName).ToList();
MySqlBulkLoader bulk = new MySqlBulkLoader(connection)
{
FieldTerminator = ",",
FieldQuotationCharacter = '"',
EscapeCharacter = '"',
LineTerminator = "\r\n",
FileName = fileurl,
NumberOfLinesToSkip = 0,
TableName = table.TableName,
};
bulk.Columns.AddRange(columns);
int result = await bulk.LoadAsync(); //https://www.cnblogs.com/doublesnow/p/10562215.html
if (result == list.Count)
{
sqlTransaction.Rollback();
return(false);
}
sqlTransaction.Commit();
return(true);
}
catch (Exception ex)
{
if (sqlTransaction != null)
{
sqlTransaction.Rollback();
}
return(false);
}
}
}
/// <summary>
/// 使用Bulk批量插入数据（适合大数据量，速度非常快）
/// </summary>
/// <typeparam name="T">实体类型</typeparam>
/// <param name="entities">数据</param>
public override void BulkInsert <T>(List <T> entities)
{
DataTable dt = entities.ToDataTable();
using (MySqlConnection conn = new MySqlConnection())
{
conn.ConnectionString = ConnectionString;
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
string tableName = string.Empty;
var tableAttribute = typeof(T).GetCustomAttributes(typeof(TableAttribute), true).FirstOrDefault();
if (tableAttribute != null)
{
tableName = ((TableAttribute)tableAttribute).Name;
}
else
{
tableName = typeof(T).Name;
}
int insertCount = 0;
string tmpPath = Path.Combine(Path.GetTempPath(),
DateTime.Now.ToCstTime().Ticks.ToString() + "_" + Guid.NewGuid().ToString() + ".tmp");
string csv = dt.ToCsvStr();
File.WriteAllText(tmpPath, csv, Encoding.UTF8);
using (MySqlTransaction tran = conn.BeginTransaction())
{
MySqlBulkLoader bulk = new MySqlBulkLoader(conn)
{
FieldTerminator = ",",
FieldQuotationCharacter = '"',
EscapeCharacter = '"',
LineTerminator = "\r\n",
FileName = tmpPath,
NumberOfLinesToSkip = 0,
TableName = tableName
};
try
{
bulk.Columns.AddRange(dt.Columns.Cast <DataColumn>().Select(col => col.ColumnName).ToList());
insertCount = bulk.Load();
tran.Commit();
}
catch (MySqlException ex)
{
if (tran != null)
{
tran.Rollback();
}
throw ex;
}
}
File.Delete(tmpPath);
}
}
public override bool Import(DataTable Data, ODAParameter[] Prms)
{
bool HaveBlob = false;
for (int k = 0; k < Prms.Length; k++)
{
if (Prms[k].DBDataType == ODAdbType.OBinary)
{
HaveBlob = true;
break;
}
}
if (HaveBlob)
{
return(base.Import(Data, Prms));
}
int ImportCount = 0;
MySqlConnection conn = null;
DataTable ImportData = Data.Copy();
string tmpPath = Path.GetTempFileName();
try
{
MySqlBulkLoader bulk = null;
if (this.Transaction != null)
{
bulk = new MySqlBulkLoader((MySqlConnection)this.Transaction.Connection);
}
else
{
conn = (MySqlConnection)this.GetConnection();
bulk = new MySqlBulkLoader((MySqlConnection)conn);
}
bool noCol = true;
for (int m = 0; m < Prms.Length; m++)
{
noCol = true;
for (int n = 0; n < ImportData.Columns.Count; n++)
{
if (Prms[m].ColumnName == ImportData.Columns[n].ColumnName)
{
noCol = false;
break;
}
}
if (noCol)
{
ImportData.Columns.Add(new DataColumn(Prms[m].ColumnName));
}
ImportData.Columns[Prms[m].ColumnName].SetOrdinal(m);///对DataTable字段排序
bulk.Columns.Add(Prms[m].ParamsName);
}
string csv = DataTableToCsv(ImportData, Prms.Length);
File.WriteAllText(tmpPath, csv);
bulk.FieldTerminator = ",";
bulk.FieldQuotationCharacter = '"';
bulk.EscapeCharacter = '"';
bulk.LineTerminator = "\r\n";
bulk.FileName = tmpPath;
bulk.NumberOfLinesToSkip = 0;
bulk.TableName = ImportData.TableName;
ImportCount = bulk.Load();
return(ImportCount > 0);
}
finally
{
if (conn != null)
{
conn.Close();
conn.Dispose();
}
if (File.Exists(tmpPath))
{
File.Delete(tmpPath);
}
}
}
/// <summary>
/// MySQL 大批量数据插入,返回成功插入行数
/// </summary>
/// <param name="table">数据表</param>
/// <returns>返回成功插入行数</returns>
private int MySqlBulkInsert(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();
File.WriteAllText(tmpPath, csv);
var connectionString = _dbContext.Database.Connection.ConnectionString;
using (MySqlConnection conn = new MySqlConnection(connectionString))
{
MySqlTransaction tran = null;
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 <DataColumn>().Select(colum => colum.ColumnName).ToArray());
insertCount = bulk.Load();
tran.Commit();
}
catch
{
tran?.Rollback();
return(0);
}
}
File.Delete(tmpPath);
return(insertCount);
string DataTableToCsv()
{
//以半角逗号（即,）作分隔符，列为空也要表达其存在。
//列内容如存在半角逗号（即,）则用半角引号（即""）将该字段值包含起来。
//列内容如存在半角引号（即"）则应替换成半角双引号（""）转义，并用半角引号（即""）将该字段值包含起来。
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());
}
}
