public static void generate(string xlsxPath, string datatypePath = null) { string filename = Path.GetFileNameWithoutExtension(xlsxPath); filename = filename.Replace(" ", "_"); string pathWOExtension = Path.GetDirectoryName(xlsxPath)+ "\\" + filename; DatabaseInfo.DatabaseName = filename; //convert from xlsx to csv string csvPath = pathWOExtension + ".csv"; Console.WriteLine("csvPath is: " + csvPath); XLSXToCSVConverter.toCSV(xlsxPath, csvPath); //represent csv as an object CSVWrapper csv = new CSVWrapper(); csv.openCSV(csvPath); //make the contents of the sql script string sqlPath = pathWOExtension + ".sql"; Console.WriteLine("sqlPath is: " + sqlPath); string sqlContent = new SQLBuilder(csv, filename, filename+"Table", datatypePath). CreateDatabase().DropTable().CreateUse().CreateTable().CreateInsert().Build(); //make the contents of the connection string file string dbPath = pathWOExtension + ProjectStrings.CONNECTION_STRING_FILE_EXTENSION; string dbContent = DatabaseInfo.DatabaseName; //write sql to file System.IO.File.WriteAllText(sqlPath, sqlContent); System.IO.File.WriteAllText(dbPath, dbContent); string[] lines = sqlContent.Split('\n'); createDatabaseFromSql(lines, filename); populateDatabaseFromSql(lines, filename); }
private void checkAttributes(String[] expected, CSVWrapper csv) { for (int i = 0; i < csv.Attributes.Length; i++) { Assert.AreEqual(expected[i], csv.Attributes[i]); } }
public void openCSV_existing_EmptyFile() { CSVWrapper csv = new CSVWrapper(); csv.openCSV(FormatPath.formatPath("empty.csv")); Assert.IsNull(csv.Attributes); Assert.AreEqual(0, csv.Data.Count); }
public void CreateInsertTest_emptyCSV() { CSVWrapper csv = new CSVWrapper(); csv.openCSV(FormatPath.formatPath("empty.csv")); SQLBuilder builder = new SQLBuilder(csv, "", "Employee", ""); Assert.AreEqual("", builder.Build()); builder.CreateInsert(); Assert.AreEqual("", builder.Build()); }
public void CleanStringTestSpace() { CSVWrapper csv = new CSVWrapper(); csv.openCSV(FormatPath.formatPath("space.csv")); SQLBuilder builder = new SQLBuilder(csv, "space", "spaceTable", null); Assert.AreEqual("", builder.Build()); builder.CreateTable(); Assert.AreEqual("CREATE TABLE spaceTable (RowID int IDENTITY (1,1) PRIMARY KEY, Name text, Gender text, Salary text, Marriage_Status text);\n", builder.Build()); }
private void checkData(String[][] expected, CSVWrapper csv) { for (int i = 0; i<csv.Data.Count; i++) { for (int j = 0; j<csv.Data[i].Length; j++) { Assert.AreEqual(expected[i][j], csv.Data[i][j]); } } }
public void CleanStringTestSpecialCharacter() { CSVWrapper csv = new CSVWrapper(); csv.openCSV(FormatPath.formatPath("specialSymbol.csv")); SQLBuilder builder = new SQLBuilder(csv, "", "Employee", ""); Assert.AreEqual("", builder.Build()); builder.CreateInsert(); string[] insertLines = builder.Build().Split('\n'); Assert.AreEqual("INSERT INTO Employee(amt_in, amt1, amt2, sum) VALUES ('10', '50', '50','100');", insertLines[0]); Assert.AreEqual("INSERT INTO Employee(amt_in, amt1, amt2, sum) VALUES ('12', '20', '70', '90');", insertLines[1]); }
public void CreateInsertTest_smallCSV() { CSVWrapper csv = new CSVWrapper(); csv.openCSV(FormatPath.formatPath("test1.csv")); SQLBuilder builder = new SQLBuilder(csv, "", "Employee", ""); Assert.AreEqual("", builder.Build()); builder.CreateInsert(); string[] insertLines = builder.Build().Split('\n'); Assert.AreEqual("INSERT INTO Employee(Name, Gender, Salary) VALUES ('Tom', 'M', '20');", insertLines[0]); Assert.AreEqual("INSERT INTO Employee(Name, Gender, Salary) VALUES ('Adam', 'M', '30');", insertLines[1]); Assert.AreEqual("INSERT INTO Employee(Name, Gender, Salary) VALUES ('Sara', 'F', '40');", insertLines[2]); Assert.AreEqual("INSERT INTO Employee(Name, Gender, Salary) VALUES ('Serena', 'F', '50');", insertLines[3]); }
public SQLBuilder(CSVWrapper csv, string schemaName, string tableName, string datatypePath) { this.csv = csv; this.schemaName = schemaName; this.tableName = tableName; CleanNames(); builder = new StringBuilder(); datatype = null; if (!string.IsNullOrEmpty(datatypePath) && File.Exists(datatypePath)) { datatype = File.ReadAllText(datatypePath).Split(','); } }
public void openCSV_existing_smallFile() { CSVWrapper csv = new CSVWrapper(); csv.openCSV(FormatPath.formatPath("test1.csv")); Assert.IsNotNull(csv.Attributes); string[] expectedAttributes = {"Name", "Gender", "Salary" }; checkAttributes(expectedAttributes, csv); string[][] expectedData = { new string[] { "Tom", "M", "20" }, new string[] {"Adam", "M", "30" }, new string[] {"Sara", "F", "40" }, new string[] {"Serena", "F", "50" } }; checkData(expectedData, csv); }
public void openCSV_emptyPath() { CSVWrapper csv = new CSVWrapper(); csv.openCSV(""); }
public void CreateTableTest_name_smallCSV() { CSVWrapper csv = new CSVWrapper(); csv.openCSV(FormatPath.formatPath("test1.csv")); SQLBuilder builder = new SQLBuilder(csv, "", "Employee", ""); Assert.AreEqual("", builder.Build()); builder.CreateTable(); Assert.AreEqual("CREATE TABLE Employee (RowID int IDENTITY (1,1) PRIMARY KEY, Name text, Gender text, Salary text);\n", builder.Build()); }
public void CreateTableTest_name_noAttributes() { CSVWrapper csv = new CSVWrapper(); csv.openCSV(FormatPath.formatPath("empty.csv")); SQLBuilder builder = new SQLBuilder(csv, "", "Account", ""); Assert.AreEqual("", builder.Build()); builder.CreateTable(); Assert.AreEqual("CREATE TABLE Account (RowID int IDENTITY (1,1) PRIMARY KEY);\n", builder.Build()); }