Best way to import XML into a database C# .NET Core

I’m working on a project that requires that I take data from an XML API that I don’t control and insert that data into a database (I control) for consumption by a different application (I control but can’t modify code too it)

We already managed to get the full database layout from the XML API via the providers special endpoint but all other queries (to get data) are via XML.

Currently what I have is the following:

Book.cs – This represents one of the database tables

using System;
using System.ComponentModel.DataAnnotations;

namespace Example.Models
{
    public partial class Book
    {
        [Key]
        public int Recordno { get; set; }
        public decimal? Amount { get; set; }
        public string Title { get; set; }
        public string Description { get; set; }
        public DateTime? Whenmodified { get; set; }
        public DateTime? Whencreated { get; set; }
        public int? Createdby { get; set; }
        public int? Modifiedby { get; set; }
    }
}

API Return XML – This represents the XML returned by the API (an SDK already converts it to an XMLDocument Type)

<BOOKS>
   <BOOK>
      <RECORDNO>1</RECORDNO>
      <AMOUNT>24.12</AMOUNT>
      <TITLE>This is a title</TITLE>
      <DESCRIPTION>This is a description</DESCRIPTION>
      <WHENMODIFIED></WHENMODIFIED>
      <WHENCREATED>13/03/20 15:23:12</WHENCREATED>
      <CREATEDBY>3</CREATEDBY>
      <MODIFIEDBY></MODIFIEDBY>
   </BOOK>
   <BOOK>
      <RECORDNO>1</RECORDNO>
      <AMOUNT>24.12</AMOUNT>
      <TITLE>This is another title</TITLE>
      <DESCRIPTION>This is another description</DESCRIPTION>
      <WHENMODIFIED>21/03/20 12:45:23</WHENMODIFIED>
      <WHENCREATED>15/03/20 15:23:12</WHENCREATED>
      <CREATEDBY>6</CREATEDBY>
      <MODIFIEDBY>2</MODIFIEDBY>
   </BOOK>
</BOOKS>

Currently the way we are doing this is via switch statements, but I’m hoping that there is a better method. Partial BookController.cs

var books = new List<Book>();

// response.Data return is a List<XElement>
foreach (var result in response.Data)
{
    var xElements = result.Elements();
    var book = new Book();
    foreach (var element in xElements)
    {
        switch (element.Name.ToString())
        {
            case "RECORDNO":
                book.Recordno = int.Parse(element.Value);
                break;
            case "AMOUNT":
                if (element.Value != string.Empty)
                {
                    book.Amount = int.Parse(element.Value);
                }
                break;
             // etc.
        }
    }
    books.Add(book);
}

I have a feeling that there is a much easier method of doing this that I’m just unaware of, my concern is that I have about fifty tables and hundreds of elements to do making this method inefficient time wise.

Answer

Use xml serialization

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml;
using System.Xml.Serialization;


namespace ConsoleApplication8
{
    class Program
    {
        const string FILENAME = @"c:temptest.xml";
        static void Main(string[] args)
        {
            XmlReader reader = XmlReader.Create(FILENAME);
            XmlSerializer serializer = new XmlSerializer(typeof(Books));
            Books books = (Books)serializer.Deserialize(reader);


        }
    }
    [XmlRoot("BOOKS")]
    public class Books
    {
        [XmlElement("BOOK")]
        public List<Book> books { get; set; }
    }
    public partial class Book
    {
        //[Key]
        [XmlElement("RECORDNO")]
        public int Recordno { get; set; }
        [XmlElement("AMOUNT")]
        public decimal? Amount { get; set; }
        [XmlElement("TITLE")]
        public string Title { get; set; }
        [XmlElement("DESCRIPTION")]
        public string Description { get; set; }
        private DateTime Whenmodified { get;set;}
        [XmlElement("WHENMODIFIED")]
        public string _Whenmodified {
            get { return Whenmodified.ToString("dd/MM/yy HH:mm:ss");  }
            set { DateTime.ParseExact(value,"dd/MM/yy HH:mm:ss", System.Globalization.CultureInfo.InvariantCulture);}
        }
        private DateTime Whencreated { get; set; }
        public string _Whencreated
        {
            get { return Whencreated.ToString("dd/MM/yy HH:mm:ss"); }
            set { DateTime.ParseExact(value, "dd/MM/yy HH:mm:ss", System.Globalization.CultureInfo.InvariantCulture); }
        }
        [XmlElement("CREATEDBY")]
        public int? Createdby { get; set; }
        [XmlElement("MODIFIEDBY")]
        public int? Modifiedby { get; set; }
    }


}