Using Linq to pull from IEnumerable and store in data table

Question

I have a datatable that looks like the following

public static DataTable SetColumnHeaders(DataTable KeyDataTable)
{
    KeyDataTable.Columns.Add("First_Name", typeof(string));
    KeyDataTable.Columns.Add("Last_Name", typeof(string));
    KeyDataTable.Columns.Add("Address1", typeof(string));
    KeyDataTable.Columns.Add("Address2", typeof(bool));
    KeyDataTable.Columns.Add("City", typeof(string));
    KeyDataTable.Columns.Add("State", typeof(bool));
    KeyDataTable.Columns.Add("Zip", typeof(string));
    KeyDataTable.Columns.Add("Zip4", typeof(bool));
    KeyDataTable.Columns.Add("Match_File", typeof(bool));
    return KeyDataTable;
}

My goal is to read information in from an xml and store that specific data into my datatable. I have a separate xml file for each "Quote" so I will be appending a new row to the datatable. The part of the xml I am concerned with is as follows:

    '- <ACORD>
    - <SignonRq>
    - <SignonPswd>
    - <CustId>
      <SPName>com.agencyport</SPName> 
      </CustId>
    - <CustPswd>
      <EncryptionTypeCd>NONE</EncryptionTypeCd> 
      <Pswd>default</Pswd> 
      </CustPswd>
      </SignonPswd>
      <ClientDt>2006-04-04T15:44:00</ClientDt> 
      <CustLangPref>en-US</CustLangPref> 
    - <ClientApp>
      <Org>Applied Systems</Org> 
      <Name>WinTam</Name> 
      <Version>7.1.0</Version> 
      </ClientApp>
      </SignonRq>
    - <InsuranceSvcRq>
      <RqUID>81913CB5-3EAB-F158-EE24-5910F9BE9C26</RqUID> 
    - <PersAutoPolicyQuoteInqRq>
      <RqUID>7B010E52-44F2-487A-521B-9D1E3500C23D</RqUID> 
    - <Producer id="AB4E95FF02FA91FAA4D7A2D96B59D8866A">
    - <ProducerInfo id="AF04551B40F1439BCCC77CA3A21165FFAA">
      <ContractNumber id="AD2178F32385016684F33F848830CAA18A">AP</ContractNumber> 
      </ProducerInfo>
      </Producer>

