CODEWITHSUNDEEP
c#sql-serverjsonentity-frameworklinq
Royce
Royce

Reputation: 189

API call JSON File and create Database

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

JSON file

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.

Upvotes: 2

Views: 2948

Answers (1)

Cicero
Cicero

Reputation: 2962

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.

Upvotes: 1

Related Questions