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 SourceDataEntry GetSourceDataEntry(string columnReference) { if (dataEntries.ContainsKey(columnReference)) { return(dataEntries[columnReference]); } else { return(SourceDataEntry.CreateDataEntry("", DataType.Null, columnReference)); } }
public SourceDataRow(SourceDataEntry[] dataEntries, string rowReference) { this.rowReference = rowReference; this.dataEntries = new Dictionary<string, SourceDataEntry>(); foreach (SourceDataEntry entry in dataEntries) { this.dataEntries.Add(entry.ColumnReference, entry); } }
private List <SourceDataRow> getDataRows(bool onlyFirstRow) { List <SourceDataRow> rowList = new List <SourceDataRow>(); SortedDictionary <string, string> headers = getHeaders(); using (SpreadsheetDocument document = SpreadsheetDocument.Open(path, false)) { WorkbookPart workbookPart = document.WorkbookPart; Sheet sheet = getSheetByName(workbookPart, selectedWorksheetName); WorksheetPart worksheetPart = getWorksheetPartById(workbookPart, sheet.Id); IEnumerable <Row> rows = worksheetPart.Worksheet.Descendants <Row>(); int rowsToSkip = hasHeaders ? 1 : 0; foreach (Row row in rows.Skip(rowsToSkip)) { List <SourceDataEntry> rowEntries = new List <SourceDataEntry>(); bool rowContainsData = false; string rowReference = row.RowIndex; foreach (Cell cell in row.Descendants <Cell>()) { SourceDataEntry cellDataEntry = getcellDataEntry(workbookPart, cell, headers); rowEntries.Add(cellDataEntry); if (entryContainsValue(cellDataEntry)) { rowContainsData = true; } } if (rowContainsData) { rowList.Add(new SourceDataRow(rowEntries.ToArray(), rowReference)); } if (onlyFirstRow && rowContainsData) { break; } } } return(rowList); }
private static string sqlStringValueFromDataEntry(SourceDataEntry dataEntry) { switch (dataEntry.DataType) { case DataType.Bool: return dataEntry.Value; case DataType.DateTime: return "'" + dataEntry.Value + "'"; case DataType.Error: return "NULL"; // TODO: throw exception instead? case DataType.Null: return "NULL"; case DataType.Number: return dataEntry.Value; default: return "'" + dataEntry.Value.Replace("'", "''") + "'"; } }
private bool entryContainsValue(SourceDataEntry entry) { return(entry.Value.Length > 0 || entry.DataType != DataType.Null || entry.DataType != DataType.Error); }
private SourceDataEntry getcellDataEntry(WorkbookPart workbookPart, Cell cell, SortedDictionary <string, string> headers) { DataType dataType = DataType.Null; string value = cell.InnerText; if (value.Length > 0) { dataType = DataType.Number; } if (cell.DataType != null) { switch (cell.DataType.Value) { case CellValues.Boolean: value = cell.CellValue.ToString() == "0" ? "0" : "1"; dataType = DataType.Bool; break; case CellValues.SharedString: SharedStringTablePart stringTable = workbookPart.GetPartsOfType <SharedStringTablePart>().FirstOrDefault(); value = stringTable.SharedStringTable.ElementAt(int.Parse(cell.InnerText)).InnerText; dataType = DataType.String; break; case CellValues.InlineString: value = cell.InnerText; dataType = DataType.String; break; case CellValues.String: value = cell.CellValue.InnerText; dataType = DataType.String; break; case CellValues.Error: value = ""; dataType = DataType.Error; break; default: break; } } else if (cell.StyleIndex != null && styleIndexIsDate(workbookPart.WorkbookStylesPart.Stylesheet, (int)cell.StyleIndex.Value)) { double d = Double.Parse(value, CultureInfo.InvariantCulture); value = DateTime.FromOADate(d).ToString(CultureInfo.InvariantCulture); dataType = DataType.DateTime; } else if (cell.CellFormula != null) { double v; if (Double.TryParse(cell.CellValue.InnerText, NumberStyles.Any, CultureInfo.InvariantCulture, out v)) { value = v.ToString(CultureInfo.InvariantCulture); dataType = DataType.Number; } else { value = cell.CellValue.InnerText; dataType = DataType.String; } } string columnName = getColumnName(cell.CellReference); string columnReference = headers.ContainsKey(columnName) ? headers[getColumnName(cell.CellReference)] : columnName; return(SourceDataEntry.CreateDataEntry(value, dataType, columnReference)); }
public static string Translate(SourceDataEntry dataEntry) { return sqlStringValueFromDataEntry(dataEntry); }
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 string excelColumnMappingPart(ExcelColumnMapping mapping, SourceDataEntry dataEntry) { return SQLServerDataEntryTranslator.Translate(dataEntry); }
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]); }