public void CreatProcedureWithProcedureObject()
{
//Arrange
List <ProcedureParameter> pars = new List <ProcedureParameter>()
{
new ProcedureParameter("Par1", "varchar(10)"),
new ProcedureParameter("Par2", "int", "7"),
};
ProcedureDefinition procDef = new ProcedureDefinition("dbo.Proc4", "SELECT 1 AS Test", pars);
//Act
CreateProcedureTask.CreateOrAlter(Connection, procDef);
//Assert
Assert.Equal(1, RowCountTask.Count(Connection, "sys.objects",
"type = 'P' AND object_id = object_id('dbo.Proc4')"));
Assert.Equal(2, RowCountTask.Count(Connection, "sys.parameters",
"object_id = object_id('dbo.Proc4')"));
}
public void ExecuteNonQueryWithParameter(IConnectionManager connection)
{
//Arrange
TwoColumnsTableFixture tc = new TwoColumnsTableFixture(connection, "ParameterTest");
//Act
var parameter = new List <QueryParameter>
{
new QueryParameter("value1", "INT", 1),
new QueryParameter("value2", "NVARCHAR(100)", "Test1")
};
SqlTask.ExecuteNonQuery(connection, "Test insert with parameter",
$"INSERT INTO {tc.QB}ParameterTest{tc.QE} VALUES (@value1, @value2)", parameter);
//Assert
Assert.Equal(1, RowCountTask.Count(connection, "ParameterTest", $@"{tc.QB}Col1{tc.QE} = 1 AND {tc.QB}Col2{tc.QE}='Test1'"));
}
public void OnlyOneExcelColumn()
{
//Arrange
TwoColumnsTableFixture dest2Columns = new TwoColumnsTableFixture("ExcelDestination2");
//Act
ExcelSource <OneExcelColumn> source = new ExcelSource <OneExcelColumn>("res/Excel/TwoColumnData.xlsx");
source.HasNoHeader = true;
DbDestination <OneExcelColumn> dest = new DbDestination <OneExcelColumn>(Connection, "ExcelDestination2", 2);
source.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
Assert.Equal(3, RowCountTask.Count(Connection, "ExcelDestination2", "Col1 = 0 AND Col2 LIKE 'Test%'"));
}
public void FastExecutingSqlsInParallel()
{
//Arrange
List <int> array = new List <int>()
{
1, 2, 3, 4, 5, 6, 7, 8, 9, 10
};
//Act
Parallel.ForEach(array, new ParallelOptions {
MaxDegreeOfParallelism = 8
},
curNr => SqlTask.ExecuteNonQuery(Connection, $"Test statement {curNr}",
$"INSERT INTO FastParallel VALUES({curNr})")
);
//Assert
Assert.Equal(10, RowCountTask.Count(Connection, "FastParallel"));
}
public void CheckMemoryUsageDbDestination(IConnectionManager connection, int numberOfRows, int batchSize, double deviation)
{
//Arrange
ReCreateDestinationTable(connection, "MemoryDestination");
var source = new MemorySource <CSVData>();
source.Data = GenerateWithYield(numberOfRows);
var dest = new DbDestination <CSVData>(connection, "MemoryDestination", batchSize);
//Act
source.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
Assert.Equal(numberOfRows, RowCountTask.Count(connection, "MemoryDestination"));
}
public void CSV_RowTrans_DB_NonGeneric()
{
SqlTask.ExecuteNonQuery("Create source table", @"CREATE TABLE test.Staging
(Col1 int null, Col2 nvarchar(100) null)");
CSVSource source = new CSVSource("src/DataFlow/Simple_CSV2DB.csv");
RowTransformation trans = new RowTransformation(
csvdata => {
return(new string[] { csvdata[1], csvdata[0] });
});
DBDestination dest = new DBDestination("test.Staging");
source.LinkTo(trans);
trans.LinkTo(dest);
source.Execute();
dest.Wait();
Assert.AreEqual(3, RowCountTask.Count("test.Staging"));
}
public void CleanupETLLogTablesWhileLogging()
{
//Arrange
CreateLogTablesTask.CreateLog(Connection);
Assert.Equal(1, RowCountTask.Count(Connection, "sys.tables",
$"type = 'U' AND name = 'Log' AND schema_id('etl') = schema_id"));
Assert.Equal(1, RowCountTask.Count(Connection, "sys.tables",
$"type = 'U' AND name = 'LoadProcess' AND schema_id('etl') = schema_id"));
//Act
CleanUpSchemaTask.CleanUp(Connection, "etl");
//Assert
Assert.Equal(0, RowCountTask.Count(Connection, "sys.tables",
$"type = 'U' AND name = 'Log' AND schema_id('etl') = schema_id"));
Assert.Equal(0, RowCountTask.Count(Connection, "sys.tables",
$"type = 'U' AND name = 'LoadProcess' AND schema_id('etl') = schema_id"));
}
public void TestDataflowWithSQLite()
{
//ControlFlow.CurrentDbConnection = new SQLiteConnectionManager(new SQLiteConnectionString(SQLiteConnectionStringParameter));
new SqlTask($"Test statement", $@"
CREATE TABLE source (
ID INTEGER PRIMARY KEY,
Col1 TEXT NOT NULL,
Col2 INTEGER NOT NULL
);
CREATE TABLE dest (
ID INTEGER PRIMARY KEY,
Col1 TEXT NOT NULL,
Col2 INTEGER NOT NULL
);
INSERT INTO source (Col1, Col2)
VALUES('Value1',1),
('Value2',2); ")
{
DisableLogging = true
}.ExecuteNonQuery();
var tableDefinition = new TableDefinition("source",
new List <TableColumn>()
{
new TableColumn("Col1", "TEXT"),
new TableColumn("Col2", "INTEGER")
});
DBSource source = new DBSource("source")
{
SourceTableDefinition = tableDefinition
};
DBDestination dest = new DBDestination("dest")
{
DestinationTableDefinition = tableDefinition
};
source.LinkTo(dest);
source.Execute();
dest.Wait();
Assert.AreEqual(2, RowCountTask.Count("dest").Value);
}
public void Sql_Tablename()
{
TableDefinition destinationTableDefinition = new TableDefinition("test.Destination", new List <TableColumn>()
{
new TableColumn("Col1", "nvarchar(100)", allowNulls: false),
new TableColumn("Col2", "int", allowNulls: true)
});
destinationTableDefinition.CreateTable();
DBSource <MySimpleRow> source = new DBSource <MySimpleRow>(
$@"select * from (values ('Test1',1), ('Test2',2), ('Test',3)) AS MyTable(Col1,Col2)");
DBDestination <MySimpleRow> dest = new DBDestination <MySimpleRow>("test.Destination");
source.LinkTo(dest);
source.Execute();
dest.Wait();
Assert.AreEqual(3, RowCountTask.Count("test.Destination"));
}
public void CSVIntoAccess()
{
//Arrange
TableDefinition testTable = RecreateAccessTestTable();
//Act
CsvSource <string[]> source = new CsvSource <string[]>("res/UseCases/AccessData.csv");
DbDestination <string[]> dest = new DbDestination <string[]>(testTable, AccessOdbcConnection, batchSize: 2);
var linkTo = source.LinkTo(dest);
using (linkTo.link)
{
source.Execute();
dest.Wait();
//Assert
Assert.Equal(3, RowCountTask.Count(AccessOdbcConnection, testTable.Name));
}
}
public void LoggingInRowTransformation()
{
CreateLogTablesTask.CreateLog();
DataFlow.LoggingThresholdRows = 3;
DBSource source;
DBDestination dest;
CreateSourceAndDestination(out source, out dest);
RowTransformation rowTrans = new RowTransformation(row => row);
source.LinkTo(rowTrans);
rowTrans.LinkTo(dest);
source.Execute();
dest.Wait();
Assert.AreEqual(3, RowCountTask.Count("etl.Log", "TaskType = 'DF_ROWTRANSFORMATION' AND TaskAction = 'LOG'"));
}
public void BigData_CSV_DB(int numberOfRows)
{
Stopwatch watch = new Stopwatch();
TableDefinition stagingTable = new TableDefinition("test.Staging", new List <TableColumn>()
{
new TableColumn("Col1", "nchar(1000)", allowNulls: false),
new TableColumn("Col2", "nchar(1000)", allowNulls: false),
new TableColumn("Col3", "nchar(1000)", allowNulls: false),
new TableColumn("Col4", "nchar(1000)", allowNulls: false),
});
stagingTable.CreateTable();
string fileName = "src/DataFlow/BigData_CSV2DB.csv";
BigDataHelper bigData = new BigDataHelper()
{
FileName = fileName,
NumberOfRows = numberOfRows,
TableDefinition = stagingTable
};
watch.Start();
LogTask.Info($"Create .csv file {fileName} with {numberOfRows} Rows");
bigData.CreateBigDataCSV();
LogTask.Info($"Needed {watch.Elapsed.TotalMinutes} to create .csv file");
watch.Reset();
CSVSource source = new CSVSource(fileName);
DBDestination <string[]> dest = new DBDestination <string[]>(1000)
{
DestinationTableDefinition = stagingTable
};
source.LinkTo(dest);
watch.Start();
source.Execute();
LogTask.Info($"Needed {watch.Elapsed.TotalMinutes} to read everything into memory (while constantly writing)");
LogTask.Info($"Already {RowCountTask.Count("test.Staging", RowCountOptions.QuickQueryMode)} inserted into table");
dest.Wait(); //TODO Wait should be part of source
LogTask.Info($"Needed {watch.Elapsed.TotalMinutes} to write everything into database");
Assert.AreEqual(numberOfRows, SqlTask.ExecuteScalar <int>("Check staging table", $"select count(*) from test.Staging"));
}
public void CSVIntoAccess()
{
//Arrange
TableDefinition testTable = RecreateAccessTestTable();
//Act
CSVSource source = new CSVSource("res/UseCases/AccessData.csv");
DBDestination <string[]> dest = new DBDestination <string[]>(testTable, batchSize: 2)
{
ConnectionManager = AccessOdbcConnection
};
source.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
Assert.Equal(3, RowCountTask.Count(AccessOdbcConnection, testTable.Name));
}
public void DB_CustDest()
{
TableDefinition sourceTableDefinition = CreateSourceTable("test.Source");
TableDefinition destinationTableDefinition = CreateDestinationTable("test.Destination");
DBSource <MySimpleRow> source = new DBSource <MySimpleRow>()
{
SourceTableDefinition = sourceTableDefinition
};
CustomDestination <MySimpleRow> dest = new CustomDestination <MySimpleRow>(
row => {
SqlTask.ExecuteNonQuery("Insert row", $"insert into test.Destination values('{row.Value1}',{row.Value2})");
}
);
source.LinkTo(dest);
source.Execute();
dest.Wait();
Assert.AreEqual(3, RowCountTask.Count("test.Destination"));
}
public void DB_BlockTrans_DB()
{
TableDefinition sourceTableDefinition = CreateSourceTable("test.Source");
TableDefinition destinationTableDefinition = CreateDestinationTable("test.Destination");
DBSource <MySimpleRow> source = new DBSource <MySimpleRow>(sourceTableDefinition);
DBDestination <MySimpleRow> dest = new DBDestination <MySimpleRow>(destinationTableDefinition);
Comparison <MySimpleRow> comp = new Comparison <MySimpleRow>(
(x, y) => y.Col2 - x.Col2
);
Sort <MySimpleRow> block = new Sort <MySimpleRow>(comp);
source.LinkTo(block);
block.LinkTo(dest);
source.Execute();
dest.Wait();
Assert.AreEqual(1, RowCountTask.Count("test.Destination", "[Key] = 1 and Col2 = 3"));
Assert.AreEqual(1, RowCountTask.Count("test.Destination", "[Key] = 2 and Col2 = 2"));
Assert.AreEqual(1, RowCountTask.Count("test.Destination", "[Key] = 3 and Col2 = 1"));
}
public void LoggingInSourceAndDestination()
{
CreateLogTablesTask.CreateLog();
StartLoadProcessTask.Start("Test");
DataFlow.LoggingThresholdRows = 3;
DBSource source;
DBDestination dest;
CreateSourceAndDestination(out source, out dest);
source.LinkTo(dest);
source.Execute();
dest.Wait();
EndLoadProcessTask.End();
Assert.AreEqual(4, RowCountTask.Count("etl.Log", "TaskType = 'DF_DBSOURCE' AND TaskAction = 'LOG' AND LoadProcessKey IS NOT NULL"));
Assert.AreEqual(4, RowCountTask.Count("etl.Log", "TaskType = 'DF_DBDEST' AND TaskAction = 'LOG' AND LoadProcessKey IS NOT NULL"));
Assert.AreEqual(1, RowCountTask.Count("etl.LoadProcess"));
}
public void StartLoadProcess(IConnectionManager connection)
{
//Arrange
CreateLoadProcessTableTask.Create(connection, "test_load_process");
//Act
StartLoadProcessTask.Start(connection, "Test process 1");
//Assert
Assert.True(ControlFlow.CurrentLoadProcess != null);
Assert.Equal("Test process 1", ControlFlow.CurrentLoadProcess.ProcessName);
Assert.True(ControlFlow.CurrentLoadProcess.StartDate >= DateTime.Now.AddSeconds(-1));
Assert.Equal(1, RowCountTask.Count(connection, ControlFlow.LoadProcessTable,
"start_message IS NULL and end_message IS NULL and abort_message IS NULL"));
Assert.Equal(1, RowCountTask.Count(connection, ControlFlow.LoadProcessTable,
"is_running = 1 AND was_successful=0 AND was_aborted=0"));
//Cleanup
DropTableTask.Drop(connection, "test_load_process");
}
public void CustomSourceWithWebService()
{
//Arrange
SqlTask.ExecuteNonQuery(Connection, "Create test table",
@"CREATE TABLE dbo.WebServiceDestination
( Id INT NOT NULL, Title NVARCHAR(100) NOT NULL, Completed BIT NOT NULL )"
);
DbDestination <Todo> dest = new DbDestination <Todo>(Connection, "dbo.WebServiceDestination");
WebserviceReader wsreader = new WebserviceReader();
//Act
CustomSource <Todo> source = new CustomSource <Todo>(wsreader.ReadTodo, wsreader.EndOfData);
source.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
Assert.Equal(5, RowCountTask.Count(Connection, "dbo.WebServiceDestination"));
}
public void DBDestinationWithColumnMapping()
{
SqlTask.ExecuteNonQuery("Create destination table", @"CREATE TABLE test.Destination
(ID int not null identity (1,1) primary key,
Col1 nvarchar(30) null, Col2 nvarchar(30) null)");
int index = 1;
CustomSource <ColumnMapRow> source = new CustomSource <ColumnMapRow>(() => {
return(new ColumnMapRow()
{
Col1 = "Test" + index++,
B = "Test2",
});
}, () => index > 3);
DBDestination <ColumnMapRow> dest = new DBDestination <ColumnMapRow>("test.Destination");
source.LinkTo(dest);
source.Execute();
dest.Wait();
Assert.AreEqual(3, RowCountTask.Count("test.Destination", "Col2 = 'Test2'"));
}
public void DBSourceWithStringArray()
{
SqlTask.ExecuteNonQuery("Create source table", @"CREATE TABLE test.Source
(Col1 nvarchar(100) null, Col2 int null)");
SqlTask.ExecuteNonQuery("Insert demo data", "insert into test.Source values('Test1',1)");
SqlTask.ExecuteNonQuery("Insert demo data", "insert into test.Source values('Test2',2)");
SqlTask.ExecuteNonQuery("Insert demo data", "insert into test.Source values('Test3',3)");
SqlTask.ExecuteNonQuery("Create destination table", @"CREATE TABLE test.Destination
(Col1 nvarchar(30) null, Col2 bigint null)");
DBSource source = new DBSource("test.Source");
DBDestination dest = new DBDestination("test.Destination");
source.LinkTo(dest);
source.Execute();
dest.Wait();
Assert.AreEqual(3, RowCountTask.Count("test.Destination"));
Assert.AreEqual(1, RowCountTask.Count("test.Destination", "Col1 = 'Test1' AND Col2=1"));
Assert.AreEqual(1, RowCountTask.Count("test.Destination", "Col1 = 'Test2' AND Col2=2"));
Assert.AreEqual(1, RowCountTask.Count("test.Destination", "Col1 = 'Test3' AND Col2=3"));
}
public void DboTableName_DboTablename()
{
SqlTask.ExecuteNonQuery("Create source table", @"CREATE TABLE Source
(Col1 nvarchar(100) null, Col2 int null)");
SqlTask.ExecuteNonQuery("Insert demo data", "insert into Source values('Test1',1)");
SqlTask.ExecuteNonQuery("Insert demo data", "insert into Source values('Test2',2)");
SqlTask.ExecuteNonQuery("Insert demo data", "insert into Source values('Test3',3)");
SqlTask.ExecuteNonQuery("Create destination table", @"CREATE TABLE Destination
(Col1 nvarchar(30) null, Col2 bigint null)");
DBSource <MySimpleRow> source = new DBSource <MySimpleRow>("Source");
DBDestination <MySimpleRow> dest = new DBDestination <MySimpleRow>("Destination");
source.LinkTo(dest);
source.Execute();
dest.Wait();
Assert.AreEqual(3, RowCountTask.Count("Destination"));
Assert.AreEqual(1, RowCountTask.Count("Destination", "Col1 = 'Test1' AND Col2=1"));
Assert.AreEqual(1, RowCountTask.Count("Destination", "Col1 = 'Test2' AND Col2=2"));
Assert.AreEqual(1, RowCountTask.Count("Destination", "Col1 = 'Test3' AND Col2=3"));
}
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 DB_BlockTrans_DB()
{
TableDefinition sourceTableDefinition = CreateSourceTable("test.Source");
TableDefinition destinationTableDefinition = CreateDestinationTable("test.Destination");
DBSource <MySimpleRow> source = new DBSource <MySimpleRow>(sourceTableDefinition);
DBDestination <MySimpleRow> dest = new DBDestination <MySimpleRow>(destinationTableDefinition);
BlockTransformation <MySimpleRow> block = new BlockTransformation <MySimpleRow>(
inputData => {
return(inputData.Select(row => new MySimpleRow()
{
Col1 = row.Col1, Col2 = 3
}).ToList());
});
source.LinkTo(block);
block.LinkTo(dest);
source.Execute();
dest.Wait();
Assert.AreEqual(3, RowCountTask.Count("test.Destination", "Col2 in (3)"));
}
public void AbortLoadProcess()
{
//Arrange
CreateLoadProcessTableTask.Create(SqlConnection, "test_lp_abort");
StartLoadProcessTask.Start(SqlConnection, "Test process 3");
Assert.True(ControlFlow.CurrentLoadProcess.IsRunning == true);
//Act
AbortLoadProcessTask.Abort(SqlConnection, ControlFlow.CurrentLoadProcess.Id);
//Assert
Assert.True(ControlFlow.CurrentLoadProcess.IsRunning == false);
Assert.True(ControlFlow.CurrentLoadProcess.WasAborted == true);
Assert.True(ControlFlow.CurrentLoadProcess.AbortMessage == null);
Assert.Equal(1, RowCountTask.Count(SqlConnection, "test_lp_abort"
, "is_running=0 and was_successful=0 and was_aborted=1"));
//Cleanup
DropTableTask.Drop(SqlConnection, "test_lp_abort");
}
private TimeSpan GetBulkInsertTime(IConnectionManager connection, int numberOfRows)
{
TimeSpan result = TimeSpan.FromMilliseconds(0);
if (connection.GetType() == typeof(SqlConnectionManager))
{
result = BigDataHelper.LogExecutionTime($"Copying Csv into DB (non generic) with rows of data using BulkInsert",
() =>
{
SqlTask.ExecuteNonQuery(connection, "Insert with BulkInsert",
$@"BULK INSERT [dbo].[CsvDestinationBulkInsert]
FROM '{BigDataCsvSource.GetCompleteFilePath(numberOfRows)}'
WITH ( FIRSTROW = 2, FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' );
");
});
Assert.Equal(numberOfRows, RowCountTask.Count(connection, "CsvDestinationBulkInsert"));
output.WriteLine("Elapsed " + result.TotalSeconds + " seconds for bulk insert.");
}
return(result);
}
public void SimpleAsyncFlow()
{
//Arrange
TwoColumnsTableFixture dest2Columns = new TwoColumnsTableFixture("Destination4CustomSource");
List <string> Data = new List <string>()
{
"Test1", "Test2", "Test3"
};
int _readIndex = 0;
Func <MySimpleRow> ReadData = () =>
{
if (_readIndex == 0)
{
Task.Delay(300).Wait();
}
var result = new MySimpleRow()
{
Col1 = _readIndex + 1,
Col2 = Data[_readIndex]
};
_readIndex++;
return(result);
};
Func <bool> EndOfData = () => _readIndex >= Data.Count;
//Act
CustomSource <MySimpleRow> source = new CustomSource <MySimpleRow>(ReadData, EndOfData);
DbDestination <MySimpleRow> dest = new DbDestination <MySimpleRow>(SqlConnection, "Destination4CustomSource");
source.LinkTo(dest);
Task sourceT = source.ExecuteAsync();
Task destT = dest.Completion;
//Assert
Assert.True(RowCountTask.Count(SqlConnection, "Destination4CustomSource") == 0);
sourceT.Wait();
destT.Wait();
dest2Columns.AssertTestData();
}
public void CSV_DB_WithOdbcConnection()
{
ControlFlow.CurrentDbConnection = new OdbcConnectionManager(new OdbcConnectionString(OdbcConnectionStringParameter));
CreateSchemaTask.Create("test");
TableDefinition stagingTable = new TableDefinition("test.Staging", new List <TableColumn>()
{
new TableColumn("ID", "int", allowNulls: false, isPrimaryKey: true, isIdentity: true),
new TableColumn("Col1", "bit", allowNulls: true),
new TableColumn("Col2", "decimal(10,5)", allowNulls: true),
new TableColumn("Col3", "tinyint", allowNulls: true),
new TableColumn("Col4", "int", allowNulls: true),
new TableColumn("Col5", "uniqueidentifier", allowNulls: true),
new TableColumn("Col6", "nvarchar(100)", allowNulls: true)
});
stagingTable.CreateTable();
CSVSource source = new CSVSource("src/ConnectionManager/DatatypeCSV.csv");
RowTransformation <string[], string[]> trans = new RowTransformation <string[], string[]>("Set empty values to null",
row => {
for (int i = 0; i < row.Length; i++)
{
if (row[i] == String.Empty)
{
row[i] = null;
}
}
return(row);
});
DBDestination <string[]> dest = new DBDestination <string[]>(stagingTable, 2);
source.LinkTo(trans);
trans.LinkTo(dest);
source.Execute();
dest.Wait();
Assert.AreEqual(3, RowCountTask.Count(stagingTable.Name));
}
public void MergeJoinUsingOneObject()
{
//Arrange
TwoColumnsTableFixture source1Table = new TwoColumnsTableFixture("MergeJoinDynamicSource1");
source1Table.InsertTestData();
TwoColumnsTableFixture source2Table = new TwoColumnsTableFixture("MergeJoinDynamicSource2");
source2Table.InsertTestDataSet2();
TwoColumnsTableFixture destTable = new TwoColumnsTableFixture("MergeJoinDynamicDestination");
DbSource <ExpandoObject> source1 = new DbSource <ExpandoObject>(Connection, "MergeJoinDynamicSource1");
DbSource <ExpandoObject> source2 = new DbSource <ExpandoObject>(Connection, "MergeJoinDynamicSource2");
DbDestination <ExpandoObject> dest = new DbDestination <ExpandoObject>(Connection, "MergeJoinDynamicDestination");
//Act
MergeJoin <ExpandoObject> join = new MergeJoin <ExpandoObject>(
(inputRow1, inputRow2) =>
{
dynamic ir1 = inputRow1 as ExpandoObject;
dynamic ir2 = inputRow2 as ExpandoObject;
ir1.Col1 = ir1.Col1 + ir2.Col1;
ir1.Col2 = ir1.Col2 + ir2.Col2;
return(inputRow1);
});
source1.LinkTo(join.Target1);
source2.LinkTo(join.Target2);
join.LinkTo(dest);
source1.Execute();
source2.Execute();
dest.Wait();
//Assert
Assert.Equal(3, RowCountTask.Count(Connection, "MergeJoinDynamicDestination"));
Assert.Equal(1, RowCountTask.Count(Connection, "MergeJoinDynamicDestination", "Col1 = 5 AND Col2='Test1Test4'"));
Assert.Equal(1, RowCountTask.Count(Connection, "MergeJoinDynamicDestination", "Col1 = 7 AND Col2='Test2Test5'"));
Assert.Equal(1, RowCountTask.Count(Connection, "MergeJoinDynamicDestination", "Col1 = 9 AND Col2='Test3Test6'"));
}
public void JsonPathListIntoDynamic()
{
//Arrange
FourColumnsTableFixture dest4Columns = new FourColumnsTableFixture("JsonSourceNestedDynamic4Cols");
DbDestination <ExpandoObject> dest = new DbDestination <ExpandoObject>(SqlConnection, "JsonSourceNestedDynamic4Cols");
//Act
JsonSource <ExpandoObject> source = new JsonSource <ExpandoObject>("res/JsonSource/NestedData4Cols.json", ResourceType.File);
List <JsonProperty2JsonPath> pathLookups = new List <JsonProperty2JsonPath>()
{
new JsonProperty2JsonPath()
{
JsonPropertyName = "Col2",
JsonPath = "Value",
},
new JsonProperty2JsonPath()
{
JsonPropertyName = "Object",
JsonPath = "Number[0]",
NewPropertyName = "Col4"
},
new JsonProperty2JsonPath()
{
JsonPropertyName = "Array",
JsonPath = "[1].Value",
NewPropertyName = "Col3"
}
};
source.JsonSerializer.Converters.Add(new ExpandoJsonPathConverter(pathLookups));
source.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
dest4Columns.AssertTestData();
Assert.Equal(1, RowCountTask.Count(SqlConnection, "JsonSourceNestedDynamic4Cols", $"Col2 = 'Test2' AND Col3 = 4711 AND Col4='1.23'"));
}
public void CSVGeneric_DB()
{
TableDefinition stagingTable = new TableDefinition("test.Staging", new List <TableColumn>()
{
new TableColumn("Col1", "nvarchar(100)", allowNulls: false),
new TableColumn("Col2", "int", allowNulls: true)
});
stagingTable.CreateTable();
CSVSource <CSVData> source = new CSVSource <CSVData>("src/DataFlow/Simple_CSV2DB.csv");
DBDestination <CSVData> dest = new DBDestination <CSVData>()
{
DestinationTableDefinition = stagingTable
};
source.LinkTo(dest);
source.Execute();
dest.Wait();
Assert.AreEqual(3, RowCountTask.Count("test.Staging", "Col1 Like '%ValueRow%' and Col2 <> 1"));
}