<InsuredOrPrincipal id="A498E0A503206279EE434988B68472974A">
                <GeneralPartyInfo id="A4F0BBE53B311050FD0552BB41090A523A">
                    <NameInfo id="AFBDE1032EEEA0821374C7C9428B0B44CA">
                        <PersonName id="A883A5BFD8FA8E71F52780B1E678AD64AA">
                            <Surname id="A40A625346687D257582BF6499710839BA">TEST</Surname>
                            <GivenName id="A021FD886DAAF628327F542786B6CD9B5A">TEST</GivenName>
                            <OtherGivenName id="A06DB1E21AF9BD37420B5C39E6562C78AA">TEST</OtherGivenName>
                        </PersonName>
                        <TaxIdentity id="ABC2680C3B21A161E54BCDBA78DFCCE77A">
                            <TaxIdTypeCd id="A050BE41EE9F2B1C713E934B1D6D2B31BA">SSN</TaxIdTypeCd>
                        </TaxIdentity>
                    </NameInfo>
                    <Addr id="A0C5DF11BD2CF70669AE368F685DAD141A">
                        <AddrTypeCd id="A82658A7F5CEB14239A4023874F594FC9A">MailingAddress</AddrTypeCd>
                        <Addr1 id="A0DC5C008818A7559527AD40AB1E0D8E0A">100 MAIN ST</Addr1>
                        <City id="A7DBC851540752437C649745A63508198A">Howell</City>
                        <StateProvCd id="ACDF462092E91668AD7996C662ACC1622A">MI</StateProvCd>
                        <PostalCode id="A45C6341382A3314D1EC79FEF20FE9D82A">48843</PostalCode>
                        <CountryCd id="AD69C7B00BB7F210588E016FF281675F6A">Livingston</CountryCd>
                    </Addr>
                    <Communications id="AFC53B2B003342664BE4635C38C7C6C45A">
                        <PhoneInfo id="AE5497FDB30717F033E8DFA47B3A36142A">
                            <PhoneTypeCd id="AF8662F35A8F1FD3DD993CECB53EB2FCAA">Phone</PhoneTypeCd>
                            <CommunicationUseCd id="ADA98E4A9B820C002189B1124F071D462A">Home</CommunicationUseCd>
                            <PhoneNumber id="A7F0F2A55F636FB6DCED2F6815271B352A">313-272-6576</PhoneNumber>
                        </PhoneInfo>
                    </Communications>
                </GeneralPartyInfo>
                <InsuredOrPrincipalInfo id="A09004254D9A7BE38EA45B20CCD6A0EC2A">
                    <InsuredOrPrincipalRoleCd id="A2B16D7C6D9CE94DB83DDC6C69BE52BDBA">Insured</InsuredOrPrincipalRoleCd>
                    <PersonInfo id="AE7CB4EE90C6BEBB1C79DF10415B3B8E5A">
                        <MiscParty id="A3AC37CD29B32FA46D0204601CE86F0C0A">
                            <MiscPartyInfo id="A5A9326BB8C3E68900D23F62420A06362A">
                                <MiscPartyRoleCd id="A92E022991F988677D6EF8434207DDEBBA">Employer</MiscPartyRoleCd>
                            </MiscPartyInfo>
                        </MiscParty>
                    </PersonInfo>
                </InsuredOrPrincipalInfo>
            </InsuredOrPrincipal>

What I have come up with so far is this:

public static void ExportAutoToText()
{
    DirectoryInfo AutoDir = new DirectoryInfo(FilePrep.AutoDirectory);
    DataTable AutoDataTable = new DataTable();

    AutoDataTable = SetColumnHeaders(AutoDataTable); // set column headers


    foreach (FileInfo File in AutoDir.GetFiles())
    {
        DataRow fileRow = AutoDataTable.NewRow();
        XDocument xmlDoc = XDocument.Load(AutoDir + File.Name);

        //decide if i want to keep the file
        IEnumerable<XElement> personinfo = 
            from per in xmlDoc.Root.Descendants("InsuredOrPrincipal")
            where (string)per.Element("InsuredOrPrincipalInfo")
                  .Element("InsuredOrPrincipalRoleCd") == ("Insured")
            select per;

        // I then want to update the information in my datatable
        //fileRow["First_Name"] = xVal.Element("GeneralPartyInfo")
        //                                        .Element("NameInfo")
        //                                        .Element("PersonName")
        //                                        .Element("GivenName");

        //fileRow["Last_Name"] = xVal.Element("GeneralPartyInfo")
        //                                        .Element("NameInfo")
        //                                        .Element("PersonName")
        //                                        .Element("Surname");
    }
}

This method is inside of a Class so you can assume getting the file and everything works just fine. I just need to know the most efficient way of accessing the data from the xml file and storing it in my datatable. I'v tried looping data as follows:

foreach (var Xval in personinfo)
{
   //get the element info 
}

I just dont know enough about xml to know how to access it.Thanks again and If you need more information please let me know. **

Answer 1

I prefer creating objects for each node level. It is easier to debug and test.

Using this xml library.

You'd create classes for each part, like:

public class InsuredOrPrincipal 
{
    XElement self;
    public InsuredOrPrincipal(XElement self) { this.self = self; }

    public GeneralPartyInfo GeneralPartyInfo { get { return _GeneralPartyInfo ?? (_GeneralPartyInfo = new GeneralPartyInfo(self.GetElement("GeneralPartyInfo"))); } }
    GeneralPartyInfo _GeneralPartyInfo;

