public void ExecuteBulkCopyAsync_ShouldCreateNewRows_WhenUsingEvents()
{
// Arrange
DataTable dataTable = GetDataToInsert();
// Act
BulkCopyResult result = SUT.BuildCommand()
.ForBulkCopy()
.From(dataTable)
.Into("[dbo].[SampleTable]")
.Mapping(map => map.UseAutoMap())
.Events(events => events.NotifyAfter(10).OnRowsCopied((sender, e) =>
{
var sqlRowsCopiedEventArgs = (SqlRowsCopiedEventArgs)e;
WriteLine($"Total rows copied: {sqlRowsCopiedEventArgs.RowsCopied}");
}))
.Execute();
// Assert
result.ShouldNotBeNull();
result.RowCountCopied.ShouldBe(RowCount);
// Print
WriteLine(RowCount);
}
public void ExecuteBulkCopy_ShouldCreateNewRows_WhenUsingPartialMapper()
{
// Arrange
DataTable dataTable = GetDataToInsert();
// Alter the DataTable to simulate a source where the SampleVarChar field is named something different
dataTable.Columns["SampleVarChar"].ColumnName = "SampleString";
// Now specify the mapping
var columnMapping = new ColumnMapping
{
ColumnMaps = new List <ColumnMap>
{
new ColumnMap("SampleString", "SampleVarChar")
}
};
// Act
BulkCopyResult result = SUT.BuildCommand()
.ForBulkCopy()
.From(dataTable)
.Into("[dbo].[SampleTable]")
.Mapping(opt => opt.UsePartialMap(columnMapping))
.Execute();
// Assert
result.ShouldNotBeNull();
result.RowCountCopied.ShouldBe(RowCount);
// Print
WriteLine(RowCount);
}
public async Task BulkCopyUsingPartialMap()
{
DataTable dataTable = GetDataToInsert();
// Alter the DataTable to simulate a source where the SampleVarChar field is named something different
dataTable.Columns["SampleVarChar"].ColumnName = "SampleString";
// Bulk Copy
BulkCopyResult result = await _databaseCommander.BuildCommand()
.ForBulkCopy()
.From(dataTable)
.Into("[dbo].[SampleTable]")
.Mapping(mapping => mapping.UsePartialMap(new ColumnMapping(new List <ColumnMap>
{
new ColumnMap
{
Source = "SampleString",
Destination = "SampleVarChar"
}
})))
.ExecuteAsync(new CancellationToken());
int rowCountCopied = result.RowCountCopied;
Console.WriteLine("Row count copied: {0}", rowCountCopied);
}
public async Task BulkCopyUsingAllApis()
{
DataTable dataTable = GetDataToInsert();
// Alter the DataTable to simulate a source where the SampleVarChar field is named something different
dataTable.Columns["SampleVarChar"].ColumnName = "SampleString";
BulkCopyResult result = await _databaseCommander.BuildCommand()
.ForBulkCopy <SampleEntity>()
.From(dataTable, DataRowState.Added)
.Into("[dbo].[SampleTable]")
.BatchSize(100)
.Timeout(TimeSpan.FromSeconds(30))
.Options(options => options.KeepNulls().CheckConstraints().TableLock(false).OpenConnectionWithoutRetry())
.Mapping(mapping => mapping.UsePartialMap(entity =>
{
entity.Property(e => e.SampleVarChar).MapFrom("SampleString");
}))
.OrderHints(hints => hints.Build(entity =>
{
entity.Property(e => e.SampleInt).OrderByDescending();
}))
.Events(events => events.NotifyAfter(10).OnRowsCopied((sender, e) =>
{
var sqlRowsCopiedEventArgs = (SqlRowsCopiedEventArgs)e;
Console.WriteLine($"Total rows copied: {sqlRowsCopiedEventArgs.RowsCopied}");
}))
.ExecuteAsync(new CancellationToken());
int rowCountCopied = result.RowCountCopied;
Console.WriteLine("Row count copied: {0}", rowCountCopied);
}
public async Task BulkCopyUsingStronglyTypedMap()
{
DataTable dataTable = GetDataToInsert();
// Alter the DataTable to simulate a source where all column names are different than the destination
dataTable.Columns["SampleInt"].ColumnName = "Column1";
dataTable.Columns["SampleSmallInt"].ColumnName = "Column2";
dataTable.Columns["SampleTinyInt"].ColumnName = "Column3";
dataTable.Columns["SampleBit"].ColumnName = "Column4";
dataTable.Columns["SampleDecimal"].ColumnName = "Column5";
dataTable.Columns["SampleFloat"].ColumnName = "Column6";
dataTable.Columns["SampleVarChar"].ColumnName = "Column7";
// Bulk Copy
BulkCopyResult result = await _databaseCommander.BuildCommand()
.ForBulkCopy <SampleEntity>()
.From(dataTable)
.Into("[dbo].[SampleTable]")
.Mapping(mapping => mapping.UseMap(entity =>
{
entity.Property(e => e.SampleInt).MapFrom("Column1");
entity.Property(e => e.SampleSmallInt).MapFrom("Column2");
entity.Property(e => e.SampleTinyInt).MapFrom("Column3");
entity.Property(e => e.SampleBit).MapFrom("Column4");
entity.Property(e => e.SampleDecimal).MapFrom("Column5");
entity.Property(e => e.SampleFloat).MapFrom("Column6");
entity.Property(e => e.SampleVarChar).MapFrom("Column7");
}))
.ExecuteAsync(new CancellationToken());
int rowCountCopied = result.RowCountCopied;
Console.WriteLine("Row count copied: {0}", rowCountCopied);
}
public async Task BulkCopyUsingAutoMapping()
{
DataTable dataTable = GetDataToInsert();
BulkCopyResult result = await _databaseCommander.BuildCommand()
.ForBulkCopy()
.From(dataTable)
.Into("[dbo].[SampleTable]")
.Mapping(mapping => mapping.UseAutoMap())
.ExecuteAsync(new CancellationToken());
int rowCountCopied = result.RowCountCopied;
Console.WriteLine("Row count copied: {0}", rowCountCopied);
}
public void ExecuteBulkCopy_ShouldCreateNewRows_WhenUsingMap()
{
// Arrange
DataTable dataTable = GetDataToInsert();
// Alter the DataTable to simulate a source where all column names are different than the destination
dataTable.Columns["SampleInt"].ColumnName = "Column1";
dataTable.Columns["SampleSmallInt"].ColumnName = "Column2";
dataTable.Columns["SampleTinyInt"].ColumnName = "Column3";
dataTable.Columns["SampleBit"].ColumnName = "Column4";
dataTable.Columns["SampleDecimal"].ColumnName = "Column5";
dataTable.Columns["SampleFloat"].ColumnName = "Column6";
dataTable.Columns["SampleVarChar"].ColumnName = "Column7";
// Now specify the mapping
var columnMapping = new ColumnMapping
{
ColumnMaps = new List <ColumnMap>
{
new ColumnMap("Column1", "SampleInt"),
new ColumnMap("Column2", "SampleSmallInt"),
new ColumnMap("Column3", "SampleTinyInt"),
new ColumnMap("Column4", "SampleBit"),
new ColumnMap("Column5", "SampleDecimal"),
new ColumnMap("Column6", "SampleFloat"),
new ColumnMap("Column7", "SampleVarChar"),
}
};
// Act
BulkCopyResult result = SUT.BuildCommand()
.ForBulkCopy()
.From(dataTable)
.Into("[dbo].[SampleTable]")
.Mapping(opt => opt.UseMap(columnMapping))
.Execute();
// Assert
result.ShouldNotBeNull();
result.RowCountCopied.ShouldBe(RowCount);
// Print
WriteLine(RowCount);
}
public void ExecuteBulkCopy_ShouldCreateNewRows_WhenUsingAutoMapper()
{
// Arrange
DataTable dataTable = GetDataToInsert();
// Act
BulkCopyResult result = SUT.BuildCommand()
.ForBulkCopy()
.From(dataTable)
.Into("[dbo].[SampleTable]")
.Mapping(opt => opt.UseAutoMap())
.Execute();
// Assert
result.ShouldNotBeNull();
result.RowCountCopied.ShouldBe(RowCount);
// Print
WriteLine(RowCount);
}
private void bw_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
{
if (TaskbarManager.IsPlatformSupported)
{
TaskbarManager.Instance.SetProgressState(TaskbarProgressBarState.NoProgress,this.Handle);
TaskbarManager.Instance.SetProgressValue(0,100,this.Handle);
}
timer1.Enabled = false;
lblTimer.Text = "Elapsed: 00:00:00";
lblRowPrg.Text = String.Empty;
pbRow.Value = 0;
_copying = false;
_cancelled = false;
wizardControl1.NextButtonEnabled = true;
wizardControl1.BackButtonEnabled = true;
if (e.Cancelled)
{
lblOverallPrg.Text = "Cancelled by the user";
lblTotal.Text = String.Empty;
return;
}
else if (e.Error == null)
{
lblOverallPrg.Text = "Completed";
}
if (e.Error != null)
{
lblOverallPrg.Text = "Terminated with errors";
lblTotal.Text = String.Empty;
GenericErrorDialog.ShowError("Bulk Copy Error", "Bulk copy terminated because the following error occured.", e.Error);
return;
}
BulkCopyResult result = e.Result as BulkCopyResult;
DisplayErrors(result.Errors);
}
public void ExecuteBulkCopyAsync_ShouldCreateNewRows_WhenUsingOrderHints()
{
// Arrange
DataTable dataTable = GetDataToInsert();
// Act
BulkCopyResult result = SUT.BuildCommand()
.ForBulkCopy()
.From(dataTable)
.Into("[dbo].[SampleTable]")
.Mapping(map => map.UseAutoMap())
.OrderHints(hints => hints.OrderBy("SampleInt").OrderByDescending("SampleSmallInt"))
.Execute();
// Assert
result.ShouldNotBeNull();
result.RowCountCopied.ShouldBe(RowCount);
// Print
WriteLine(RowCount);
}
public void ExecuteBulkCopyAsync_ShouldCreateNewRows_WhenUsingOptions()
{
// Arrange
DataTable dataTable = GetDataToInsert();
// Act
BulkCopyResult result = SUT.BuildCommand()
.ForBulkCopy()
.From(dataTable)
.Into("[dbo].[SampleTable]")
.Mapping(map => map.UseAutoMap())
.Options(options => options.KeepNulls().CheckConstraints().TableLock(false))
.Execute();
// Assert
result.ShouldNotBeNull();
result.RowCountCopied.ShouldBe(RowCount);
// Print
WriteLine(RowCount);
}
public async Task BulkCopyUsingEvents()
{
DataTable dataTable = GetDataToInsert();
BulkCopyResult result = await _databaseCommander.BuildCommand()
.ForBulkCopy()
.From(dataTable)
.Into("[dbo].[SampleTable]")
.Mapping(mapping => mapping.UseAutoMap())
.Events(events => events.NotifyAfter(10).OnRowsCopied((sender, e) =>
{
var sqlRowsCopiedEventArgs = (SqlRowsCopiedEventArgs)e;
Console.WriteLine($"Total rows copied: {sqlRowsCopiedEventArgs.RowsCopied}");
}))
.ExecuteAsync(new CancellationToken());
int rowCountCopied = result.RowCountCopied;
Console.WriteLine("Row count copied: {0}", rowCountCopied);
}
public void ExecuteBulkCopyAsync_ShouldCreateNewRows_WhenUsingAllApis()
{
// Arrange
DataTable dataTable = GetDataToInsert();
// Alter the DataTable to simulate a source where the SampleVarChar field is named something different
dataTable.Columns["SampleVarChar"].ColumnName = "SampleString";
// Act
BulkCopyResult result = SUT.BuildCommand()
.ForBulkCopy <SampleEntity>()
.From(dataTable, DataRowState.Added)
.Into("[dbo].[SampleTable]")
.Options(options => options.KeepNulls().CheckConstraints().TableLock(false).OpenConnectionWithoutRetry())
.OrderHints(hints => hints.OrderBy("SampleInt").OrderByDescending("SampleSmallInt"))
.BatchSize(100)
.Timeout(TimeSpan.FromSeconds(30))
.Mapping(mapping => mapping.UsePartialMap(sample =>
{
sample.Property(s => s.SampleVarChar).MapFrom("SampleString");
}))
.Events(events => events.NotifyAfter(10).OnRowsCopied((sender, e) =>
{
var sqlRowsCopiedEventArgs = (SqlRowsCopiedEventArgs)e;
WriteLine($"Total rows copied: {sqlRowsCopiedEventArgs.RowsCopied}");
}))
.OrderHints(hints => hints.Build(entity =>
{
entity.Property(e => e.SampleInt).OrderByDescending();
}))
.Execute();
// Assert
result.ShouldNotBeNull();
result.RowCountCopied.ShouldBe(RowCount);
// Print
WriteLine(RowCount);
}
public async Task BulkCopyUsingMap()
{
DataTable dataTable = GetDataToInsert();
// Alter the DataTable to simulate a source where all column names are different than the destination
dataTable.Columns["SampleInt"].ColumnName = "Column1";
dataTable.Columns["SampleSmallInt"].ColumnName = "Column2";
dataTable.Columns["SampleTinyInt"].ColumnName = "Column3";
dataTable.Columns["SampleBit"].ColumnName = "Column4";
dataTable.Columns["SampleDecimal"].ColumnName = "Column5";
dataTable.Columns["SampleFloat"].ColumnName = "Column6";
dataTable.Columns["SampleVarChar"].ColumnName = "Column7";
// Now specify the mapping
var columnMapping = new ColumnMapping
{
ColumnMaps = new List <ColumnMap>
{
new ColumnMap("Column1", "SampleInt"),
new ColumnMap("Column2", "SampleSmallInt"),
new ColumnMap("Column3", "SampleTinyInt"),
new ColumnMap("Column4", "SampleBit"),
new ColumnMap("Column5", "SampleDecimal"),
new ColumnMap("Column6", "SampleFloat"),
new ColumnMap("Column7", "SampleVarChar"),
}
};
// Bulk Copy
BulkCopyResult result = await _databaseCommander.BuildCommand()
.ForBulkCopy()
.From(dataTable)
.Into("[dbo].[SampleTable]")
.Mapping(mapping => mapping.UseMap(columnMapping))
.ExecuteAsync(new CancellationToken());
int rowCountCopied = result.RowCountCopied;
Console.WriteLine("Row count copied: {0}", rowCountCopied);
}
public async Task ExecuteBulkCopyAsync_ShouldCreateNewRows_WhenUsingStronglyTypedMap()
{
// Arrange
int expectedCount = ExecuteScalar <int>("SELECT COUNT(1) FROM [dbo].[SampleTable]") + RowCount;
DataTable dataTable = GetDataToInsert();
// Alter the DataTable to simulate a source where all column names are different than the destination
dataTable.Columns["SampleInt"].ColumnName = "Column1";
dataTable.Columns["SampleSmallInt"].ColumnName = "Column2";
dataTable.Columns["SampleTinyInt"].ColumnName = "Column3";
dataTable.Columns["SampleBit"].ColumnName = "Column4";
dataTable.Columns["SampleDecimal"].ColumnName = "Column5";
dataTable.Columns["SampleFloat"].ColumnName = "Column6";
dataTable.Columns["SampleVarChar"].ColumnName = "Column7";
// Act
BulkCopyResult result = await SUT.BuildCommand()
.ForBulkCopy <SampleEntity>()
.From(dataTable)
.Into("[dbo].[SampleTable]")
.Mapping(mapping => mapping.UseMap(sample =>
{
sample.Property(s => s.SampleInt).MapFrom("Column1");
sample.Property(s => s.SampleSmallInt).MapFrom("Column2");
sample.Property(s => s.SampleTinyInt).MapFrom("Column3");
sample.Property(s => s.SampleBit).MapFrom("Column4");
sample.Property(s => s.SampleDecimal).MapFrom("Column5");
sample.Property(s => s.SampleFloat).MapFrom("Column6");
sample.Property(s => s.SampleVarChar).MapFrom("Column7");
}))
.ExecuteAsync(new CancellationToken());
// Assert
result.ShouldNotBeNull();
result.RowCountCopied.ShouldBe(RowCount);
// Print
WriteLine(expectedCount);
}
private void bw_DoWork(object sender, DoWorkEventArgs e)
{
BulkCopyArgs args = e.Argument as BulkCopyArgs;
if (args == null)
return;
BulkCopyResult result = new BulkCopyResult();
ProgressData pd = new ProgressData();
pd.Total = args.Objects.Count;
SqlTransaction tr = null;
string copyErrors = String.Empty;
bool copyCancelled = false;
SqlConnection sourceConn = null;
SqlConnection destConn = null;
try
{
sourceConn = _cp.CreateSqlConnection(true, false);
destConn = _cpDest.CreateSqlConnection(true, false);
try
{
if (args.StopOnError)
tr = destConn.BeginTransaction();
try
{
if (rbEmpty.Checked)
{
DeleteData(destConn, tr, args.Objects);
}
else if (rbEmptyAll.Checked)
{
DeleteAllData(destConn, tr);
}
}
catch (Exception ex)
{
if (args.StopOnError)
tr.Rollback();
result.Errors += SmoHelpers.FormatExceptionMsg(ex);
return;
}
foreach (DbObjectList.DbObjectInfo selObj in args.Objects)
{
try
{
pd.Info = selObj.Name;
pd.Progress++;
if (_cancelled || bw.CancellationPending)
{
e.Cancel = true;
if (args.StopOnError)
tr.Rollback();
break;
}
else
{
bw.ReportProgress(0, pd);
if (!LoadDataAndCopy(selObj.SchemaAndName, args.CopyOptions, sourceConn, destConn, tr, out copyErrors, out copyCancelled))
{
if (args.StopOnError)
tr.Rollback();
if (copyCancelled)
{
e.Cancel = true;
break;
}
result.Errors += copyErrors;
if (args.StopOnError)
break;
}
}
}
catch (Exception ex)
{
if (args.StopOnError)
{
tr.Rollback();
result.Errors += SmoHelpers.FormatExceptionMsg(ex, "Table/View: " + selObj.Name);
break;
}
else
{
result.Errors += SmoHelpers.FormatExceptionMsg(ex, "Table/View: " + selObj.Name);
}
}
}
if (args.StopOnError && tr.Connection != null)
tr.Commit();
}
catch (Exception ex)
{
result.Errors += SmoHelpers.FormatExceptionMsg(ex);
}
}
finally
{
e.Result = result;
if (sourceConn != null)
{
if (sourceConn.State != ConnectionState.Closed)
sourceConn.Close();
sourceConn.Dispose();
}
if (destConn != null)
{
if (destConn.State != ConnectionState.Closed)
destConn.Close();
destConn.Dispose();
}
}
}