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