public void SourceAndDestinationSameColumns(IConnectionManager connection)
{
//Arrange
TwoColumnsTableFixture source2Columns = new TwoColumnsTableFixture(connection, "SourceDynamic");
source2Columns.InsertTestData();
TwoColumnsTableFixture dest2Columns = new TwoColumnsTableFixture(connection, "DestinationDynamic");
//Act
DbSource <ExpandoObject> source = new DbSource <ExpandoObject>("SourceDynamic", connection);
DbDestination <ExpandoObject> dest = new DbDestination <ExpandoObject>("DestinationDynamic", connection);
source.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
dest2Columns.AssertTestData();
}
public void SimpleFlow()
{
//Arrange
TwoColumnsTableFixture s2C = new TwoColumnsTableFixture("CSVDestDynamicObject");
s2C.InsertTestDataSet3();
DbSource <ExpandoObject> source = new DbSource <ExpandoObject>("CSVDestDynamicObject", SqlConnection);
//Act
CsvDestination <ExpandoObject> dest = new CsvDestination <ExpandoObject>("./SimpleWithDynamicObject.csv");
source.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
Assert.Equal(File.ReadAllText("./SimpleWithDynamicObject.csv"),
File.ReadAllText("res/CsvDestination/TwoColumnsSet3DynamicObject.csv"));
}
public void AccessIntoDB()
{
//Arrange
TableDefinition testTable = RecreateAccessTestTable();
InsertTestData();
TwoColumnsTableFixture destTable = new TwoColumnsTableFixture(SqlConnection, "dbo.AccessTargetTable");
//Act
DbSource <Data> source = new DbSource <Data>(AccessOdbcConnection, "TestTable");
DbDestination <Data> dest = new DbDestination <Data>(SqlConnection, "dbo.AccessTargetTable");
source.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
destTable.AssertTestData();
}
public void MergeFromEmptySource()
{
//Arrange
TwoColumnsTableFixture s2c = new TwoColumnsTableFixture(SqlConnection, "DBMergeEmptySource");
TwoColumnsTableFixture d2c = new TwoColumnsTableFixture(SqlConnection, "DBMergeEmptyDestination");
d2c.InsertTestData();
DbSource <MyMergeRow> source = new DbSource <MyMergeRow>(SqlConnection, "DBMergeEmptySource");
//Act
DbMerge <MyMergeRow> dest = new DbMerge <MyMergeRow>(SqlConnection, "DBMergeEmptyDestination");
source.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
d2c.AssertTestData();
}
public void UnknownTableViaTableDefinition()
{
//Arrange
TableDefinition def = new TableDefinition("UnknownTable",
new List <TableColumn>()
{
new TableColumn("id", "INT")
});
DbSource <string[]> source = new DbSource <string[]>(def, SqlConnection);
MemoryDestination <string[]> dest = new MemoryDestination <string[]>();
//Act & Assert
Assert.Throws <Microsoft.Data.SqlClient.SqlException>(() =>
{
source.LinkTo(dest);
source.Execute();
dest.Wait();
});
}
public void ReadAndWriteToAzure()
{
//Arrange
TwoColumnsTableFixture source2Columns = new TwoColumnsTableFixture(AzureSqlConnection, "[source].[AzureSource]");
source2Columns.InsertTestData();
TwoColumnsTableFixture dest2Columns = new TwoColumnsTableFixture(AzureSqlConnection, "[dest].[AzureDestination]");
//Act
DbSource <MySimpleRow> source = new DbSource <MySimpleRow>(AzureSqlConnection, "[source].[AzureSource]");
DbDestination <MySimpleRow> dest = new DbDestination <MySimpleRow>(AzureSqlConnection, "[dest].[AzureDestination]");
source.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
dest2Columns.AssertTestData();
}
public void SimpleFlowWithObject()
{
//Arrange
TwoColumnsTableFixture s2C = new TwoColumnsTableFixture("CSVDestSimple");
s2C.InsertTestDataSet3();
DbSource <MySimpleRow> source = new DbSource <MySimpleRow>(SqlConnection, "CSVDestSimple");
//Act
CsvDestination <MySimpleRow> dest = new CsvDestination <MySimpleRow>("./SimpleWithObject.csv");
source.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
Assert.Equal(File.ReadAllText("./SimpleWithObject.csv"),
File.ReadAllText("res/CsvDestination/TwoColumnsSet3.csv"));
}
public void SimpleFlow(IConnectionManager connection)
{
//Arrange
TwoColumnsTableFixture source2Columns = new TwoColumnsTableFixture(connection, "DbSourceSimple");
source2Columns.InsertTestData();
TwoColumnsTableFixture dest2Columns = new TwoColumnsTableFixture(connection, "DbDestinationSimple");
//Act
DbSource <MySimpleRow> source = new DbSource <MySimpleRow>(connection, "DbSourceSimple");
DbDestination <MySimpleRow> dest = new DbDestination <MySimpleRow>(connection, "DbDestinationSimple");
source.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
dest2Columns.AssertTestData();
}
public void ReadAndWriteToAzure()
{
var envvar = Environment.GetEnvironmentVariable("ETLBoxAzure");
if (envvar != "true") return;
//Arrange
TwoColumnsTableFixture source2Columns = new TwoColumnsTableFixture(AzureSqlConnection, "[source].[AzureSource]");
source2Columns.InsertTestData();
TwoColumnsTableFixture dest2Columns = new TwoColumnsTableFixture(AzureSqlConnection, "[dest].[AzureDestination]");
//Act
DbSource<MySimpleRow> source = new DbSource<MySimpleRow>(AzureSqlConnection, "[source].[AzureSource]");
DbDestination<MySimpleRow> dest = new DbDestination<MySimpleRow>(AzureSqlConnection, "[dest].[AzureDestination]");
source.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
dest2Columns.AssertTestData();
}
public void SourceMoreColumnsThanDestination(IConnectionManager connection)
{
//Arrange
FourColumnsTableFixture source4Columns = new FourColumnsTableFixture(connection, "SourceDynamic4Cols");
source4Columns.InsertTestData();
TwoColumnsTableFixture dest2Columns = new TwoColumnsTableFixture(connection, "DestinationDynamic2Cols");
//Act
DbSource <ExpandoObject> source = new DbSource <ExpandoObject>(connection, "SourceDynamic4Cols");
DbDestination <ExpandoObject> dest = new DbDestination <ExpandoObject>(connection, "DestinationDynamic2Cols");
source.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
dest2Columns.AssertTestData();
}
public void SimpleNonGeneric()
{
//Arrange
TwoColumnsTableFixture s2C = new TwoColumnsTableFixture("JsonDestSimpleNonGeneric");
s2C.InsertTestDataSet3();
DbSource <string[]> source = new DbSource <string[]>(SqlConnection, "JsonDestSimpleNonGeneric");
//Act
JsonDestination <string[]> dest = new JsonDestination <string[]>("./SimpleNonGeneric.json", ResourceType.File);
source.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
Assert.Equal(File.ReadAllText("res/JsonDestination/TwoColumnsSet3StringArray.json")
, File.ReadAllText("./SimpleNonGeneric.json"));
}
public void SimpleFlowWithObject()
{
//Arrange
TwoColumnsTableFixture s2C = new TwoColumnsTableFixture("JsonDestSimple");
s2C.InsertTestDataSet3();
DbSource <MySimpleRow> source = new DbSource <MySimpleRow>("JsonDestSimple", SqlConnection);
//Act
JsonDestination <MySimpleRow> dest = new JsonDestination <MySimpleRow>("./SimpleWithObject.json", ResourceType.File);
source.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
Assert.Equal(File.ReadAllText("res/JsonDestination/TwoColumnsSet3.json")
, File.ReadAllText("./SimpleWithObject.json"));
}
public void SimpleOnlyAttributes()
{
//Arrange
TwoColumnsTableFixture s2C = new TwoColumnsTableFixture("XmlDestOnlyAttributes");
s2C.InsertTestDataSet3();
DbSource <MyAttributeRow> source = new DbSource <MyAttributeRow>("XmlDestOnlyAttributes", SqlConnection);
//Act
XmlDestination <MyAttributeRow> dest = new XmlDestination <MyAttributeRow>("./SimpleOnlyAttributes.xml", ResourceType.File);
source.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
Assert.Equal(File.ReadAllText("res/XmlDestination/TwoColumnsAttributesSet3.xml")
, File.ReadAllText("./SimpleOnlyAttributes.xml"));
}
public void SimpleFlowWithObject()
{
//Arrange
TwoColumnsTableFixture s2C = new TwoColumnsTableFixture("XmlDestDynamic");
s2C.InsertTestDataSet3();
DbSource <ExpandoObject> source = new DbSource <ExpandoObject>("XmlDestDynamic", SqlConnection);
//Act
XmlDestination <ExpandoObject> dest = new XmlDestination <ExpandoObject>("./SimpleWithDynamicObject.xml", ResourceType.File);
source.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
Assert.Equal(File.ReadAllText("res/XmlDestination/TwoColumnsSet3DynamicObject.xml"),
File.ReadAllText("./SimpleWithDynamicObject.xml"));
}
public void TestTransferBetweenDBs(IConnectionManager sourceConnection, IConnectionManager destConnection)
{
//Arrange
TwoColumnsTableFixture source2Columns = new TwoColumnsTableFixture(sourceConnection, "Source");
source2Columns.InsertTestData();
TwoColumnsTableFixture dest2Columns = new TwoColumnsTableFixture(destConnection, "Destination");
//Act
DbSource <string[]> source = new DbSource <string[]>(sourceConnection, "Source");
DbDestination <string[]> dest = new DbDestination <string[]>(destConnection, "Destination");
source.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
dest2Columns.AssertTestData();
}
public void OnlyNullValue(IConnectionManager connection)
{
//Arrange
SqlTask.ExecuteNonQuery(connection, "Create destination table", @"CREATE TABLE source_onlynulls
(col1 VARCHAR(100) NULL, col2 VARCHAR(100) NULL)");
SqlTask.ExecuteNonQuery(connection, "Insert demo data"
, $@"INSERT INTO source_onlynulls VALUES(NULL, NULL)");
//Act
DbSource <string[]> source = new DbSource <string[]>(connection, "source_onlynulls");
MemoryDestination <string[]> dest = new MemoryDestination <string[]>();
source.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
Assert.Collection <string[]>(dest.Data,
row => Assert.True(row[0] == null && row[1] == null));
}
public void DifferentOutputType()
{
//Arrange
TwoColumnsTableFixture source2Columns = new TwoColumnsTableFixture("RowMultiplicationSource");
source2Columns.InsertTestData();
DbSource <MySimpleRow> source = new DbSource <MySimpleRow>(SqlConnection, "RowMultiplicationSource");
RowMultiplication <MySimpleRow, MyOtherRow> multiplication = new RowMultiplication <MySimpleRow, MyOtherRow>(
row =>
{
List <MyOtherRow> result = new List <MyOtherRow>();
for (int i = 0; i <= row.Col1; i++)
{
result.Add(new MyOtherRow()
{
Col3 = i * row.Col1,
});
}
return(result);
});
MemoryDestination <MyOtherRow> dest = new MemoryDestination <MyOtherRow>();
//Act
source.LinkTo(multiplication);
multiplication.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
Assert.Collection(dest.Data,
d => Assert.True(d.Col3 == 0),
d => Assert.True(d.Col3 == 1),
d => Assert.True(d.Col3 == 0),
d => Assert.True(d.Col3 == 2),
d => Assert.True(d.Col3 == 4),
d => Assert.True(d.Col3 == 0),
d => Assert.True(d.Col3 == 3),
d => Assert.True(d.Col3 == 6),
d => Assert.True(d.Col3 == 9)
);
}
public void ReturningNewDynamicObject()
{
//Arrange
TwoColumnsTableFixture source2Columns = new TwoColumnsTableFixture("RowMultiplicationSource");
source2Columns.InsertTestData();
DbSource source = new DbSource(SqlConnection, "RowMultiplicationSource");
RowMultiplication multiplication = new RowMultiplication(
row =>
{
List <ExpandoObject> result = new List <ExpandoObject>();
dynamic r = row as ExpandoObject;
for (int i = 0; i <= r.Col1; i++)
{
dynamic newdynamic = new ExpandoObject();
newdynamic.Col3 = i * r.Col1;
result.Add(newdynamic);
}
return(result);
});
MemoryDestination dest = new MemoryDestination();
//Act
source.LinkTo(multiplication);
multiplication.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
Assert.Collection(dest.Data,
d => { dynamic r = d as dynamic; Assert.True(r.Col3 == 0); },
d => { dynamic r = d as dynamic; Assert.True(r.Col3 == 1); },
d => { dynamic r = d as dynamic; Assert.True(r.Col3 == 0); },
d => { dynamic r = d as dynamic; Assert.True(r.Col3 == 2); },
d => { dynamic r = d as dynamic; Assert.True(r.Col3 == 4); },
d => { dynamic r = d as dynamic; Assert.True(r.Col3 == 0); },
d => { dynamic r = d as dynamic; Assert.True(r.Col3 == 3); },
d => { dynamic r = d as dynamic; Assert.True(r.Col3 == 6); },
d => { dynamic r = d as dynamic; Assert.True(r.Col3 == 9); }
);
}
public void SimpleNonGeneric()
{
//Arrange
TwoColumnsTableFixture s2C = new TwoColumnsTableFixture("CSVDestSimpleNonGeneric");
s2C.InsertTestDataSet3();
DbSource <string[]> source = new DbSource <string[]>(SqlConnection, "CSVDestSimpleNonGeneric");
//Act
CsvDestination <string[]> dest = new CsvDestination <string[]>("./SimpleNonGeneric.csv");
source.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
//Assert
Assert.Equal(File.ReadAllText("./SimpleNonGeneric.csv"),
File.ReadAllText("res/CsvDestination/TwoColumnsSet3NoHeader.csv"));
}
public void DisableHeader()
{
//Arrange
TwoColumnsTableFixture s2c = new TwoColumnsTableFixture("CsvSourceNoHeader");
s2c.InsertTestData();
DbSource <MySimpleRow> source = new DbSource <MySimpleRow>(SqlConnection, "CsvSourceNoHeader");
//Act
CsvDestination <MySimpleRow> dest = new CsvDestination <MySimpleRow>("./ConfigurationNoHeader.csv");
dest.Configuration.HasHeaderRecord = false;
source.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
Assert.Equal(File.ReadAllText("./ConfigurationNoHeader.csv"),
File.ReadAllText("res/CsvDestination/TwoColumnsNoHeader.csv"));
}
public void Run()
{
PostgresConnectionManager conMan = new PostgresConnectionManager(PostgresConnectionString);
//Import CSV
CsvSource sourceCSV = new CsvSource("NameList.csv");
DbDestination importDest = new DbDestination(conMan, "NameTable");
sourceCSV.LinkTo(importDest);
sourceCSV.Execute();
importDest.Wait();
//Export again
DbSource <NameListElement> sourceTable = new DbSource <NameListElement>(conMan, "NameTable");
CsvDestination <NameListElement> destCSV = new CsvDestination <NameListElement>("Export.csv");
destCSV.Configuration.Delimiter = ";";
sourceTable.LinkTo(destCSV);
sourceTable.Execute();
destCSV.Wait();
}
public void WithoutErrorLinking(IConnectionManager connection)
{
//Arrange
CreateSourceTable(connection, "DbSourceNoErrorLinking");
TwoColumnsTableFixture dest2Columns = new TwoColumnsTableFixture(connection, "DbDestinationNoErrorLinking");
//Act
DbSource <MySimpleRow> source = new DbSource <MySimpleRow>(connection, "DbSourceNoErrorLinking");
DbDestination <MySimpleRow> dest = new DbDestination <MySimpleRow>(connection, "DbDestinationNoErrorLinking");
source.LinkTo(dest);
//Assert
Assert.Throws <System.FormatException>(() =>
{
source.Execute();
dest.Wait();
});
}
public void DifferentColumnsInView(IConnectionManager connection)
{
//Arrange
FourColumnsTableFixture s4c = new FourColumnsTableFixture(connection, "dbsource_extended");
s4c.InsertTestData();
CreateViewTask.CreateOrAlter(connection, "DbSourceViewExtended", $"SELECT {s4c.QB}Col2{s4c.QE}, {s4c.QB}Col4{s4c.QE} FROM dbsource_extended");
FourColumnsTableFixture d4c = new FourColumnsTableFixture(connection, "DbDestinationExtended", 1);
//Act
DbSource <MyExtendedRow> source = new DbSource <MyExtendedRow>("DbSourceViewExtended", connection);
DbDestination <MyExtendedRow> dest = new DbDestination <MyExtendedRow>("DbDestinationExtended", connection);
source.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
d4c.AssertTestData();
}
public void SimpleFlow(IConnectionManager connection)
{
//Arrange
TwoColumnsTableFixture source2Columns = new TwoColumnsTableFixture(connection, "dbsource_simple");
source2Columns.InsertTestData();
CreateViewTask.CreateOrAlter(connection, "DbSourceView", "SELECT * FROM dbsource_simple");
TwoColumnsTableFixture dest2Columns = new TwoColumnsTableFixture(connection, "DbDestinationSimple");
//Act
DbSource <MySimpleRow> source = new DbSource <MySimpleRow>("DbSourceView", connection);
DbDestination <MySimpleRow> dest = new DbDestination <MySimpleRow>("DbDestinationSimple", connection);
source.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
dest2Columns.AssertTestData();
}
public void WithAdditionalNullableCol(IConnectionManager connection)
{
//Arrange
TwoColumnsTableFixture s2c = new TwoColumnsTableFixture(connection, "source_additionalnullcol");
s2c.InsertTestData();
SqlTask.ExecuteNonQuery(connection, "Create destination table", @"CREATE TABLE destination_additionalnullcol
(col1 VARCHAR(100) NULL, col2 VARCHAR(100) NULL, col3 VARCHAR(100) NULL)");
//Act
DbSource <string[]> source = new DbSource <string[]>("source_additionalnullcol", connection);
DbDestination <string[]> dest = new DbDestination <string[]>("destination_additionalnullcol", connection);
source.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
s2c.AssertTestData();
}
public void RandomDoubling()
{
//Arrange
TwoColumnsTableFixture source2Columns = new TwoColumnsTableFixture("RowMultiplicationSource");
source2Columns.InsertTestData();
DbSource <string[]> source = new DbSource <string[]>(SqlConnection, "RowMultiplicationSource");
RowMultiplication <string[]> multiplication = new RowMultiplication <string[]>(
row =>
{
List <string[]> result = new List <string[]>();
int id = int.Parse(row[0]);
for (int i = 0; i < id; i++)
{
result.Add(new string[]
{
(id + i).ToString(),
"Test" + (id + i)
});
}
return(result);
});
MemoryDestination <string[]> dest = new MemoryDestination <string[]>();
//Act
source.LinkTo(multiplication);
multiplication.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
Assert.Collection(dest.Data,
d => Assert.True(d[0] == "1" && d[1] == "Test1"),
d => Assert.True(d[0] == "2" && d[1] == "Test2"),
d => Assert.True(d[0] == "3" && d[1] == "Test3"),
d => Assert.True(d[0] == "3" && d[1] == "Test3"),
d => Assert.True(d[0] == "4" && d[1] == "Test4"),
d => Assert.True(d[0] == "5" && d[1] == "Test5")
);
}
public void Run()
{
PostgresConnectionManager postgresConMan = new PostgresConnectionManager(PostgresConnectionString);
SqlConnectionManager sqlConMan = new SqlConnectionManager(SqlServerConnectionString);
//Transfer across databases
DbSource <NameListElement> source = new DbSource <NameListElement>(postgresConMan, "NameTable");
RowTransformation <NameListElement> trans = new RowTransformation <NameListElement>(
row =>
{
row.FullName = row.LastName + "," + row.FirstName;
return(row);
});
DbDestination <NameListElement> dest = new DbDestination <NameListElement>(sqlConMan, "FullNameTable");
source.LinkTo(trans);
trans.LinkTo(dest);
source.Execute();
dest.Wait();
}
public void SimpleFlowWithObject()
{
//Arrange
TwoColumnsTableFixture s2C = new TwoColumnsTableFixture("JsonDestDynamic");
s2C.InsertTestDataSet3();
DbSource <ExpandoObject> source = new DbSource <ExpandoObject>(SqlConnection, "JsonDestDynamic");
//Act
JsonDestination <ExpandoObject> dest = new JsonDestination <ExpandoObject>("./SimpleWithDynamicObject.json", ResourceType.File);
source.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
//Null values can't be ignored:
//https://github.com/JamesNK/Newtonsoft.Json/issues/1466
Assert.Equal(File.ReadAllText("res/JsonDestination/TwoColumnsSet3DynamicObject.json"),
File.ReadAllText("./SimpleWithDynamicObject.json"));
}
public void WriteIntoTableWithPKAndFK(IConnectionManager connection)
{
//Arrange
ReCreateOtherTable(connection, "FKReferenceTable");
ReCreateTable(connection, "FKSourceTable");
ReCreateTable(connection, "FKDestTable");
InsertTestData(connection, "FKSourceTable");
AddFKConstraint(connection, "FKDestTable", "FKReferenceTable");
DbSource <MyRow> source = new DbSource <MyRow>(connection, "FKSourceTable");
//Act
DbDestination <MyRow> dest = new DbDestination <MyRow>(connection, "FKDestTable");
source.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
Assert.Equal(3, RowCountTask.Count(connection, "FKDestTable").Value);
}
public void ColumnMapping(IConnectionManager connection)
{
//Arrange
TwoColumnsTableFixture source2Columns = new TwoColumnsTableFixture(connection, "Source");
source2Columns.InsertTestData();
//Act
DbSource <ColumnMapRow> source = new DbSource <ColumnMapRow>(connection, "Source");
CustomDestination <ColumnMapRow> dest = new CustomDestination <ColumnMapRow>(
input =>
{
//Assert
Assert.InRange(input.Col1, 1, 3);
Assert.StartsWith("Test", input.B);
});
source.LinkTo(dest);
source.Execute();
dest.Wait();
}