Ejemplo n.º 1
0
        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());
        }
Ejemplo n.º 2
0
        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());
        }
Ejemplo n.º 3
0
        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);
        }
Ejemplo n.º 4
0
        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);
        }
Ejemplo n.º 5
0
        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);
        }