using System;

using Newtonsoft.Json;

using Newtonsoft.Json.Linq;

using System.Linq;

using System.Collections.Generic;

using System.Data;

using System.Data.DataSetExtensions;

using System.Data.Linq;

using System.Xml;

using System.Text.RegularExpressions;

​

class Field

{

    public string Path;

    public string Type;

    public string FieldName;

    public bool isRoot;

    public string TableName;

    public string Name;

    public Field(string Path, string Type, bool isArray = false)

    {

        this.Type = Type;

        this.Path = Path;

        this.Name = Path.Substring(Path.LastIndexOf('.') + 1);

        this.FieldName = this.Path.Substring(0, 1) == "." ? this.Path.Substring(1) : this.Path;

        string[] str = this.FieldName.Split('.');

        this.isRoot = str.Length == 1;

        if (this.isRoot)

        {

            this.TableName = "llodiroot";

        }

        else if (isArray)

        {

            this.TableName = this.Name;

            this.Name += "_Text";

        }

        else

        {

            this.TableName = str[str.Length - 2];

        }

    }

}

​

class Table

{

    public bool isRoot;

    public bool isUltimateChildren;

    public string TableName;

    public string ParentTable;

    public int ParentIndex;

    public string PK;

    public string FK;

    //public int NRows;

    //public DataRowCollection Rows;

    //public DataColumnCollection Columns;

    public Table(DataTable dt)

    {

        this.isRoot = dt.ParentRelations.Count == 0;

        this.isUltimateChildren = dt.ChildRelations.Count == 0;

        this.TableName = dt.TableName;

        this.ParentTable = this.isRoot ? null : dt.ParentRelations[0].ParentTable.TableName;

        this.ParentIndex = this.isRoot ? 0 : dt.DataSet.Tables.IndexOf(dt.ParentRelations[0].ParentTable);

        this.PK = this.isUltimateChildren ? null : dt.PrimaryKey[0].ColumnName;

        this.FK = this.isRoot ? null : dt.ParentRelations[0].ChildColumns[0].ColumnName;

        //this.NRows = dt.Rows.Count;

        //this.Rows = dt.Rows;

        //this.Columns = dt.Columns;

    }

}

​

public class Program

{

    List<Field> Fields = new List<Field>();

    List<Table> Tables = new List<Table>();

    List<List<int>> Rows = new List<List<int>>();

    public void Main()

    {

        string json = "[{\"nombre\":\"paco\",\"apellido\":\"lopez\",\"edad\":18,\"salario\":0.12,\"soltero\":true,\"familia\":[{\"tipo\":\"abuelos\",\"miembros\":[{\"nombre\":\"antonio\"},{\"nombre\":\"javier\",\"apodo\":\"yayo\"}],\"conyuges\":[\"rocio\",\"maria\"]}],\"aspecto\":{\"pelo\":\"negro\",\"estatura\":1.80,\"peso\":90}},{\"nombre\":\"mario\",\"edad\":81,\"salario\":120.12,\"soltero\":false,\"familia\":[{\"tipo\":\"padres\",\"miembros\":[{\"nombre\":\"piti\"},{\"nombre\":\"encarna\"}]},{\"tipo\":\"hermanos\",\"miembros\":[{\"nombre\":\"miguel\"}]}],\"aspecto\":{\"pelo\":\"escaso\",\"estatura\":1.78,\"peso\":110}}]";

        JToken parsed = (JToken)JsonConvert.DeserializeObject(json);

        ParseJson(parsed);

        DataSet ds = ToDataSet(json);

        DeclareTables(ds);

        //Test(2,ds,0,0,"nombre");

        DataTable indexes = MakeIndex (ds);

        foreach (DataColumn col in indexes.Columns) 

        {

            Console.Write(col.ColumnName+",");  

        }

        foreach (DataRow r in indexes.Rows)

        {

            Console.WriteLine();

            foreach (DataColumn c in indexes.Columns) Console.Write(r[c]+",");

        }

​

​

        //Test(3,ds,0,0,"nombre");

        //Print(ds);

    }

///////////EN DESARROLLO

    public DataTable MakeIndex (DataSet ds)

