public void CreateDatabaseTest_name()
 {
     SQLBuilder builder = new SQLBuilder(null, "nameDB", "", "");
     Assert.AreEqual("", builder.Build());
     builder.CreateDatabase();
     Assert.AreEqual("IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = 'nameDB') CREATE DATABASE nameDB;\n", builder.Build());
 }
        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);
        }
 public void CreateDatabaseTest_empty()
 {
     SQLBuilder builder = new SQLBuilder(null, "", "", "");
     Assert.AreEqual("", builder.Build());
     builder.CreateDatabase();
     Assert.AreEqual("", builder.Build());
 }
 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());
 }
 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 void CreateSchemaTest_empty()
 {
     SQLBuilder builder = new SQLBuilder(null, "", "", "");
     Assert.AreEqual("", builder.Build());
     builder.createSchema();
     Assert.AreEqual("", builder.Build());
 }
 public void DropTableTest_name()
 {
     SQLBuilder builder = new SQLBuilder(null, "", "nameTable", "");
     Assert.AreEqual("", builder.Build());
     builder.DropTable();
     Assert.AreEqual("If Exists(Select object_id From sys.tables Where name = 'nameTable') Drop Table nameTable;\n", builder.Build());
 }
 public void CreateUse()
 {
     SQLBuilder builder = new SQLBuilder(null, "useME", "", "");
     Assert.AreEqual("", builder.Build());
     builder.CreateUse();
     Assert.AreEqual("USE useME;\n", builder.Build());
 }
 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());
 }
 public void CreateTableTest_emptyCSV()
 {
     SQLBuilder builder = new SQLBuilder(new CSVWrapper(), "", "", "");
     Assert.AreEqual("", builder.Build());
     builder.CreateTable();
     Assert.AreEqual("", builder.Build());
 }