How to convert a JObject to System.Data.DataSet? Or how to deserialize json to dataset?

Kinetic ERP
, ,
1

Hi all I’ve created a small Function that calls a REST API we have created linking to our solidworks PDM.

Basically, if I call https://ourserver/locate/partnum, it returns a json response containing solidworks path information, eg for partnum 100007:

{"ModelFullPath": "100000/100007.SLDPRT", 
"DrawingFullPath": "100000/100007.SLDDRW",
...
}

My function (which is hacked together from Does anyone have any C# documentation of working with XML/JSON and calling a 3rd party API? - #10 by Aaron_Moreng )

I’m on very uncertain ground trying to deserialize the json to produce a dataset i can return.

Code here (i’m not using the parameters argument yet, and for service I’m actually supplying locate/{Part.PartNum} - I know this part of the implementation looks clunky but its a first attempt):

{
  string url = $"https://{Server}/{Service}{Parameters}"; 
    
  var request = (HttpWebRequest)WebRequest.Create(url);  
  request.Method = "GET";  

                   
  //listen to response
  try
  {    
    var response = (HttpWebResponse)request.GetResponse();

    switch(response.StatusCode)
    {       
        case HttpStatusCode.OK:           
          using (var streamReader = new StreamReader(response.GetResponseStream()))
          {
              var result = streamReader.ReadToEnd();  
              dynamic content = JsonConvert.DeserializeObject(result);
              ReturnObj = content;  // FAILS HERE!!!!


              this.PublishInfoMessage(" Response: " + result, Ice.Common.BusinessObjectMessageType.Information, Ice.Bpm.InfoMessageDisplayMode.Individual, "", "");
              
          }      
          break;
    }
  }
  catch(Exception ex)
  {
    this.PublishInfoMessage( " Bad Response: " + ex.Message + ex.InnerException + $" {url}", Ice.Common.BusinessObjectMessageType.Information, Ice.Bpm.InfoMessageDisplayMode.Individual, "", "");

  } 
    
}

So my problem is with ReturnObj = content , lhs is a System.Data.DataSet and rhs is Newtonsoft.Json.Linq.JObject.

I tried a hopeful typecast but looks like there’s more to it than that!

2

Please try

switch (response.StatusCode)
    {
        case HttpStatusCode.OK:
            using (var streamReader = new StreamReader(response.GetResponseStream()))
            {
                try
                {
                    var result = streamReader.ReadToEnd();
                    var jObject = JObject.Parse(result);
                    var dataTable = JsonConvert.DeserializeObject<DataTable>(jObject.ToString());

                    // Now you have the JSON data in a DataTable
                    // If you need a DataSet, create one and add the DataTable to it
                    var dataSet = new DataSet();
                    dataSet.Tables.Add(dataTable);

                    // Now you can use the dataSet as needed

                    this.PublishInfoMessage(" Response: " + result, Ice.Common.BusinessObjectMessageType.Information, Ice.Bpm.InfoMessageDisplayMode.Individual, "", "");
                }
                catch (JsonException ex)
                {
                    // Handle JSON parsing errors
                    this.PublishInfoMessage("JSON parsing error: " + ex.Message, Ice.Common.BusinessObjectMessageType.Information, Ice.Bpm.InfoMessageDisplayMode.Individual, "", "");
                }
                catch (Exception ex)
                {
                    // Handle other exceptions
                    this.PublishInfoMessage("Exception: " + ex.Message, Ice.Common.BusinessObjectMessageType.Information, Ice.Bpm.InfoMessageDisplayMode.Individual, "", "");
                }
            }
            break;
        // ... (Your other cases)
    }
3

Thanks! Will try shortly.

4

If that is the full format of the json you get back, here are two ways to do that:
There are more :slight_smile:

Way 1:
That json (as you showed it) is a dictionary. Dictionary<string, object>
A table is pretty much just a list of exact matching dictionaries: List<Dictionary<string, object>>
so…

string json ="{\"ModelFullPath\": \"100000/100007.SLDPRT\", \"DrawingFullPath\": \"100000/100007.SLDDRW\"}";

DataSet dataSet = new DataSet();

Dictionary<string, object> dictionary = JsonConvert.DeserializeObject<Dictionary<string, object>>(json);

List<Dictionary<string, object>> listOfDictionary = new List<Dictionary<string, object>>();

listOfDictionary.Add(dictionary);

DataTable dataTable = JsonConvert.DeserializeObject<DataTable>(JsonConvert.SerializeObject(listOfDictionary));
dataTable.TableName = "ILikeThisTableName";

dataSet.Tables.Add(dataTable);

Console.WriteLine(JsonConvert.SerializeObject(dataSet, Formatting.Indented));

.

Or you can totally cheat and make a template of sorts and do this:
Way2:

string json ="{\"ModelFullPath\": \"100000/100007.SLDPRT\", \"DrawingFullPath\": \"100000/100007.SLDDRW\"}";

string dataSetTemplate = $"{{\"IlikeThisTableName\": [{json}]}}";

DataSet dataSet = JsonConvert.DeserializeObject<DataSet>(dataSetTemplate);

Console.WriteLine(JsonConvert.SerializeObject(dataSet, Formatting.Indented));

.

Output for both is exactly the same:

{
  "IlikeThisTableName": [
    {
      "ModelFullPath": "100000/100007.SLDPRT",
      "DrawingFullPath": "100000/100007.SLDDRW"
    }
  ]
}
1 Like
5

Yours won’t work because it’s not an array.(at least the way he showed it) However if you modify it like this to make it an array, it will:

var dataTable = JsonConvert.DeserializeObject<DataTable>($"[{jObject.ToString()}]" );

string json ="{\"ModelFullPath\": \"100000/100007.SLDPRT\", \"DrawingFullPath\": \"100000/100007.SLDDRW\"}";

var jObject = JObject.Parse(json);
var dataTable = JsonConvert.DeserializeObject<DataTable>($"[{jObject.ToString()}]" );
dataTable.TableName = "IlikeThisTableName";

var dataSet = new DataSet();
dataSet.Tables.Add(dataTable);

Console.WriteLine(JsonConvert.SerializeObject(dataSet, Formatting.Indented));
1 Like
6

Yep did the trick, big thanks @aarong and @klincecum for the assistance!

Final code for this block here; it raises with me as many questions as it answers (templates?
{{ syntax? ) but happy to go and google these in my spare time… I know this is an Epicor forum not a C# for newbies tutorial, but really needed this nugget working to make my app studio magic happen. Onwards!

        case HttpStatusCode.OK:
            using (var streamReader = new StreamReader(response.GetResponseStream()))
            {
                try
                {
                    var result = streamReader.ReadToEnd();
                    //var jObject = JObject.Parse(result);
                    string dataSetTemplate = $"{{\"MyTable\": [{result}]}}";

                    ReturnObj = JsonConvert.DeserializeObject<DataSet>(dataSetTemplate);

                    // this.PublishInfoMessage(" Response: " + result, Ice.Common.BusinessObjectMessageType.Information, Ice.Bpm.InfoMessageDisplayMode.Individual, "", "");
                }
                catch (JsonException ex)
                {
                    // Handle JSON parsing errors
                    this.PublishInfoMessage("JSON parsing error: " + ex.Message, Ice.Common.BusinessObjectMessageType.Information, Ice.Bpm.InfoMessageDisplayMode.Individual, "", "");
                }
                catch (Exception ex)
                {
                    // Handle other exceptions
                    this.PublishInfoMessage("Exception: " + ex.Message, Ice.Common.BusinessObjectMessageType.Information, Ice.Bpm.InfoMessageDisplayMode.Individual, "", "");
                }
            }
            break;
7

Not an official term Just a description.
I took an empty dataset, with an empty table and serialized it so I knew exactly what it looked like.

The {{ is how you escape { when it’s inside an interpreted string $"", because you can’t just use { in there because { is how you let it know you are going to put another string in there.

ie:

string a = "Yo";

string b = $"{a} Adrian, I Did It!"; // = Yo Adrian, I Did It!

string c = $"{{a}} Adrian, I Did It!"; // = {Yo} Adrian, I Did It!
1 Like
8

Yo! I see!

1 Like
9

6 posts were split to a new topic: Json from API To DataSet