C# - Store JSON array string into SQL table

Question

I am new to C#

Following is the JSON string I am getting from the web API.

I am trying to store the JSON string into a class and then store the JSON string into an SQL table.

But the C# code is failing to deserialize JSON into class. And the message box returns the null exception error.

JSON

{
   "Count":3,
   "data":[
      {
         "Cost1":{
            "amount":111,
            "currencyCode":"ABC"
         },
         "Cost2":{
            "amount":22.2,
            "currencyCode":"XYZ"
         },
         "Id":"007"
      },
      {
         "Cost1":{
            "amount":555,
            "currencyCode":"ABC"
         },
         "Cost2":{
            "amount":444,
            "currencyCode":"XYZ"
         },
         "Id":"008"
      },
      {
         "Cost1":{
            "amount":666,
            "currencyCode":"ABC"
         },
         "Cost2":{
            "amount":8882,
            "currencyCode":"XYZ"
         },
         "Id":"009"
      }
   ],
   "pending":[
      
   ],
   "@up":"Test Data"
}

C# Code

public class ParceJSN {
  public int Count {
    get;
    set;
  }
  public string data {
    get;
    set;
  }
  public string pending {
    get;
    set;
  }
  public string up {
    get;
    set;
  }
}

public void Main() {

  Task < string > task = MakeRequest(db_token); //Returns the JSON string

  var fr = task.Result;

  ParceJSN rst = JsonConvert.DeserializeObject < ParceJSN > (fr.ToString());

  MessageBox.Show(rst.TotalCount.ToString());

  Dts.TaskResult = (int) ScriptResults.Success;
}

Data and pending are arrays of objects... not strings

Jawad
– Jawad

2020-11-19 18:06:41 +00:00
Commented Nov 19, 2020 at 18:06 — Jawad
– Jawad, Commented Nov 19, 2020 at 18:06

ShaneDemskie · Accepted Answer · 2020-11-19 18:40:22Z

1

Your C# model is incorrect. Here's a really handy tool that I use when I need to generate C# classes based on some JSON - json2csharp.com

The correct class is:

public class Cost1    {
    public int amount { get; set; } 
    public string currencyCode { get; set; } 
}

public class Cost2    {
    public double amount { get; set; } 
    public string currencyCode { get; set; } 
}

public class Datum    {
    public Cost1 Cost1 { get; set; } 
    public Cost2 Cost2 { get; set; } 
    public string Id { get; set; } 
}

public class Root    {
    public int Count { get; set; } 
    public List<Datum> data { get; set; } 
    public List<object> pending { get; set; } 
    [JsonProperty("@up")]
    public string Up { get; set; } 
}

Now that you have the correct model, you can now do the following to deserialize your JSON string into a Root object which is defined above:

Root myDeserializedClass = JsonConvert.DeserializeObject<Root>(YOUR_JSON_STRING);

answered Nov 19, 2020 at 18:40

ShaneDemskie

3982 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

TechMaxy Over a year ago

Thanks, I am trying to extract the Each object string from the data array. Here I am not sure of keys inside array as it is large. Then each single object string, I would like to insert into SQL.

ShaneDemskie Over a year ago

You're very welcome. Could you please share your SQL schema? Without knowing what you're tables look like, I can't give you a query that will help. Also, are you only worried about storing the data in the data collection? What about the pending collection?

TechMaxy Over a year ago

ID int IDENTITY(1,1), TotalCount int, JSONString nvarchar(max), NextValue nvarchar(4000), Created datetime , at this point, I only need to insert each JSON object string to the staging table. ` { "Cost1":{ "amount":555, "currencyCode":"ABC" }, "Cost2":{ "amount":444, "currencyCode":"XYZ" }, "Id":"008" },` There are Stored procedures to distribute data into different tables from the staging table.

Collectives™ on Stack Overflow

C# - Store JSON array string into SQL table

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related