public void Start()
{
//Recreate database
ControlFlow.CurrentDbConnection = new SqlConnectionManager(new ConnectionString("Data Source=.;Integrated Security=SSPI;"));
DropDatabaseTask.Drop("DemoDB");
CreateDatabaseTask.Create("DemoDB");
ControlFlow.CurrentDbConnection = new SqlConnectionManager(new ConnectionString("Data Source=.;Integrated Security=SSPI;Initial Catalog=DemoDB;"));
//Logging (only works with existing configuration nlog config in App.config)
CreateLogTablesTask.CreateLog();
StartLoadProcessTask.Start("Process 1");
ControlFlow.STAGE = "Staging";
SqlTask.ExecuteNonQuery("some sql", "Select 1 as test");
TransferCompletedForLoadProcessTask.Complete();
ControlFlow.STAGE = "DataVault";
Sequence.Execute("some custom code", () => { });
LogTask.Warn("Some warning!");
EndLoadProcessTask.End("Everything successful");
string jsonLP = GetLoadProcessAsJSONTask.GetJSON();
string jsonLog = GetLogAsJSONTask.GetJSON(1);
}
public void CreateWithCollation(IConnectionManager connection)
{
//Arrange
string dbName = "ETLBox_" + HashHelper.RandomString(10);
string collation = "Latin1_General_CS_AS";
if (connection.GetType() == typeof(PostgresConnectionManager))
{
collation = "en_US.utf8";
}
if (connection.GetType() == typeof(MySqlConnectionManager))
{
collation = "latin1_swedish_ci";
}
//Act
CreateDatabaseTask.Create(connection, dbName, collation);
//Assert
var dbList = GetDatabaseListTask.List(connection);
Assert.Contains <string>(dbName, dbList);
//Cleanup
DropDatabaseTask.Drop(connection, dbName);
}
public void TestCreateWithAllParameters()
{
string dbName = TestContext.Properties["dbName"].ToString();
var sqlTask = new SqlTask("Get assert data", $"select cast(db_id('{dbName}') as int)");
DropDatabaseTask.Drop(dbName);
Assert.IsFalse(sqlTask.ExecuteScalarAsBool());
CreateDatabaseTask.Create(dbName, RecoveryModel.Simple, "Latin1_General_CS_AS");
Assert.IsTrue(sqlTask.ExecuteScalarAsBool());
}
public void TestDropDB()
{
string dbName = TestContext.Properties["dbName"].ToString();
var sqlTask = new SqlTask("Get assert data", $"select cast(db_id('{dbName}') as int)");
CreateDatabaseTask.Create(dbName);
Assert.IsTrue(sqlTask.ExecuteScalarAsBool());
DropDatabaseTask.Drop(dbName);
Assert.IsFalse(sqlTask.ExecuteScalarAsBool());
}
public void Preparation()
{
ControlFlow.CurrentDbConnection = new SqlConnectionManager(new ConnectionString("Data Source=.;Integrated Security=SSPI;"));
DropDatabaseTask.Drop("DemoDB");
CreateDatabaseTask.Create("DemoDB");
ControlFlow.CurrentDbConnection = new SqlConnectionManager(new ConnectionString("Data Source=.;Integrated Security=SSPI;Initial Catalog=DemoDB"));
CreateSchemaTask.Create("demo");
OrderDataTableDef.CreateTable();
CustomerTableDef.CreateTable();
CustomerRatingTableDef.CreateTable();
SqlTask.ExecuteNonQuery("Fill customer table", "INSERT INTO demo.Customer values('Sandra Kettler')");
SqlTask.ExecuteNonQuery("Fill customer table", "INSERT INTO demo.Customer values('Nick Thiemann')");
SqlTask.ExecuteNonQuery("Fill customer table", "INSERT INTO demo.Customer values('Zoe Rehbein')");
SqlTask.ExecuteNonQuery("Fill customer table", "INSERT INTO demo.Customer values('Margit Gries')");
}
public void Drop()
{
//Arrange
string dbName = "ETLBox_" + HashHelper.RandomString(10);
var sqlTask = new SqlTask("Get assert data", $"select cast(db_id('{dbName}') as int)")
{
ConnectionManager = MasterConnection
};
CreateDatabaseTask.Create(MasterConnection, dbName);
Assert.True(sqlTask.ExecuteScalarAsBool());
//Act
DropDatabaseTask.Drop(MasterConnection, dbName);
//Assert
Assert.False(sqlTask.ExecuteScalarAsBool());
}
public void IfDatabaseExists(IConnectionManager connection)
{
//Arrange
string dbName = ("ETLBox_" + HashHelper.RandomString(10)).ToLower();
var existsBefore = IfDatabaseExistsTask.IsExisting(connection, dbName);
//Act
SqlTask.ExecuteNonQuery(connection, "Create DB", $"CREATE DATABASE {dbName}");
var existsAfter = IfDatabaseExistsTask.IsExisting(connection, dbName);
//Assert
Assert.False(existsBefore);
Assert.True(existsAfter);
//Cleanup
DropDatabaseTask.Drop(connection, dbName);
}
public void Drop(IConnectionManager connection)
{
//Arrange
string dbName = "ETLBox_" + TestHashHelper.RandomString(10);
CreateDatabaseTask.Create(connection, dbName);
bool existsBefore = IfDatabaseExistsTask.IsExisting(connection, dbName);
//Act
DropDatabaseTask.Drop(connection, dbName);
//Assert
bool existsAfter = IfDatabaseExistsTask.IsExisting(connection, dbName);
Assert.True(existsBefore);
Assert.False(existsAfter);
}
public void CreateSimple(IConnectionManager connection)
{
//Arrange
string dbName = "ETLBox_" + HashHelper.RandomString(10);
var dbListBefore = GetDatabaseListTask.List(connection);
Assert.DoesNotContain <string>(dbName, dbListBefore);
//Act
CreateDatabaseTask.Create(connection, dbName);
//Assert
var dbListAfter = GetDatabaseListTask.List(connection);
Assert.Contains <string>(dbName, dbListAfter);
//Cleanup
DropDatabaseTask.Drop(connection, dbName);
}
public void Start()
{
//Basics
ControlFlow.CurrentDbConnection = new SqlConnectionManager(new ConnectionString("Data Source=.;Integrated Security=SSPI;"));
DropDatabaseTask.Drop("DemoDB");
CreateDatabaseTask.Create("DemoDB");
ControlFlow.CurrentDbConnection = new SqlConnectionManager(new ConnectionString("Data Source=.;Integrated Security=SSPI;Initial Catalog=DemoDB;"));
CreateSchemaTask.Create("demo");
CreateTableTask.Create("demo.table1", new List <TableColumn>()
{
new TableColumn(name: "key", dataType: "int", allowNulls: false, isPrimaryKey: true, isIdentity: true),
new TableColumn(name: "value", dataType: "nvarchar(100)", allowNulls: true)
});
SqlTask.ExecuteNonQuery("Insert data",
$@"insert into demo.table1 (value) select * from (values ('Ein Text'), ('Noch mehr Text')) as data(v)");
int count = RowCountTask.Count("demo.table1").Value;
Debug.WriteLine($"Found {count} entries in demo table!");
//Truncate:
//TruncateTableTask.Truncate("demo.table1");
//Batch processing / Go keyword:
//SqlTask.ExecuteNonQuery("sql with go keyword", @"insert into demo.table1 (value) select '####';
//go 2");
ControlFlow.CurrentDbConnection = new SMOConnectionManager(new ConnectionString("Data Source=.;Integrated Security=SSPI;Initial Catalog=DemoDB;"));
SqlTask.ExecuteNonQuery("sql with go keyword", @"insert into demo.table1 (value) select '####';
go 2");
AddFileGroupTask.AddFileGroup("FGName", "DemoDB", "200MB", "10MB", isDefaultFileGroup: true);
CRUDProcedureTask.CreateOrAlter("demo.proc1", "select 1 as test");
}
public void NotSupportedWithSQLite()
{
Assert.Throws <ETLBoxNotSupportedException>(
() => DropDatabaseTask.Drop(SQLiteConnection, "Test")
);
}
public void NotSupportedWithSQLite()
{
Assert.Throws <ETLBoxNotSupportedException>(
() => DropDatabaseTask.Drop(Config.SQLiteConnection.ConnectionManager("ControlFlow"), "Test")
);
}