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