using System;

using System.Text.RegularExpressions;

using MySql.Data;

using MySqlConnection;

using MySqlCommand;

​

namespace csv2sql{

    public class csv2sql

    {

        public static void Main(string[] args){

            String path = "data.csv";

            File arqin = new File(path);

            File arqout = new File(path+".sql");

            MySqlConnection conexao = new MySqlConnection("server=localhost;database=newDb;user=root;password=");

            Data d = new Data();

            d.loadData(arqin);

            d.saveData(arqout);

            d.saveData(conn);

        }

    }

​

    public class File {

​

        private String path;

        private System.IO.StreamReader entrada;

        private System.IO.StreamWriter saida;

​

        public File(String p) {

            path = p;

        }

​

        public String getPath() {

            return path;

        }

​

        public void setPath(String path) {

            this.path = path;

        }

​

        public System.IO.StreamReader openRead() {

            var filestream = new System.IO.FileStream(path,

                        System.IO.FileMode.Open,

                        System.IO.FileAccess.Read,

                        System.IO.FileShare.ReadWrite);

            this.entrada = new System.IO.StreamReader(filestream, System.Text.Encoding.UTF8, true, 128);

            return this.entrada;

        }

​

        public System.IO.StreamWriter openWrite() {

            saida = new System.IO.StreamWriter(path);

            return saida;

        }

​

        public String nextLine(){

            return entrada.ReadLine();

        }

​

​

    }

​

    public class Data {

​

        private String[] lines = new String[20];

        private System.IO.StreamReader entrada;

​

        public void loadData(File arq) {

            this.entrada = arq.openRead();

            var dataRow = this.entrada.ReadLine();

            string[] cols = Regex.Split(entrada.ReadLine(), "\r\n");

            String line;

            while ((line = entrada.ReadLine()) != null){

                String[] dataArray = Regex.Split(line,";");

                String colunas = "";

                String linha = "";

                String valores = "";

                int l = 0;

                if(l != 0){

                    for (int i = 0; i < dataArray.Length; i++) { 

                        if(colunas != "") colunas+=",";

                        colunas += "`"+ cols[i] +"`";

                        if(valores != "") valores+=",";

                        valores += "'"+ dataArray[i].Replace("'", "") +"'";

                    }

                    linha = "INSERT INTO tabela (" + colunas + 

                                    ") VALUES (" + valores + ");";

                    lines[l] = linha;

                    l++;

                    linha = ""; 

                } else {

                    for (int i = 0; i < dataArray.Length; i++) { 

                        cols[i] = dataArray[i].Replace(' ', '_');

                        if(colunas != "") colunas+=",";

                        colunas += "`"+ dataArray[i].Replace(' ', '_')  + "` VARCHAR(255)";

                    }

                    linha = "CREATE TABLE tabela ("+ colunas + ");";

                    lines[l] = linha;

                    l++;

                    valores = "";

                    linha = "";

                }

                dataRow = entrada.ReadLine();

            }

        }

​

        public void saveData(MySqlConnection conn){

            conn.open();

            int count = 0;

            foreach(String l in this.lines){

                if(conn.prepare(l)){

                    conn.runInstruction();

                }

                if(count%100 == 0){

                    Console.WriteLine(count);

                }

                count++;

            }

            conn.close();  

        }

​

        public void saveData(File arq){

            var saida = arq.openWrite();

            foreach (string line in lines){

                saida.WriteLine(line);

            }

            saida.Close();

        }       

​

        public void showData(){

            foreach(String s in lines){

                Console.Write(s);

            }

        }

    }

​

}