DataTable to complex nested JSON-CodePudding

I have a SQL Query into a DataTable that I would like to convert into a nested json document. I tried to use nested dictionaries but I don't think I created enough nests and I'm wondering if that's really the best approach. I also tried dynamic parameters but wanted to take a step back and ask if I'm doing this correctly; I'm fairly new to C#. Another issue is the request for "header information" that they would like to appear at the top of the JSON; this information isn't in the DataTable as it's currently manually entered information into a config file variable

below is an example of the data table

database	table	tablesize	column	datatype
marketing	contacts	535MB	contactid	int
marketing	contacts	535MB	contact_firstname	varchar(50)
marketing	contacts	535MB	contact_lastname	varchar(50)
marketing	addresses	1200MB	addressid	int
marketing	addresses	1200MB	address1	varchar(100)
HR	employees	600MB	employeeID	int
HR	employees	600MB	employee_firstname	varchar(50)

below is the expected JSON structure

 REQUESTED_BY: bob smith
 REQUEST_DATE: 12/30/2022  08:30
 COMPLETED_BY: jane doe
 COMPLETED_ON: 12/30/2022  08:55
      Databases:
            marketing
                     tables:
                          contacts
                          tablesize:535MB
                               columns:
                                    contactid
                                         datatype: int
                                    contact_firstname
                                         datatype: varchar(50)
                                    contact_lastname
                                         datatype: varchar(50)
                          addresses
                          tablesize:1200MB
                               columns:
                                    addressid
                                         datatype: int
                                    address1
                                         datatype: varchar(100)
            HR
                     tables:
                          employees
                          tablesize:600MB
                               columns:
                                    employeeid
                                         datatype: int
                                    employee_firstname
                                         datatype: varchar(50)

here's a code snippet. I tried to start small and work my way out of the nest

        var tbls = new Dictionary<string, Dictionary<string,string>>();

        foreach (DataRow row in dbrequestdt.Rows)
        {
            string table = row["table"].ToString();
            Dictionary<string, string> columns;

            if(!tbls.TryGetValue(table, out columns))
            {
                columns = new Dictionary<string, string>();
                tbls.Add(table, columns);
            }
            columns.Add(row["column"].ToString(), row["datatype"].ToString());
        }

CodePudding user response：

I have generated JSON based on your text table for testing purposes.

string jsonBasedOnTable = "[{\"database\":\"marketing\",\"table\":\"contacts\",\"tablesize\":\"535MB\",\"column\":\"contactid\",\"datatype\":\"int\"},{\"database\":\"marketing\",\"table\":\"contacts\",\"tablesize\":\"535MB\",\"column\":\"contact_firstname\",\"datatype\":\"varchar(50)\"},{\"database\":\"marketing\",\"table\":\"contacts\",\"tablesize\":\"535MB\",\"column\":\"contact_lastname\",\"datatype\":\"varchar(50)\"},{\"database\":\"marketing\",\"table\":\"addresses\",\"tablesize\":\"1200MB\",\"column\":\"addressid\",\"datatype\":\"int\"},{\"database\":\"marketing\",\"table\":\"addresses\",\"tablesize\":\"1200MB\",\"column\":\"address1\",\"datatype\":\"varchar(100)\"},{\"database\":\"HR\",\"table\":\"employees\",\"tablesize\":\"600MB\",\"column\":\"employeeID\",\"datatype\":\"int\"},{\"database\":\"HR\",\"table\":\"employees\",\"tablesize\":\"600MB\",\"column\":\"employee_firstname\",\"datatype\":\"varchar(50)\"}]";

According to your table structure implemented the model classes as,

public class DatabaseInfo
{
    public string database { get; set; }
    public TableInfo[] tables { get; set; }
}

public class TableInfo
{
    public string table { get; set; }
    public string tablesize { get; set; }
    public ColumnInfo[] columns { get; set; }
}

public class ColumnInfo
{
    public string column { get; set; }
    public string datatype { get; set; }
}

Then you can do something like this.

DataTable dataTable = (DataTable)JsonConvert.DeserializeObject(jsonBasedOnTable, (typeof(DataTable)));

List < DatabaseInfo > databases = new List < DatabaseInfo > ();
foreach(DataRow row in dataTable.Rows) {
  string database = row["database"].ToString();
  string table = row["table"].ToString();
  string tablesize = row["tablesize"].ToString();
  string column = row["column"].ToString();
  string datatype = row["datatype"].ToString();

  // here I'm cheking same record is avialble
  DatabaseInfo databaseInfo = databases.FirstOrDefault(d => d.database == database);
  if (databaseInfo == null) {
    databaseInfo = new DatabaseInfo {
      database = database,
        tables = new TableInfo[0]
    };
    databases.Add(databaseInfo);
  }

  TableInfo tableInfo = databaseInfo.tables.FirstOrDefault(t => t.table == table);
  if (tableInfo == null) {
    tableInfo = new TableInfo {
      table = table,
        tablesize = tablesize,
        columns = new ColumnInfo[0]
    };
    databaseInfo.tables = databaseInfo.tables.Concat(new TableInfo[] {
      tableInfo
    }).ToArray();
  }

  tableInfo.columns = tableInfo.columns.Concat(new ColumnInfo[] {
    new ColumnInfo {
      column = column, datatype = datatype
    }
  }).ToArray();
}

string jsons = JsonConvert.SerializeObject(databases);

Working fiddle https://dotnetfiddle.net/yAzh71