public void TestDataflowDbToDb()
{
using (TestDb)
{
string destSchema = "test";
string destTable = "Staging3";
string destObject = $"[{destSchema}].[{destTable}]";
new DropAndCreateTableTask(TestDb.SqlConnection).Execute(destSchema, destTable, new List <TableColumn>()
{
Ziel_F0, Ziel_F1, Ziel_F2, Ziel_F3
});
SqlSource <Datensatz_DbToDb> DBSource = new SqlSource <Datensatz_DbToDb>(TestDb.getNewSqlConnection()
, "SELECT 0 as F1"
+ " UNION ALL SELECT 4 as F1"
+ " UNION ALL SELECT -3 as F1"
+ " UNION ALL SELECT -2 as F1"
);
DBSource.DataMappingMethod = ReaderAdapter_DbToDb.Read;
SqlDestination <Datensatz_DbToDb> destination = new SqlDestination <Datensatz_DbToDb>();
destination.ObjectName = destObject;
destination.FieldCount = 4;
destination.ObjectMappingMethod = WriterAdapter_DbToDb.Fill;
destination.SqlConnection = TestDb.SqlConnection;
Graph g = new Graph();
g.GetVertex(0, DBSource);
g.GetVertex(1, new RowTransformation <Datensatz_DbToDb>(RowTransformationDB));
g.GetVertex(2, new RowTransformation <Datensatz_DbToDb>(RowTransformationDB2));
g.GetVertex(3, destination);
g.AddEdge(0, 1); // connect 0 to 1
g.AddEdge(1, 2); // connect 1 to 2
g.AddEdge(2, 3); // connect 2 to 3
DataFlowTask <Datensatz_DbToDb> .Execute("Test dataflow task", 10000, 1, g);
//TestHelper.VisualizeGraph(g);
Assert.AreEqual(4, new ExecuteSQLTask(TestDb.SqlConnection).ExecuteScalar(string.Format("select count(*) from {0}", destObject)));
}
}
public void should_return_the_collection_with_ids_set_if_table_has_identity_col()
{
var adapter = new InMemoryAdapter();
adapter.SetAutoIncrementColumn("Record", "Id");
Database.UseMockAdapter(adapter);
var destination = new SqlDestination("foo");
var inserted = destination.Load(new[] {
new Record {
Name = "Foo"
},
new Record {
Name = "Bar"
}
});
Assert.True(inserted.First().Id > 0);
}
public void should_insert_the_specified_collection_into_the_db()
{
var adapter = new InMemoryAdapter();
Database.UseMockAdapter(adapter);
var destination = new SqlDestination("foo");
destination.Load(new[] {
new Record {
Id = 42, Name = "Foo"
},
new Record {
Id = 1337, Name = "Bar"
}
});
var records = Database.Open().Record.All().ToList();
Assert.Equal(records.Count, 2);
}
public void TestDataflow_Massendaten()
{
using (TestDb)
{
int SkalaGrenze = 10000;
for (int i = SkalaGrenze * -1; i < SkalaGrenze; i = i + 50)
{
MetrischeSkala.Add(new IntervalPointMetric(i, i));
}
int Anzahl_je_Faktor = 10000;
int Anzahl_Faktoren = 10;
string TempObjectNameName = "test.tmp";
new DropTableTask(TestDb.getNewSqlConnection()).Execute(TempObjectNameName);
string QuellSchemaName = "test";
string QuellTabelle = "source";
string QuellObjekt = $"[{QuellSchemaName}].[{QuellTabelle}]";
new DropAndCreateTableTask(TestDb.getNewSqlConnection()).Execute(QuellSchemaName, QuellTabelle, new List <TableColumn>()
{
new TableColumn("Key", SqlDbType.Int, false, true, true),
new TableColumn("F1", SqlDbType.Int, true),
new TableColumn("F2", SqlDbType.Int, true),
new TableColumn("F3", SqlDbType.Int, true),
new TableColumn("F4", SqlDbType.Int, true),
new TableColumn("F5", SqlDbType.Int, true),
new TableColumn("F6", SqlDbType.Int, true),
new TableColumn("F7", SqlDbType.Int, true),
new TableColumn("F8", SqlDbType.Int, true),
new TableColumn("F9", SqlDbType.Int, true),
new TableColumn("F10", SqlDbType.Int, true),
});
string sql_generate_Massendaten = @"
select top 0 F1,F2,F3,F4,F5,F6,F7,F8,F9,F10 into " + TempObjectNameName + @" from " + QuellObjekt + @" -- tmp-Tabelle erstellen
declare @grenze as int = " + SkalaGrenze + @"
declare @i as int = 0
while (@i < " + Anzahl_je_Faktor + @")
begin
insert into test.tmp
select @i % @grenze, @i % @grenze + 1, @i % @grenze + 2, (@i % @grenze) * -1, (@i % @grenze) * -1 -1, @i % @grenze, @i % @grenze -1, @i % @grenze +2, @i% @grenze+3, @i % @grenze+4
set @i = @i + 1
end
declare @j as int = 0
while (@j < " + Anzahl_Faktoren + @")
begin
insert into " + QuellObjekt + @"
select F1,F2,F3,F4,F5,F6,F7,F8,F9,F10 from test.tmp
set @j = @j + 1
end
"
;
Debug.WriteLine("Generiere Massendaten ... ");
new ExecuteSQLTask(TestDb.getNewSqlConnection()).ExecuteNonQuery(sql_generate_Massendaten);
string ZielSchemaName = "test";
string ZielTabelle = "destination";
string ZielObjekt = $"[{ZielSchemaName}].[{ZielTabelle}]";
new DropAndCreateTableTask(TestDb.getNewSqlConnection()).Execute(ZielSchemaName, ZielTabelle, new List <TableColumn>()
{
new TableColumn("Key", SqlDbType.Int, false, true, true),
new TableColumn("F1", SqlDbType.Int, true), new TableColumn("F1_calc", SqlDbType.Int, true),
new TableColumn("F2", SqlDbType.Int, true), new TableColumn("F2_calc", SqlDbType.Int, true),
new TableColumn("F3", SqlDbType.Int, true), new TableColumn("F3_calc", SqlDbType.Int, true),
new TableColumn("F4", SqlDbType.Int, true), new TableColumn("F4_calc", SqlDbType.Int, true),
new TableColumn("F5", SqlDbType.Int, true), new TableColumn("F5_calc", SqlDbType.Int, true),
new TableColumn("F6", SqlDbType.Int, true), new TableColumn("F6_calc", SqlDbType.Int, true),
new TableColumn("F7", SqlDbType.Int, true), new TableColumn("F7_calc", SqlDbType.Int, true),
new TableColumn("F8", SqlDbType.Int, true), new TableColumn("F8_calc", SqlDbType.Int, true),
new TableColumn("F9", SqlDbType.Int, true), new TableColumn("F9_calc", SqlDbType.Int, true),
new TableColumn("F10", SqlDbType.Int, true), new TableColumn("F10_calc", SqlDbType.Int, true),
});
System.Data.SqlClient.SqlConnectionStringBuilder builder_CurrentDbConnection
= new System.Data.SqlClient.SqlConnectionStringBuilder(TestDb.getNewSqlConnection().ConnectionString);
string Current_InitialCatalog = builder_CurrentDbConnection.InitialCatalog;
string Current_DataSource = builder_CurrentDbConnection.DataSource;
SqlSource <Datensatz> DBSource = new SqlSource <Datensatz>(TestDb.getNewSqlConnection()
, string.Format("select [Key],F1,F2,F3,F4,F5,F6,F7,F8,F9,F10 from {0}", QuellObjekt)
);
DBSource.DataMappingMethod = ReaderAdapter.Read;
SqlDestination <Datensatz> Ziel_Schreibe = new SqlDestination <Datensatz>();
Ziel_Schreibe.ObjectName = ZielObjekt;
Ziel_Schreibe.FieldCount = FieldCount;
Ziel_Schreibe.ObjectMappingMethod = WriterAdapter.Fill;
Ziel_Schreibe.SqlConnection = TestDb.SqlConnection;
Graph g = new Graph();
g.GetVertex(0, DBSource);
g.GetVertex(1, new RowTransformation <Datensatz>(RowTransformationDB));
g.GetVertex(2, Ziel_Schreibe);
g.AddEdge(0, 1); // connect 0 to 1
g.AddEdge(1, 2); // connect 1 to 2
//TestHelper.VisualizeGraph(g);
int MaxDegreeOfParallelism = 1;
new ExecuteSQLTask(TestDb.getNewSqlConnection()).ExecuteNonQuery(string.Format("truncate table {0}", ZielObjekt));
Debug.WriteLine("Start Laufzeittest MaxDegreeOfParallelism {0} ... ", MaxDegreeOfParallelism);
Stopwatch s = Stopwatch.StartNew();
DBSource.SqlConnection = TestDb.getNewSqlConnection();
DataFlowTask <Datensatz> .Execute("Test dataflow task", 10000, MaxDegreeOfParallelism, g);
Debug.WriteLine("Laufzeit in ms: {0}", s.ElapsedMilliseconds);
MaxDegreeOfParallelism = 5;
new ExecuteSQLTask(TestDb.getNewSqlConnection()).ExecuteNonQuery(string.Format("truncate table {0}", ZielObjekt));
Debug.WriteLine("Start Laufzeittest MaxDegreeOfParallelism {0} ... ", MaxDegreeOfParallelism);
s = Stopwatch.StartNew();
DBSource.SqlConnection = TestDb.getNewSqlConnection();
DataFlowTask <Datensatz> .Execute("Test dataflow task", 10000, MaxDegreeOfParallelism, g);
Debug.WriteLine("Laufzeit in ms: {0}", s.ElapsedMilliseconds);
MaxDegreeOfParallelism = 10;
new ExecuteSQLTask(TestDb.getNewSqlConnection()).ExecuteNonQuery(string.Format("truncate table {0}", ZielObjekt));
Debug.WriteLine("Start Laufzeittest MaxDegreeOfParallelism {0} ... ", MaxDegreeOfParallelism);
s = Stopwatch.StartNew();
DBSource.SqlConnection = TestDb.getNewSqlConnection();
DataFlowTask <Datensatz> .Execute("Test dataflow task", 10000, MaxDegreeOfParallelism, g);
Debug.WriteLine("Laufzeit in ms: {0}", s.ElapsedMilliseconds);
Assert.AreEqual(Anzahl_je_Faktor * Anzahl_Faktoren, new ExecuteSQLTask(TestDb.SqlConnection).ExecuteScalar(string.Format("select count(*) from {0}", QuellObjekt)));
}
}
public void TestSampleDataflow()
{
using (TestDb)
{
TableColumn keyCol = new TableColumn("Key", SqlDbType.Int, false, true, true);
TableColumn col1 = new TableColumn("Col1", SqlDbType.NVarChar, 100, false);
TableColumn col2 = new TableColumn("Col2", SqlDbType.NVarChar, 50, true);
TableColumn col3 = new TableColumn("Col3", SqlDbType.Int, false);
string destSchema = "test";
string destTable1 = "Staging1";
string destObject1 = $"[{destSchema}].[{destTable1}]";
new DropAndCreateTableTask(TestDb.getNewSqlConnection()).Execute(destSchema, destTable1, new List <TableColumn>()
{
keyCol, col1, col2, col3
});
SqlDestination <string[]> destination1 = new SqlDestination <string[]>();
destination1.ObjectName = destObject1;
destination1.FieldCount = 4;
destination1.ObjectMappingMethod = WriterAdapter_SampleDataflow.Fill;
destination1.SqlConnection = TestDb.getNewSqlConnection();
string destTable2 = "Staging2";
string destObject2 = $"[{destSchema}].[{destTable2}]";
new DropAndCreateTableTask(TestDb.getNewSqlConnection()).Execute(destSchema, destTable2, new List <TableColumn>()
{
keyCol, col1, col2, col3
});
SqlDestination <string[]> destination2 = new SqlDestination <string[]>();
destination2.ObjectName = destObject2;
destination2.FieldCount = 4;
destination2.ObjectMappingMethod = WriterAdapter_SampleDataflow.Fill;
destination2.SqlConnection = TestDb.getNewSqlConnection();
CSVSource <string[]> CSVSource =
new CSVSource <string[]>("DataFlow/InputData.csv");
Graph g = new Graph();
g.GetVertex(0, CSVSource);
g.GetVertex(1, new RowTransformation <string[]>(RowTransformation1));
g.GetVertex(11, new RowTransformation <string[]>(RowTransformation2));
g.GetVertex(10, new BroadCast <string[]>(CloneTransformation1));
g.GetVertex(12, new RowTransformationMany <string[]>(RowTransformationMany));
g.GetVertex(20, new RowTransformation <string[]>(RowTransformation3));
g.GetVertex(100, destination1);
g.GetVertex(110, destination2);
Edge e1 = g.AddEdge(0, 1); // connect 0 to 1
Edge e2 = g.AddEdge(1, 10); // connect 1 to 10
Edge e3 = g.AddEdge(10, 20);
Edge e4 = g.AddEdge(20, 100);
Edge e5 = g.AddEdge(10, 11);
Edge e6 = g.AddEdge(11, 12);
Edge e7 = g.AddEdge(12, 110);
DataFlowTask <string[]> .Execute("Test dataflow task", 1000, 1, g);
e2.cost = counter_RowTransformation1;
e6.cost = counter_RowTransformation2;
e4.cost = counter_RowTransformation3;
e7.cost = counter_RowTransformationMany;
//TestHelper.VisualizeGraph(g);
Assert.AreEqual(4, new ExecuteSQLTask(TestDb.getNewSqlConnection()).ExecuteScalar(string.Format("select count(*) from {0}", destObject1)));
Assert.AreEqual(8, new ExecuteSQLTask(TestDb.getNewSqlConnection()).ExecuteScalar(string.Format("select count(*) from {0}", destObject2)));
}
}
