public void TableBodyPartInsertTest() { DBTable table = new DBTable("dbo", "TestTable"); DBColumn col1 = new DBColumn(table, "TestCol1", true, DBDatatype.integer); DBColumn col2 = new DBColumn(table, "TestCol2", false, DBDatatype.nvarchar); table.Columns = new List <DBColumn>() { col1, col2 }; ColumnMapping colMap1 = new NullColumnMapping(col1, ColumnUse.Exclude); ColumnMapping colMap2 = new LiteralColumnMapping("2", LiteralType.String, col2, ColumnUse.Insert); TableMapping tableMapping = new TableMapping(table, TableMappingImportType.Insert, new ColumnMapping[] { colMap1, colMap2 }); 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(3, bodyParts.Length); Assert.AreEqual("INSERT INTO dbo.TestTable (TestCol2)", bodyParts[0]); Assert.AreEqual("OUTPUT inserted.TestCol1, inserted.TestCol2 INTO @sqlimport_table_" + tableMapping.TableMappingReference.Replace(".", "_") + "(TestCol1, TestCol2)", bodyParts[1]); Assert.AreEqual("VALUES ('2')", bodyParts[2]); }
public TableMappingViewModel(DBTable table, MappingPageViewModel mappingPageViewModel) { this.viewModel = mappingPageViewModel; ColumnMapping[] mappings = new ColumnMapping[table.Columns.Count]; ObservableCollection <ColumnMappingViewModel> viewModels = new ObservableCollection <ColumnMappingViewModel>(); for (int i = 0; i < table.Columns.Count; i++) { mappings[i] = new NullColumnMapping(table.Columns[i], ColumnUse.Insert); viewModels.Add(new NullColumnMappingViewModel((NullColumnMapping)mappings[i], this)); } this.tableMapping = new TableMapping(table, TableMappingImportType.Insert, mappings); this.columnsMappingViewModels = viewModels.ToList(); }
public TableMappingViewModel(TableMapping tableMapping, MappingPageViewModel mappingPageViewModel) { this.viewModel = mappingPageViewModel; ObservableCollection <ColumnMappingViewModel> viewModels = new ObservableCollection <ColumnMappingViewModel>(); foreach (DBColumn column in tableMapping.DestinationTable.Columns) { ColumnMapping columnMapping = tableMapping.ColumnMappings.Where(c => c.DestinationColumn == column).FirstOrDefault(); if (columnMapping == null) { columnMapping = new NullColumnMapping(column, tableMapping.AllowedColumnUses()[0]); } viewModels.Add(ColumnMappingViewModelCreator.CreateFromColumnMapping(columnMapping, this)); } ColumnMapping[] columnMappings = viewModels.Select(v => v.ColumnMapping).ToArray(); tableMapping.ColumnMappings = columnMappings; this.tableMapping = tableMapping; this.columnsMappingViewModels = viewModels.ToList(); }
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()); }
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 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 nullColumnMappingPart(NullColumnMapping mapping) { return("NULL"); }
public NullColumnMappingViewModel(NullColumnMapping nullColumnMapping, TableMappingViewModel tableMappingViewModel) : base(nullColumnMapping, tableMappingViewModel) { this.importType = ColumnMappingImportType.Null; }