, MemberData(nameof(Access))] //If access fails with "Internal OLE Automation error", download and install: https://www.microsoft.com/en-us/download/confirmation.aspx?id=50040
//see also: https://stackoverflow.com/questions/54632928/internal-ole-automation-error-in-ms-access-using-oledb
public void StringArray(IConnectionManager connection)
{
//Arrange
TwoColumnsTableFixture destTable = new TwoColumnsTableFixture(connection, "BulkInsert2Columns");
TableData <string[]> data = new TableData <string[]>(destTable.TableDefinition);
string[] values = { "1", "Test1" };
data.Rows.Add(values);
string[] values2 = { "2", "Test2" };
data.Rows.Add(values2);
string[] values3 = { "3", "Test3" };
data.Rows.Add(values3);
//Act
SqlTask.BulkInsert(connection, "Bulk insert demo data", data, "BulkInsert2Columns");
//Assert
destTable.AssertTestData();
if (connection.GetType() == typeof(AccessOdbcConnectionManager))
{
connection.Close();
}
//Assert connection is closed
Assert.True(connection.State == null);
}
protected override void TryBulkInsertData(TInput[] data)
{
TryAddDynamicColumnsToTableDef(data);
try
{
TableData.ClearData();
ConvertAndAddRows(data);
var sql = new SqlTask(this, $"Execute Bulk insert")
{
DisableLogging = true,
ConnectionManager = BulkInsertConnectionManager
};
sql
.BulkInsert(TableData, DestinationTableDefinition.Name);
BulkInsertConnectionManager.CheckLicenseOrThrow(ProgressCount);
}
catch (Exception e)
{
if (!ErrorHandler.HasErrorBuffer)
{
throw e;
}
ErrorHandler.Send(e, ErrorHandler.ConvertErrorData <TInput[]>(data));
}
}
protected override void BulkInsertData(TInput[] data)
{
AddDynamicColumnsToTableDef(data);
TableData.ClearData();
ConvertAndAddRows(data);
var sql = new SqlTask($"Execute Bulk insert")
{
DisableLogging = true,
ConnectionManager = BulkInsertConnectionManager
};
sql.CopyLogTaskProperties(this);
sql
.BulkInsert(TableData, DestinationTableDefinition.Name);
BulkInsertConnectionManager.CheckLicenseOrThrow(ProgressCount);
}
public void ExceptionWhenNoColumnMapping()
{
//Arrange
TwoColumnsTableFixture destTable = new TwoColumnsTableFixture(SqlConnection, "NoColumnMapping");
var td = new TableDefinition("test", new List <TableColumn>());
TableData <string[]> data = new TableData <string[]>(td);
string[] values = { "1", "Test1" };
data.Rows.Add(values);
//Act & Assert
Assert.Throws <ETLBoxException>(() =>
{
SqlTask.BulkInsert(SqlConnection, "Bulk insert demo data", data, "NoColumnMapping");
});
}
, MemberData(nameof(Access))] //If access fails with "Internal OLE Automation error", download and install: https://www.microsoft.com/en-us/download/confirmation.aspx?id=50040
//see also: https://stackoverflow.com/questions/54632928/internal-ole-automation-error-in-ms-access-using-oledb
public void StringArray(IConnectionManager connection)
{
//Arrange
TwoColumnsTableFixture destTable = new TwoColumnsTableFixture(connection, "BulkInsert2Columns");
TableData <string[]> data = new TableData <string[]>(destTable.TableDefinition);
string[] values = { "1", "Test1" };
data.Rows.Add(values);
string[] values2 = { "2", "Test2" };
data.Rows.Add(values2);
string[] values3 = { "3", "Test3" };
data.Rows.Add(values3);
//Act
SqlTask.BulkInsert(connection, "Bulk insert demo data", data, "BulkInsert2Columns");
//Assert
destTable.AssertTestData();
}
public void TestBulkInsertWithTableDefinition()
{
TableDefinition tableDefinition = new TableDefinition("dbo.BulkInsert", new List <TableColumn>()
{
new TableColumn("ID", "int", allowNulls: false, isPrimaryKey: true, isIdentity: true),
new TableColumn("Col1", "nvarchar(4000)", allowNulls: true),
new TableColumn("Col2", "nvarchar(4000)", allowNulls: true)
});
tableDefinition.CreateTable();
TableData data = new TableData(tableDefinition);
string[] values = { "Value1", "Value2" };
data.Rows.Add(values);
string[] values2 = { "Value3", "Value4" };
data.Rows.Add(values2);
string[] values3 = { "Value5", "Value6" };
data.Rows.Add(values3);
SqlTask.BulkInsert("Bulk insert demo data", data, "dbo.BulkInsert");
}
, MemberData(nameof(Access))] //If access fails with "Internal OLE Automation error", download and install: https://www.microsoft.com/en-us/download/confirmation.aspx?id=50040
//see also: https://stackoverflow.com/questions/54632928/internal-ole-automation-error-in-ms-access-using-oledb
public void StringArray(IConnectionManager connection)
{
//Arrange
TwoColumnsTableFixture destTable = new TwoColumnsTableFixture(connection, "BulkInsert2Columns");
TableData <string[]> data = new TableData <string[]>(
destTable.TableDefinition,
new List <object[]>
{
new [] { "1", "Test1" },
new [] { "2", "Test2" },
new [] { "3", "Test3" }
}
);
//Act
SqlTask.BulkInsert(connection, "Bulk insert demo data", data, "BulkInsert2Columns");
//Assert
destTable.AssertTestData();
}
public void WithIdentityShift(IConnectionManager connection, int identityIndex)
{
//SQLite does not support Batch Insert on Non Nullable Identity Columns
if (connection.GetType() != typeof(SQLiteConnectionManager))
{
//Arrange
FourColumnsTableFixture destTable = new FourColumnsTableFixture(connection, "BulkInsert4Columns", identityIndex);
TableData data = new TableData(destTable.TableDefinition, 2);
object[] values = { "Test1", null, 1.2 };
data.Rows.Add(values);
object[] values2 = { "Test2", 4711, 1.23 };
data.Rows.Add(values2);
object[] values3 = { "Test3", 185, 1.234 };
data.Rows.Add(values3);
//Act
SqlTask.BulkInsert(connection, "Bulk insert demo data", data, "BulkInsert4Columns");
//Assert
destTable.AssertTestData();
}
}
protected override void DoWriteBatch(TInput[] data)
{
var tableData = CreateTableDataObject(ref data);
try
{
var task = new SqlTask(this, $"Execute Bulk insert")
{
DisableLogging = true
};
task.BulkInsert(tableData);
tableData.Reset();
OnBatchInserted(tableData);
}
catch (Exception e)
{
if (!ErrorHandler.HasErrorBuffer)
{
throw;
}
ErrorHandler.Send(e, ErrorHandler.ConvertErrorData <TInput[]>(data));
}
}
protected override void TryBulkInsertData(TInput[] data)
{
TableData <TInput> td = CreateTableDataObject(ref data);
try
{
var sql = new SqlTask(this, $"Execute Bulk insert")
{
DisableLogging = true,
ConnectionManager = BulkInsertConnectionManager
};
sql
.BulkInsert(td, DestinationTableDefinition.Name);
}
catch (Exception e)
{
FinishWrite();
if (!ErrorHandler.HasErrorBuffer)
{
throw e;
}
ErrorHandler.Send(e, ErrorHandler.ConvertErrorData <TInput[]>(data));
}
}