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 static void RecreateDatabase(string dbName, SqlConnectionString connectionString)
{
var masterConnection = new SqlConnectionManager(connectionString.GetMasterConnection());
DropDatabaseTask.DropIfExists(masterConnection, dbName);
CreateDatabaseTask.Create(masterConnection, dbName);
}
void RecreateDatabase(string dbName, SqlConnectionString connectionString)
{
var masterConnection = new SqlConnectionManager(connectionString.CloneWithMasterDbName());
DropDatabaseTask.DropIfExists(masterConnection, dbName);
CreateDatabaseTask.Create(masterConnection, dbName);
}
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.Delete(dbName);
Assert.IsFalse(sqlTask.ExecuteScalarAsBool());
}
public void TestCreateWithAllParameters()
{
string dbName = TestContext.Properties["dbName"].ToString();
var sqlTask = new SqlTask("Get assert data", $"select cast(db_id('{dbName}') as int)");
DropDatabaseTask.Delete(dbName);
Assert.IsFalse(sqlTask.ExecuteScalarAsBool());
CreateDatabaseTask.Create(dbName, RecoveryModel.Simple, "Latin1_General_CS_AS");
Assert.IsTrue(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());
}
static void Preparation()
{
SqlConnectionManager masterConnection = new SqlConnectionManager("Data Source=.;Integrated Security=false;User=sa;password=reallyStrongPwd123");
DropDatabaseTask.DropIfExists(masterConnection, "demo");
CreateDatabaseTask.Create(masterConnection, "demo");
SqlConnectionManager dbConnection = new SqlConnectionManager("Data Source=.;Initial Catalog=demo;Integrated Security=false;User=sa;password=reallyStrongPwd123");
CreateTableTask.Create(dbConnection, "OrderTable", new List <TableColumn>()
{
new TableColumn("Id", "INT"),
new TableColumn("Item", "NVARCHAR(200)"),
new TableColumn("Quantity", "INT"),
new TableColumn("Price", "MONEY")
});
}
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 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);
}
static void Main(string[] args)
{
//Set up the connection manager to master
var masterConnection = new SqlConnectionManager("Data Source=localhost;User Id=sa;Password=YourStrong@Passw0rd;");
//Recreate database
DropDatabaseTask.DropIfExists(masterConnection, "demo");
CreateDatabaseTask.Create(masterConnection, "demo");
//Get connection manager to previously create database
var dbConnection = new SqlConnectionManager("Data Source=localhost;User Id=sa;Password=YourStrong@Passw0rd;Initial Catalog=demo;");
//Create destination table
CreateTableTask.Create(dbConnection, "Table1", new List <TableColumn>()
{
new TableColumn("ID", "int", allowNulls: false, isPrimaryKey: true, isIdentity: true),
new TableColumn("Col1", "nvarchar(100)", allowNulls: true),
new TableColumn("Col2", "smallint", allowNulls: true)
});
//Create dataflow for loading data from csv into table
CsvSource <string[]> source = new CsvSource <string[]>("input.csv");
RowTransformation <string[], MyData> row = new RowTransformation <string[], MyData>(
input =>
new MyData()
{
Col1 = input[0], Col2 = input[1]
}
);
DbDestination <MyData> dest = new DbDestination <MyData>(dbConnection, "Table1");
//Link components & run data flow
source.LinkTo(row);
row.LinkTo(dest);
source.Execute();
dest.Wait();
//Check if data exists in destination
SqlTask.ExecuteReader(dbConnection, "Read all data from table1",
"select Col1, Col2 from Table1",
col1 => Console.WriteLine(col1.ToString() + ","),
col2 => Console.WriteLine(col2.ToString()));
Console.WriteLine("Press any key to continue...");
Console.ReadLine();
}
static void Preparation()
{
//Recreate database if it doesn't exist
SqlConnectionManager masterConnection = new SqlConnectionManager("Data Source=localhost;User Id=sa;Password=YourStrong@Passw0rd;");
DropDatabaseTask.DropIfExists(masterConnection, "demo");
CreateDatabaseTask.Create(masterConnection, "demo");
SqlConnectionManager dbConnection = new SqlConnectionManager("Data Source=localhost;User Id=sa;Password=YourStrong@Passw0rd;Initial Catalog=demo;");
//Create destination table
CreateTableTask.Create(dbConnection, "OrderTable", new List <TableColumn>()
{
new TableColumn("Id", "INT", allowNulls: false, isPrimaryKey: true, isIdentity: true),
new TableColumn("Item", "NVARCHAR(50)"),
new TableColumn("Quantity", "INT"),
new TableColumn("Price", "DECIMAL(10,2)")
});
}
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");
}
static void Main(string[] args)
{
var masterConnection = new SqlConnectionManager("Data Source=.;Integrated Security=false;User=sa;password=reallyStrongPwd123");
DropDatabaseTask.DropIfExists(masterConnection, "demo");
CreateDatabaseTask.Create(masterConnection, "demo");
var dbConnection = new SqlConnectionManager("Data Source=.;Initial Catalog=demo;Integrated Security=false;User=sa;password=reallyStrongPwd123");
CreateTableTask.Create(dbConnection, "Table1", new List <TableColumn>()
{
new TableColumn("ID", "int", allowNulls: false, isPrimaryKey: true, isIdentity: true),
new TableColumn("Col1", "nvarchar(100)", allowNulls: true),
new TableColumn("Col2", "smallint", allowNulls: true)
});
CsvSource <string[]> source = new CsvSource <string[]>("input.csv");
RowTransformation <string[], MyData> row = new RowTransformation <string[], MyData>(
input => new MyData()
{
Col1 = input[0], Col2 = input[1]
});
DbDestination <MyData> dest = new DbDestination <MyData>(dbConnection, "Table1");
source.LinkTo(row);
row.LinkTo(dest);
source.Execute();
dest.Wait();
SqlTask.ExecuteReader(dbConnection, "Read all data from table1",
"select Col1, Col2 from Table1",
col1 => Console.WriteLine(col1.ToString() + ","),
col2 => Console.WriteLine(col2.ToString()));
Console.WriteLine("Press any key to continue...");
Console.ReadLine();
}
public void NotSupportedWithSQLite()
{
Assert.Throws <ETLBoxNotSupportedException>(
() => DropDatabaseTask.Drop(Config.SQLiteConnection.ConnectionManager("ControlFlow"), "Test")
);
}
public void NotSupportedWithSQLite()
{
Assert.Throws <ETLBoxNotSupportedException>(
() => DropDatabaseTask.Drop(SQLiteConnection, "Test")
);
}
