Convert 2 DataTable to Netsed JSon Object

Question

I have 2 Datatables which I get from database and fill them like this

 DataTable dt1 = new DataTable();
 DataTable dt2 = new DataTable();

   MySqlDataReader sdr;
   using (MySqlConnection Conn = new MySqlConnection(dbConn))
   {
       Conn.Open();
       using (MySqlCommand myCmd = new MySqlCommand("Select Query 1", Conn))
       {
          // Filtering 
           sdr = myCmd.ExecuteReader();
           dt1.Load(sdr);
       }
       using (MySqlCommand myCmd = new MySqlCommand("Select Query 2", Conn))
       {
          // Filtering 
           sdr = myCmd.ExecuteReader();
           dt2.Load(sdr);
       }
       sdr.Close();
       Conn.Close();
   }

dt1 returns 1 row of data while dt2 returns nrows of data.

Sample Data For td1 will be like this

 Column1        Column2         columnN
 ---------------------------------------------
 ColumnValue    ColumnValue     ColumnValue

Sample Data For td2 will be like this

 Column1        Column2         columnN
 ---------------------------------------------
 ColumnValue    ColumnValue     ColumnValue
 ColumnValue    ColumnValue     ColumnValue
 ColumnValue    ColumnValue     ColumnValue
 ...........    ............    ............

Now my Goal is to convert these 2 datatables into a nested json object like this

 Data = {
   "dt1":{
         "Column1":"Value",
         "Column2":"Value",
         "ColumnN":"Value"
       },
    "dt2":[
       {
         "column1":"Value",
         "column2":"Value",
         "columnN":"Value"
       },
       {
         "Column1":"Value",
         "Column2":"Value",
         "ColumnN":"Value"
       },
           ...... and so on
      ]
}

just to be clear, These Data will be use in Dashboard hence its coming from differen tables via Subqueries example column in dt1 will be like Total Users, Active Users, etc. and example column in dt2 will be like Activities by User, etc.

I have tried String builder( which I think is the solution) and JavaScript serilizer like this Article

Answer 1

it is absolutely unclear from your question what type you want, but if you want c# classes

Data data = new Data { dt1 = JArray.FromObject(dt1)[0].ToObject<L<Dt1>(), 
                       dt2 = JArray.FromObject(dt2).ToObject<List<Dt2>>() };

public class Data
{
    public Dt1 dt1 { get; set; }
    public List<Dt2> dt2 { get; set; }
}

public class Dt1
{
    public string Column1 { get; set; }
    public string Column2 { get; set; }
    public string ColumnN { get; set; }
}

public class Dt2
{
    public string column1 { get; set; }
    public string column2 { get; set; }
    public string columnN { get; set; }

}

or if you need just a json string, you don't need any classes

string json = JsonConvert.SerializeObject( new { dt1 = JArray.FromObject(dt1)[0], dt2 = dt2 });

or JObject

JObject jObj = JObject.FromObject(new { dt1 = JArray.FromObject(dt1)[0], dt2 = dt2 });