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