protected virtual bool OnRowsInserted(BulkCopyEventArgs e)
{
bool hasErrors = false;
bool isComplete = true;
foreach (BulkLoadJob job in this)
{
if (job.Tasks.State == CopyEventType.Error)
{
hasErrors = true;
}
if (!(job.Tasks.State == CopyEventType.Error || job.Tasks.State == CopyEventType.Complete))
{
isComplete = false;
}
}
_state = isComplete ? hasErrors ? CopyEventType.Error : CopyEventType.Complete : CopyEventType.Active;
if (RowsInserted != null)
{
RowsInserted(null, e);
}
if (_state == CopyEventType.Complete || _state == CopyEventType.Error)
{
OnComplete();
}
return(e.Abort);
}
public MsSqlBulkCopy(string connectionString, SqlBulkCopyOptions options)
{
_inner = new SqlBulkCopy(connectionString, options);
_inner.SqlRowsCopied += (s, e) =>
{
BulkCopyEventArgs args = new BulkCopyEventArgs {Count = e.RowsCopied, Type = CopyEventType.Active};
OnRowsInserted(args);
e.Abort = args.Abort;
};
}
public MsSqlBulkCopy(string connectionString, SqlBulkCopyOptions options)
{
_inner = new SqlBulkCopy(connectionString, options);
_inner.SqlRowsCopied += (s, e) =>
{
BulkCopyEventArgs args = new BulkCopyEventArgs {
Count = e.RowsCopied, Type = CopyEventType.Active
};
OnRowsInserted(args);
e.Abort = args.Abort;
};
}
private void UpdateTaskItem(BulkCopyTask task, ListViewItem listViewItem, BulkCopyEventArgs args)
{
args.Abort = _abort;
listViewItem.SubItems[2].Text = task.Count.ToString();
listViewItem.SubItems[3].Text = task.State.ToString();
listViewItem.SubItems[4].Text = args.Message;
switch (args.Type)
{
case CopyEventType.Active:
listViewItem.BackColor = Color.GhostWhite;
listViewItem.ForeColor = Color.DarkGreen;
break;
case CopyEventType.Processing:
listViewItem.BackColor = Color.LightCyan;
listViewItem.ForeColor = Color.DarkBlue;
break;
case CopyEventType.Complete:
if (_abort)
{
listViewItem.BackColor = Color.Pink;
listViewItem.ForeColor = Color.DarkRed;
listViewItem.SubItems[4].Text = "Aborted";
}
else
{
listViewItem.BackColor = Color.LightGreen;
listViewItem.ForeColor = Color.DarkGreen;
}
break;
case CopyEventType.Error:
listViewItem.BackColor = Color.Pink;
listViewItem.ForeColor = Color.DarkRed;
break;
default:
listViewItem.BackColor = Color.Transparent;
listViewItem.ForeColor = Color.Black;
break;
}
}
public override void WriteToServer(IDataReader reader)
{
const string insertFormat = "insert into {0}_tmp ({1}) values ({2});";
string path = Path.Combine(_source, _schema + "." + _table + ".db3");
if (File.Exists(path))
{
File.Delete(path);
}
string connectionString = string.Format("Data Source={0};Version=3;New=True;", path);
int reportCounter = 0;
int totalRecords = 0;
bool finished = false;
using (SQLiteConnection connection = new SQLiteConnection(connectionString))
{
connection.Open();
_command.Connection = connection;
_command.CommandText = "pragma journal_mode = off;pragma foreign_keys=off;";
_command.ExecuteNonQuery();
_command.CommandText =
Common.GetTextResource("Loaders\\Scripts\\sqlite-tmp.sql", Assembly.GetExecutingAssembly());
_command.ExecuteNonQuery();
string names = string.Join(",",
_command.Parameters.Cast <SQLiteParameter>().Select(p => p.ParameterName).
ToArray());
string vals = string.Join(",", _command.Parameters.Cast <object>().Select(p => "?").ToArray());
_command.CommandText = string.Format(insertFormat, _table, names, vals);
_command.Connection = connection;
while (!finished)
{
using (DbTransaction dbTrans = connection.BeginTransaction())
{
for (int i = 0; i < BatchSize; i++)
{
if (!reader.Read())
{
finished = true;
break;
}
try
{
for (int p = 0; p < _command.Parameters.Count; p++)
{
_command.Parameters[p].Value = reader.GetValue(p);
}
_command.ExecuteNonQuery();
}
catch (Exception ex)
{
Trace.WriteLine(ex.Message);
throw;
}
reportCounter++;
totalRecords++;
if (reportCounter >= NotifyAfter)
{
reportCounter = 0;
BulkCopyEventArgs args = new BulkCopyEventArgs
{
Count = totalRecords, Type = CopyEventType.Active
};
OnRowsInserted(args);
if (args.Abort)
{
finished = true;
break;
}
}
}
dbTrans.Commit();
}
}
OnRowsInserted(new BulkCopyEventArgs {
Count = totalRecords, Type = CopyEventType.Complete
});
}
}
public override void WriteToServer(IDataReader reader)
{
const string insertFormat = "insert into `{3}`.`{0}` ({1}) values ({2});";
string names = string.Join(",",
_command.Parameters.Cast<MySqlParameter>().Select(p => p.ParameterName).ToArray());
string vals = string.Join(",",
_command.Parameters.Cast<MySqlParameter>().Select(p => "?" + p.ParameterName).
ToArray());
_command.CommandText = string.Format(insertFormat, _table, names, vals, _schema);
int reportCounter = 0;
int totalRecords = 0;
bool finished = false;
using (MySqlConnection connection = new MySqlConnection(_source))
{
connection.Open();
_command.Connection = connection;
_command.Prepare();
while (!finished)
{
using (MySqlTransaction dbTrans = connection.BeginTransaction(IsolationLevel.ReadUncommitted))
{
for (int i = 0; i < BatchSize; i++)
{
if (!reader.Read())
{
finished = true;
break;
}
try
{
for (int p = 0; p < _command.Parameters.Count; p++)
{
_command.Parameters[p].Value = reader.GetValue(p);
}
_command.ExecuteNonQuery();
}
catch (Exception ex)
{
Trace.WriteLine(ex.Message);
throw;
}
reportCounter++;
totalRecords++;
if (reportCounter >= NotifyAfter)
{
reportCounter = 0;
BulkCopyEventArgs args = new BulkCopyEventArgs
{Count = totalRecords, Type = CopyEventType.Active};
OnRowsInserted(args);
if (args.Abort)
{
finished = true;
break;
}
}
}
dbTrans.Commit();
}
}
}
}
public override void WriteToServer(IDataReader reader)
{
const string insertFormat = "insert into `{3}`.`{0}` ({1}) values ({2});";
string names = string.Join(",",
_command.Parameters.Cast <MySqlParameter>().Select(p => p.ParameterName).ToArray());
string vals = string.Join(",",
_command.Parameters.Cast <MySqlParameter>().Select(p => "?" + p.ParameterName).
ToArray());
_command.CommandText = string.Format(insertFormat, _table, names, vals, _schema);
int reportCounter = 0;
int totalRecords = 0;
bool finished = false;
using (MySqlConnection connection = new MySqlConnection(_source))
{
connection.Open();
_command.Connection = connection;
_command.Prepare();
while (!finished)
{
using (MySqlTransaction dbTrans = connection.BeginTransaction(IsolationLevel.ReadUncommitted))
{
for (int i = 0; i < BatchSize; i++)
{
if (!reader.Read())
{
finished = true;
break;
}
try
{
for (int p = 0; p < _command.Parameters.Count; p++)
{
_command.Parameters[p].Value = reader.GetValue(p);
}
_command.ExecuteNonQuery();
}
catch (Exception ex)
{
Trace.WriteLine(ex.Message);
throw;
}
reportCounter++;
totalRecords++;
if (reportCounter >= NotifyAfter)
{
reportCounter = 0;
BulkCopyEventArgs args = new BulkCopyEventArgs
{
Count = totalRecords, Type = CopyEventType.Active
};
OnRowsInserted(args);
if (args.Abort)
{
finished = true;
break;
}
}
}
dbTrans.Commit();
}
}
}
}
public override void WriteToServer(IDataReader reader)
{
const string insertFormat = "insert into {0}_tmp ({1}) values ({2});";
string path = Path.Combine(_source, _schema + "." + _table + ".db3");
if (File.Exists(path))
{
File.Delete(path);
}
string connectionString = string.Format("Data Source={0};Version=3;New=True;", path);
int reportCounter = 0;
int totalRecords = 0;
bool finished = false;
using (SQLiteConnection connection = new SQLiteConnection(connectionString))
{
connection.Open();
_command.Connection = connection;
_command.CommandText = "pragma journal_mode = off;pragma foreign_keys=off;";
_command.ExecuteNonQuery();
_command.CommandText =
Common.GetTextResource("Loaders\\Scripts\\sqlite-tmp.sql", Assembly.GetExecutingAssembly());
_command.ExecuteNonQuery();
string names = string.Join(",",
_command.Parameters.Cast<SQLiteParameter>().Select(p => p.ParameterName).
ToArray());
string vals = string.Join(",", _command.Parameters.Cast<object>().Select(p => "?").ToArray());
_command.CommandText = string.Format(insertFormat, _table, names, vals);
_command.Connection = connection;
while (!finished)
{
using (DbTransaction dbTrans = connection.BeginTransaction())
{
for (int i = 0; i < BatchSize; i++)
{
if (!reader.Read())
{
finished = true;
break;
}
try
{
for (int p = 0; p < _command.Parameters.Count; p++)
{
_command.Parameters[p].Value = reader.GetValue(p);
}
_command.ExecuteNonQuery();
}
catch (Exception ex)
{
Trace.WriteLine(ex.Message);
throw;
}
reportCounter++;
totalRecords++;
if (reportCounter >= NotifyAfter)
{
reportCounter = 0;
BulkCopyEventArgs args = new BulkCopyEventArgs
{Count = totalRecords, Type = CopyEventType.Active};
OnRowsInserted(args);
if (args.Abort)
{
finished = true;
break;
}
}
}
dbTrans.Commit();
}
}
}
}
protected virtual bool OnRowsInserted(object sender, BulkCopyEventArgs e)
{
return(OnRowsInserted(e));
}
public override void WriteToServer(IDataReader reader)
{
const string insertFormat = "insert into `{3}`.`{0}` ({1}) values ({2});";
string names = string.Join(",",
_command.Parameters.Cast <MySqlParameter>().Select(p => p.ParameterName).ToArray());
string vals = string.Join(",",
_command.Parameters.Cast <MySqlParameter>().Select(p => "?" + p.ParameterName).
ToArray());
_command.CommandText = string.Format(insertFormat, _table, names, vals, _schema);
int reportCounter = 0;
int totalRecords = 0;
bool finished = false;
using (MySqlConnection connection = new MySqlConnection(_source))
{
connection.Open();
string oldMode;
using (var modeCommand = connection.CreateCommand())
{
modeCommand.CommandText = "SELECT @@SESSION.sql_mode";
oldMode = modeCommand.ExecuteScalar() as string;
}
using (var setModeCommand = connection.CreateCommand())
{
var newMode = string.IsNullOrWhiteSpace(oldMode)
? "NO_AUTO_VALUE_ON_ZERO"
: $"{oldMode},NO_AUTO_VALUE_ON_ZERO";
setModeCommand.CommandText = "SET SESSION sql_mode = @NewMode";
setModeCommand.Parameters.AddWithValue("@NewMode", newMode);
setModeCommand.ExecuteNonQuery();
}
_command.Connection = connection;
_command.Prepare();
while (!finished)
{
using (MySqlTransaction dbTrans = connection.BeginTransaction(IsolationLevel.ReadUncommitted))
{
for (int i = 0; i < BatchSize; i++)
{
if (!reader.Read())
{
finished = true;
break;
}
try
{
for (int p = 0; p < _command.Parameters.Count; p++)
{
_command.Parameters[p].Value = reader.GetValue(p);
}
_command.ExecuteNonQuery();
}
catch (Exception ex)
{
Trace.WriteLine(ex.Message);
throw;
}
reportCounter++;
totalRecords++;
if (reportCounter >= NotifyAfter)
{
reportCounter = 0;
BulkCopyEventArgs args = new BulkCopyEventArgs
{
Count = totalRecords, Type = CopyEventType.Active
};
OnRowsInserted(args);
if (args.Abort)
{
finished = true;
break;
}
}
}
dbTrans.Commit();
}
}
OnRowsInserted(new BulkCopyEventArgs {
Count = totalRecords, Type = CopyEventType.Complete
});
}
}