    public InsuredOrPrincipalInfo InsuredOrPrincipalInfo 
    { get { return _InsuredOrPrincipalInfo ?? (_InsuredOrPrincipalInfo = new InsuredOrPrincipalInfo(self.GetElement("InsuredOrPrincipalInfo"))); } }
    InsuredOrPrincipalInfo _InsuredOrPrincipalInfo;
}

public class GeneralPartyInfo
{
    XElement self;
    public GeneralPartyInfo(XElement self) { this.self = self; }

    public NameInfo NameInfo { get { return _NameInfo ?? (_NameInfo = new NameInfo(self.GetElement("NameInfo"))); } }
    NameInfo _NameInfo;

}

public class InsuredOrPrincipalInfo
{
    XElement self;
    public InsuredOrPrincipalInfo(XElement self) { this.self = self; }

    public string InsuredOrPrincipalRoleCd
    {
        get { return self.Get("InsuredOrPrincipalRoleCd", string.Empty); }
    }
}

public class NameInfo
{
    XElement self;
    public NameInfo(XElement self) { this.self = self; }

    public PersonName PersonName { get { return _PersonName ?? (_PersonName = new PersonName(self.GetElement("PersonName"))); } }
    PersonName _PersonName;
}

public class PersonName
{
    XElement self;
    public PersonName(XElement self) { this.self = self; }

    public string Surname 
    { 
         get { return self.Get("Surname", string.Empty); }
         set { self.Set("Surname", value, false); }
    }
}

You would use it like this:

foreach (FileInfo File in AutoDir.GetFiles())
{
    DataRow fileRow = AutoDataTable.NewRow();
    XDocument xmlDoc = XDocument.Load(AutoDir + File.Name);

    InsuredOrPrincipal[] insured = xmlDoc.Root
        .Descendants("InsuredOrPrincipal")
        .Select(x => new InsuredOrPrincipal(x))
        .Where(ip => ip.InsuredOrPrincipalInfo.InsuredOrPrincipalRoleCd == "Insured")
        .ToArray();

    foreach(var person in insured)
    {
        string surname = person.GeneralPartyInfo.NameInfo.PersonName.Surname;
    }
}

Depending on your needs you can expand or shrink the number of classes and information per class as you need, but this is the way I'd go about it, as it makes more sense to me.

Tested with this code:

XElement test = new XElement("test");
var ip = new InsuredOrPrincipal(test);
ip.GeneralPartyInfo.NameInfo.PersonName.Surname = "Surname";
test.Save(Path.Combine(Application.StartupPath, "insuredOrPrincipal.xml"));

Which gave me the expected output:

<?xml version="1.0" encoding="utf-8"?>
<InsuredOrPrincipal>
  <GeneralPartyInfo>
    <NameInfo>
      <PersonName>
        <Surname>Surname</Surname>
      </PersonName>
    </NameInfo>
  </GeneralPartyInfo>
</InsuredOrPrincipal>

Answer 2

You can bake your "isInsured" check and your selection of a person into the one query;

var query = from p in xmlDoc.Elements()
            where ((string)p.Element("InsuredOrPrincipalInfo")
                        .Element("InsuredOrPrincipalRoleCd")) == "Insured"
            select new
            {
                Firstname = (string)p.Element("GeneralPartyInfo")
                .Element("NameInfo")
                .Element("PersonName")
                .Element("GivenName"),

                LastName = (string)p.Element("GeneralPartyInfo")
                .Element("NameInfo")
                .Element("PersonName")
                .Element("Surname"),
            };

var person = query.FirstOrDefault();
if (person != null)
{
    var fileRow = AutoDataTable.NewRow();
    fileRow["First_Name"] = person.Firstname;
    fileRow["Last_name"] = person.LastName;
}

This isn't super-robust in terms of handling XML in an unexpected format, but it should serve as a good starting point for you. Just fill out the anonymous object initialization with the rest of your elements, casting to the desired types.

Answer 3

This is what I have so far.

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

