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