Ejemplo n.º 1
0
        public static void kToSqlCreateTable(string tblsFile, string sqlFile = cSqlCreateTblScriptFile)
        {
            string tSqlFile = string.Format("{0}k{1}.{2}", cScriptHomePath, sqlFile, cSqlFileExt);

            SqlScriptLogger scriptLogger = new SqlScriptLogger(tSqlFile);

            scriptLogger.kWriteLine("USE akm_m6dtx;" + Environment.NewLine);
            Console.WriteLine(string.Format("Creating {0} . . .", tSqlFile));

            string        tCsvFile = string.Format("{0}{1}_{2}.{3}", cDataHomePath, tblsFile, cDataPostfix, cCsvFileExt);
            CsvFileReader reader   = new CsvFileReader(tCsvFile);


            Console.WriteLine(string.Format("Opening {0} . . .", tCsvFile));
            Console.WriteLine("[Press the ENTER key to begin . . .]");
            Console.ReadKey();

            CsvRow row = new CsvRow();

            bool isHeader = true;

            while (reader.ReadRow(row))
            {
                if (isHeader)
                {
                    isHeader = false;
                    continue;
                }
                foreach (string tblSchema in row)
                {
                    string script = string.Empty;
                    try
                    {
                        string tTblSchemaFile = string.Format("{0}_{1}", tblSchema, cSchemaPostfix);

                        scriptLogger.kWriteLineHeader(tblSchema);
                        scriptLogger.kWriteLine(2);
                        scriptLogger.kWriteLine(SqlScriptTrans.kToSqlCreateTable(tTblSchemaFile, string.Format("{0}{1}.{2}", cDataHomePath, tTblSchemaFile, cCsvFileExt)));
                        scriptLogger.kWriteLine(2);
                        scriptLogger.kWriteLineFooter();
                    }
                    catch (FileNotFoundException)
                    {
                        scriptLogger.kWriteLine("## [ERROR::" + tblSchema + "] The table does not exists in the database.");
                        continue;
                    }
                    scriptLogger.kWriteLine(4);
                }
            }
            reader.Close();
            scriptLogger.kClose();
        }
Ejemplo n.º 2
0
        public static void kToSqlCreateTable(string tblsFile, string sqlFile = cSqlCreateTblScriptFile)
        {
            string tSqlFile = string.Format("{0}k{1}.{2}", cScriptHomePath, sqlFile, cSqlFileExt);

            SqlScriptLogger scriptLogger = new SqlScriptLogger(tSqlFile);
            scriptLogger.kWriteLine("USE akm_m6dtx;" + Environment.NewLine);
            Console.WriteLine(string.Format("Creating {0} . . .", tSqlFile));

            string tCsvFile = string.Format("{0}{1}_{2}.{3}", cDataHomePath, tblsFile, cDataPostfix, cCsvFileExt);
            CsvFileReader reader = new CsvFileReader(tCsvFile);

            Console.WriteLine(string.Format("Opening {0} . . .", tCsvFile));
            Console.WriteLine("[Press the ENTER key to begin . . .]");
            Console.ReadKey();

            CsvRow row = new CsvRow();

            bool isHeader = true;
            while (reader.ReadRow(row))
            {
                if (isHeader)
                {
                    isHeader = false;
                    continue;
                }
                foreach (string tblSchema in row)
                {
                    string script = string.Empty;
                    try
                    {
                        string tTblSchemaFile = string.Format("{0}_{1}", tblSchema, cSchemaPostfix);

                        scriptLogger.kWriteLineHeader(tblSchema);
                        scriptLogger.kWriteLine(2);
                        scriptLogger.kWriteLine(SqlScriptTrans.kToSqlCreateTable(tTblSchemaFile, string.Format("{0}{1}.{2}", cDataHomePath, tTblSchemaFile, cCsvFileExt)));
                        scriptLogger.kWriteLine(2);
                        scriptLogger.kWriteLineFooter();

                    }
                    catch (FileNotFoundException)
                    {
                        scriptLogger.kWriteLine("## [ERROR::" + tblSchema + "] The table does not exists in the database.");
                        continue;
                    }
                    scriptLogger.kWriteLine(4);

                }
            }
            reader.Close();
            scriptLogger.kClose();
        }
