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