using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;
using Microsoft.CSharp;
using System.CodeDom;
using System.IO;

namespace buildSchema
{
  class Program
  {
    static void Main(string[] args)
    {
      #region Strutture
      if (!true)
      {
        string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\dotNet\Melis\Melis.Web\App_Data\Strutture.mdb";
        string nameSpace = "Melis.Strutture.Schema";
        string outPath = @"D:\dotNet\Melis\Melis.Strutture.Schema\";

        //buildSchema("Lingua", outPath, nameSpace, connectionString);
        //buildSchema("Struttura", outPath, nameSpace, connectionString);
        //buildSchema("StrutturaDettaglio", outPath, nameSpace, connectionString);
        //buildSchema("StrutturaDettaglioTipo", outPath, nameSpace, connectionString);
        //buildSchema("StrutturaTipoServizio", outPath, nameSpace, connectionString);
        //buildSchema("StrutturaDescrizione", outPath, nameSpace, connectionString);
        DataTable tabelle = GetTabelle(connectionString);
        foreach (DataRow tabella in tabelle.Rows)
        {          
          Console.WriteLine(tabella["TABLE_NAME"].ToString() + " - " + tabella["TABLE_TYPE"].ToString());
          if ((tabella["TABLE_TYPE"].ToString() == "TABLE") && (tabella["TABLE_NAME"].ToString().StartsWith("Struttura")))
            buildSchema(tabella["TABLE_NAME"].ToString(), outPath, nameSpace, connectionString);
        }
      }
      #endregion
      #region Ricette
      if (true)
      {
        string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\dotNet\Melis\Melis.Web\App_Data\Ricette.mdb";
        string nameSpace = "Melis.Ricette.Schema";
        string outPath = @"D:\dotNet\Melis\Melis.Ricette.Schema\";
        DataTable tabelle = GetTabelle(connectionString);
        foreach (DataRow tabella in tabelle.Rows)
        {
          Console.WriteLine(tabella["TABLE_NAME"].ToString() + " - " + tabella["TABLE_TYPE"].ToString());
          if (tabella["TABLE_TYPE"].ToString() == "TABLE")
            buildSchema(tabella["TABLE_NAME"].ToString(), outPath, nameSpace, connectionString);
        }
      }
      #endregion
      #region Articoli
      if (!true)
      {
        string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\dotNet\Melis\Melis.Web\App_Data\Articoli.mdb";
        string nameSpace = "Melis.Articoli.Schema";
        string outPath = @"D:\dotNet\Melis\Melis.Articoli.Schema\";
        DataTable tabelle = GetTabelle(connectionString);
        foreach (DataRow tabella in tabelle.Rows)
        {
          Console.WriteLine(tabella["TABLE_NAME"].ToString() + " - " + tabella["TABLE_TYPE"].ToString());
          if (tabella["TABLE_TYPE"].ToString() == "TABLE")
            buildSchema(tabella["TABLE_NAME"].ToString(), outPath, nameSpace, connectionString);
        }
      }
      #endregion
      #region Venere
      if (!true)
      {
        DataSet ds = new DataSet("Venere");
        ds.ReadXml(@"D:\dotNet\Melis\_Test\TestVenere\App_Data\Venere_catalog_it.xml", XmlReadMode.Auto);
        buildSchema("Venere", @"D:\dotNet\Melis\_Test\struttureBuilderHelper\", "struttureBuilderHelper.Venere", ds);
      }
      #endregion
      Console.WriteLine("");
    }

    static void buildSchema(string nomeTabella, string outPath, string nameSpace, string connectionString)
    {
      DataSet ds = new DataSet(nomeTabella + "Set");
      ds.Namespace = nameSpace;
      ds.EnforceConstraints = true;
      ds.CaseSensitive = false;

      OleDbConnection connection = new OleDbConnection(connectionString);
      OleDbCommand command = new OleDbCommand(nomeTabella, connection);
      command.CommandType = CommandType.TableDirect;
      OleDbDataAdapter dataAdapter = new OleDbDataAdapter(command);
      DataTable dt = new DataTable(nomeTabella);
      dataAdapter.FillSchema(ds, SchemaType.Source, nomeTabella);

      buildSchema(nomeTabella, outPath, nameSpace, ds);
    }

    static void buildSchema(string nomeTabella, string outPath, string nameSpace, DataSet dataSet)
    {
      dataSet.WriteXmlSchema(outPath + nomeTabella + ".xsd");
      ScriviFile(outPath + nomeTabella + ".Designer.cs", GetCode(dataSet, nameSpace));
    }

    static DataTable GetTabelle(string connectionString)
    {
      OleDbConnection connection = new OleDbConnection(connectionString);

      connection.Open();
      DataTable tabelle = connection.GetSchema("Tables");
      connection.Close();

      return tabelle;
    }

    static string GetCode(DataSet schema, string nameSpace)
    {
      // Create the Writer to get the output
      CSharpCodeProvider codeDomProvider = new CSharpCodeProvider();

      //ICodeGenerator codegen = (new CSharpCodeProvider()).CreateGenerator();
      CodeCompileUnit unit = new CodeCompileUnit();
      CodeNamespace ns = new CodeNamespace(nameSpace);
      unit.Namespaces.Add(ns);
      unit.ReferencedAssemblies.Add("System.dll");
      unit.ReferencedAssemblies.Add("System.Data.dll");
      unit.ReferencedAssemblies.Add("System.Xml.dll");

      // Generazione del typed dataset    
      System.Data.Design.TypedDataSetGenerator.Generate(schema, ns, codeDomProvider);


      MemoryStream stream = new MemoryStream();
      StreamWriter writer = new StreamWriter(stream);

      codeDomProvider.GenerateCodeFromCompileUnit(unit, writer, null);

      writer.Flush();
      stream.Seek(0, SeekOrigin.Begin);

      StreamReader reader = new StreamReader(stream);
      string codeString = reader.ReadToEnd();
      stream.Close();

      return codeString;
    }

    static void ScriviFile(string PathFile, string Contenuto)
    {
      System.IO.FileStream fs = new System.IO.FileStream(PathFile, System.IO.FileMode.Create);

      System.IO.StreamWriter sw = new System.IO.StreamWriter(fs, System.Text.Encoding.ASCII);

      sw.Write(Contenuto);
      sw.Flush();

      sw.Close();
      fs.Close();
    }
  }
}