CODEWITHSUNDEEP
c#jsondataset
Alan
Alan

Reputation: 2086

Join JSON and Dataset

I have a dataset back from a Mysql query that I need to restructure with some additional information prior to hitting an HTTP endpoint. The desired output looks like this:

{
  "data": [
    {
      "reference_id": 19,
      "category": 2,
      "title": "Unconfirmed",
      "code": "Unconfrm"
    },
    {
      "reference_id": 20,
      "category": 2,
      "title": "Left Msg",
      "code": "LeftMsg"
    }
  ],
  "meta": {
    "firstRecord": 0, //for pagination
    "lastRecord": 2,  //for pagination
    "page": 1,        //for pagination
    "sync": "null"    //internal use
  }
}

I am getting back the data element from the following:

public class DataConnection
{
    public static DataSet ExecuteQuery(string strQuery)
    {
        DataSet ds = new DataSet();

        using (MySqlConnection mySqlConnection = new MySqlConnection("server=localhost;uid=root;database=sampledb"))
        {
            mySqlConnection.Open();

            using (MySqlDataAdapter dataAdapter = new MySqlDataAdapter(strQuery, mySqlConnection))
            {
                dataAdapter.TableMappings.Add("Table", "data");

                dataAdapter.Fill(ds);
            }

            mySqlConnection.Close();
        }
        return ds;
    }
}

I need to populate the meta part but can't figure out how. I converted the dataset to a JObject and JArray and was able to count the results, and while I haven't implemented pagination yet, I will soon. Either way, the trouble is how do I join the DataSet from Mysql with the meta object? The .Add() methods don't seem to work at the root level, only if I were to try adding it under the data element.

I was trying along this line:

DataSet definitionsReturned = DataConnection.ExecuteQuery("SELECT DefNum AS reference_id, Category AS category, ItemName AS title, ItemValue AS code from definition WHERE category IN(2,13) ORDER BY category");
        string meta = @"[{
            'firstRecord': 0,
            'lastRecord': 27,
            'page': 1,
            'sync': 'null'
        }]";
        DataSet myDataSet = JsonConvert.DeserializeObject<DataSet>(meta);
        definitionsReturned.Merge(myDataSet);
        string json = JsonConvert.SerializeObject(definitionsReturned);

        //also tried this
        // JArray jsonArray = (JArray) jsonParsed[0];  //also tried this
        //jsonArray.Add(myDataSet);

Upvotes: 0

Views: 57

Answers (1)

AdricoM
AdricoM

Reputation: 579

You could create a class containing both your dataset and your meta :

public class MyReturnObject {
    public List<MyDataObject> Data { get; set; }
    public Metadata Meta { get; set; }
}

public class Metadata {
    public int FirstRecord { get; set; }
    public int LastRecord { get; set; }
    public int Page { get; set; }
    public WhateverType Sync { get; set; }
}

Please note that you can also get directly a list of typed results when ExecuteQuery as described here.

Like that you just have to do: 

List<MyDataObject> definitionsReturned = DataConnection.ExecuteQuery<MyDataObject>("SELECT DefNum AS reference_id, Category AS category, ItemName AS title, ItemValue AS code from definition WHERE category IN(2,13) ORDER BY category").ToList();

MyReturnObject res = new MyReturnObject {
    Data = definitionsReturned,
    Meta = new Metadata {
        FirstRecord = 0,
        LastRecord = 27,
        Page = 1,
        Sync = null
    }
};

string json = JsonConvert.SerializeObject(res);

Upvotes: 1

Related Questions