Ejemplo n.º 3
0
        public static void kToSqlInsertTable(string tblFileName, string csvFilePath)
        {
            string cmdText = string.Empty;

            using (CsvFileReader reader = new CsvFileReader(csvFilePath))
            {
                CsvRow          row          = new CsvRow();
                bool            isHeader     = true;
                string          tTblName     = tblFileName.Substring(0, tblFileName.LastIndexOf("_data"));
                string          tblFilePath  = string.Format("{0}k{1}.{2}", SqlScriptGen.cScriptHomePath, tTblName, SqlScriptGen.cSqlFileExt);
                SqlScriptLogger scriptLogger = new SqlScriptLogger(tblFilePath);


                scriptLogger.kWriteLine("USE akm_m6dtx;" + Environment.NewLine);
                scriptLogger.kWriteLineHeader(tblFileName);
                scriptLogger.kWriteLine(2);

                cmdText = string.Format("INSERT INTO {0} (", tTblName) + Environment.NewLine;
                while (reader.ReadRow(row))
                {
                    string insertCmd = string.Empty;

                    /**
                     * Filter the columns of the table and translate to its template header script.
                     */
                    if (isHeader)
                    {
                        isHeader = false;

                        row.ForEach(col => { cmdText += string.Format("`{0}`,", col); });

                        cmdText  = cmdText.Remove(cmdText.LastIndexOf(","), 1);
                        cmdText += ") VALUES (" + Environment.NewLine;
                    }
                    else
                    {
                        insertCmd = cmdText;
                        foreach (string col in row)
                        {
                            // TODO: Improve filtering by using Regex
                            if ((col.IndexOf("AM") > 0 || col.IndexOf("PM") > 0) &&
                                col.IndexOf(@"/") > 0 &&
                                col.IndexOf(@":") > 0)
                            {
                                insertCmd += string.Format(@"STR_TO_DATE('{0}', '%m/%d/%Y %l:%i:%s %p'),", col);
                            }
                            else
                            {
                                insertCmd += string.Format("\t'{0}',", col.Replace('\'', '^')) + Environment.NewLine;
                            }
                        }
                        insertCmd  = insertCmd.Remove(insertCmd.LastIndexOf(","), 1);
                        insertCmd += Environment.NewLine + ");";

                        Console.Write("c o d i n g . . .    t r a n s c o d i n g   . . .  t r a n s    . . . . .   ");
                        scriptLogger.kWriteLine(insertCmd);
                        Console.WriteLine("DONE !!!");
                    }
                }
                scriptLogger.kWriteLine(2);
                scriptLogger.kWriteLineFooter();
                scriptLogger.kClose();

                reader.Close();
            }
        }
Ejemplo n.º 4
0
        public static void kToSqlInsertTable(string tblFileName, string csvFilePath)
        {
            string cmdText = string.Empty;
            using (CsvFileReader reader = new CsvFileReader(csvFilePath))
            {
                CsvRow row = new CsvRow();
                bool isHeader = true;
                string tTblName = tblFileName.Substring(0, tblFileName.LastIndexOf("_data"));
                string tblFilePath = string.Format("{0}k{1}.{2}", SqlScriptGen.cScriptHomePath, tTblName, SqlScriptGen.cSqlFileExt);
                SqlScriptLogger scriptLogger = new SqlScriptLogger(tblFilePath);

                scriptLogger.kWriteLine("USE akm_m6dtx;" + Environment.NewLine);
                scriptLogger.kWriteLineHeader(tblFileName);
                scriptLogger.kWriteLine(2);

                cmdText = string.Format("INSERT INTO {0} (", tTblName) + Environment.NewLine;
                while (reader.ReadRow(row))
                {
                    string insertCmd = string.Empty;

                    /**
                     * Filter the columns of the table and translate to its template header script.
                     */
                    if (isHeader)
                    {
                        isHeader = false;

                        row.ForEach(col => { cmdText += string.Format("`{0}`,", col); });

                        cmdText = cmdText.Remove(cmdText.LastIndexOf(","), 1);
                        cmdText += ") VALUES (" + Environment.NewLine;
                    }
                    else
                    {
                        insertCmd = cmdText;
                        foreach (string col in row)
                        {
                            // TODO: Improve filtering by using Regex
                            if ((col.IndexOf("AM") > 0 || col.IndexOf("PM") > 0) &&
                                col.IndexOf(@"/") > 0 &&
                                col.IndexOf(@":") > 0)
                            {
                                insertCmd += string.Format(@"STR_TO_DATE('{0}', '%m/%d/%Y %l:%i:%s %p'),", col);
                            }
                            else
                                insertCmd += string.Format("\t'{0}',", col.Replace('\'', '^')) + Environment.NewLine;

                        }
                        insertCmd = insertCmd.Remove(insertCmd.LastIndexOf(","), 1);
                        insertCmd += Environment.NewLine + ");";

                        Console.Write("c o d i n g . . .    t r a n s c o d i n g   . . .  t r a n s    . . . . .   ");
                        scriptLogger.kWriteLine(insertCmd);
                        Console.WriteLine("DONE !!!");

                    }
                }
                scriptLogger.kWriteLine(2);
                scriptLogger.kWriteLineFooter();
                scriptLogger.kClose();

                reader.Close();
            }
        }