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