C# how to populate cass created from xsd.exe from database

Question

i'm currently working a case where i have to import a lot of data from the database and output it as an xml that matches to an xsd schema. i used xsd.exe to generate the c# class for a particular schema.

my question is, how do i use this class to be populated w/ data from the database?

my class looks like this: `

[System.CodeDom.Compiler.GeneratedCodeAttribute("xsd", "4.0.30319.33440")]
    [System.SerializableAttribute()]
    [System.Diagnostics.DebuggerStepThroughAttribute()]
    [System.ComponentModel.DesignerCategoryAttribute("code")]
    [System.Xml.Serialization.XmlTypeAttribute(AnonymousType = true, Namespace = "TTC_Queue")]
    [System.Xml.Serialization.XmlRootAttribute(Namespace = "MT_Queue", IsNullable = false)]
    public partial class STAGING_Low
    {
        private object[] itemsField;

        /// <remarks/>
        [System.Xml.Serialization.XmlElementAttribute("TTC_Queue", typeof(TTC_Queue))]
        [System.Xml.Serialization.XmlElementAttribute("ROW_COUNTS", typeof(ROW_COUNTS))]
        public object[] Items
        {
            get
            {
                return this.itemsField;
            }
            set
            {
                this.itemsField = value;
            }
        }
    }

    /// <remarks/>
    [System.CodeDom.Compiler.GeneratedCodeAttribute("xsd", "4.0.30319.33440")]
    [System.SerializableAttribute()]
    [System.Diagnostics.DebuggerStepThroughAttribute()]
    [System.ComponentModel.DesignerCategoryAttribute("code")]
    [System.Xml.Serialization.XmlTypeAttribute(AnonymousType = true, Namespace = "TTC_Queue")]
    public partial class TTC_Queue
    {
        private System.Data.SqlTypes.SqlDecimal reportingUnitCodeField;

        private bool reportingUnitCodeFieldSpecified;

        private System.Data.SqlTypes.SqlString preparerNField;
        //more fields

`

i will have multiple elements of the "ttc_queue" to populate in this. i already have populated an object[] of ttc_queue to be used in this.

how do i set this array to the "item fields" and then also deserialize this?

i currently have:

 STAGING_low low = new STAGING_Low();
low.Items = new TTC_Queue[1];
low.Items.SetValue(myObject[],0);

where i'm setting the value i get an error:

An unhandled exception of type 'System.InvalidCastException' occurred in mscorlib.dll

Additional information: Object cannot be stored in an array of this type.

i'm not sure what i'm missing.

thanks for the assistance.

score 1 · Accepted Answer · answered Sep 08 '16 at 00:31

1

I don't think serializing is the right approach in this case. If you generate a SQL Query properly I think the code below should work

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

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            DataSet ds = new DataSet();
            ds.ReadXmlSchema("filename");
            string SQL = "SELECT QUERY";
            string connStr = "Enter you connection string here";
            SqlDataAdapter adapter = new SqlDataAdapter(SQL, connStr);
            adapter.Fill(ds);

            ds.WriteXml("filename");
        }
    }
}

answered Sep 08 '16 at 00:31

jdweng

33,250
2
15
20

That worked and created the data as xml like I needed. However, for each record it's placing it in
tags and not the defined element tag in the xsd. How could I modify this? Also, how can I can the other element that is required from another table to the same xml? How do you combine all tables into one query that the adapter will recognize and the dataset will read? For this particular use case I'll have roughly 20k elements of TTC_Queue and 1 of ROW_COUNTS that are all in the same root. Please let me know if more information is needed. Thank you for the assistance. – orlando15767 Sep 08 '16 at 01:11
A dataset consists of multiple tables so you could use DataTable dt = new DataTable(); adapter.Fill(dt); ds.Tables.Add(dt); Each table has a name property so you can change the Table name as follows : dt.TableName = "New Table Name"; or in the constructor new DataTable("table name"); You can change column names in DataTable by using an 'AS' in the query to the database like : SELECT COLA as abc – jdweng Sep 08 '16 at 01:36
Is there a way using this method to update the tags to include the xmlns="_TTC_Queue" portion and also edit/modify the root to include the xmlns:xsi and schemaLocation? thanks – orlando15767 Sep 08 '16 at 19:40
Not sure. I will investigate. – jdweng Sep 08 '16 at 21:00
DataSet and DataTable both have string properties NameSpace and Prefix. Leaving Prefix null will give default namespace and setting Namespace to "TTC_Queue" will give xmlns="TTC_Queue". Setting prefix to 'xsi' will and Namespace to "TTC_Queue" will give xmlns:xsi="TTC_Queue". – jdweng Sep 09 '16 at 06:09

C# how to populate cass created from xsd.exe from database

1 Answers1

Linked