public void MoreColumnsInDestination()
{
//Arrange
CreateTableTask.Create(SqlConnection, "CsvSourceDynamicColsInDest",
new List <TableColumn>()
{
new TableColumn("Col2", "VARCHAR(100)", allowNulls: true),
new TableColumn("Id", "INT", allowNulls: false, isPrimaryKey: true, isIdentity: true),
new TableColumn("Col1", "INT", allowNulls: true),
new TableColumn("ColX", "INT", allowNulls: true),
});
DbDestination <ExpandoObject> dest = new DbDestination <ExpandoObject>(SqlConnection, "CsvSourceDynamicColsInDest");
//Act
CsvSource <ExpandoObject> source = new CsvSource <ExpandoObject>("res/CsvSource/TwoColumnsForDynamic.csv");
source.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
Assert.Equal(3, RowCountTask.Count(SqlConnection, "CsvSourceDynamicColsInDest"));
Assert.Equal(1, RowCountTask.Count(SqlConnection, "CsvSourceDynamicColsInDest", $"Col1 = 1 AND Col2='Test1' AND Id > 0 AND ColX IS NULL"));
Assert.Equal(1, RowCountTask.Count(SqlConnection, "CsvSourceDynamicColsInDest", $"Col1 = 2 AND Col2='Test2' AND Id > 0 AND ColX IS NULL"));
Assert.Equal(1, RowCountTask.Count(SqlConnection, "CsvSourceDynamicColsInDest", $"Col1 = 3 AND Col2='Test3' AND Id > 0 AND ColX IS NULL"));
}
public void CreateTableWithDefault()
{
//Arrange
List <TableColumn> columns = new List <TableColumn>()
{
new TableColumn("value1", "int", allowNulls: false)
{
DefaultValue = "0"
},
new TableColumn("value2", "nvarchar(10)", allowNulls: false)
{
DefaultValue = "Test"
},
new TableColumn("value3", "decimal", allowNulls: false)
{
DefaultConstraintName = "TestConstraint", DefaultValue = "3.12"
}
};
//Act
CreateTableTask.Create(SqlConnection, "dbo.CreateTable8", columns);
//Assert
Assert.True(IfExistsTask.IsExisting(SqlConnection, "CreateTable8"));
var td = TableDefinition.GetDefinitionFromTableName("CreateTable8", SqlConnection);
Assert.Contains(td.Columns, col => col.DefaultValue == "0");
Assert.Contains(td.Columns, col => col.DefaultValue == "Test");
Assert.Contains(td.Columns, col => col.DefaultValue == "3.12" && col.DefaultConstraintName == "TestConstraint");
}
public void DestinationMoreColumnsThanSource(IConnectionManager connection)
{
//Arrange
TwoColumnsTableFixture source2Columns = new TwoColumnsTableFixture(connection, "SourceDynamicDiffCols");
source2Columns.InsertTestData();
CreateTableTask.Create(connection, "DestinationDynamicDiffCols",
new List <TableColumn>()
{
new TableColumn("Col5", "VARCHAR(100)", allowNulls: true),
new TableColumn("Col2", "VARCHAR(100)", allowNulls: true),
new TableColumn("Col1", "INT", allowNulls: true),
new TableColumn("ColX", "INT", allowNulls: true),
});
//Act
DBSource <ExpandoObject> source = new DBSource <ExpandoObject>(connection, "SourceDynamicDiffCols");
DBDestination <ExpandoObject> dest = new DBDestination <ExpandoObject>(connection, "DestinationDynamicDiffCols");
source.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
string QB = ConnectionManagerSpecifics.GetBeginQuotation(connection);
string QE = ConnectionManagerSpecifics.GetEndQuotation(connection);
Assert.Equal(3, RowCountTask.Count(connection, "DestinationDynamicDiffCols"));
Assert.Equal(1, RowCountTask.Count(connection, "DestinationDynamicDiffCols", $"{QB}Col1{QE} = 1 AND {QB}Col2{QE}='Test1' AND {QB}Col5{QE} IS NULL AND {QB}ColX{QE} IS NULL"));
Assert.Equal(1, RowCountTask.Count(connection, "DestinationDynamicDiffCols", $"{QB}Col1{QE} = 2 AND {QB}Col2{QE}='Test2' AND {QB}Col5{QE} IS NULL AND {QB}ColX{QE} IS NULL"));
Assert.Equal(1, RowCountTask.Count(connection, "DestinationDynamicDiffCols", $"{QB}Col1{QE} = 3 AND {QB}Col2{QE}='Test3' AND {QB}Col5{QE} IS NULL AND {QB}ColX{QE} IS NULL"));
}
public static void Prepare()
{
TableDefinition SourceTableDef = new TableDefinition("source",
new List <TableColumn>()
{
new TableColumn("Key", "int", allowNulls: false, isPrimaryKey: true),
new TableColumn("Value", "nvarchar(100)", allowNulls: false)
});
TableDefinition DestinationTableDef = new TableDefinition("destination",
new List <TableColumn>()
{
new TableColumn("Key", "int", allowNulls: false, isPrimaryKey: true),
new TableColumn("Value", "nvarchar(100)", allowNulls: false)
});
CreateTableTask.Create(SqlConnection, SourceTableDef);
CreateTableTask.Create(SqlConnection, DestinationTableDef);
//Create demo tables & fill with demo data
SqlTask.ExecuteNonQuery(SqlConnection, "Fill source table",
"INSERT INTO source VALUES (1, 'Test - Insert')");
SqlTask.ExecuteNonQuery(SqlConnection, "Fill source table",
"INSERT INTO source VALUES (2, 'Test - Update')");
SqlTask.ExecuteNonQuery(SqlConnection, "Fill source table",
"INSERT INTO source VALUES (3, 'Test - Exists')");
SqlTask.ExecuteNonQuery(SqlConnection, "Fill destination table",
"INSERT INTO destination VALUES (2, 'XXX')");
SqlTask.ExecuteNonQuery(SqlConnection, "Fill source table",
"INSERT INTO destination VALUES (3, 'Test - Exists')");
SqlTask.ExecuteNonQuery(SqlConnection, "Fill source table",
"INSERT INTO destination VALUES (4, 'Test - Deleted')");
}
public void HashCalculationForOneTable()
{
//Arrange
CreateSchemaTask.Create(Connection, "hash");
List <TableColumn> columns = new List <TableColumn>()
{
new TableColumn("value", "int")
};
CreateTableTask.Create(Connection, "DatabaseHash", columns);
//Act
string hash = CalculateDatabaseHashTask.Calculate(Connection, new List <string>()
{
"hash"
});
string hashAgain = CalculateDatabaseHashTask.Calculate(Connection, new List <string>()
{
"hash"
});
//Assert
Assert.Equal(hash, hashAgain);
Assert.Equal("DA39A3EE5E6B4B0D3255BFEF95601890AFD80709", hash);
}
public void CreateTableWithDefault(IConnectionManager connection)
{
//Arrange
List <TableColumn> columns = new List <TableColumn>()
{
new TableColumn("value1", "INT", allowNulls: false)
{
DefaultValue = "0"
},
new TableColumn("value2", "NVARCHAR(10)", allowNulls: false)
{
DefaultValue = "Test"
},
new TableColumn("value3", "DECIMAL(10,2)", allowNulls: false)
{
DefaultValue = "3.12"
}
};
//Act
CreateTableTask.Create(connection, "CreateTable8", columns);
//Assert
Assert.True(IfTableOrViewExistsTask.IsExisting(connection, "CreateTable8"));
var td = TableDefinition.GetDefinitionFromTableName("CreateTable8", connection);
Assert.Contains(td.Columns, col => col.DefaultValue == "0");
Assert.Contains(td.Columns, col => col.DefaultValue == "Test" || col.DefaultValue == "'Test'");
Assert.Contains(td.Columns, col => col.DefaultValue == "3.12");
}
public void SimpleFlowWithDefaultConnection()
{
//Arrange
CreateTableTask.Create("TestSourceTable",
new List <TableColumn>()
{
new TableColumn("Col1", "VARCHAR(100)")
});
SqlTask.ExecuteNonQuery("Insert test data", "INSERT INTO TestSourceTable VALUES ('T');");
CreateTableTask.Create("TestDestinationTable",
new List <TableColumn>()
{
new TableColumn("Col1", "VARCHAR(100)")
});
DbSource <MySimpleRow> source = new DbSource <MySimpleRow>("TestSourceTable");
DbDestination <MySimpleRow> dest = new DbDestination <MySimpleRow>("TestDestinationTable");
//Act
source.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
Assert.True(RowCountTask.Count("TestDestinationTable").Value == 1);
}
public void CopyTableUsingTableDefinition(IConnectionManager connection)
{
//Arrange
List <TableColumn> columns = new List <TableColumn>()
{
new TableColumn("Id", "INT", allowNulls: false, isPrimaryKey: true, isIdentity: true),
new TableColumn("value1", "NVARCHAR(10)", allowNulls: true),
new TableColumn("value2", "DECIMAL(10,2)", allowNulls: false)
{
DefaultValue = "3.12"
}
};
CreateTableTask.Create(connection, "CreateTable10", columns);
//Act
var definition =
TableDefinition.GetDefinitionFromTableName(connection, "CreateTable10");
definition.Name = "CreateTable10_copy";
CreateTableTask.Create(connection, definition);
//Assert
Assert.True(IfTableOrViewExistsTask.IsExisting(connection, "CreateTable10_copy"));
}
public void SpecialCharsInTableName(IConnectionManager connection)
{
//Arrange
List <TableColumn> columns = new List <TableColumn>()
{
new TableColumn("Id1", "INT", allowNulls: false, isPrimaryKey: true),
new TableColumn("Id2", "INT", allowNulls: false, isPrimaryKey: true),
new TableColumn("value", "DATE", allowNulls: true)
};
string tableName = "";
if (connection.GetType() == typeof(SqlConnectionManager))
{
tableName = @"[dbo].[ T""D"" 1 ]";
}
else if (connection.GetType() == typeof(PostgresConnectionManager))
{
tableName = @"""public""."" T [D] 1 """;
}
else
{
tableName = @""" T [D] 1 """;
}
CreateTableTask.Create(connection, tableName, columns);
//Assert
Assert.True(IfTableOrViewExistsTask.IsExisting(connection, tableName));
var td = TableDefinition.GetDefinitionFromTableName(tableName, connection);
Assert.True(td.Columns.Where(col => col.IsPrimaryKey && col.Name.StartsWith("Id")).Count() == 2);
}
public void CreateTableWithPKConstraintName(IConnectionManager connection)
{
var columns = new List <TableColumn>()
{
new TableColumn
{
Name = "ThisIsAReallyLongAndPrettyColumnNameWhichICantChange",
DataType = "int",
IsPrimaryKey = true,
},
new TableColumn
{
Name = "JustRandomColumn",
DataType = "int"
},
};
var tableDefinition = new TableDefinition("ThisIsAReallyLongTableWhichICantChange", columns);
tableDefinition.PrimaryKeyConstraintName = "shortname";
CreateTableTask.Create(connection, tableDefinition);
var td = TableDefinition.GetDefinitionFromTableName(connection, "ThisIsAReallyLongTableWhichICantChange");
Assert.True(td.Columns.Where(col => col.IsPrimaryKey &&
col.Name == "ThisIsAReallyLongAndPrettyColumnNameWhichICantChange").Count() == 1);
}
public void ReCreateIndex(IConnectionManager connection)
{
//Arrange
string indexName = "ix_IndexTest2";
CreateTableTask.Create(connection, "IndexCreationTable2", new List <TableColumn>()
{
new TableColumn("Key1", "INT", allowNulls: false),
new TableColumn("Key2", "INT", allowNulls: true),
});
CreateIndexTask.CreateOrRecreate(connection, indexName, "IndexCreationTable2",
new List <string>()
{
"Key1", "Key2"
});
//Act
CreateIndexTask.CreateOrRecreate(connection, indexName, "IndexCreationTable2",
new List <string>()
{
"Key1", "Key2"
});
//Assert
Assert.True(IfIndexExistsTask.IsExisting(connection, "ix_IndexTest2", "IndexCreationTable2"));
}
public void CopyTableFromOracleToSqlServer()
{
//Arrange
List<TableColumn> columns = new List<TableColumn>() {
new TableColumn("Id", "INT",allowNulls:false, isPrimaryKey:true, isIdentity:true),
new TableColumn("value1", "NVARCHAR(10)",allowNulls:true),
new TableColumn("value2", "DECIMAL(10,2)",allowNulls:false),
new TableColumn("value3", "DATE",allowNulls:false)
};
CreateTableTask.Create(OracleConnection, "CopyTable_Oracle2Sql", columns);
//Act
var definition =
TableDefinition.FromTableName(OracleConnection, "CopyTable_Oracle2Sql");
definition.Name = "CopyTable_Oracle2Sql_copy";
var ct = new CreateTableTask(definition)
{
IgnoreCollation = true,
ConnectionManager = SqlConnection
};
ct.Create();
//Assert
Assert.True(IfTableOrViewExistsTask.IsExisting(SqlConnection, "CopyTable_Oracle2Sql_copy"));
}
public void CreateIndexWithInclude()
{
//Arrange
string indexName = "ix_IndexTest3";
CreateTableTask.Create(SqlConnection, "dbo.IndexCreation3", new List <TableColumn>()
{
new TableColumn("Key1", "INT", allowNulls: false),
new TableColumn("Key2", "CHAR(2)", allowNulls: true),
new TableColumn("Value1", "NVARCHAR(10)", allowNulls: true),
new TableColumn("Value2", "DECIMAL(10,2)", allowNulls: false),
});
//Act
CreateIndexTask.CreateOrRecreate(SqlConnection, indexName, "dbo.IndexCreation3",
new List <string>()
{
"Key1", "Key2"
},
new List <string>()
{
"Value1", "Value2"
});
//Assert
Assert.True(IfIndexExistsTask.IsExisting(SqlConnection, "ix_IndexTest3", "dbo.IndexCreation3"));
}
private void InitCreateTableTask()
{
List <ITableColumn> lpColumns = new List <ITableColumn>()
{
new TableColumn("id", "BIGINT", allowNulls: false, isPrimaryKey: true, isIdentity: true),
new TableColumn("start_date", "DATETIME", allowNulls: false),
new TableColumn("end_date", "DATETIME", allowNulls: true),
new TableColumn("source", "NVARCHAR(20)", allowNulls: true),
new TableColumn("process_name", "NVARCHAR(100)", allowNulls: false)
{
DefaultValue = "N/A"
},
new TableColumn("start_message", "NVARCHAR(4000)", allowNulls: true),
new TableColumn("is_running", "SMALLINT", allowNulls: false)
{
DefaultValue = "1"
},
new TableColumn("end_message", "NVARCHAR(4000)", allowNulls: true),
new TableColumn("was_successful", "SMALLINT", allowNulls: false)
{
DefaultValue = "0"
},
new TableColumn("abort_message", "NVARCHAR(4000)", allowNulls: true),
new TableColumn("was_aborted", "SMALLINT", allowNulls: false)
{
DefaultValue = "0"
}
};
LoadProcessTable = new CreateTableTask(LoadProcessTableName, lpColumns)
{
DisableLogging = true
};
}
public void CreateTableWithComputedColumn(IConnectionManager connection)
{
if (connection.GetType() != typeof(SQLiteConnectionManager) &&
connection.GetType() != typeof(PostgresConnectionManager))
{
//Arrange
List <TableColumn> columns = new List <TableColumn>()
{
new TableColumn("value1", "INT", allowNulls: false),
new TableColumn("value2", "INT", allowNulls: false),
new TableColumn("compValue", "BIGINT", allowNulls: true)
{
ComputedColumn = "(value1 * value2)"
}
};
//Act
CreateTableTask.Create(connection, "CreateTable9", columns);
//Assert
Assert.True(IfTableOrViewExistsTask.IsExisting(connection, "CreateTable9"));
var td = TableDefinition.GetDefinitionFromTableName("CreateTable9", connection);
if (connection.GetType() == typeof(SqlConnectionManager))
{
Assert.Contains(td.Columns, col => col.ComputedColumn == "[value1]*[value2]");
}
else if (connection.GetType() == typeof(MySqlConnectionManager))
{
Assert.Contains(td.Columns, col => col.ComputedColumn == "(`value1` * `value2`)");
}
}
}
private static void CreateSourceAndDestinationTables(IConnectionManager sourceConnection, IConnectionManager destConnection, string QB, string QE)
{
DropTableTask.DropIfExists(sourceConnection, "Name");
CreateTableTask.Create(sourceConnection, "Name",
new List <TableColumn>()
{
new TableColumn("ID", "INT", false, true, true),
new TableColumn("FIRST_NAME", "NVARCHAR(100)", true),
new TableColumn("LAST_NAME", "NVARCHAR(100)", true)
});
DropTableTask.DropIfExists(sourceConnection, "People");
CreateTableTask.Create(destConnection, "People",
new List <TableColumn>()
{
new TableColumn("Id", "INT", false, true),
new TableColumn("FirstName", "NVARCHAR(100)", true),
new TableColumn("LastName", "NVARCHAR(100)", true)
});
SqlTask.ExecuteNonQuery(sourceConnection, "Test data",
"INSERT INTO Name (FIRST_NAME, LAST_NAME) VALUES ('Bugs', NULL)");
SqlTask.ExecuteNonQuery(sourceConnection, "Test data",
"INSERT INTO Name (FIRST_NAME, LAST_NAME) VALUES (NULL, 'Pig')");
SqlTask.ExecuteNonQuery(sourceConnection, "Test data",
"INSERT INTO Name (FIRST_NAME, LAST_NAME) VALUES ('Franky', NULL)");
SqlTask.ExecuteNonQuery(destConnection, "Test data",
$"INSERT INTO {QB}People{QE} ({QB}Id{QE}, {QB}FirstName{QE}, {QB}LastName{QE}) VALUES (1, 'Bugs', NULL)");
SqlTask.ExecuteNonQuery(destConnection, "Test data",
$"INSERT INTO {QB}People{QE} ({QB}Id{QE}, {QB}FirstName{QE}, {QB}LastName{QE}) VALUES (2, 'Peggy', NULL)");
}
public void DestinationWithIdentityColumn(IConnectionManager connection)
{
//Arrange
TwoColumnsTableFixture source2Columns = new TwoColumnsTableFixture(connection, "SourceDynamicIDCol");
source2Columns.InsertTestData();
CreateTableTask.Create(connection, "DestinationDynamicIdCol",
new List <TableColumn>()
{
new TableColumn("Id", "BIGINT", allowNulls: false, isPrimaryKey: true, isIdentity: true),
new TableColumn("Col2", "VARCHAR(100)", allowNulls: true),
new TableColumn("Col1", "INT", allowNulls: true),
new TableColumn("ColX", "INT", allowNulls: true),
});
//Act
DbSource <ExpandoObject> source = new DbSource <ExpandoObject>(connection, "SourceDynamicIDCol");
DbDestination <ExpandoObject> dest = new DbDestination <ExpandoObject>(connection, "DestinationDynamicIdCol");
source.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
string QB = ConnectionManagerSpecifics.GetBeginQuotation(connection);
string QE = ConnectionManagerSpecifics.GetEndQuotation(connection);
Assert.Equal(3, RowCountTask.Count(connection, "DestinationDynamicIdCol"));
Assert.Equal(1, RowCountTask.Count(connection, "DestinationDynamicIdCol", $"{QB}Col1{QE} = 1 AND {QB}Col2{QE}='Test1' AND {QB}Id{QE} > 0 AND {QB}ColX{QE} IS NULL"));
Assert.Equal(1, RowCountTask.Count(connection, "DestinationDynamicIdCol", $"{QB}Col1{QE} = 2 AND {QB}Col2{QE}='Test2' AND {QB}Id{QE} > 0 AND {QB}ColX{QE} IS NULL"));
Assert.Equal(1, RowCountTask.Count(connection, "DestinationDynamicIdCol", $"{QB}Col1{QE} = 3 AND {QB}Col2{QE}='Test3' AND {QB}Id{QE} > 0 AND {QB}ColX{QE} IS NULL"));
}
public void WithOwnImplementation()
{
//Arrange
List <TableColumn> columns = new List <TableColumn>()
{
new TableColumn("somedate", "ABC"),
new TableColumn("sometext", "TEXT")
};
//Act
var ctt = new CreateTableTask("CreateTableIDataTypeConverter", columns)
{
ConnectionManager = SqlConnection,
DataTypeConverter = new MyDataTypeConverter()
};
ctt.Create();
//Assert
Assert.True(IfTableOrViewExistsTask.IsExisting(SqlConnection, "CreateTableIDataTypeConverter"));
var td = TableDefinition.FromTableName(SqlConnection, "CreateTableIDataTypeConverter");
Assert.Collection <TableColumn>(td.Columns,
col => Assert.True(col.DataType == "DATETIME"),
col => Assert.True(col.DataType == "VARCHAR(MAX)")
);
}
public void TestDB2DBDataflow()
{
//TODO Table Objekt anlegen der Vollständigkeit halber
Table inputT = new Table();
inputT.Add(keyCol);
inputT.Add(col1);
Table outputT = new Table();
outputT.Add(keyCol);
outputT.Add(col2);
CreateTableTask.Create("test.Source", new List <TableColumn>()
{
keyCol, col1
});
CreateTableTask.Create("test.Destination", new List <TableColumn>()
{
keyCol, col2
});
NewDataFlowTask <InputDS, OutputDS> df = new NewDataFlowTask <InputDS, OutputDS>();
df.RowTrans = input => new OutputDS();
//DataFlowTask.Execute("Test dataflow task", "DataFlow/InputData.csv", "test.Staging", 3, RowTransformation, BatchTransformation);
//Assert.AreEqual(4, SqlTask.ExecuteScalar<int>("Check staging table", "select count(*) from test.Staging"));
}
public SqlTaskInParallelTests(ControlFlowDatabaseFixture dbFixture)
{
CreateTableTask.Create(Connection, "FastParallel",
new List <TableColumn>()
{
new TableColumn("id", "int")
});
}
public void MixedTypes(IConnectionManager connection)
{
CreateTableTask.Create(connection, "datatypedestination",
new List <TableColumn>()
{
new TableColumn("IntCol", "INT", allowNulls: true),
new TableColumn("LongCol", "BIGINT", allowNulls: true),
new TableColumn("DecimalCol", "FLOAT", allowNulls: true),
new TableColumn("DoubleCol", "FLOAT", allowNulls: true),
new TableColumn("DateTimeCol", "DATETIME", allowNulls: true),
new TableColumn("DateCol", "DATE", allowNulls: true),
new TableColumn("StringCol", "VARCHAR(200)", allowNulls: true),
new TableColumn("CharCol", "CHAR(1)", allowNulls: true),
new TableColumn("DecimalStringCol", "DECIMAL(12,10)", allowNulls: true),
new TableColumn("NullCol", "CHAR(1)", allowNulls: true),
});
//Arrange
MemorySource <MyDataTypeRow> source = new MemorySource <MyDataTypeRow>(
new List <MyDataTypeRow>()
{
new MyDataTypeRow()
{
IntCol = 1,
LongCol = -1,
DecimalCol = 2.3M,
DoubleCol = 5.4,
DateTimeCol = new DateTime(2010, 1, 1, 10, 10, 10),
DateCol = new DateTime(2020, 1, 1),
StringCol = "Test",
CharCol = 'T',
DecimalStringCol = "13.4566",
NullCol = null
}
});
//Act
DbDestination <MyDataTypeRow> dest = new DbDestination <MyDataTypeRow>("datatypedestination", connection);
source.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
// IntCol LongCol DecimalCol DoubleCol DateTimeCol DateCol StringCol CharCol DecimalStringCol NullCol
//1 - 1 2.3 5.4 2010 - 01 - 01 10:10:10.100 2020 - 01 - 01 Test T 13.4566000000 NULL
SqlTask.ExecuteReaderSingleLine(connection, "Check data", "SELECT * FROM datatypedestination",
col => Assert.True(Convert.ToInt32(col) == 1),
col => Assert.True(Convert.ToInt64(col) == -1),
col => Assert.True(Convert.ToDecimal(col) == 2.3M),
col => Assert.True(Convert.ToDecimal(col) == 5.4M),
col => Assert.True(Convert.ToDateTime(col) == new DateTime(2010, 1, 1, 10, 10, 10)),
col => Assert.True(Convert.ToDateTime(col) == new DateTime(2020, 1, 1)),
col => Assert.True(Convert.ToString(col) == "Test"),
col => Assert.True(Convert.ToString(col) == "T" || Convert.ToString(col) == "84"),
col => Assert.True(Convert.ToString(col).Replace("0", "") == "13.4566"),
col => Assert.True(col == null)
);
}
public void TestSimpleDataflow()
{
CreateTableTask.Create("test.Staging", new List <TableColumn>()
{
keyCol, col1, col2, col3
});
DataFlowTask.Execute("Test dataflow task", "DataFlow/InputData.csv", "test.Staging", 3, RowTransformation, BatchTransformation);
Assert.AreEqual(4, SqlTask.ExecuteScalar <int>("Check staging table", "select count(*) from test.Staging"));
}
public void CheckCreateTableTask()
{
//Arrange
//Act & Assert
Assert.Throws <ETLBoxException>(() =>
{
CreateTableTask.Create("test", new List <TableColumn>());
});
}
public void TestCreateTableWithNullable()
{
List <TableColumn> columns = new List <TableColumn>()
{
new TableColumn("value", "int"), new TableColumn("value2", "datetime", true)
};
CreateTableTask.Create("test.Table2", columns);
Assert.IsTrue(SqlTask.ExecuteScalarAsBool("Check if table exists", $"select count(*) from sys.objects where type = 'U' and object_id = object_id('test.Table2')"));
}
static void RecreateTargetTable()
{
DropTableTask.DropIfExists(sqlConnMan, "orders");
CreateTableTask.Create(sqlConnMan, "orders", new List <TableColumn>()
{
new TableColumn("Id", "INT", allowNulls: false, isPrimaryKey: true),
new TableColumn("Description", "VARCHAR(50)"),
new TableColumn("CustomerName", "VARCHAR(500)"),
new TableColumn("Quantity", "SMALLINT")
});
}
private void CreateDestinationTable(string tableName)
{
DropTableTask.DropIfExists(SqlConnection, tableName);
CreateTableTask.Create(SqlConnection, tableName,
new List <TableColumn>()
{
new TableColumn("Id", "UNIQUEIDENTIFIER", allowNulls: false, isPrimaryKey: true),
new TableColumn("LastUpdated", "DATETIMEOFFSET", allowNulls: false),
new TableColumn("Value", "CHAR(1)", allowNulls: false),
});
}
public void Prepare()
{
SqlConnectionManager conMan = new SqlConnectionManager(SqlServerConnectionString);
List <TableColumn> tc = new List <TableColumn>()
{
new TableColumn("Id", "INTEGER", allowNulls: false, isPrimaryKey: true, isIdentity: true),
new TableColumn("FullName", "VARCHAR(1000)", allowNulls: true)
};
CreateTableTask.Create(conMan, "FullNameTable", tc);
}
public void CreateTableWithDefaultConnection()
{
//Arrange
//Act
CreateTableTask.Create("TestTable",
new List <TableColumn>()
{
new TableColumn("value", "INT")
});
//Assert
Assert.True(IfTableOrViewExistsTask.IsExisting("TestTable"));
}
void ReCreateTable(IConnectionManager connection, string tableName)
{
DropTableTask.DropIfExists(connection, tableName);
CreateTableTask.Create(connection, tableName,
new List <TableColumn>()
{
new TableColumn("Key1", "INT", allowNulls: false, isPrimaryKey: true),
new TableColumn("Key2", "INT", allowNulls: false, isPrimaryKey: true),
new TableColumn("Value1", "VARCHAR(100)", allowNulls: true, isPrimaryKey: false),
});
}
public void TestLogging()
{
CreateLogTablesTask.CreateLog();
CreateTableTask.Create("test.Table8", new List <TableColumn>()
{
new TableColumn("value", "int")
});
Assert.AreEqual(2, new SqlTask("Find log entry", "select count(*) from etl.Log where TaskType='CREATETABLE' group by TaskHash")
{
DisableLogging = true
}.ExecuteScalar <int>());
}