parse JSON file and insert data into SQL server

Question

I have the following JSON object and trying to create INSERT query. What is the best method create and insert the data into database? I am using JSON.NET to parse the file. I appreciate any suggestions.

JsonTextReader reader = new JsonTextReader(new StringReader(json));
while (reader.Read())
{
    if(reader.Value != null)
    Console.WriteLine("Field: {0}, Value: {1}", reader.TokenType, reader.Value);
}

Here is my JSON looks like.

{
  "persons": {
    "person": {
      "i_date":  "2014-03-20", 
      "i_location": "test", 
      "i_summary": "test test" 
    },
    "people": {
      "people1": {
        "first_name": "first name test1", 
        "last_name": "last name test1"  
      },
      "people2": {
        "first_name": "first name test2", 
        "last_name": "last name test2" 
      }, 
      "people3": {
        "first_name": "first name test3", 
        "last_name": "last name test3" 
      }
    }
  }
}

I am using SQL server. I can use LINQ. The data should go to two seperate tables. I am trying to insert Person data(i_date, i_location, i_summary) into TABLE_A and people1, people2 and people3 in TABLE_B. — nav100
– nav100, Commented Mar 20, 2014 at 20:13
That is some ugly JSON you're having to deal with. Do you have any control over the format of the incoming JSON? If you do I'd suggest changing "people" to an array of objects. Having a series of objects inside "people" each with a different name makes this much more difficult than it needs to be, unless there will always be exactly three objects under "people". — Craig W.
– Craig W., Commented Mar 20, 2014 at 20:45
Yes. I have control over the format. There will be more than 3 (max 11) objects under "people" or less sometimes. Could you please suggest the format? — nav100
– nav100, Commented Mar 20, 2014 at 20:50
I'm still a little confused about the relationships in your data. You have persons (implying multiple) then you have a person (could there be more than one?) and under the person you have people (implying more than one people per person). It's just a very confusing relationship. If you could update your question to explain the relationships in business terms it would probably help. — Craig W.
– Craig W., Commented Mar 20, 2014 at 21:08

Craig W. · Accepted Answer · 2014-03-21 20:10:23Z

2

First I'd restructure the JSON so it made more sense. You said a "person" can have multiple "people", so structure it that way. A "person" has three attributes (i.e. i_date, i_location, and i_summary) and a collection of people.

{
  "person":{
    "i_date":"2014-03-20",
    "i_location":"test",
    "i_summary":"test test",
    "people":[
      {
        "first_name":"first name test1",
        "last_name":"last name test1"
      },
      {
        "first_name":"first name test2",
        "last_name":"last name test2"
      },
      {
        "first_name": "first name test3",
        "last_name":"last name test3"
      }
    ]
  }
}

Now you can declare some .NET classes that represent the structure.

public class Person2
{
    public string first_name { get; set; }
    public string last_name { get; set; }
}

public class Person
{
    public string i_date { get; set; }
    public string i_location { get; set; }
    public string i_summary { get; set; }
    public List<Person2> people { get; set; }
}

public class RootObject
{
    public Person person { get; set; }
}

Finally, use JsonConvert.DeserializeObject to get a set of object instances.

var root = JsonConvert.DeserializeObject<RootObject>( json );

You can now iterate over the "people" attached to the "person" and do stuff with it.

Console.WriteLine( root.person.i_date );
Console.WriteLine( root.person.i_location );
Console.WriteLine( root.person.i_summary );

foreach(var p in root.person.people)
{
    Console.WriteLine( p.first_name );
    Console.WriteLine( p.last_name );
}

At this point you can use either ADO.NET or Entity Framework to transfer the values from the objects into either SQL Parameters (ADO.NET) or EF classes to persist it into the database.

edited Mar 21, 2014 at 20:10

answered Mar 20, 2014 at 22:26

Craig W.

18.2k6 gold badges52 silver badges83 bronze badges

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

4 Comments

nav100 Over a year ago

I followed the same code. I am getting myJsonObject is null. Could you please let me know what could be wrong?

Craig W. Over a year ago

Perhaps the JSON you are feeding into the call is empty. Have you tried debugging the code to make sure the string you're passing in has the correct format?

nav100 Over a year ago

I see there is a JSON string and it is in the above format.

BerggreenDK Over a year ago

You also need to check for keyconstraints / duplicate keys when inserting data into subtabels

Collectives™ on Stack Overflow

parse JSON file and insert data into SQL server

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related