    {

        //List<string> keys = new List<string>();

        //List<string> fields = new List<string>();

        DataTable initial = ds.Tables["llodiroot"];

        //keys.Add("llodiroot_Id");

        //foreach(Field f in Fields.FindAll(x => x.isRoot == true)) fields.Add(f.FieldName); //en root, Name y FieldName son iguales

        for (int i = 1; i < ds.Tables.Count; i++)

        {

            //Console.WriteLine(i.ToString() + " por " + ds.Tables[i].TableName + " que es " + Tables[i].TableName + " con padre " + Tables[i].ParentTable);

            string newKey = Tables[i].PK;

            var linq = from t1 in initial.AsEnumerable()

                        join t2 in ds.Tables[i].AsEnumerable() 

                on t1.Field<int>(Tables[Tables[i].ParentIndex].PK) 

                equals t2.Field<int>(Tables[i].FK) into ps

                from t2 in ps.DefaultIfEmpty()

                select new {t1,t2};

                DataTable replace = initial.Clone();

                replace.Constraints.Clear();

                //foreach (dynamic c in replace.Constraints) replace.Constraints.Remove(c);

                //DataTable replace = new DataTable();

                //foreach (string key in keys) replace.Columns.Add(new DataColumn(key, typeof(Int32))); //add previous keys

                //foreach (string field in fields) replace.Columns.Add(new DataColumn(field)); //add previous fields

                //foreach (DataColumn c in initial.Columns) replace.Columns.Add(new Col);

                replace.Columns.Add(new DataColumn(newKey, typeof(Int32))); //add new key

                foreach (Field f in Fields.FindAll(x => x.TableName == ds.Tables[i].TableName)) 

                {

                    //Console.WriteLine("Adding column for field "+f.FieldName + " in table " + f.TableName);

                    replace.Columns.Add(new DataColumn(f.FieldName)); //add new fields  

                }

                foreach (dynamic row in linq)

                {

                    DataRow dr = replace.NewRow();

                    foreach (DataColumn c in initial.Columns) dr[c.ColumnName] = row.t1[c.ColumnName];

                    //foreach (string key in keys) dr[key] = (int)row.t1[key]; //get previous keys

                    if(!Tables[i].isUltimateChildren) dr[newKey] = (int)row.t2[newKey]; //get new key

                    //foreach (string field in fields) dr[field] = row.t1[field]; //get previous fields

                    //foreach (DataColumn c in initial.Columns) dr[c.ColumnName] = row.t1[c.ColumnName]; //get previous fields

                    foreach (Field f in Fields.FindAll(x => x.TableName == ds.Tables[i].TableName)) if(row.t2 != null) dr[f.FieldName] = row.t2[f.Name]; //get new fields

                    replace.Rows.Add(dr);                   

                }

                //if(!Tables[i].isUltimateChildren) keys.Add(Tables[i].PK); //add key

                //foreach (Field f in Fields.FindAll(x => x.TableName == ds.Tables[i].TableName)) fields.Add(f.FieldName);//add fields

                initial = replace;

        }

        return initial;

    }

    public void Test(int test, DataSet ds = null, int IndexTabla = 0, int RowNum = 0, string fieldname = "nombre")

    {

        switch(test)

        {

            case 0: //print fields

                foreach (Field f in Fields) Console.WriteLine(f.FieldName + " (" + f.TableName + " - " + f.isRoot.ToString() + ")");

                break;

            case 1: //print relaciones

                foreach (Table t in Tables) if (!t.isRoot) Console.WriteLine(t.TableName + " relacionado con " + t.ParentTable + " por " + t.FK);

                break;

            case 2: //print tablas y columnas del DataSet

                foreach (DataTable dt in ds.Tables)

                {

                    Console.WriteLine("\n\n"+dt.TableName+"\n\n");  

                    foreach (DataColumn col in dt.Columns) Console.WriteLine(col.ColumnName);

                }

                break;

            case 3: //pintar un valor concreto

            default:

                //Console.WriteLine(Tables[IndexTabla].Rows[RowNum][fieldname]);

                break;

        }

    }

/////////////////

    public void ParseJson(JToken parsedObject)

    {

        foreach (dynamic entry in parsedObject)

        {

            string type = entry.GetType().ToString().Split('.')[3];

            string entryPath = entry.Path == null ? "" : Regex.Replace(entry.Path, @"\[([0-9]+)\]+", "");

            switch (type)

            {

                case "JProperty":

                    foreach (dynamic child in entry.Children())

                    {

                        string childType = child.GetType().ToString().Split('.')[3];

                        string childPath = child.Path == null ? "" : Regex.Replace(child.Path, @"\[([0-9]+)\]+", "");

                        switch (childType)

                        {

                            case "JValue":

                                if (!Fields.Exists(x => x.Path == childPath))

                                    Fields.Add(new Field(childPath, child.Value.GetType().ToString()));

                                break;

                            case "JArray":

                                ParseJson(child);

                                break;

                            default:

                                //no insertamos JObjects en campos

                                ParseJson(child);

                                break;

                        }

                    }

                    break;

                case "JValue":

                    if (!Fields.Exists(x => x.Path == entryPath))

                        Fields.Add(new Field(entryPath, entry.Value.GetType().ToString(),true));

                    break;

                case "JObject":

                    ParseJson(entry);

                    break;

            }

        }

    }

    public DataSet ToDataSet(string json)

    {

        string rootJson = "{\"llodiroot\": " + json + "}";

        XmlDocument xml = (XmlDocument)JsonConvert.DeserializeXmlNode(rootJson, "llodiroot");

        DataSet ds = new DataSet();

        ds.ReadXml(new XmlNodeReader(xml));

        return ds;

    }

    public void DeclareTables(DataSet ds)

    {

        foreach (DataTable dt in ds.Tables)

        {

            Tables.Add(new Table(dt));

        }

    }

}