cdb 2nd stage done

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Threading.Tasks;
using System.Collections;
using System.Diagnostics;

using System.Data.SQLite;

using MySql.Data;
using MySql.Data.MySqlClient;

namespace adonet_4
{
    public class Globals
        {
            private static SortedDictionary<string, List<String>> _mapNameToDataList = new SortedDictionary<string, List<String>>();
            private static List<string> _listTablenames;

            public static SortedDictionary<string, List<String>> mapNameToDataList
            {
                get { return _mapNameToDataList; }
                set { _mapNameToDataList = value; }
            }

            public static List<string> listTableNames
            {
                get { return _listTablenames; }
                set { _listTablenames = value; }
            }
        }

    class Program
    {

        static void execReaderSqlite(SQLiteCommand command)
        {
            try
            {
                SQLiteDataReader reader = command.ExecuteReader();
                Console.WriteLine("Executing Sqlite reader for query:" + command.CommandText);
                do
                {
                    while (reader.Read())
                    {
                        Object[] values = new Object[reader.FieldCount];
                        int fieldCount = reader.GetValues(values);

                        for (int i = 0; i < fieldCount; i++)
                        {
                            Console.Write("\t{0}", values[i]);
                        }
                        Console.WriteLine();
                    }

                } while (reader.NextResult());

                reader.Close();

            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }

            Console.WriteLine();
        }


        //      P E R E G R O O Z K A
        static void execReaderMysql(MySqlCommand command)
        {
            try
            {
                MySqlDataReader reader = command.ExecuteReader();
                Console.WriteLine("Executing Mysql reader for query:" + command.CommandText);
                do
                {
                    while (reader.Read())
                    {
                        Object[] values = new Object[reader.FieldCount];
                        int fieldCount = reader.GetValues(values);

                        for (int i = 0; i < fieldCount; i++)
                        {
                            Console.Write("\t{0}", values[i]);

                        }
                        Console.WriteLine();
                    }

                } while (reader.NextResult());

                reader.Close();

            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }

            Console.WriteLine();
        }




