Question 87419 in ASP.NET Question posted in
The official documentation can be found here.

How to save data from API to database? – Asp.net

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.

Tags:

3

Answers


  1. 0

    Assuming you’re using EF, and EquipmentDB is your context class you’d want to create a new equipmentDB object within the foreach e.g:

    foreach(var item in result)
{
   ctx.EquipmentDbs.Add(new EquipmentDb
   {
      SerialNo = item.items.serial_number, 
      Latitude = item.items.location.latitude,
      Latitude = item.items.location.longitude
   });
};

    otherwise your just overriding the same object and will only save the last result.

    Then Call the save after the loop is done with your new populated list :)!

    -Also assuming you meant GetDataFromAPI instead of GetEquipment()

    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
  3. 0

    Try saving it to a list first, and then call AddRange on the context

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

            using (HUDAContext ctx = new HUDAContext())
            {
                
                List<EquipmentDb> equipmentlist = new List<EquipmentDb>()

                    foreach(var item in result)
                    {
                        equipmentlist.add(new EquipmentDb{
                            SerialNo = item.items.serial_number, 
                            Latitude = item.items.location.latitude, 
                            Longitude = item.items.location.longitude
                        })
                    };

                ctx.EquipmentDbs.AddRange(equipmentlist);
                await ctx.SaveChangesAsync();
            }                                            
        }
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search