Beispiel #1
0
        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 BoolTest()
        {
            SourceDataEntry trueEntry  = SourceDataEntry.CreateDataEntry("1", DataType.Bool, "");
            SourceDataEntry falseEntry = SourceDataEntry.CreateDataEntry("0", DataType.Bool, "");

            Assert.AreEqual("1", SQLServerDataEntryTranslator.Translate(trueEntry));
            Assert.AreEqual("0", SQLServerDataEntryTranslator.Translate(falseEntry));
        }
        public void StringTest()
        {
            SourceDataEntry stringEntry1 = SourceDataEntry.CreateDataEntry("Test", DataType.String, "");
            SourceDataEntry stringEntry2 = SourceDataEntry.CreateDataEntry("Test's", DataType.String, "");

            Assert.AreEqual("'Test'", SQLServerDataEntryTranslator.Translate(stringEntry1));
            Assert.AreEqual("'Test''s'", SQLServerDataEntryTranslator.Translate(stringEntry2));
        }
        public void CellDataTypesTest()
        {
            ExcelReader reader = new ExcelReader(dataTypesTestFile);

            reader.HasHeaders = true;
            SourceDataTable dataTable = reader.ReadToDataTable();

            string[] headers = reader.GetHeaderNames();

            SourceDataEntry entry1 = dataTable.GetDataRow(0).GetSourceDataEntry(headers[0]);

            Assert.AreEqual(DataType.Bool, entry1.DataType);
            Assert.AreEqual("1", entry1.Value);

            SourceDataEntry entry2 = dataTable.GetDataRow(0).GetSourceDataEntry(headers[1]);

            Assert.AreEqual(DataType.DateTime, entry2.DataType);
            Assert.AreEqual("12/30/1899 00:00:00", entry2.Value);

            SourceDataEntry entry3 = dataTable.GetDataRow(0).GetSourceDataEntry(headers[2]);

            Assert.AreEqual(DataType.Error, entry3.DataType);
            Assert.AreEqual("", entry3.Value);

            SourceDataEntry entry4 = dataTable.GetDataRow(0).GetSourceDataEntry(headers[3]);

            Assert.AreEqual(DataType.String, entry4.DataType);
            Assert.AreEqual("Test string", entry4.Value);

            SourceDataEntry entry5 = dataTable.GetDataRow(0).GetSourceDataEntry(headers[4]);

            Assert.AreEqual(DataType.Number, entry5.DataType);
            Assert.AreEqual("1.23", entry5.Value);

            SourceDataEntry entry6 = dataTable.GetDataRow(0).GetSourceDataEntry(headers[5]);

            Assert.AreEqual(DataType.String, entry6.DataType);
            Assert.AreEqual("Test string", entry6.Value);

            SourceDataEntry entry7 = dataTable.GetDataRow(0).GetSourceDataEntry(headers[6]);

            Assert.AreEqual(DataType.String, entry7.DataType);
            Assert.AreEqual("Test string", entry7.Value);

            SourceDataEntry entry8 = dataTable.GetDataRow(0).GetSourceDataEntry(headers[7]);

            Assert.AreEqual(DataType.Number, entry8.DataType);
            Assert.AreEqual("1.23", entry8.Value);
        }
        public void WorksheetWithEmptyCellTest()
        {
            ExcelReader reader = new ExcelReader(worksheetEmptyCell);

            reader.HasHeaders = true;
            SourceDataTable dataTable = reader.ReadToDataTable();

            Assert.AreEqual(4, dataTable.NumberOfRows);

            string          header         = reader.GetHeaderNames()[1];
            SourceDataEntry emptyDataEntry = dataTable.GetDataRow(1).GetSourceDataEntry(header);

            Assert.AreEqual(DataReader.DataType.Null, emptyDataEntry.DataType);
            Assert.AreEqual("NULL", emptyDataEntry.Value);
        }
Beispiel #6
0
        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());
        }
Beispiel #7
0
        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 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("'", "''") + "'");
            }
        }
 public string GetColumnMappingValue()
 {
     if (columnMapping is ExcelColumnMapping)
     {
         string          header = ((ExcelColumnMapping)columnMapping).SourceHeader;
         SourceDataEntry entry  = sourceDataRow.GetSourceDataEntry(header);
         return(excelColumnMappingPart((ExcelColumnMapping)columnMapping, entry));
     }
     else if (columnMapping is TableColumnMapping)
     {
         var tableColMapping = (TableColumnMapping)columnMapping;
         StatementTableVariablePart tableVariablePart = new StatementTableVariablePart(tableColMapping.SourceTableMapping);
         string tableVariable = tableVariablePart.GetTableVariable();
         return(tableColumnMappingPart(tableColMapping, tableVariable));
     }
     else if (columnMapping is LiteralColumnMapping)
     {
         return(literalColumnMappingPart((LiteralColumnMapping)columnMapping));
     }
     else
     {
         return(nullColumnMappingPart((NullColumnMapping)columnMapping));
     }
 }
 public static string Translate(SourceDataEntry dataEntry)
 {
     return(sqlStringValueFromDataEntry(dataEntry));
 }
        public void NumberTest()
        {
            SourceDataEntry numberEntry = SourceDataEntry.CreateDataEntry("1.23", DataType.Number, "");

            Assert.AreEqual("1.23", SQLServerDataEntryTranslator.Translate(numberEntry));
        }
        public void NullTest()
        {
            SourceDataEntry nullEntry = SourceDataEntry.CreateDataEntry("Test", DataType.Null, "");

            Assert.AreEqual("NULL", SQLServerDataEntryTranslator.Translate(nullEntry));
        }
        public void ErrorTest()
        {
            SourceDataEntry errorEntry = SourceDataEntry.CreateDataEntry("Test", DataType.Error, "");

            Assert.AreEqual("NULL", SQLServerDataEntryTranslator.Translate(errorEntry));
        }
        public void DateTimeTest()
        {
            SourceDataEntry datetimeEntry = SourceDataEntry.CreateDataEntry("2015-12-26 23:59:59", DataType.DateTime, "");

            Assert.AreEqual("'2015-12-26 23:59:59'", SQLServerDataEntryTranslator.Translate(datetimeEntry));
        }
        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]);
        }
 private string excelColumnMappingPart(ExcelColumnMapping mapping, SourceDataEntry dataEntry)
 {
     return(SQLServerDataEntryTranslator.Translate(dataEntry));
 }