        static List<string> retrieveTablenames(SQLiteConnection connection)
        {
            List<string> tmpList = new List<string>();

            try
            {

                string q = "SELECT name FROM sqlite_master WHERE type = 'table'";
                SQLiteCommand cmd = new SQLiteCommand(q, connection);

                SQLiteDataReader reader = cmd.ExecuteReader();

                if (reader.HasRows)
                {
                    int j = 0;
                    while (reader.Read())
                    {
                        for (int i = 0; i < reader.FieldCount; i++)
                        {
                            string tableName = reader.GetValue(i).ToString();

                            tmpList.Add(tableName);
                        }
                        j++;
                    }
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
                Console.ReadLine();
            }

            return tmpList;
        }


        static List<String> retrieveData(string tableName, SQLiteConnection connection)
        {

            string q = string.Format("SELECT * FROM {0}", tableName);
            SQLiteCommand cmd = new SQLiteCommand(q, connection);

            List<String> data = new List<String>();

            try {
                Console.WriteLine("\treading table [" + tableName + "]");

                if (null != connection && ConnectionState.Open != connection.State)
                {
                    connection.Open();
                }
                //string q = "SELECT name FROM sqlite_master WHERE type = 'table'";
                //q = "select * from Persons";
                //SQLiteCommand cmd = new SQLiteCommand(query, connection);

                SQLiteDataReader reader = cmd.ExecuteReader();


                int entryNum = 0;

                if (reader.HasRows)
                {

                    while (reader.Read())
                    {

                        String entry = "(";

                        for (int i = 0; i < reader.FieldCount; i++)
                        {
                            //Console.Write("\t{0}", Convert.ToString(reader[i]));
                            try
                            {
                                string cellType = reader.GetDataTypeName(i).ToString();
                                switch (cellType)
                                {
                                    case "INTEGER":
                                        break;
                                    case "DATETIME":
                                        entry += "'";
                                        break;
                                    default:
                                        entry += "'"; //\"
                                        break;
                                }

                                string cellValue = reader.GetValue(i).ToString();
                                cellValue = cellValue.Replace("'", "''");
                                if (cellValue.Equals(""))
                                {
                                    entry = entry.Remove(entry.Length - 1);
                                    entry += "null";
                                    goto fuck;
                                }

                                entry += cellValue;

                                switch (cellType)
                                {
                                    case "INTEGER":
                                        break;
                                    case "DATETIME":
                                        entry += "'";
                                        break;
                                    default:
                                        entry += "'"; //\"
                                        break;
                                }


                                fuck:
                                entry += ", ";


                            }
                            catch (Exception e)
                            {
                                Console.WriteLine(e.Message);
                            }

                        }

                        entry = entry.Remove(entry.Length - 2);
                        entry += ")";

                        data.Add(entry);

                        entryNum++;


                        if (1 == entryNum)
                        {
                            Console.WriteLine("\tEntry example: " + entry);
                        }
                    }
                }

                reader.Close();

                Console.WriteLine("\t" + entryNum + " entries for table [" + tableName + "] has been processed");

            }
            catch (SQLiteException e)
            {
                Console.WriteLine(e.Message);
                Console.ReadLine();
            }
            finally
            {
                connection.Close();
            }

            return data;
        }





        static void insertData(String tableName, List<String> data, MySqlConnection connection)
        {
            try
            {
                Console.WriteLine("\treading table [" + tableName + "]");

                var dataAsString = String.Join(", ", data);
                Console.WriteLine("\tquery length: " + dataAsString.Length);


                if (null != connection && ConnectionState.Open != connection.State)
                {
                    connection.Open();
                }

                String q = String.Format("insert into {0} values {1}", tableName, dataAsString);

                MySqlCommand cmd = new MySqlCommand(q, connection);

                cmd.ExecuteNonQuery();

            }
            catch (MySqlException e)
            {
                Console.WriteLine(e.Message);
                Console.ReadLine();
            }
            finally
            {
                connection.Close();
            }
        }




        static void Main(string[] args)
        {
            int tableNumSqlite = 0, tableNumMysql = 0;

            Console.WriteLine("============================================================================");
            Console.WriteLine("---------------------------------SQLITE-------------------------------------");
            Console.WriteLine("============================================================================");

            string connStringSqlite = @"Data Source=C:\User
            using (SQLiteConnection conn1 = new SQLiteConnection(connStringSqlite))
            {
                try
                {
                    conn1.Open();
                    Console.WriteLine("connection to SQLite has been set\n");

                    Console.Write("retrieving tablenames: ");

                    Globals.listTableNames = retrieveTablenames(conn1);

                    Console.WriteLine("successfully retrieved\n");


                    Stopwatch stopWatch = new Stopwatch();
                    stopWatch.Start();
                    foreach (string table in Globals.listTableNames)
                    {
                        Console.WriteLine("retrieving data for table " + ++tableNumSqlite);

                        List<String> data = retrieveData(table, conn1);
                        Globals.mapNameToDataList.Add(table, data);

                        Console.WriteLine("\tdata has been added to map");
                    }
                    stopWatch.Stop();



                    Console.WriteLine("\nelapsed time for " + tableNumSqlite + " tables: " + stopWatch.Elapsed.ToString() + "\n");

                }
                catch (Exception e)
                {
                    Console.WriteLine(e.Message);
                }
            }


            Console.WriteLine("============================================================================");
            Console.WriteLine("----------------------------------MYSQL-------------------------------------");
            Console.WriteLine("============================================================================\n");


            string connStringMysql = "Server=localho
            using (MySqlConnection conn2 = new MySqlConnection(connStringMysql))
            {
                try
                {
                    conn2.Open();
                    Console.WriteLine("connection to MySQL has been set\n");


                    Stopwatch stopWatch = new Stopwatch();
                    stopWatch.Start();
                    foreach (string table in Globals.listTableNames)
                    {
                        //if (!table.Equals("Sessions"))
                        //{
                            Console.WriteLine("inserting data into table " + ++tableNumMysql + ": " + table);

                            if (table.Equals("RelTasksModules") || table.Equals("FailedTests"))
                            {
                                Console.WriteLine("\t-skipping-");
                                continue;
                            }

                            if (table.Equals("Comments"))
                            {

                                int divisionParts = 200;
                                int count = Globals.mapNameToDataList["Comments"].Count - (Globals.mapNameToDataList["Comments"].Count % 10);
                                int partSize = (int)(count / divisionParts);
                                int from = 0;

                                //List<String> dataInRange = Globals.mapNameToDataList["Comments"].GetRange(7000, 10000);
                                //insertData("Comments", dataInRange, conn2);

                                Console.WriteLine("\tall data count:" + count);

                                int f = 0;
                                while (from < count)
                                {



                                    List<String> dataInRange = Globals.mapNameToDataList["Comments"].GetRange(from, partSize);

                                    if (dataInRange.Contains("'2013-04-04 20:10:17'"))
                                    {
                                        Console.WriteLine("found it");
                                        from += partSize;
                                        continue;
                                    }

                                    Console.WriteLine("\t" + f++ + " data range:" + from + ".." + (from + partSize));
                                    insertData("Comments", dataInRange, conn2);


                                    from += partSize;
                                }
                                Console.WriteLine("\tdone");

                                //Console.WriteLine("count:" + Globals.mapNameToDataList["Comments"].Count / 10);
                                //List<String> first50k = Globals.mapNameToDataList["Comments"].
                                //  GetRange(0, Globals.mapNameToDataList["Comments"].Count / 10);
                                //List<String> rest50k = Globals.mapNameToDataList["Comments"].
                                    //GetRange(Globals.mapNameToDataList["Comments"].Count / 2,
                                        //   Globals.mapNameToDataList["Comments"].Count - 1);

                                //insertData("Comments", first50k, conn2);
                                //Console.WriteLine("\t30k");
                                //insertData("Comments", rest50k, conn2);
                                //Console.WriteLine("\trest");



                                continue;
                            }



                            List<String> data = Globals.mapNameToDataList[table];
                            insertData(table, data, conn2);

                            if (table.Equals("RelLanguagesModules"))
                            {
                                //insert RelTasksModules first
                                List<String> specData = Globals.mapNameToDataList["RelTasksModules"];
                                insertData("RelTasksModules", specData, conn2);
                                Console.WriteLine("\tpre-added RelTasksModules");
                                continue;
                            }




                        //}



                    }
                    stopWatch.Stop();



                    Console.WriteLine("\nelapsed time for " + tableNumMysql + " tables: " + stopWatch.Elapsed.ToString() + "\n");


                }
                catch (Exception e)
                {
                    Console.WriteLine(e.Message);
                }


                //MySqlCommand cmd2 = new MySqlCommand("select table_name from information_schema.tables", conn2);
                //execReaderMysql(cmd2);
                //Console.WriteLine("cmd2 done");

                MySqlCommand cmd3 = new MySqlCommand("select * from persons", conn2);
                execReaderMysql(cmd3);

                Console.WriteLine("cmd3 done");

                /*string q4 = "insert into persons(personid, firstname, lastname, login, password, avatar, recoversession, banned) values " + Globals.mapNameToData["Persons"];
                Console.WriteLine(q4);
                MySqlCommand cmd4 = new MySqlCommand(q4, conn2);
                cmd4.ExecuteNonQuery();
                Console.WriteLine("cmd4 done");*/

                execReaderMysql(cmd3);

                conn2.Dispose();


                Console.WriteLine("all done");
                Console.ReadLine();
            }
        }

    }
}