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)

Output from WriteXML using DataSet

Desired Output XML

CodePudding user response：

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();
                }

            }

        }
    }
 
}

CodePudding user response：

SQL server can generate xml for You, since that is what you're trying to parse the strings as by the looks of it, try this

IEnumerable<string> lst = connection.Query<string>("select * from Events FOR XML PATH");

Presuming that your connection query does return a valid string result if all row is now in a XML string. If not let me know I will verify that part semantics as well :)