public static TableDefinition GetDefinitionFromTableName(string tableName, IConnectionManager connection)
{
IfTableOrViewExistsTask.ThrowExceptionIfNotExists(connection, tableName);
ConnectionManagerType connectionType = ConnectionManagerSpecifics.GetType(connection);
if (connectionType == ConnectionManagerType.SqlServer)
{
return(ReadTableDefinitionFromSqlServer(tableName, connection));
}
else if (connectionType == ConnectionManagerType.SQLite)
{
return(ReadTableDefinitionFromSQLite(tableName, connection));
}
else if (connectionType == ConnectionManagerType.MySql)
{
return(ReadTableDefinitionFromMySqlServer(tableName, connection));
}
else if (connectionType == ConnectionManagerType.Postgres)
{
return(ReadTableDefinitionFromPostgres(tableName, connection));
}
else
{
throw new ETLBoxException("Unknown connection type - please pass a valid TableDefinition!");
}
}
private static void GetColumns(IConnectionManager connectionManager, string name, List <TableColumn> columns)
{
ValidateName(name);
IfTableOrViewExistsTask.ThrowExceptionIfNotExists(connectionManager, name);
ConnectionManagerType connectionType = ConnectionManagerSpecifics.GetType(connectionManager);
ObjectNameDescriptor TN = new ObjectNameDescriptor(name, connectionType);
if (connectionType == ConnectionManagerType.SqlServer)
{
GetColumnsFromSqlServer(connectionManager, TN, columns);
}
else if (connectionType == ConnectionManagerType.SQLite)
{
GetColumnsFromSQLite(connectionManager, TN, columns);
}
else if (connectionType == ConnectionManagerType.MySql)
{
GetColumnsFromMySqlServer(connectionManager, TN, columns);
}
else if (connectionType == ConnectionManagerType.Postgres)
{
GetColumnsFromPostgres(connectionManager, TN, columns);
}
else if (connectionType == ConnectionManagerType.Access)
{
GetColumnsFromAccess(connectionManager, TN, columns);
}
else
{
throw new ETLBoxException("Unknown connection type - please pass a valid TableDefinition!");
}
}
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 Test(IConnectionManager sourceConnection, IConnectionManager destConnection)
{
//Arrange
string QB = ConnectionManagerSpecifics.GetBeginQuotation(destConnection);
string QE = ConnectionManagerSpecifics.GetEndQuotation(destConnection);
CreateSourceAndDestinationTables(sourceConnection, destConnection, QB, QE);
//Act
var nameSource = new DbSource <Name>(sourceConnection, "Name");
var personMerge = new DbMerge <People>(destConnection, "People");
var transform = new RowTransformation <Name, People>(d =>
{
return(new People()
{
FirstName = d.FIRST_NAME,
LastName = d.LAST_NAME,
Id = d.ID
});
});
nameSource.LinkTo(transform);
transform.LinkTo(personMerge);
nameSource.Execute();
personMerge.Wait();
//Assert
Assert.Equal(1, RowCountTask.Count(destConnection, "People", $"{QB}Id{QE} = 1 AND {QB}FirstName{QE} = 'Bugs' AND {QB}LastName{QE} IS NULL"));
Assert.Equal(1, RowCountTask.Count(destConnection, "People", $"{QB}Id{QE} = 2 AND {QB}FirstName{QE} IS NULL AND {QB}LastName{QE} = 'Pig'"));
Assert.Equal(1, RowCountTask.Count(destConnection, "People", $"{QB}Id{QE} = 3 AND {QB}FirstName{QE} = 'Franky' AND {QB}LastName{QE} IS NULL"));
}
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 void CreateSchemaWithSpecialChar(IConnectionManager connection)
{
if (connection.GetType() != typeof(MySqlConnectionManager))
{
string QB = ConnectionManagerSpecifics.GetBeginQuotation(connection);
string QE = ConnectionManagerSpecifics.GetEndQuotation(connection);
//Arrange
string schemaName = $"{QB} s#!/ {QE}";
//Act
CreateSchemaTask.Create(connection, schemaName);
//Assert
Assert.True(IfSchemaExistsTask.IsExisting(connection, schemaName));
}
}
public TableNameDescriptor(string tableName, IConnectionManager connection)
{
this.FullName = tableName;
this.ConnectionType = ConnectionManagerSpecifics.GetType(connection);
}
public ObjectNameDescriptor(string objectName, IConnectionManager connection)
{
this.ObjectName = objectName;
this.ConnectionType = ConnectionManagerSpecifics.GetType(connection);
ParseSchemaAndTable();
}
