How does one use an IEnumerable<string> as replacement of a TextReader?

Question

I am working on a TimeRegistry. I have a C# program running that reads and XML file, downloads new data from a server, adds it to the XML file and exports the data from the XML file to an SQL database. What I would like to do is skip the part in which the XML file is used as input. I would like the select data from the SQL database, use this to get data from the server and then add it to the SQL database.

The problem is however, that the SDK that I am using only accepts a certain type as input. I have added the classes that contain this type. To better understand my question, I have also added a working code sample and the new non-working code sample. Could someone please help me to convert my list of strings to a stream that can be deserialized to the Events type? (Or has a better idea to solving this problem)

http://sqlfiddle.com/#!9/0f856/8

original sample that works

    public static Events Load(string aFileName)
    {
        XmlSerializer xs = new XmlSerializer(typeof(Events));
        TextReader tr = new StreamReader(aFileName);
        try
        {
            return (Events)xs.Deserialize(tr);
        }
        finally
        {
            tr.Close();
            tr.Dispose();
        }
    }

new sample that does not work

    public static Events Load()
    {
        XmlSerializer xs = new XmlSerializer(typeof(Events));
        using (IDbConnection connection = new SqlConnection(Helper.CnnVal("TimeReg")))            
        {
            try
            {
                IEnumerable<string> lst = connection.Query<string>($"select * from Events");
                var stream = new ByteStream(Encode(lst, Encoding.UTF8));
                return (Events)xs.Deserialize(stream);      
            }
            finally
            {
                connection.Close();
            }
         }
     }

Classes that contain the required datatype

    public static Events Load(string aFileName)
    {
        XmlSerializer xs = new XmlSerializer(typeof(Events));
        TextReader tr = new StreamReader(aFileName);
        try
        {
            return (Events)xs.Deserialize(tr);
        }
        finally
        {
            tr.Close();
            tr.Dispose();
        }
    }


    public Events()
    {
        EventList = new List<ReadEventRequestResponse.DATA>();
    }

    static public class ReadEventRequestResponse
    {
        [XmlRoot(ElementName = "DATA")]
        public class DATA
        {
            [XmlAttribute(AttributeName = "dt")]
            public string LocalTime { get; set; }

Input to WriteXML

    public class Events
{        
    public List<ReadEventRequestResponse.DATA> EventList { get; set; }

    public Events()
    {
        EventList = new List<ReadEventRequestResponse.DATA>();
    }
    const string FILENAME = @"C:\Users\...\Documents\Events_check.xml";
    public static Events Load(string aFileName)
    {
        
        DataTable dt = null;
        XmlSerializer xs = new XmlSerializer(typeof(Events));
        using (SqlConnection connection = new SqlConnection("connection string")
        {
            try
            {
                string query = "select * from Events";
                SqlDataAdapter adapter = new SqlDataAdapter(query, connection);
                dt = new DataTable("Events");
                adapter.Fill(dt);
            }
            finally
            {
                connection.Close();
            }
            dt.WriteXml(FILENAME);
            MemoryStream ms = new MemoryStream();
            dt.WriteXml(ms);
            ms.Position = 0;
            try
            {
                return (Events)xs.Deserialize(ms);
            }
            finally
            {
                ms.Close();
                ms.Dispose();
            }

        }
    }

Output from WriteXML using DataTable

Output from WriteXML using DataSet

Desired Output XML

Answer 1

Try code below :

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml;
using System.Xml.Linq;
using System.Data;
using System.Data.SqlClient;
using System.IO;


namespace ConsoleApplication23
{

    class Program
    {
        const string FILENAME = @"c:\temp\test.xml";
        static void Main(string[] args)
        {
            DataTable dt = null;
            DataRow row = null;
            MemoryStream ms = null;
            //add test data to datatable
            //this is equivalent to the adapter.Fill below.
            //remove this code when getting data from query
            dt = new DataTable();
            dt.Columns.Add("sernum", typeof(int));
            dt.Columns.Add("id",typeof(string));
            dt.Columns.Add("dt", typeof(DateTime));
            row = dt.Rows.Add();
            row["dt"] = DateTime.Parse("2022-03-30T19:10:33+02:00");
            row = dt.Rows.Add();
            row["dt"] = DateTime.Parse("2022-03-30T19:10:33+02:00");

            string ident = "<?xml version=\"1.0\" encoding=\"utf-8\"?><Events xmlns:xsi=\"w3.org/2001/XMLSchema-instaqnce\" xmlns:xsd=\"w3.org/2001/XMLSchema\"></Events>";
            XDocument doc = XDocument.Parse(ident);
            XElement events = doc.Root;
            XElement eventList = new XElement("EventList");
            events.Add(eventList);


            //move this code to after the try below where query files the datatable
            foreach(DataRow dataRow in dt.AsEnumerable())
            {
                XElement data = new XElement("DATA", new XAttribute("dt", dataRow.Field<DateTime>("dt").ToString("yyyy-MM-dd HH:mm")));
                eventList.Add(data);
            }
            doc.Save(FILENAME);
            ms = new MemoryStream();
            doc.Save(ms);
            ms.Position = 0;

            Console.ReadLine();


            SqlConnection conn = new SqlConnection("connection string");
            using (SqlConnection connection = new SqlConnection())
            {
                try
                {
                    string query = "select * from Events";
                    SqlDataAdapter adapter = new SqlDataAdapter(query, connection);
                    dt = new DataTable();
                    adapter.Fill(dt);
                }
                finally
                {
                    connection.Close();
                }


                doc.Save(FILENAME);
                ms = new MemoryStream();
                doc.Save(ms);
                ms.Position = 0;                    
                try
                {

                }
                finally
                {
                    ms.Close();
                    ms.Dispose();
                }

            }

        }
    }
 
}