使用 Linq 从 IEnumerable中提取数据并存储在数据表中

Question

我有一个如下所示的数据表

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

我的目标是从 xml 读取信息并将特定数据存储到我的数据表中。我为每个“报价”都有一个单独的 xml 文件，因此我将在数据表中附加一个新行。我关心的 xml 部分如下：

    '- <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>

到目前为止我想到的是：

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

这个方法位于类内部，因此您可以假设获取文件并且一切正常。我只需要知道从 xml 文件访问数据并将其存储在数据表中的最有效方法。我尝试按如下方式循环数据：

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

我只是对 xml 不太了解，不知道如何访问它。再次感谢，如果您需要更多信息，请告诉我。 **

Answer 1

我更喜欢为每个节点级别创建对象。更容易调试和测试。

使用此 xml 库。

您可以为每个部分创建类，例如：

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

您可以这样使用它：

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

根据您的需要，您可以根据需要扩展或缩小类的数量以及每个类的信息，但这就是我的处理方式，因为这对我来说更有意义。

使用此代码进行测试：

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

这给了我预期的输出：

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

Answer 2

您可以将“isInsured”检查和您对人员的选择烘焙到一个查询中；

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

就以意外格式处理 XML 而言，这并不是非常强大，但它应该作为您的一个很好的起点。只需使用其余元素填写匿名对象初始化，并转换为所需的类型。

Answer 3

这是我到目前为止所拥有的。

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

    }
}

Comp 类看起来像这样。

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

当然，我导出回文本文件以进行追加。

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

现在，在进行一些处理后，我仍然需要将数据附加回相同的 xml 文件中。我还必须将数据去个性化。所以我假设我只需要做集合而不是获取。尚不确定此过程的语法。如果您有改进建议，请随时发表评论。