Deserialize part of json to datatable in C# using JSON.NET

Question

This is probably very simple and a basic lack of understanding on my part. I am calling an API which returns a JSON as below:

{
    disclaimer: "https://openexchangerates.org/terms/",
    license: "https://openexchangerates.org/license/",
    timestamp: 1449877801,
    base: "USD",
    rates: {
        AED: 3.672538,
        AFN: 66.809999,
        ALL: 125.716501,
        AMD: 484.902502,
        ANG: 1.788575,
        AOA: 135.295998,
        ARS: 9.750101,
        AUD: 1.390866,
    }
}

All I want to do is return the result into a datatable which has the columns Timestamp, base, currency and rate where timestamp and base are from those fields and the currency and rate are from the nested rates section

I've spent the morning reading up and trying many different ways and to be honest I am no closer to figuring out what I need. I can't even read the base currency because obviously stuff.base isn't allowed

var response = httpClient.GetStringAsync(new Uri(url)).Result;

JObject jsonResponse = JObject.Parse(response);
CurrentContext.Message.Display(response);

dynamic stuff = JsonConvert.DeserializeObject(response);

var timestamp = stuff.timestamp;
CurrentContext.Message.Display("TIMESTAMP IS :"+ timestamp);

var basecur = stuff.base;
CurrentContext.Message.Display("TIMESTAMP IS :" + basecur);


DataTable outputData = new DataTable();
outputData.Columns.Add("timestamp", typeof(System.String));
outputData.Columns.Add("basecurrency", typeof(System.String));
outputData.Columns.Add("currency", typeof(System.String));
outputData.Columns.Add("rate", typeof(System.Int16));

Thank you for the answer from David, my code now works:

var response = httpClient.GetStringAsync(new Uri(url)).Result;
            var result = JsonConvert.DeserializeObject<Deserialize>(response);

            DataTable outputData = new DataTable();
            outputData.Columns.Add("timestamp", typeof(System.String));
            outputData.Columns.Add("basecurrency", typeof(System.String));
            outputData.Columns.Add("currency", typeof(System.String));
            outputData.Columns.Add("rate", typeof(System.Decimal));

            foreach (KeyValuePair<string, double> entry in result.Rates)
            {

                outputData.Rows.Add(result.Timestamp,result.Base,entry.Key,entry.Value);
            }

            return outputData;

The code is correct but incomplete. You need to add the values to the datatable now. Follow this solution https://stackoverflow.com/questions/10394680/how-to-create-dynamic-datatable — Balaji Birajdar, Aug 08 '18 at 10:03
If you are already have a JObject, you could use `JObject.Value("yourprop")` instead of dynamics. — derpirscher, Aug 08 '18 at 10:03

score 3 · Accepted Answer · answered Aug 08 '18 at 10:04

3

You should deserialise into a proper C# class, for example:

public class Foo
{
    public string Disclaimer { get; set; }
    public string License { get; set; }
    public int Timestamp { get; set; }
    public string Base { get; set; }
    public Dictionary<string, double> Rates { get; set; }
}

Now you can do this:

var result = JsonConvert.DeserializeObject<Foo>(response);

Now it will be trivial to use the data in there:

var baseCurrency = result.Base;

answered Aug 08 '18 at 10:04

DavidG

113,891
12
217
223

Thank you David, I will give this a go. I think I missed the dictionary element in all my reading this morning – Adam Davies Aug 08 '18 at 10:21
quick follow up question, how do you do this if the name is dynamic? so for example a result could be: {"USD_GBP":{"val":0.777803}} or {"USD_PHP":{"val":9.123456}} however the two currency codes would be known – Adam Davies Aug 08 '18 at 13:22
1

If you have a follow up question, you need to write a new questions! – DavidG Aug 08 '18 at 13:31

Deserialize part of json to datatable in C# using JSON.NET

1 Answers1

Linked