namespace xmlCustomReformat
{
    class importXml
    {
        public DataTable _ComboDataTable;
        public void ExportAutoToText()
        {
            DirectoryInfo AutoDir = new DirectoryInfo(FilePrep.AutoDirectory);

            CreateDataTable(); // set column headers

           foreach (FileInfo File in AutoDir.GetFiles())
           {
               DataRow fileRow = _ComboDataTable.NewRow();
               XDocument xmlDoc = XDocument.Load(AutoDir + File.Name);

               InsuredOrPrincipal[] insured = xmlDoc.Root
                   .Descendants("InsuredOrPrincipal")
                   .Select(x => new InsuredOrPrincipal(x))
                   .Where(ip => ip.InsuredOrPrincipalInfo.InsuredOrPrincipalRoleCd == "Insured")
                   .ToArray();

               foreach (var person in insured)
               {
                   fileRow["First_Name"] = person.GeneralPartyInfo.NameInfo.PersonName.GivenName;
                   fileRow["Last_name"] = person.GeneralPartyInfo.NameInfo.PersonName.Surname;
                   fileRow["Address1"] = person.GeneralPartyInfo.Addr.Address1;
                   fileRow["City"] = person.GeneralPartyInfo.Addr.City;
                   fileRow["State"] = person.GeneralPartyInfo.Addr.State;
                   fileRow["Zip"] = person.GeneralPartyInfo.Addr.Zip;
                   fileRow["Address2"] = " ";
                   fileRow["Zip4"] = " ";
                   fileRow["Match_File"] = File.Name.ToString();
                   _ComboDataTable.Rows.Add(fileRow);
               }
           }
        }

        public void ExportHomeToText()
        {
            DirectoryInfo HomeDir = new DirectoryInfo(FilePrep.HomeDirectory);

            foreach (FileInfo File in HomeDir.GetFiles())
            {
                DataRow fileRow = _ComboDataTable.NewRow();
                XDocument xmlDoc = XDocument.Load(HomeDir + File.Name);

                InsuredOrPrincipal[] insured = xmlDoc.Root
                    .Descendants("InsuredOrPrincipal")
                    .Select(x => new InsuredOrPrincipal(x))
                    .Where(ip => ip.InsuredOrPrincipalInfo.InsuredOrPrincipalRoleCd == "Insured")
                    .ToArray();

                foreach (var person in insured)
                {
                    fileRow["First_Name"] = person.GeneralPartyInfo.NameInfo.PersonName.GivenName;
                    fileRow["Last_name"] = person.GeneralPartyInfo.NameInfo.PersonName.Surname;
                    fileRow["Address1"] = person.GeneralPartyInfo.Addr.Address1;
                    fileRow["City"] = person.GeneralPartyInfo.Addr.City;
                    fileRow["State"] = person.GeneralPartyInfo.Addr.State;
                    fileRow["Zip"] = person.GeneralPartyInfo.Addr.Zip;
                    fileRow["Address2"] = " ";
                    fileRow["Zip4"] = " ";
                    fileRow["Match_File"] = File.Name.ToString();
                    _ComboDataTable.Rows.Add(fileRow);
                }
            }
            ExportDataTable.Write(_ComboDataTable, HomeDir.Parent.FullName.ToString());
        }

        public void CreateDataTable()
        {
            _ComboDataTable = new DataTable();

            _ComboDataTable.Columns.Add("First_Name", typeof(string));
            _ComboDataTable.Columns.Add("Last_Name", typeof(string));
            _ComboDataTable.Columns.Add("Address1", typeof(string));
            _ComboDataTable.Columns.Add("Address2", typeof(string));
            _ComboDataTable.Columns.Add("City", typeof(string));
            _ComboDataTable.Columns.Add("State", typeof(string));
            _ComboDataTable.Columns.Add("Zip", typeof(string));
            _ComboDataTable.Columns.Add("Zip4", typeof(string));
            _ComboDataTable.Columns.Add("Match_File", typeof(string));  
        }

    }
}

