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 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 <Dictionary <string, string> > GetAndDeleteRecords(string tableName, string[] targetColumns, List <string> filters)
{
string sqlquery = "DELETE dbo." + tableName + " OUTPUT DELETED.* ";
string conditions = "";
if (filters != null && filters.Count > 0)
{
conditions = " Where ";
for (int i = 0; i < filters.Count; i++)
{
if (i > 0)
{
conditions += " AND ";
}
conditions += filters[i];
}
}
sqlquery += conditions;
SQLServerAccessor dbAccessor = new SQLServerAccessor();
List <Dictionary <string, string> > output = dbAccessor.TableSearch(targetColumns, sqlquery);
return(output);
}
private List <ExamItem> GenerateProcessItems(string sqlquery)
{
SQLServerAccessor dbAccessor = new SQLServerAccessor();
List <Dictionary <string, string> > output = dbAccessor.TableSearch(targetColumns, sqlquery);
List <ExamItem> list = new List <ExamItem>();
int index = 0;
foreach (Dictionary <string, string> rec in output)
{
ExamItem item = new ExamItem();
item.index = index++;
item.question = rec["Question"];
item.answerRegex = rec["AnswerRegex"];
item.answer = rec["Answer"];
item.answerType = rec["AnswerType"];
item.interpret = rec["Interpret"];
item.askbackMessage = rec["AskbackMessage"];
if (!string.IsNullOrWhiteSpace(rec["Score"]))
{
item.score = int.Parse(rec["Score"]);
}
list.Add(item);
}
return(list);
}
public List <TestItem> GenerateTestItems(int count, string courseInTest)
{
string tableName = "calculator3";
string sqlquery = "select top " + count.ToString() + " * from dbo." + tableName + " Where CourseName = N'" + courseInTest + "' order by newid()";
string[] targetColumns = { "Formula", "Result", "ResultMatchRule" };
SQLServerAccessor dbAccessor = new SQLServerAccessor();
List <Dictionary <string, string> > output = dbAccessor.TableSearch(targetColumns, sqlquery);
List <TestItem> list = new List <TestItem>();
int index = 0;
foreach (Dictionary <string, string> rec in output)
{
TestItem item = new TestItem();
item.index = index++;
item.question = rec["Formula"];
item.correctResult = rec["Result"];
item.answerMatchRule = rec["ResultMatchRule"];
list.Add(item);
}
return(list);
}
private Dictionary <int, IRItem> GenerateProcessItems(string sqlquery)
{
SQLServerAccessor dbAccessor = new SQLServerAccessor();
List <Dictionary <string, string> > output = dbAccessor.TableSearch(targetColumns, sqlquery);
Dictionary <int, IRItem> dict = new Dictionary <int, IRItem>();
int index = 0;
foreach (Dictionary <string, string> rec in output)
{
IRItem item = new IRItem();
item.index = index++;
item.stepNo = int.Parse(rec["StepNo"]);
item.content = rec["Content"];
item.options = rec["Options"];
item.optionRegex = rec["OptionRegex"];
item.nextStep = rec["NextStep"];
item.askbackMessage = rec["AskbackMessage"];
dict.Add(item.stepNo, item);
}
return(dict);
}
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);
}
public List <TestItem> GenerateTestItems(int count)
{
string tableName = "scorescripts";
string sqlquery = "select top " + count.ToString() + " * from dbo." + tableName + " order by newid()";
string[] targetColumns = { "script" };
SQLServerAccessor dbAccessor = new SQLServerAccessor();
List <Dictionary <string, string> > output = dbAccessor.TableSearch(targetColumns, sqlquery);
List <TestItem> list = new List <TestItem>();
foreach (Dictionary <string, string> rec in output)
{
TestItem item = new TestItem();
item.question = rec["script"];
list.Add(item);
}
return(list);
}
