Deserialize XML classes to one class for writing to database

Question

I am trying to deserialize XML and save the results to a database using entity framework.

The first section of code is just to get the needed xml file from an API. Please see below:

    public static void Main()
    {
        Program semoAPI = new Program();

        using (WebClient webClient = new WebClient())
        {
            WebClient n = new WebClient();


            //Bid Ask Curves
            var bidAskCurves = n.DownloadString("https://reports.semopx.com/api/v1/documents/static-reports?" +
            "page=1&page_size=1&order_by=ASC&ReportName=Bid/Ask%20Curves&Group=Market%20Data");

            semoReports = JsonConvert.DeserializeObject<SemoReports>(bidAskCurves);

            Console.WriteLine("Bid Ask Curves Report: ");

            Console.WriteLine(semoReports.ResourceBaseUri + "/" + semoReports.Items[0].ResourceName);

            string bidAskCurvesXML = semoReports.ResourceBaseUri + "/" + semoReports.Items[0].ResourceName;

            XDocument bacDoc = XDocument.Load(bidAskCurvesXML);

            //Execute DeserializeBidAskCurves
            semoAPI.DeserializeBidAskCurves(bidAskCurvesXML);
        }
    }

Below is how my class is setup which contains the XML Elements I need:

namespace SEMO_app
{
    [XmlRoot("BidAskCurves")]
    public class BidAskCurves
    {
        [Key]
        public int ReportID { get; set; }

        [XmlElement("MarketArea")]
        public MarketArea[] MarketAreas{ get; set; }
    }

    public class MarketArea
    {
        public string MarketAreaName { get; set; }

        [XmlElement("DeliveryDay")]
        public DeliveryDay[] DeliveryDays { get; set; }
    }

    public class DeliveryDay
    {
        public string Day { get; set; }

        [XmlElement("TimeStep")]
        public TimeStep[] TimeSteps{ get; set; }
    }

    public class TimeStep
    {
        public string TimeStepID { get; set; }

        [XmlElement("Purchase")]
        public Purchase[] Purchases { get; set; }
    }

    public class Purchase
    {
        public string Price { get; set; }

        public string Volume { get; set; }
    }
} 

From here i would like to deserialize the XML and save the information to a database, below is the code I have so far which deserialize's the XML and gives the results back fine in the console.writesection.

However I am unable to save these values to the database table. The code complies and executes fine and a the database table updates, however the table only contains a report id column. Where I would like it to contain the items listed in the console.write section.

        private void DeserializeBidAskCurves(string filename)
    {
        //Visual only not needed
        Console.WriteLine("\n" + "Reading BidAskCurves XML File");
        Console.WriteLine("===========================================================");

        // Create an instance of the XmlSerializer.
        XmlSerializer serializer = new XmlSerializer(typeof(BidAskCurves));

        // Declare an object variable of the type to be deserialized.
        BidAskCurves item;

        using (XmlReader reader = XmlReader.Create(filename))
        {
            // Call the Deserialize method to restore the object's state.
            item = (BidAskCurves)serializer.Deserialize(reader);

            //Write out the properties of the object. (Visual Only, not needed)
            Console.Write(
                item.MarketAreas[0].MarketAreaName + "\t" +
                item.MarketAreas[0].DeliveryDays[0].Day + "\t" +
                item.MarketAreas[0].DeliveryDays[0].TimeSteps[0].TimeStepID + "\t" +
                item.MarketAreas[0].DeliveryDays[0].TimeSteps[0].Purchases[0].Price + "\t" +
                item.MarketAreas[0].DeliveryDays[0].TimeSteps[0].Purchases[0].Volume);


            //write the properties to the db 
            using (SEMOContext context = new SEMOContext())
            {
                context.BidAskCurvesReports.Add(item);
                context.SaveChanges();
            }
        }
    }

Link to xml file: https://reports.semopx.com/documents/BidAskCurves_NI-IDA3_20190401_20190401161933.xml

Thanks for any help in advance.

Answer 1

Initially, it's hard to know what's the problem actually is?

But after visiting URI generated by

string bidAskCurvesXML = semoReports.ResourceBaseUri + "/" + semoReports.Items[0].ResourceName;

And that is https://reports.semopx.com/documents/BidAskCurves_NI-IDA3_20190401_20190401161933.xml

So, The class structure that you are using is different than xml generated by URI.

You need to use below class structure for your xml

[XmlRoot("Purchase")]
public class Purchase
{
    [XmlElement("Price")]
    public string Price { get; set; }
    [XmlElement("Volume")]
    public string Volume { get; set; }
}

[XmlRoot("Sell")]
public class Sell
{
    [XmlElement("Price")]
    public string Price { get; set; }
    [XmlElement("Volume")]
    public string Volume { get; set; }
}

[XmlRoot("TimeStep")]
public class TimeStep
{
    [XmlElement("TimeStepID")]
    public string TimeStepID { get; set; }
    [XmlElement("Purchase")]
    public List<Purchase> Purchase { get; set; }
    [XmlElement("Sell")]
    public List<Sell> Sell { get; set; }
}

[XmlRoot("DeliveryDay")]
public class DeliveryDay
{
    [XmlElement("Day")]
    public string Day { get; set; }
    [XmlElement("TimeStep")]
    public List<TimeStep> TimeStep { get; set; }
}

[XmlRoot("MarketArea")]
public class MarketArea
{
    [XmlElement("MarketAreaName")]
    public string MarketAreaName { get; set; }
    [XmlElement("DeliveryDay")]
    public DeliveryDay DeliveryDay { get; set; }
}

[XmlRoot("BidAskCurves")]
public class BidAskCurves
{
    [XmlElement("MarketArea")]
    public MarketArea MarketArea { get; set; }
}

And after using above class structure with XmlSerializer there are total 12 timestamp available

Usage:

XmlSerializer serializer = new XmlSerializer(typeof(BidAskCurves));

BidAskCurves item;

using (XmlReader reader = XmlReader.Create("https://reports.semopx.com/documents/BidAskCurves_NI-IDA3_20190401_20190401161933.xml"))
{
    item = (BidAskCurves)serializer.Deserialize(reader);

    //Your code to add above parsed data into database.
}

Output: (From Debugger)

Edit1:

To add first purchase's volume and price and sell's volume and price then,

...

item = (BidAskCurves)serializer.Deserialize(reader);

foreach (var ts in item.MarketArea.DeliveryDay.TimeStep)
{
    BidAskCurvesData bidAskCurvesData = new BidAskCurvesData
    {
        ReportID = 123,
        MarketAreaName = item.MarketArea.MarketAreaName,
        Day = item.MarketArea.DeliveryDay.Day,
        TimeSetID = ts.TimeStepID,
        PurchasePrice = ts.Purchase[0].Price,
        PurchaseVolume = ts.Purchase[0].Volume,
        SellPrice = ts.Sell[0].Price,
        SellVolume = ts.Sell[0].Volume
    };

    using (SEMOContext context = new SEMOContext())
    {
        context.BidAskCurvesReports.Add(item);
        context.SaveChanges();
    }
}