public void SqlWriterTest_TruncateTableStatement_NO() { // Arrange var jsonSettings = @" { ""importFiles"": [ { ""file"": ""..\\..\\..\\..\\TestCsv\\simpleComma.csv"", ""batchSize"": 4, ""truncate"": false } ] }"; var importTasks = (List <ImportFileOptions>)CsvToSql.Configuration.ImportTasks.ReadTasks(log, jsonSettings); var sqlWriter = new SqlServerWriter(log, ""); var headers = new List <string>() { "head1", "head2" }; // Act sqlWriter.Init(importTasks.First(), headers); var tableTruncSql = sqlWriter.GetTruncateTableStatement(); // Assert Assert.IsTrue(sqlWriter != null); Assert.IsTrue(tableTruncSql.Length < 3); Assert.IsFalse(tableTruncSql.Contains("TRUNCATE TABLE")); }
public void SqlWriterTest_GetInsertStatmentsWithConkretImportFileName() { // Arrange var jsonSettings = @" { ""importFiles"": [ { ""file"": ""..\\..\\..\\..\\TestCsv\\simpleComma.csv"", ""batchSize"": 4, ""columnMapping"": [ { ""head1"": ""NewHeadOne"", ""head2"": ""NewHeadTwo"", ""**20200911132530_20200907_Clients_SE.csv"": ""DCSource"" } ] } ] }"; var importTasks = (List <ImportFileOptions>)CsvToSql.Configuration.ImportTasks.ReadTasks(log, jsonSettings); var sqlWriter = new SqlServerWriter(log, ""); var headers = new List <string>() { "head1", "head2", "head3" }; var linesToWrite = new List <List <string> >() { new List <string>() { "a0", "a1", "a2" }, new List <string>() { "b0", "b1", "b2" } }; sqlWriter.Init(importTasks.First(), headers); // Act string insertSql = sqlWriter.GetInsertStatements(linesToWrite); // Assert Assert.IsTrue(sqlWriter != null); Assert.AreEqual(sqlWriter.GetHeaderFields().Count, 4); // { "head1", "head2", "head3", "DCSource"} Assert.AreEqual(sqlWriter.GetHeaderFields()[3].Name, "DCSource"); Assert.IsTrue(insertSql.Contains("INSERT INTO")); Assert.IsTrue(insertSql.Contains("20200911132530_20200907_Clients_SE.csv")); }
public void SqlWriterTest_GetInsertStatments() { // Arrange var jsonSettings = @" { ""importFiles"": [ { ""file"": ""..\\..\\..\\..\\TestCsv\\simpleComma.csv"", ""batchSize"": 4 } ] }"; var importTasks = (List <ImportFileOptions>)CsvToSql.Configuration.ImportTasks.ReadTasks(log, jsonSettings); var sqlWriter = new SqlServerWriter(log, ""); var headers = new List <string>() { "head1", "head2", "head3" }; var linesToWrite = new List <List <string> >() { new List <string>() { "a0", "a1", "a2" }, new List <string>() { "b0", "b1", "b2" } }; sqlWriter.Init(importTasks.First(), headers); // Act string insertSql = sqlWriter.GetInsertStatements(linesToWrite); // Assert Assert.IsTrue(sqlWriter != null); Assert.AreEqual(sqlWriter.GetHeaderFields().Count, 3); // { "head1", "head2", "head3"} Assert.IsTrue(insertSql.Contains("INSERT INTO")); }
public void SqlWriterTest_InitHeadersWithcolumnMappingImportDate() { // Arrange var jsonSettings = @" { ""importFiles"": [ { ""file"": ""..\\..\\..\\..\\TestCsv\\simpleComma.csv"", ""batchSize"": 4, ""saveMode"": true, ""columnMapping"": [ { ""head1"": ""NewHeadOne"", ""head2"": ""NewHeadTwo"", ""##ImportDate"" : ""DCImportDate"" } ] } ] }"; var importTasks = (List <ImportFileOptions>)CsvToSql.Configuration.ImportTasks.ReadTasks(log, jsonSettings); var sqlWriter = new SqlServerWriter(log, ""); var headers = new List <string>() { "head1", "head2", "head3" }; // Act sqlWriter.Init(importTasks.First(), headers); var headerFields = sqlWriter.GetHeaderFields(); // Assert Assert.AreEqual(headerFields.Count, 4); // { "NewHeadOne", "NewHeadTwo", "head3", "DCImportDate"} Assert.AreEqual(headerFields[0].Name, "NewHeadOne"); Assert.AreEqual(headerFields[1].Name, "NewHeadTwo"); Assert.AreEqual(headerFields[2].Name, "head3"); Assert.AreEqual(headerFields[3].Name, "DCImportDate"); Assert.IsTrue(sqlWriter != null); }
public void SqlWriterTest_CreateTableStatement() { // Arrange var jsonSettings = @" { ""importFiles"": [ { ""file"": ""..\\..\\..\\..\\TestCsv\\simpleComma.csv"", ""batchSize"": 4, ""forceCreateTable"": true } ] }"; var importTasks = (List <ImportFileOptions>)CsvToSql.Configuration.ImportTasks.ReadTasks(log, jsonSettings); var sqlWriter = new SqlServerWriter(log, ""); var headers = new List <string>() { "head1", "head2" }; // Act sqlWriter.Init(importTasks.First(), headers); var tableDropSql = sqlWriter.GetDropTableStatement(); var tableCreateSql = sqlWriter.GetCreateTableStatement(); // Assert Assert.IsTrue(sqlWriter != null); Assert.IsTrue(tableCreateSql.Contains("CREATE TABLE")); Assert.IsTrue(tableDropSql.Contains("DROP TABLE")); Assert.IsTrue(tableCreateSql.Contains("head1")); Assert.IsTrue(tableCreateSql.Contains("head2")); }
public void SqlWriterTest_GetInsertStatmentsWithUniqueOnly() { // Arrange var jsonSettings = @" { ""importFiles"": [ { ""file"": ""..\\..\\..\\..\\TestCsv\\simpleComma.csv"", ""batchSize"": 4, ""saveMode"": false, ""uniqueOnly"": true, ""columnMapping"": [ { ""head1"": ""NewHeadOne"", ""head2"": ""NewHeadTwo"", ""##ImportDate"": ""DCImportDate"" } ] } ] }"; var importTasks = (List <ImportFileOptions>)CsvToSql.Configuration.ImportTasks.ReadTasks(log, jsonSettings); var sqlWriter = new SqlServerWriter(log, ""); var sqlWriter2 = new SqlServerWriter(log, ""); var headers = new List <string>() { "head1", "head2", "head3" }; var linesToWrite = new List <List <string> >() { new List <string>() { "a0", "a1", "a2" }, new List <string>() { "b0", "b1", "b2" } }; var linesToWrite2 = new List <List <string> >() { new List <string>() { "a0", "a1", "a2" }, new List <string>() { "b0", "b1", "b2" }, new List <string>() { "b0", "b1", "b2" }, new List <string>() { "b0", "b1", "b2" } }; sqlWriter.Init(importTasks.First(), headers); sqlWriter2.Init(importTasks.First(), headers); // Act string insertSql = sqlWriter.GetInsertStatements(linesToWrite); string insertSqlNotUniq = sqlWriter2.GetInsertStatements(linesToWrite2); // Assert Assert.IsTrue(sqlWriter != null); Assert.AreEqual(sqlWriter.GetHeaderFields().Count, 4); // { "head1", "head2", "head3", "DCImportDate"} Assert.IsTrue(insertSql.Contains("INSERT INTO")); Assert.IsTrue(insertSql.Contains("CONVERT(DATETIME")); Assert.AreEqual(insertSql, insertSqlNotUniq); }