The class Comp looks like this.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Xml.Linq;
namespace xmlCustomReformat
{
    public class InsuredOrPrincipal 
    {
        XElement self;
        public InsuredOrPrincipal(XElement self) { this.self = self; }

        public GeneralPartyInfo GeneralPartyInfo { get { return _GeneralPartyInfo ?? (_GeneralPartyInfo = new GeneralPartyInfo(self.Element("GeneralPartyInfo"))); } }
        GeneralPartyInfo _GeneralPartyInfo;

        public InsuredOrPrincipalInfo InsuredOrPrincipalInfo 
        { get { return _InsuredOrPrincipalInfo ?? (_InsuredOrPrincipalInfo = new InsuredOrPrincipalInfo(self.Element("InsuredOrPrincipalInfo"))); } }
        InsuredOrPrincipalInfo _InsuredOrPrincipalInfo;
    }

    public class GeneralPartyInfo
    {
        XElement self;
        public GeneralPartyInfo(XElement self) { this.self = self; }

        public NameInfo NameInfo { get { return _NameInfo ?? (_NameInfo = new NameInfo(self.Element("NameInfo"))); } }
        NameInfo _NameInfo;

        public Addr Addr { get { return _Addr ?? (_Addr = new Addr(self.Element("Addr"))); } }
        Addr _Addr;

    }

    public class InsuredOrPrincipalInfo
    {
        XElement self;
        public InsuredOrPrincipalInfo(XElement self) { this.self = self; }

        public string InsuredOrPrincipalRoleCd
        {
            get { return (string)self.Element("InsuredOrPrincipalRoleCd"); }
        }
    }

    public class NameInfo
    {
        XElement self;
        public NameInfo(XElement self) { this.self = self; }

        public PersonName PersonName { get { return _PersonName ?? (_PersonName = new PersonName(self.Element("PersonName"))); } }
        PersonName _PersonName;
    }

    public class Addr
    {
        XElement self;
        public Addr(XElement self) { this.self = self; }

        public string Address1
        {
            get { return (string)self.Element("Addr1"); }
        }
        public string City
        {
            get { return (string)self.Element("City"); }
        }
        public string State
        {
            get { return (string)self.Element("StateProvCd"); }
        }
        public string Zip
        {
            get { return (string)self.Element("PostalCode"); }
        }
    }

    public class PersonName
    {
        XElement self;
        public PersonName(XElement self) { this.self = self; }

        public string Surname 
        { 
             get { return (string)self.Element("Surname"); } 
        }
        public string GivenName 
        {
            get { return (string)self.Element("GivenName"); }
        }
    }
}

And Of course my export back to a text file for my append.

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.IO;
using System.Windows.Forms;

namespace xmlCustomReformat
{
    public static class ExportDataTable
    {
       public static void Write(DataTable dt, string filePath)
        {
            int i = 0;
            StreamWriter sw = null;

            try
            {

                sw = new StreamWriter(filePath + "\\Acord_Combined.txt", false);

                for (i = 0; i < dt.Columns.Count-1; i++)
                {

                    sw.Write(String.Format("{0,-50}",dt.Columns[i].ColumnName));

                }
                sw.Write(dt.Columns[i].ColumnName);
                sw.WriteLine();

                foreach (DataRow row in dt.Rows)
                {
                    object[] array = row.ItemArray;

                    for (i = 0; i < array.Length - 1; i++)
                    {
                        sw.Write(String.Format("{0,-50}",array[i].ToString()));
                    }
                    sw.Write(array[i].ToString());
                    sw.WriteLine();

                }

                sw.Close();
            }

            catch (Exception ex)
            {
                MessageBox.Show("Invalid Operation : \n" + ex.ToString(),
                                "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }
    }
}

Now I still need to append data back into The same xml files after I do some processing. As well as I have to depersonalize the data. So I'm assuming I just need to do sets rather than Gets. Not sure of the syntax for this process yet. If you have suggestions to improve this please feel free to leave a comment.