Example #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());
        }
Example #2
0
        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());
        }
Example #3
0
        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);
        }
Example #4
0
        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);
        }
Example #5
0
        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);
        }
Example #6
0
        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);
        }