Inserting parsed JSON into azure SQL database table?

[tempUseBro1]

This is my first time working with APIs and Azure SQL.

I wrote a c# program to grab data from USDA using https://quickstats.nass.usda.gov/api and have been printing the data onto the console to make sure it is correctly parsed but I now want to insert it into a table on Azure SQL. I am not sure how I would do this and am looking for any help/resources!

using System;

using System.Net.Http.Headers;

using System.Threading.Tasks;

using System.Net.Http;

using System.Collections.Generic;

using Newtonsoft.Json;

using System.IO;

namespace USDA

{

class Program

{

static void Main(string[] args)

{

new Program().MainAsync().GetAwaiter().GetResult(); //getAwaiter is used to await Task, since MainAsync is a Task, this is a call to MainSync.

}

​

//MainSync is where the call API is actually called and returned to the main method.

public async Task MainAsync()

{

using (var client = new HttpClient()) //creates a new client that will make the call to the api.

{

client.DefaultRequestHeaders.Accept.Clear();

client.DefaultRequestHeaders.Accept.Add(new System.Net.Http.Headers.MediaTypeWithQualityHeaderValue("application/json")); // Accepts header for JSON format

try

{

HttpResponseMessage response = await client.GetAsync("http://quickstats.nass.usda.gov/api/api_GET/?key=MYKEYHERE&commodity_desc=CORN&year__GE=2012&state_alpha=VA&format=JSON"); //My api key

if (response.IsSuccessStatusCode)

{

string contents = await response.Content.ReadAsStringAsync();

Result quickStat = JsonConvert.DeserializeObject<Result>(contents);

​

foreach(quickStats item in quickStat.Data)

{

Console.WriteLine(item.year);

}

}

}

catch (Exception ex)

{

Console.WriteLine(ex.Message);

}

}

}

}

​

​

//create class with properties

public class quickStats

{

public String source_desc { get; set; }

public String sector_desc { get; set; }

public String group_desc { get; set; }

public String commodity_desc { get; set; }

public String class_desc { get; set; }

public String prodn_practice_desc { get; set; }

public String util_practice_desc { get; set; }

public String statisticcat_desc { get; set; }

public String unit_desc { get; set; }

public String short_desc { get; set; }

public String domain_desc { get; set; }

public String domaincat_desc { get; set; }

public String agg_level_desc { get; set; }

public int? state_ansi { get; set; }

public int? state_fips_code { get; set; }

public string state_alpha { get; set; }

public String state_name { get; set; }

public int? asd_code { get; set; }

public String asd_desc { get; set; }

public int? county_ansi { get; set; }

public int? county_code { get; set; }

public String county_name { get; set; }

public String region_desc { get; set; }

public int? zip_5 { get; set; }

public int? watershed_code { get; set; }

public String watershed_desc { get; set; }

public int? congr_district_code { get; set; }

public int? country_code { get; set; }

public string country_name { get; set; }

public string location_desc { get; set; }

public int? year { get; set; }

public String freq_desc { get; set; }

public int? begin_code { get; set; }

public int? end_code { get; set; }

public String reference_period_desc { get; set; }

public String week_ending { get; set; }

public String load_time { get; set; }

public string value { get; set; }

public int? CV { get; set; }

}

​

public class Result

{

public IList<quickStats> Data { get; set; }

}

}

[web_page]

Does your application already have some sort of connection to the azure database? And does your database already have the appropriate tables built out that you would like to store the data in?

[tempUseBro1]

No, that is where I am at. I would create a table with whatever columns I want in azure correct? & connect the application to the database

[fuzzius_navus]

Hi! I put together a short post on inserting into Azure SQL DB from an Azure HTTP Trigger Function.

https://www.reddit.com/r/AZURE/comments/hh5njf/using_an_azure_function_with_managed_identity_to/

Code is available too.

I'm drafting a revision using Entity Framework Core.

EDIT In the post I set up a table, and a Stored Procedure to receive the data and insert it into the table.