public void CycleTestTwoTable()
{
DBTable table1 = new DBTable("dbo", "1");
DBColumn table1ID = new DBColumn(table1, "1_id", true, DBDatatype.integer);
table1.Columns = new List <DBColumn>()
{
table1ID
};
DBTable table2 = new DBTable("dbo", "2");
DBColumn table2ID = new DBColumn(table2, "2_id", true, DBDatatype.integer);
table2.Columns = new List <DBColumn>()
{
table2ID
};
TableMapping t1Mapping = new TableMapping(table1, TableMappingImportType.Insert, new ColumnMapping[0]);
TableMapping t2Mapping = new TableMapping(table2, TableMappingImportType.Insert, new ColumnMapping[0]);
TableColumnMapping t1ColMaping = new TableColumnMapping(t2Mapping, table2ID, table1ID, ColumnUse.Insert);
TableColumnMapping t2ColMaping = new TableColumnMapping(t1Mapping, table1ID, table2ID, ColumnUse.Insert);
t1Mapping.ColumnMappings = new ColumnMapping[] { t1ColMaping };
t2Mapping.ColumnMappings = new ColumnMapping[] { t2ColMaping };
TableMapping[] mappingArray = new TableMapping[] { t1Mapping, t2Mapping };
TableMappingOrderer tableMappingOrderer = new TableMappingOrderer(mappingArray);
TableMapping[] order = tableMappingOrderer.OrderTableMappings();
}
public void CycleTestFourTable()
{
DBTable table1 = new DBTable("dbo", "1");
DBColumn table1ID = new DBColumn(table1, "1_id", true, DBDatatype.integer);
table1.Columns = new List <DBColumn>()
{
table1ID
};
DBTable table2 = new DBTable("dbo", "2");
DBColumn table2ID = new DBColumn(table2, "2_id", true, DBDatatype.integer);
table2.Columns = new List <DBColumn>()
{
table2ID
};
DBTable table3 = new DBTable("dbo", "3");
DBColumn table3ID = new DBColumn(table3, "3_id", true, DBDatatype.integer);
table3.Columns = new List <DBColumn>()
{
table3ID
};
DBTable table4 = new DBTable("dbo", "4");
DBColumn table4ID = new DBColumn(table4, "4_id", true, DBDatatype.integer);
table4.Columns = new List <DBColumn>()
{
table4ID
};
TableMapping t1Mapping = new TableMapping(table1, TableMappingImportType.Insert, new ColumnMapping[0]);
TableMapping t2Mapping = new TableMapping(table2, TableMappingImportType.Insert, new ColumnMapping[0]);
TableMapping t3Mapping = new TableMapping(table3, TableMappingImportType.Insert, new ColumnMapping[0]);
TableMapping t4Mapping = new TableMapping(table4, TableMappingImportType.Insert, new ColumnMapping[0]);
TableColumnMapping t21ColMaping = new TableColumnMapping(t1Mapping, table1ID, table2ID, ColumnUse.Insert);
TableColumnMapping t24ColMaping = new TableColumnMapping(t4Mapping, table1ID, table2ID, ColumnUse.Insert);
TableColumnMapping t3ColMaping = new TableColumnMapping(t2Mapping, table1ID, table2ID, ColumnUse.Insert);
TableColumnMapping t4ColMaping = new TableColumnMapping(t3Mapping, table1ID, table2ID, ColumnUse.Insert);
t2Mapping.ColumnMappings = new ColumnMapping[] { t21ColMaping, t24ColMaping };
t3Mapping.ColumnMappings = new ColumnMapping[] { t3ColMaping };
t4Mapping.ColumnMappings = new ColumnMapping[] { t4ColMaping };
TableMapping[] mappingArray = new TableMapping[] { t1Mapping, t2Mapping, t3Mapping, t4Mapping };
TableMappingOrderer tableMappingOrderer = new TableMappingOrderer(mappingArray);
TableMapping[] order = tableMappingOrderer.OrderTableMappings();
}
public static ColumnMappingViewModel CreateBlankSourceViewModel(ColumnMappingImportType importType, TableMappingViewModel tableMappingViewModel,
DBColumn destinationColumn, ColumnUse columnUse)
{
switch (importType)
{
case (SQLImporter.ViewModel.ColumnMappingImportType.Excel):
ExcelColumnMapping excelColumnMapping = new ExcelColumnMapping("", destinationColumn, columnUse);
return(new ExcelColumnMappingViewModel(excelColumnMapping, tableMappingViewModel));
case (SQLImporter.ViewModel.ColumnMappingImportType.Table):
TableColumnMapping tableColumnMapping = new TableColumnMapping(null, null, destinationColumn, columnUse);
return(new TableColumnMappingViewModel(tableColumnMapping, tableMappingViewModel));
case (SQLImporter.ViewModel.ColumnMappingImportType.Literal):
LiteralColumnMapping literalColumnMapping = new LiteralColumnMapping("", LiteralType.String, destinationColumn, columnUse);
return(new LiteralColumnMappingViewModel(literalColumnMapping, tableMappingViewModel));
default:
NullColumnMapping nullColumnMapping = new NullColumnMapping(destinationColumn, columnUse);
return(new NullColumnMappingViewModel(nullColumnMapping, tableMappingViewModel));
}
}
public void StatementColumnMappingPartTest()
{
DBColumn col = new DBColumn(null, "TestCol", true, DBDatatype.integer);
SourceDataEntry entry = SourceDataEntry.CreateDataEntry("Test", DataType.String, "Test");
SourceDataRow row = new SourceDataRow(new SourceDataEntry[] { entry }, "");
DBTable table = new DBTable("dbo", "TestTable");
DBColumn col1 = new DBColumn(table, "TestCol1", true, DBDatatype.integer);
table.Columns = new List <DBColumn>()
{
col1
};
TableMapping tableMapping = new TableMapping(table, TableMappingImportType.Insert, null);
NullColumnMapping nullColumnMapping = new NullColumnMapping(col, ColumnUse.Insert);
LiteralColumnMapping literalColumnMapping1 = new LiteralColumnMapping("Test", LiteralType.String, col, ColumnUse.Insert);
LiteralColumnMapping literalColumnMapping2 = new LiteralColumnMapping("Test's", LiteralType.String, col, ColumnUse.Insert);
ExcelColumnMapping excelColumnMapping = new ExcelColumnMapping("Test", col, ColumnUse.Insert);
TableColumnMapping tableColMapping = new TableColumnMapping(tableMapping, col1, col, ColumnUse.Insert);
StatementColumnMappingPart nullColumnPart = new StatementColumnMappingPart(nullColumnMapping, row);
StatementColumnMappingPart literalColumnPart1 = new StatementColumnMappingPart(literalColumnMapping1, row);
StatementColumnMappingPart literalColumnPart2 = new StatementColumnMappingPart(literalColumnMapping2, row);
StatementColumnMappingPart excelColumnPart = new StatementColumnMappingPart(excelColumnMapping, row);
StatementColumnMappingPart tableColumnPart = new StatementColumnMappingPart(tableColMapping, row);
Assert.AreEqual("NULL", nullColumnPart.GetColumnMappingValue());
Assert.AreEqual("'Test'", literalColumnPart1.GetColumnMappingValue());
Assert.AreEqual("'Test''s'", literalColumnPart2.GetColumnMappingValue());
Assert.AreEqual("'Test'", excelColumnPart.GetColumnMappingValue());
StatementTableVariablePart tableVariablePart = new StatementTableVariablePart(tableMapping);
Assert.AreEqual(String.Format("(SELECT TOP 1 t.TestCol1 FROM {0} t)", tableVariablePart.GetTableVariable()),
tableColumnPart.GetColumnMappingValue());
}
public void TableBodyPartUpdateTest()
{
DBTable table = new DBTable("dbo", "TestTable");
DBColumn col1 = new DBColumn(table, "TestCol1", true, DBDatatype.integer);
DBColumn col2 = new DBColumn(table, "TestCol2", false, DBDatatype.nvarchar);
DBColumn col3 = new DBColumn(table, "TestCol3", false, DBDatatype.integer);
table.Columns = new List <DBColumn>()
{
col1, col2, col3
};
TableMapping sourceTablemapping = new TableMapping(new DBTable("dbo", "TestTable2"), TableMappingImportType.Insert, null);
ColumnMapping colMap1 = new TableColumnMapping(sourceTablemapping, col1, col1, ColumnUse.Where);
ColumnMapping colMap2 = new LiteralColumnMapping("2", LiteralType.String, col2, ColumnUse.Where);
ColumnMapping colMap3 = new LiteralColumnMapping("3", LiteralType.String, col3, ColumnUse.Set);
TableMapping tableMapping = new TableMapping(table, TableMappingImportType.Update, new ColumnMapping[] { colMap1, colMap2, colMap3 });
ImportConfiguration config = getTestImportConfig();
SourceDataEntry[] entries = new SourceDataEntry[] { SourceDataEntry.CreateDataEntry("", DataType.String, "") };
SourceDataRow[] rows = new SourceDataRow[] { new SourceDataRow(entries, "0") };
SourceDataTable dt = new SourceDataTable(rows, new string[] { "" });
StatementTableMappingPart part = new StatementTableMappingPart(tableMapping, dt.GetDataRow(0));
string[] bodyParts = part.GetStatementBodyPart().Split(new string[] { "\n" }, StringSplitOptions.None).Where(s => s.Length > 0).ToArray();
Assert.AreEqual(4, bodyParts.Length);
Assert.AreEqual("UPDATE dbo.TestTable", bodyParts[0]);
Assert.AreEqual("SET TestCol3='3'", bodyParts[1]);
Assert.AreEqual("OUTPUT inserted.TestCol1, inserted.TestCol2, inserted.TestCol3 INTO @sqlimport_table_" +
tableMapping.TableMappingReference.Replace(".", "_") + "(TestCol1, TestCol2, TestCol3)", bodyParts[2]);
Assert.AreEqual("WHERE TestCol1 = (SELECT TOP 1 t.TestCol1 FROM @sqlimport_table_" +
sourceTablemapping.TableMappingReference.Replace(".", "_") + " t) and TestCol2 = '2'", bodyParts[3]);
}
private void LoadMappings()
{
_mappingsByType = new Dictionary <Type, Mappings>();
// Skip all views.
var entityTypes = _ctx.Model.GetEntityTypes()
.Where(t => t.GetViewName() == null)
.ToArray();
foreach (var entityType in entityTypes)
{
var m = new Mappings();
if (_mappingsByType.ContainsKey(entityType.ClrType))
{
Console.WriteLine($@"Trying to add duplicate mapping for {entityType.ClrType}");
continue;
}
_mappingsByType.Add(entityType.ClrType, m);
m.TableName = new TableName
{
Name = entityType.GetTableName(),
Schema = entityType.GetSchema()
};
//
// Add mappings for all scalar properties. That is, for all properties
// that do not represent other entities (navigation properties).
//
var tableColumnMappings = new List <TableColumnMapping>();
var properties = entityType
.GetProperties()
.Where(p => p.ValueGenerated == ValueGenerated.Never || p.IsPrimaryKey())
;
foreach (var p in properties)
{
var tableColumnMapping = new TableColumnMapping();
tableColumnMappings.Add(tableColumnMapping);
tableColumnMapping.EntityProperty = p;
tableColumnMapping.TableColumn = p.GetTableColumnMappings().First(); // When would this contain more than one entry?
tableColumnMapping.IsPrimaryKey = p.IsPrimaryKey();
tableColumnMapping.IsIdentity = p.GetValueGenerationStrategy() == SqlServerValueGenerationStrategy.IdentityColumn;
}
var columnMappingByPropertyName = tableColumnMappings.ToDictionary(m => m.EntityProperty.Name, m => m);
var columnMappingByColumnName =
tableColumnMappings.ToDictionary(m => m.TableColumn.Column.Name, m => m);
m.ColumnMappingByColumnName = columnMappingByColumnName;
m.ColumnMappingByPropertyName = columnMappingByPropertyName;
var foreignKeyMappings = new List <ForeignKeyMapping>();
foreach (var n in entityType.GetNavigations())
{
// Only bother with unknown relationships
if (foreignKeyMappings.All(m => m.NavigationPropertyName != n.Name))
{
var fkMapping = new ForeignKeyMapping
{
NavigationPropertyName = n.Name,
IsCollection = n.IsCollection,
FromType = n.ForeignKey.DeclaringEntityType.ClrType.ToString(),
ToType = n.ForeignKey.PrincipalEntityType.ClrType.ToString(),
};
foreignKeyMappings.Add(fkMapping);
var foreignKeyRelations = new List <ForeignKeyRelation>();
for (int i = 0; i < n.ForeignKey.Properties.Count; i++)
{
foreignKeyRelations.Add(new ForeignKeyRelation
{
FromProperty = n.ForeignKey.PrincipalKey.Properties[i].Name,
ToProperty = n.ForeignKey.Properties[i].Name,
});
}
fkMapping.ForeignKeyRelations = foreignKeyRelations.ToArray();
}
}
m.ToForeignKeyMappings = foreignKeyMappings.Where(m => m.FromType == entityType.Name).ToArray();
m.FromForeignKeyMappings = foreignKeyMappings.Where(m => m.ToType == entityType.Name).ToArray();
}
}
public Mappings GetMappings(DbContext ctx, Type t)
{
Discriminator discriminator = null;
var objectContext = ((IObjectContextAdapter)ctx).ObjectContext;
var workspace = objectContext.MetadataWorkspace;
var containerName = objectContext.DefaultContainerName;
t = ObjectContext.GetObjectType(t);
var entityName = t.Name;
// If we are dealing with table inheritance we need the base type name as well.
var baseEntityName = t.BaseType?.Name;
var storageMapping =
(EntityContainerMapping)workspace.GetItem <GlobalItem>(containerName, DataSpace.CSSpace);
var entitySetMaps = storageMapping.EntitySetMappings.ToList();
var associationSetMaps = storageMapping.AssociationSetMappings.ToList();
//
// Add mappings for all scalar properties. That is, for all properties
// that do not represent other entities (navigation properties).
//
var entitySetMap = entitySetMaps
.Single(m =>
m.EntitySet.ElementType.Name == entityName ||
m.EntitySet.ElementType.Name == baseEntityName);
var typeMappings = entitySetMap.EntityTypeMappings;
var propertyMappings = new List <PropertyMapping>();
NavigationProperty[] navigationProperties = new NavigationProperty[0];
// As long as we do not deal with table inheritance
// we assume there is only one type mapping available.
if (typeMappings.Count() == 1)
{
var typeMapping = typeMappings[0];
var fragments = typeMapping.Fragments;
var fragment = fragments[0];
propertyMappings.AddRange(fragment.PropertyMappings);
navigationProperties =
typeMapping.EntityType.DeclaredMembers
.Where(m => m.BuiltInTypeKind == BuiltInTypeKind.NavigationProperty)
.Cast <NavigationProperty>()
.Where(p => p.RelationshipType is AssociationType)
.ToArray();
}
// If we have more than one type mapping we assume that we are
// dealing with table inheritance.
else
{
foreach (var tm in typeMappings)
{
var name = tm.EntityType != null ? tm.EntityType.Name : tm.IsOfEntityTypes[0].Name;
if (name == baseEntityName ||
name == entityName)
{
var fragments = tm.Fragments;
var fragment = fragments[0];
if (fragment.Conditions.Any())
{
var valueConditionMapping =
(ValueConditionMapping)fragment.Conditions[0];
discriminator = new Discriminator
{
Column = valueConditionMapping.Column,
Value = valueConditionMapping.Value
};
}
var uknownMappings = fragment.PropertyMappings
.Where(m => propertyMappings.All(pm => pm.Property.Name != m.Property.Name));
propertyMappings.AddRange(uknownMappings);
}
}
//var typeMapping = typeMappings.Single(tm => tm.IsOfEntityTypes[0].Name == entityName);
}
var columnMappings = new List <TableColumnMapping>();
columnMappings.AddRange(
propertyMappings
.Where(p => p is ScalarPropertyMapping)
.Cast <ScalarPropertyMapping>()
.Where(m => !m.Column.IsStoreGeneratedComputed)
.Select(p => new TableColumnMapping
{
EntityProperty = p.Property,
TableColumn = p.Column
}));
var complexPropertyMappings = propertyMappings
.Where(p => p is ComplexPropertyMapping)
.Cast <ComplexPropertyMapping>()
.ToArray();
if (complexPropertyMappings.Any())
{
columnMappings.AddRange(GetTableColumnMappings(complexPropertyMappings, true));
}
var columnMappingByPropertyName = columnMappings.ToDictionary(m => m.EntityProperty.Name, m => m);
var columnMappingByColumnName = columnMappings.ToDictionary(m => m.TableColumn.Name, m => m);
//
// Add mappings for all navigation properties.
//
//
var foreignKeyMappings = new List <ForeignKeyMapping>();
foreach (var navigationProperty in navigationProperties)
{
var relType = (AssociationType)navigationProperty.RelationshipType;
// Only bother with unknown relationships
if (foreignKeyMappings.All(m => m.NavigationPropertyName != navigationProperty.Name))
{
var fkMapping = new ForeignKeyMapping
{
NavigationPropertyName = navigationProperty.Name,
BuiltInTypeKind = navigationProperty.TypeUsage.EdmType.BuiltInTypeKind,
};
//
// Many-To-Many
//
if (associationSetMaps.Any() &&
associationSetMaps.Any(m => m.AssociationSet.Name == relType.Name))
{
var map = associationSetMaps.Single(m => m.AssociationSet.Name == relType.Name);
var sourceMapping =
new TableColumnMapping
{
TableColumn = map.SourceEndMapping.PropertyMappings[0].Column,
EntityProperty = map.SourceEndMapping.PropertyMappings[0].Property,
};
var targetMapping =
new TableColumnMapping
{
TableColumn = map.TargetEndMapping.PropertyMappings[0].Column,
EntityProperty = map.TargetEndMapping.PropertyMappings[0].Property,
};
fkMapping.FromType = (map.SourceEndMapping.AssociationEnd.TypeUsage.EdmType as RefType)
?.ElementType.Name;
fkMapping.ToType = (map.TargetEndMapping.AssociationEnd.TypeUsage.EdmType as RefType)
?.ElementType.Name;
var schema = map.StoreEntitySet.Schema;
var name = map.StoreEntitySet.Table ?? map.StoreEntitySet.Name;
fkMapping.AssociationMapping = new AssociationMapping
{
TableName = new TableName
{
Name = name,
Schema = schema,
},
Source = sourceMapping,
Target = targetMapping
};
}
//
// One-To-One or One-to-Many
//
else
{
fkMapping.FromType = relType.Constraint.FromProperties.First().DeclaringType.Name;
fkMapping.ToType = relType.Constraint.ToProperties.First().DeclaringType.Name;
var foreignKeyRelations = new List <ForeignKeyRelation>();
for (int i = 0; i < relType.Constraint.FromProperties.Count; i++)
{
foreignKeyRelations.Add(new ForeignKeyRelation
{
FromProperty = relType.Constraint.FromProperties[i].Name,
ToProperty = relType.Constraint.ToProperties[i].Name,
});
}
fkMapping.ForeignKeyRelations = foreignKeyRelations.ToArray();
}
foreignKeyMappings.Add(fkMapping);
}
}
var tableName = GetTableName(ctx, t);
var mappings = new Mappings
{
TableName = tableName,
Discriminator = discriminator,
ComplexPropertyNames = complexPropertyMappings.Select(m => m.Property.Name).ToArray(),
ColumnMappingByPropertyName = columnMappingByPropertyName,
ColumnMappingByColumnName = columnMappingByColumnName,
ToForeignKeyMappings = foreignKeyMappings.Where(m => m.ToType == entityName).ToArray(),
FromForeignKeyMappings = foreignKeyMappings.Where(m => m.FromType == entityName).ToArray()
};
foreach (var toPropertyName in mappings.ToForeignKeyMappings.SelectMany(m =>
m.ForeignKeyRelations.Select(r => r.ToProperty)))
{
if (mappings.ColumnMappingByPropertyName.ContainsKey(toPropertyName))
{
var tableColumnMapping = mappings.ColumnMappingByPropertyName[toPropertyName];
tableColumnMapping.IsForeignKey = true;
}
}
foreach (var toPropertyName in mappings.FromForeignKeyMappings.SelectMany(m =>
m.ForeignKeyRelations.Select(r => r.ToProperty)))
{
if (mappings.ColumnMappingByPropertyName.ContainsKey(toPropertyName))
{
var tableColumnMapping = mappings.ColumnMappingByPropertyName[toPropertyName];
tableColumnMapping.IsForeignKey = true;
}
}
var associationMappings = mappings.ToForeignKeyMappings
.Where(m => m.AssociationMapping != null)
.Select(m => m.AssociationMapping);
foreach (var associationMapping in associationMappings)
{
associationMapping.Source.IsForeignKey = true;
associationMapping.Target.IsForeignKey = true;
}
associationMappings = mappings.FromForeignKeyMappings
.Where(m => m.AssociationMapping != null)
.Select(m => m.AssociationMapping);
foreach (var associationMapping in associationMappings)
{
associationMapping.Source.IsForeignKey = true;
associationMapping.Target.IsForeignKey = true;
}
return(mappings);
}
internal void tableMappingSwitchDatabase(TableMapping[] tableMappings, Database db)
{
Dictionary <int, TableMapping> newTableMappings = new Dictionary <int, TableMapping>();
foreach (TableMapping tableMapping in tableMappings)
{
DBTable table = db.Tables.Where(t => t.Reference == tableMapping.DestinationTable.Reference).FirstOrDefault();
if (table != null)
{
TableMapping newTableMapping = new TableMapping(table, tableMapping.ImportType, new ColumnMapping[0]);
newTableMappings.Add(tableMapping.Index, newTableMapping);
}
}
foreach (int index in newTableMappings.Keys)
{
TableMapping newTableMapping = newTableMappings[index];
List <ColumnMapping> newColumnMappings = new List <ColumnMapping>();
TableMapping oldTableMapping = tableMappings
.Where(t => t.Index == index).First();
ColumnMapping[] oldColumnMappings = oldTableMapping.ColumnMappings;
foreach (ColumnMapping oldColumnMapping in oldColumnMappings)
{
DBColumn column = newTableMapping.DestinationTable.Columns
.Where(c => c.Name == oldColumnMapping.DestinationColumn.Name)
.FirstOrDefault();
if (column != null)
{
string type = oldColumnMapping.GetType().ToString();
ColumnMapping newColumnMapping = null;
if (type == typeof(ExcelColumnMapping).ToString())
{
var excelColumnMapping = (ExcelColumnMapping)oldColumnMapping;
newColumnMapping = new ExcelColumnMapping(excelColumnMapping.SourceHeader, column, oldColumnMapping.ColumnUse);
}
else if (type == typeof(TableColumnMapping).ToString())
{
var tableColumnMapping = (TableColumnMapping)oldColumnMapping;
if (newTableMappings.ContainsKey(tableColumnMapping.SourceTableMapping.Index))
{
TableMapping newSourceTableMapping = newTableMappings[tableColumnMapping.SourceTableMapping.Index];
DBColumn newSourceColumn = newSourceTableMapping.DestinationTable.Columns
.Where(c => c.Name == tableColumnMapping.SourceColumn.Name)
.FirstOrDefault();
if (newSourceColumn != null)
{
newColumnMapping = new TableColumnMapping(newSourceTableMapping, newSourceColumn, column, oldColumnMapping.ColumnUse);
}
}
}
else if (type == typeof(LiteralColumnMapping).ToString())
{
var literalColumnMapping = (LiteralColumnMapping)oldColumnMapping;
newColumnMapping = new LiteralColumnMapping(literalColumnMapping.Literal, literalColumnMapping.LiteralType, column, literalColumnMapping.ColumnUse);
}
if (newColumnMapping == null)
{
newColumnMapping = new NullColumnMapping(column, oldColumnMapping.ColumnUse);
}
newColumnMappings.Add(newColumnMapping);
}
}
newTableMapping.ColumnMappings = newColumnMappings.ToArray();
}
this.TableMappings = newTableMappings.Values.ToList();
}
public void StatementCreatorOrderTest()
{
DBTable table = new DBTable("dbo", "TestTable");
DBColumn col1 = new DBColumn(table, "TestCol1", true, DBDatatype.integer);
DBColumn col2 = new DBColumn(table, "TestCol2", false, DBDatatype.nvarchar);
DBColumn col3 = new DBColumn(table, "TestCol3", false, DBDatatype.integer);
table.Columns = new List <DBColumn>()
{
col1, col2, col3
};
Database db = new Database("TestDB", new List <DBTable>()
{
table
});
TableMapping sourceTablemapping = new TableMapping(new DBTable("dbo", "TestTable2"), TableMappingImportType.Insert, null);
ColumnMapping colMap1 = new TableColumnMapping(sourceTablemapping, col1, col1, ColumnUse.Where);
ColumnMapping colMap2 = new LiteralColumnMapping("2", LiteralType.String, col2, ColumnUse.Where);
ColumnMapping colMap3 = new LiteralColumnMapping("3", LiteralType.String, col2, ColumnUse.Set);
TableMapping tableMapping = new TableMapping(table, TableMappingImportType.Update, new ColumnMapping[] { colMap1, colMap2, colMap3 });
ErrorHandling errorHandling = new ErrorHandling();
ImportConfiguration config = new ImportConfiguration(new TableMapping[] { tableMapping }, null, "TestDB", errorHandling);
SourceDataEntry[] entries = new SourceDataEntry[] { SourceDataEntry.CreateDataEntry("", DataType.String, "") };
SourceDataRow[] rows = new SourceDataRow[] { new SourceDataRow(entries, "0") };
SourceDataTable dt = new SourceDataTable(rows, new string[] { "" });
SQLServerStatementCreator statementCreator = new SQLServerStatementCreator(config, dt);
ImportStatement statement = statementCreator.CreateStatement(0);
ImportStatement[] statements = statementCreator.CreateStatements();
Assert.AreEqual(1, statements.Length);
Assert.AreEqual(statement.RowReference, statements[0].RowReference);
Assert.AreEqual(statement.SqlStatement, statements[0].SqlStatement);
string[] lines = statement.SqlStatement
.Split(new string[] { "\n" }, StringSplitOptions.None).Where(s => s.Length > 0).ToArray();
StatementSetupPart setupPart = new StatementSetupPart(config);
Assert.AreEqual(setupPart.GetDatabasePart(), lines[0]);
Assert.AreEqual(setupPart.GetWarningsPart(), lines[1]);
StatementTransactionPart transPart = new StatementTransactionPart(config);
string[] transStartPart = transPart.GetTransactionStartPart()
.Split(new string[] { "\n" }, StringSplitOptions.None).Where(s => s.Length > 0).ToArray();;
Assert.AreEqual(2, transStartPart.Length);
Assert.AreEqual(transStartPart[0], lines[2]);
Assert.AreEqual(transStartPart[1], lines[3]);
StatementTableMappingPart tmParts = new StatementTableMappingPart(tableMapping, dt.GetDataRow(0));
string variablePart = tmParts.GetTableVariablePart().Replace("\n", "");
Assert.AreEqual(variablePart, lines[4]);
string[] bodyParts = tmParts.GetStatementBodyPart()
.Split(new string[] { "\n" }, StringSplitOptions.None).Where(s => s.Length > 0).ToArray();
Assert.AreEqual(4, bodyParts.Length);
Assert.AreEqual(bodyParts[0], lines[5]);
Assert.AreEqual(bodyParts[1], lines[6]);
Assert.AreEqual(bodyParts[2], lines[7]);
Assert.AreEqual(bodyParts[3], lines[8]);
string[] transEndPart = transPart.GetTransactionEndPart()
.Split(new string[] { "\n" }, StringSplitOptions.None).Where(s => s.Length > 0).ToArray();
Assert.AreEqual(12, transEndPart.Length);
Assert.AreEqual(transEndPart[0], lines[9]);
Assert.AreEqual(transEndPart[1], lines[10]);
Assert.AreEqual(transEndPart[2], lines[11]);
Assert.AreEqual(transEndPart[3], lines[12]);
Assert.AreEqual(transEndPart[4], lines[13]);
Assert.AreEqual(transEndPart[5], lines[14]);
Assert.AreEqual(transEndPart[6], lines[15]);
Assert.AreEqual(transEndPart[7], lines[16]);
Assert.AreEqual(transEndPart[8], lines[17]);
Assert.AreEqual(transEndPart[9], lines[18]);
Assert.AreEqual(transEndPart[10], lines[19]);
Assert.AreEqual(transEndPart[11], lines[20]);
}
public static TableMapping[] TableMappingTestData()
{
DBTable addressTable = new DBTable("dbo", "Address");
DBColumn a_idCol = new DBColumn(addressTable, "a_id", true, DBDatatype.integer);
DBColumn streetNameCol = new DBColumn(addressTable, "StreetName", false, DBDatatype.nvarchar);
DBColumn streetNumberCol = new DBColumn(addressTable, "StreetNumber", false, DBDatatype.integer);
DBColumn zipCodeCol = new DBColumn(addressTable, "ZipCode", false, DBDatatype.integer);
addressTable.Columns = new List <DBColumn>()
{
a_idCol, streetNameCol, streetNumberCol, zipCodeCol
};
NullColumnMapping a_idMapping = new NullColumnMapping(a_idCol, ColumnUse.Insert);
ExcelColumnMapping streetNameMapping = new ExcelColumnMapping("Street name", streetNameCol, ColumnUse.Insert);
ExcelColumnMapping streetNumberMapping = new ExcelColumnMapping("Street number", streetNumberCol, ColumnUse.Insert);
ExcelColumnMapping zipCodeMapping = new ExcelColumnMapping("Zip code", zipCodeCol, ColumnUse.Insert);
TableMapping addressTableMapping = new TableMapping(addressTable, TableMappingImportType.Insert, new ColumnMapping[] { a_idMapping, streetNameMapping, streetNumberMapping, zipCodeMapping });
DBTable personTable = new DBTable("dbo", "Person");
DBColumn p_idCol = new DBColumn(personTable, "p_id", true, DBDatatype.integer);
DBColumn firstNameCol = new DBColumn(personTable, "FirstName", false, DBDatatype.nvarchar);
DBColumn lastNameCol = new DBColumn(personTable, "LastName", false, DBDatatype.nvarchar);
DBColumn a_idPersonCol = new DBColumn(personTable, "a_id", false, DBDatatype.integer);
personTable.Columns = new List <DBColumn>()
{
p_idCol, firstNameCol, lastNameCol, a_idPersonCol
};
TableMapping personTableMapping = new TableMapping(personTable, TableMappingImportType.Insert, new ColumnMapping[0]);
NullColumnMapping p_idMapping = new NullColumnMapping(p_idCol, ColumnUse.Insert);
ExcelColumnMapping firstNameMapping = new ExcelColumnMapping("FirstName", firstNameCol, ColumnUse.Insert);
ExcelColumnMapping lastNameMapping = new ExcelColumnMapping("Surname", lastNameCol, ColumnUse.Insert);
TableColumnMapping aIdMapping = new TableColumnMapping(addressTableMapping, a_idMapping.DestinationColumn, a_idPersonCol, ColumnUse.Insert);
personTableMapping.ColumnMappings = new ColumnMapping[] { p_idMapping, firstNameMapping, lastNameMapping, aIdMapping };
DBTable contactInfoTable = new DBTable("dbo", "ContactInfo");
DBColumn pn_idCol = new DBColumn(contactInfoTable, "pn_id", true, DBDatatype.integer);
DBColumn textCol = new DBColumn(contactInfoTable, "text", false, DBDatatype.nvarchar);
DBColumn p_idCICol = new DBColumn(contactInfoTable, "p_id", false, DBDatatype.integer);
DBColumn ci_idCICol = new DBColumn(contactInfoTable, "ci_id", false, DBDatatype.integer);
contactInfoTable.Columns = new List <DBColumn>()
{
pn_idCol, textCol, p_idCICol, ci_idCICol
};
ExcelColumnMapping phoneNumberMapping = new ExcelColumnMapping("Phone", textCol, ColumnUse.Insert);
TableColumnMapping pIDMapping = new TableColumnMapping(personTableMapping, p_idMapping.DestinationColumn, p_idCICol, ColumnUse.Insert);
LiteralColumnMapping citIdMapping = new LiteralColumnMapping("1", LiteralType.Integer, ci_idCICol, ColumnUse.Insert);
TableMapping phoneTableMapping = new TableMapping(contactInfoTable, TableMappingImportType.Insert, new ColumnMapping[] { phoneNumberMapping, pIDMapping, citIdMapping });
ExcelColumnMapping mobileNumberMapping = new ExcelColumnMapping("Mobile", textCol, ColumnUse.Insert);
TableColumnMapping pIDMobileMapping = new TableColumnMapping(personTableMapping, p_idMapping.DestinationColumn, p_idCICol, ColumnUse.Insert);
LiteralColumnMapping citIdMobileMapping = new LiteralColumnMapping("2", LiteralType.Integer, ci_idCICol, ColumnUse.Insert);
TableMapping mobileTableMapping = new TableMapping(contactInfoTable, TableMappingImportType.Insert, new ColumnMapping[] { mobileNumberMapping, pIDMobileMapping, citIdMobileMapping });
return(new TableMapping[] { personTableMapping, phoneTableMapping, addressTableMapping, mobileTableMapping });
}
private string tableColumnMappingPart(TableColumnMapping mapping, string tableVariable)
{
return(string.Format("(SELECT TOP 1 t.{0} FROM {1} t)", mapping.SourceColumn.Name, tableVariable));
}
public TableColumnMappingViewModel(TableColumnMapping tableColumnMapping,
TableMappingViewModel tableMappingViewModel)
: base(tableColumnMapping, tableMappingViewModel)
{
this.importType = ColumnMappingImportType.Table;
}
