API call JSON File and create Database

Question

I am wondering how to create a database based off a JSON file. The file I'm working with is:

What I am using is Visual Studio 2015 and MSSQL 2012. A few of the included options I'm using are Entity Framework with MVC and WEB API template. I want to use LINQ, but am lost on how to accomplish that. What I do know is how to make a SQL connection and create a database and tables, and store info. using SQLcommands function.

Questions:

I want to know how to make an API call using jquery (from data.gov) to get the JSON file and create a database schema with the necessary tables from that JSON file provided above. Then use LINQ to create database. OR

If I make the JSON file as a resource to my project and create the database schema and tables, how would I parse that data in correctly using LINQ?

Is there a way to see what kind of tables and columns the JSON file has visually, like a cool software or something?

From there, I will be able to re-populate the same JSON file into a better database design in 3rd normal form (if it's not in that already).

Conclusion:

As mentioned before, I'm using Entity Framework with MVC capabilities that I will custom code specifically for that data with basic CRUD functions. If I'm unclear or this question is not relevant please comment.

Is that a standardized JSON format? I've never seen anything like it before. — Jonathan Allen
– Jonathan Allen, Commented Apr 19, 2016 at 0:15

Cicero · Accepted Answer · 2016-04-19 00:48:03Z

1

A lot of questions.
So here's some answers:

Making an API call:
(read about data.gov's API)
I'm not familiar with JQuery (their docs), but i do know that you can do it with the WebClient or HttpClient in C#
Example:

var Client = new HttpClient();
string request;
try
{
    using (Client)
    {
        request = await Client.GetStringAsync(new Uri(url));
    }
}
catch (Exception)
{
    request = null;
}

return request;

The url would be the API call, something like:
http://catalog.data.gov/api/3/?query=blahblah&API_KEY
or
https://data.cityofnewyork.us/api/views/5b3a-rs48/rows.json?accessType=DOWNLOAD
The returned request would be your JSON string.

Parsing JSON:
Regarding parsing from JSON to a C# object, you can use a Newtonsoft's Json.NET, you can get it in Nuget.
This however requires you to have a class that contains all the properties that the JSON string contains.
Example:

var myJsonObject = JsonConvert.DeserializeObject<MyCustomJsonClass>(json);

Creating Class based on provided JSON:
You can do this in 3 ways or more..:
1. Use Json Utils or Json2Csharp or copy the JSON string and go into visual studio and use Edit -> Paste Special -> Paste JSON as Classes
2. Write it yourself
3. Use Json Utils source code in your project.

Creating the tables (and using LINQ) in Entity Framework can be done by using your new class based on your JSON string (Guide)

This is very unclear:
Is there a way to see what kind of tables and columns the JSON file has visually, like a cool software or something?
If you mean after you've created a table in a SQL database and put in the values, yes. You should be able to use Visual Studio.

edited Apr 19, 2016 at 0:48

answered Apr 19, 2016 at 0:36

Cicero

2,9723 gold badges25 silver badges33 bronze badges

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

5 Comments

Royce Over a year ago

Thank you so much Cicero. I will try what you have suggested and update you on my progress.

Royce Over a year ago

I have been working with your post and everything works great so far, thanks again. I haven't found a problem with your answer yet, so I marked it as the answer.

Cicero Over a year ago

@Roys Thanks and you're welcome. I'm glad it worked. Good luck with your project!

Royce Over a year ago

Out of curiosity though in an MVC (Entity Framework) design where is the best place to put the code for the API call? I just put it in an empty created controller.

Cicero Over a year ago

@Roys Use a controller to do the API calls and use your EF database context inside the controller to save the data to the database. After you've converted it to the proper object of course. That should be sufficient. But it's really a matter of what you think works best.

Collectives™ on Stack Overflow

API call JSON File and create Database

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related