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; }