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 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 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 };
}
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();
}
private TableMapping[] readTableMappings(XmlReader reader, Database database)
{
Dictionary <int, TableMapping> tableMappings = new Dictionary <int, TableMapping>();
Dictionary <TableMapping, XElement> columnMappingReaders = new Dictionary <TableMapping, XElement>();
if (reader.ReadToFollowing("TableMappings"))
{
using (XmlReader tmlReader = reader.ReadSubtree())
{
while (tmlReader.ReadToFollowing("TableMapping"))
{
{
int index = int.Parse(tmlReader.GetAttribute("index"));
string destinationTableReference = tmlReader.GetAttribute("destinationTableReference");
string importType = tmlReader.GetAttribute("importType");
DBTable table = database.Tables.Where(t => t.Reference == destinationTableReference).First();
TableMapping tableMapping = new TableMapping(table,
(TableMappingImportType)Enum.Parse(typeof(TableMappingImportType), importType), null);
tableMappings.Add(index, tableMapping);
if (tmlReader.ReadToFollowing("ColumnMappings"))
{
XmlReader cmlReader = tmlReader.ReadSubtree();
XElement cml = XElement.Load(cmlReader);
columnMappingReaders.Add(tableMapping, cml);
}
}
}
foreach (TableMapping tableMapping in columnMappingReaders.Keys)
{
List <ColumnMapping> columnMappings = new List <ColumnMapping>();
XElement columnMappingsElement = columnMappingReaders[tableMapping];
IEnumerable <XElement> columnMappingElements = columnMappingsElement.Elements();
foreach (XElement columnMappingElement in columnMappingElements)
{
if (columnMappingElement.Name == "ColumnMapping")
{
string type = columnMappingElement.Attribute("type").Value;
ColumnUse columnUse = (ColumnUse)Enum.Parse(typeof(ColumnUse), columnMappingElement.Attribute("columnUse").Value);
string destinationColumnReference = columnMappingElement.Attribute("destinationColumnReference").Value;
DBColumn destinationColumn = tableMapping.DestinationTable.Columns
.Where(c => c.Name.ToLower() == destinationColumnReference.ToLower()).First();
ColumnMapping columnMapping = null;
if (type == typeof(ExcelColumnMapping).Name)
{
string sourceHeader = columnMappingElement.Attribute("sourceHeader").Value;
columnMapping = new ExcelColumnMapping(sourceHeader, destinationColumn, columnUse);
}
else if (type == typeof(TableColumnMapping).Name)
{
int sourceTableMappingIndex = int.Parse(columnMappingElement.Attribute("sourceTableMappingIndex").Value);
string sourceColumnReference = columnMappingElement.Attribute("sourceColumnReference").Value;
TableMapping sourceTableMapping = tableMappings[sourceTableMappingIndex];
DBColumn sourceColumn = sourceTableMapping.DestinationTable.Columns
.Where(c => c.Name.ToLower() == sourceColumnReference.ToLower()).First();
columnMapping = new TableColumnMapping(sourceTableMapping, sourceColumn, destinationColumn, columnUse);
}
else if (type == typeof(LiteralColumnMapping).Name)
{
string litearal = columnMappingElement.Attribute("literal").Value;
string literalType = columnMappingElement.Attribute("literalType").Value;
columnMapping = new LiteralColumnMapping(litearal,
(LiteralType)Enum.Parse(typeof(LiteralType), literalType), destinationColumn, columnUse);
}
else
{
columnMapping = new NullColumnMapping(destinationColumn, columnUse);
}
columnMappings.Add(columnMapping);
}
}
tableMapping.ColumnMappings = columnMappings.ToArray();
}
}
}
return(tableMappings.Values.ToArray());
}
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();
}
private TableMapping[] readTableMappings(XmlReader reader, Database database)
{
Dictionary<int, TableMapping> tableMappings = new Dictionary<int, TableMapping>();
Dictionary<TableMapping, XElement> columnMappingReaders = new Dictionary<TableMapping, XElement>();
if (reader.ReadToFollowing("TableMappings"))
{
using (XmlReader tmlReader = reader.ReadSubtree())
{
while (tmlReader.ReadToFollowing("TableMapping"))
{
{
int index = int.Parse(tmlReader.GetAttribute("index"));
string destinationTableReference = tmlReader.GetAttribute("destinationTableReference");
string importType = tmlReader.GetAttribute("importType");
DBTable table = database.Tables.Where(t => t.Reference == destinationTableReference).First();
TableMapping tableMapping = new TableMapping(table,
(TableMappingImportType)Enum.Parse(typeof(TableMappingImportType), importType), null);
tableMappings.Add(index, tableMapping);
if (tmlReader.ReadToFollowing("ColumnMappings"))
{
XmlReader cmlReader = tmlReader.ReadSubtree();
XElement cml = XElement.Load(cmlReader);
columnMappingReaders.Add(tableMapping, cml);
}
}
}
foreach (TableMapping tableMapping in columnMappingReaders.Keys)
{
List<ColumnMapping> columnMappings = new List<ColumnMapping>();
XElement columnMappingsElement = columnMappingReaders[tableMapping];
IEnumerable<XElement> columnMappingElements = columnMappingsElement.Elements();
foreach (XElement columnMappingElement in columnMappingElements)
{
if (columnMappingElement.Name == "ColumnMapping")
{
string type = columnMappingElement.Attribute("type").Value;
ColumnUse columnUse = (ColumnUse)Enum.Parse(typeof(ColumnUse), columnMappingElement.Attribute("columnUse").Value);
string destinationColumnReference = columnMappingElement.Attribute("destinationColumnReference").Value;
DBColumn destinationColumn = tableMapping.DestinationTable.Columns
.Where(c => c.Name.ToLower() == destinationColumnReference.ToLower()).First();
ColumnMapping columnMapping = null;
if (type == typeof(ExcelColumnMapping).Name)
{
string sourceHeader = columnMappingElement.Attribute("sourceHeader").Value;
columnMapping = new ExcelColumnMapping(sourceHeader, destinationColumn, columnUse);
}
else if (type == typeof(TableColumnMapping).Name)
{
int sourceTableMappingIndex = int.Parse(columnMappingElement.Attribute("sourceTableMappingIndex").Value);
string sourceColumnReference = columnMappingElement.Attribute("sourceColumnReference").Value;
TableMapping sourceTableMapping = tableMappings[sourceTableMappingIndex];
DBColumn sourceColumn = sourceTableMapping.DestinationTable.Columns
.Where(c => c.Name.ToLower() == sourceColumnReference.ToLower()).First();
columnMapping = new TableColumnMapping(sourceTableMapping, sourceColumn, destinationColumn, columnUse);
}
else if (type == typeof(LiteralColumnMapping).Name)
{
string litearal = columnMappingElement.Attribute("literal").Value;
string literalType = columnMappingElement.Attribute("literalType").Value;
columnMapping = new LiteralColumnMapping(litearal,
(LiteralType)Enum.Parse(typeof(LiteralType), literalType), destinationColumn, columnUse);
}
else
{
columnMapping = new NullColumnMapping(destinationColumn, columnUse);
}
columnMappings.Add(columnMapping);
}
}
tableMapping.ColumnMappings = columnMappings.ToArray();
}
}
}
return tableMappings.Values.ToArray();
}
private string nullColumnMappingPart(NullColumnMapping mapping)
{
return "NULL";
}