public static void GenerateCreateScript()
{
SQLServerAccessor accessor = new SQLServerAccessor();
string tsvfilepath = "d:\\data\\ta\\IntelligenceRoute.tsv";
//"d:\\data\\ta\\Examination.tsv";//"C:\\WS\\OfficialAccountBotExtension\\Hackthon\\TABot\\DBSourceData\\Calculator3.tsv";
string csvfilepath = "C:\\WS\\OfficialAccountBotExtension\\Hackthon\\TABot\\DBSourceData\\PSAScheduleData2.csv";
// "C:\\WS\\OfficialAccountBotExtension\\Hackthon\\TABot\\DBSourceData\\PSANotificationData.csv";
// "C:\\WS\\OfficialAccountBotExtension\\Hackthon\\TABot\\DBSourceData\\PSAESCachedObject.csv";
//CSVParser parser = new CSVParser(csvfilepath);
TSVParser parser = new TSVParser(tsvfilepath);
DBTableSchema schema = parser.ParserSchema();
string createtablestr = parser.GenerateSQLQueryForCreateTable(schema);
Console.WriteLine(createtablestr);
accessor.ExecSQL(createtablestr);
List <string> insertList = parser.GenerateSQLQueryForInsertRow(schema);
StringBuilder builder = new StringBuilder();
foreach (string line in insertList)
{
builder.AppendLine(line);
Console.WriteLine(line);
}
accessor.ExecSQL(builder.ToString());
}
public static void GenerateCreateScript()
{
SQLServerAccessor accessor = new SQLServerAccessor();
string tsvfilepath = @"D:\repository\Mitutor\DBSourceData\ScoreScripts.tsv";
string csvfilepath = "D:\\hackson\\TABot\\TABot\\DBSourceData\\PSAScheduleData2.csv";
//CSVParser parser = new CSVParser(csvfilepath);
TSVParser parser = new TSVParser(tsvfilepath);
DBTableSchema schema = parser.ParserSchema();
string createtablestr = parser.GenerateSQLQueryForCreateTable(schema);
Console.WriteLine(createtablestr);
//accessor.ExecSQL(createtablestr);
List <string> insertList = parser.GenerateSQLQueryForInsertRow(schema);
StringBuilder builder = new StringBuilder();
foreach (string line in insertList)
{
builder.AppendLine(line);
Console.WriteLine(line);
}
accessor.ExecSQL(builder.ToString());
}
public List <string> GenerateSQLQueryForInsertRow(DBTableSchema schema)
{
string tableName = schema.tableName;
List <string> results = new List <string>();
foreach (string line in dataLines)
{
StringBuilder builder = new StringBuilder();
builder.Append("INSERT INTO dbo." + tableName + "(");
string[] tmps = line.Split(separator);
string columns = "";
string values = "";
for (int i = 0; i < tmps.Length; i++)
{
if (!string.IsNullOrWhiteSpace(tmps[i]))
{
columns += schema.headers[i].name + ",";
string columnValue = tmps[i];
if (columnValue.StartsWith("file://"))
{
string path = columnValue.Replace("file://", "");
columnValue = File.ReadAllText(path);
columnValue = columnValue.Replace("'", "''");
}
if ((schema.headers[i].type == ColumnType.Int) || (schema.headers[i].type == ColumnType.Float))
{
values += columnValue + ",";
}
else
{
values += "N'" + columnValue + "',";
}
}
}
columns = columns.Substring(0, columns.Length - 1);
values = values.Substring(0, values.Length - 1);
builder.Append(columns + ") VALUES (" + values + ");");
results.Add(builder.ToString());
}
return(results);
}
public string GenerateSQLQueryForCreateTable(DBTableSchema schema)
{
string tableName = "dbo." + schema.tableName;
StringBuilder builder = new StringBuilder();
//builder.AppendLine("DROP TABLE IF EXISTS " + tableName);
builder.AppendLine("CREATE TABLE " + tableName + " (");
builder.AppendLine("Id INT NOT NULL IDENTITY(1,1) PRIMARY KEY,");
foreach (ColumnSchema column in schema.headers)
{
string line = column.name + " ";
string type = "nvarchar(256)";
if (column.type == ColumnType.Int)
{
type = "INT";
}
else if (column.type == ColumnType.Float)
{
type = "FLOAT";
}
else if (column.type == ColumnType.DateTime)
{
type = "DateTime";
}
else if (column.type == ColumnType.LongString)
{
type = "nvarchar(MAX)";
}
else if (column.type == ColumnType.Binary)
{
type = "VARBINARY(MAX)";
}
line += type + " NULL,";
builder.AppendLine(line);
}
string result = builder.ToString(0, builder.Length - 3);
result += "\r\n)";
return(result);
}
public DBTableSchema ParserSchema()
{
DBTableSchema schema = new DBTableSchema();
schema.tableName = tableName;
string[] headers = headerLine.Split(separator);
int len = headers.Length;
if (len < 1)
{
return(null);
}
ColumnSchema[] columns = new ColumnSchema[len];
for (int i = 0; i < len; i++)
{
ColumnSchema column = new ColumnSchema();
column.rowIndex = i;
string columnStr = headers[i];
string[] tmps = columnStr.Split(':');
if (tmps.Length == 1)
{
column.type = ColumnType.String;
column.name = tmps[0];
}
else if (tmps.Length == 2)
{
column.type = (ColumnType)Enum.Parse(typeof(ColumnType), tmps[1], true);
column.name = tmps[0];
}
columns[i] = column;
}
schema.headers = columns;
return(schema);
}