[SOLVED] How to save data from API to database?

Issue

I wrote a program which consumes external API and it works great. I went through SO and other resources but can’t solve my problem. I’m quite new to c#. I created model GetEquipment where I store data returned from API and EquipmentDb which is a SQL table where I want to insert data. I tried to save it this way:

Consume API:

    public async Task<IEnumerable<GetEquipment>> GetDataFromAPI()
    {
         try
         {
             var token = await _accountService.GetToken(
                  _configuration["CredentialsXY:client_id"],
                  _configuration["CredentialsXY:client_secret"]);

             var equipment = await _callAPIService.GetData(token);

             return equipment;

         }
         catch (Exception e)
         {
             Debug.Write(e);
             return Enumerable.Empty<GetEquipment>();
         }
     }

Model – SQL table:

    public partial class EquipmentDb
    {
        public string SerialNo { get; set; }
        public double? Latitude { get; set; }
        public double? Longitude { get; set; }
    }

Model – data returned from API:

        public class GetEquipment
        {
            public Item[] items { get; set; }
        }

        public class Item
        {
            public string serial_number { get; set; }
            public Location location { get; set; }
        }
        public class Location
        {
            public float longitude { get; set; }
            public float latitude { get; set; }
        }

Save data to db:

        public async Task Index()
        {
            var result = await GetDataFromAPI();  // result from API

            using (var ctx = new HUDAContext())
            {
                var equipment = new EquipmentDb(); // SQL table

                    foreach(var item in result)
                    {
                        equipment.SerialNo = item.items.serial_number,  // here I iterate through 'result' where I store data from API but it does not work, 'items' is highlighted
                        equipment.Latitude = item.items.location.latitude,
                        equipment.Latitude = item.items.location.longitude
                    };

                ctx.EquipmentDbs.Add(equipment);
                await ctx.SaveChangesAsync();
            }                                            
        }   

Inserting data is not working, I try to assign data from result to each field in my SQL table but it doesn’t work out.

Solution

It’s not clear from the question exactly how the data is formed. The important thing to understand is you have two collection wrappers: the IEnumerable in the API result, which includes the Item[] inside it. You have to look inside both of those.

However, I suspect you only ever expect one result for one or the other (again: it’s not clear which from the question). With that in mind, you might want either this:

public async Task Index()
{
    var result = await GetEquipment();  // result from API
    var transformed = result.Select(e =>
          new EquipmentDB {
              SerialNo = e.items[0].serial_number,  // here I iterate through 'result' where I store data from API but it does not work, 'items' is highlighted
              Latitude = e.items[0].location.latitude,
              Latitude = e.items[0].location.longitude
          }
    );

    using var ctx = new HUDAContext();
    foreach(var item in transformed)
    {
        ctx.EquipmentDbs.Add(item);
        await ctx.SaveChangesAsync();
    }                                            
}

Or this:

public async Task Index()
{
    var result = await GetEquipment();  // result from API
    var transformed = result.First().Select(e =>
          new EquipmentDB {
              SerialNo = e.serial_number,  // here I iterate through 'result' where I store data from API but it does not work, 'items' is highlighted
              Latitude = e.location.latitude,
              Latitude = e.location.longitude
          }
    );

    using var ctx = new HUDAContext();
    foreach(var item in transformed)
    {
        ctx.EquipmentDbs.Add(item);
        await ctx.SaveChangesAsync();
    }                                            
}

And again, you might really need to do both (look at SelectMany() for this), and visit at each element inside the enumerable, and for each of those elements visit each item inside the array.

Answered By – Joel Coehoorn

Answer Checked By – Clifford M. (BugsFixing Volunteer)

Leave a Reply

Your email address will not be published. Required fields are marked *