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 = "C:\\github\\TABot\\DBSourceData\\Calculator3.tsv";
string csvfilepath = "C:\\github\\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 void DeleteTable(string tableName, List <string> conditions)
{
StringBuilder builder = new StringBuilder();
builder.Append("DELETE FROM dbo." + tableName + " WHERE ");
string conditionStr = "";
foreach (string condition in conditions)
{
if (string.IsNullOrWhiteSpace(conditionStr))
{
conditionStr += condition;
}
else
{
conditionStr += " AND " + condition;
}
}
builder.Append(conditionStr);
string sql = builder.ToString();
SQLServerAccessor dbAccessor = new SQLServerAccessor();
dbAccessor.ExecSQL(sql);
}
public void UpdateTable(string tableName, string[] columns, ColumnType[] types, string[] values, List <string> conditions)
{
StringBuilder builder = new StringBuilder();
builder.Append("Update dbo." + tableName + " SET");
string str = "";
for (int i = 0; i < columns.Length; i++)
{
if (!string.IsNullOrWhiteSpace(columns[i]))
{
str += " " + columns[i] + "=";
if ((types[i] == ColumnType.Int) || (types[i] == ColumnType.Float))
{
str += values[i];
}
else
{
str += "N'" + values[i] + "'";
}
str += ",";
}
}
str = str.Substring(0, str.Length - 1);
builder.Append(str + " Where ");
string conditionStr = "";
foreach (string condition in conditions)
{
if (string.IsNullOrWhiteSpace(conditionStr))
{
conditionStr += condition;
}
else
{
conditionStr += " AND " + condition;
}
}
builder.Append(conditionStr);
string sql = builder.ToString();
SQLServerAccessor dbAccessor = new SQLServerAccessor();
dbAccessor.ExecSQL(sql);
}
public void InsertOrUpdateTable(string tableName, string[] columns, ColumnType[] types, string[] values, List <string> conditions)
{
string sql = "Update dbo." + tableName + " SET ";
string valueStr = "";
for (int i = 0; i < columns.Length; i++)
{
if (!string.IsNullOrWhiteSpace(columns[i]))
{
valueStr += columns[i] + " = ";
if ((types[i] == ColumnType.Int) || (types[i] == ColumnType.Float))
{
valueStr += values[i] + ",";
}
else
{
valueStr += "N'" + values[i] + "',";
}
}
}
valueStr = valueStr.Substring(0, valueStr.Length - 1);
sql += valueStr + "Where ";
string conditionStr = "";
foreach (string condition in conditions)
{
if (string.IsNullOrWhiteSpace(conditionStr))
{
conditionStr += condition;
}
else
{
conditionStr += " AND " + condition;
}
}
sql += conditionStr;
SQLServerAccessor dbAccessor = new SQLServerAccessor();
dbAccessor.ExecSQL(sql);
}
public void InserTable(string tableName, string[] columns, ColumnType[] types, List <string[]> valueList)
{
StringBuilder builder = new StringBuilder();
foreach (string[] values in valueList)
{
string columnStr = "";
string valueStr = "";
builder.Append("INSERT INTO dbo." + tableName + "(");
for (int i = 0; i < columns.Length; i++)
{
if (!string.IsNullOrWhiteSpace(columns[i]))
{
columnStr += columns[i] + ",";
if ((types[i] == ColumnType.Int) || (types[i] == ColumnType.Float))
{
valueStr += values[i] + ",";
}
else
{
valueStr += "N'" + values[i] + "',";
}
}
}
columnStr = columnStr.Substring(0, columnStr.Length - 1);
valueStr = valueStr.Substring(0, valueStr.Length - 1);
builder.AppendLine(columnStr + ") VALUES (" + valueStr + ");");
}
string sql = builder.ToString();
SQLServerAccessor dbAccessor = new SQLServerAccessor();
dbAccessor.ExecSQL(sql);
}