how do you store data from an external api into a ms sql database?

Hello this is my first answer and I’m learning english I will try to do better.

Do you have two tables? A Master-Detail?

I recommend you organize your code and separate responsibilities first you need to have and Dto Object to represent your JSON object and an entity to insert the data into a Database then separate the methods but I will share you the solution with your current entity.

First you need to have a DbContext with your entities and your connectionstring, an example of DbContext with EntityFrameworkCore:

public class MyDbContext:DbContext
    {
        private readonly string connectionString;

        public MyDbContext(string connectionString)
        {
            this.connectionString = connectionString;
            _migrateDatabase = true;
        }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            base.OnConfiguring(optionsBuilder);
            optionsBuilder.UseMySql(connectionString);
        }

        public DbSet<JsonResponse> JsonResponses { get; set; }
        public DbSet<Devices> Devices { get; set; }
    }

If you have a master detail you need to create a navigation property with a foreign key in this case I created this property in the Device entity.

public partial class JsonResponse
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public int Id { get; set; }
    [JsonProperty("prtg-version")]
    public string PrtgVersion { get; set; }
    [JsonProperty("treesize")]
    public int TreeSize { get; set; }

    [JsonProperty("devices")]
    public List<Devices> devices { get; set; }
}

public partial class Devices
{
    [Key]
    [JsonProperty("objid")]
    public int objid { get; set; }
    [JsonProperty("probe")]
    public string probe { get; set; }
    [JsonProperty("device")]
    public string device { get; set; }
    [JsonProperty("host")]
    public string host { get; set; }

    public int JsonResponseId { get; set; }
    
    [ForeignKey("JsonResponseId")]
    public JsonResponse JsonResponse { get; set; }
}

To save the data you need to add the principal entity and the detail will saved automatically:

public class CMDBController : Controller
{
    private MyDbContext db = new MyDbContext("mycnn");

    public async Task<ActionResult> Test()
    {
        JsonResponse data = GetJsonData();
        db.JsonResponses.Add(data);
        db.SaveChanges();
        return View(data);
    }

    JsonResponse GetJsonData(){
         using (var httpClient = new HttpClient())
        {
          var response = await httpClient.GetAsync(
                    "/api/table.json?content=devices&output=json&columns=objid,probe,group,device,host");
          string apiResponse = await response.Content.ReadAsStringAsync();
          return JsonConvert.DeserializeObject<JsonResponse>(apiResponse);
        }
    }
}

If you don’t have a master detail and you need to save a range of data you must use AddRange, and save all the data in one action:

public async Task<ActionResult> Test()
    {
        JsonResponse data = GetJsonData();
        db.Devices.AddRange(data.Devices);
        db.SaveChanges();
        return View(data);
    }

CLICK HERE to find out more related problems solutions.

Leave a Comment

Your email address will not be published.

Scroll to Top