c# - 将列添加到数据集以用作 XML 父节点

Question

我正在尝试从 MySQL 查询格式化 XML 以模拟客户端前端对输入的期望。我无法控制客户端需要什么，所以我必须匹配我从 Wireshark 捕获中获得的内容。我不同意向数据集中添加列来执行此操作的想法，我可能只需搜索并替换对 XML 的添加，但是，我有大量非常相似但不同的查询和输出写作，我更愿意做一些规模很好的事情。不幸的是，它会被丢弃代码，因为当我为此编写新的前端客户端时，我们不会跟踪当前遗留系统所做的大量数据，例如客户端 IP 地址，或者所谓的唯一“ActionID”您将在下面看到引用，我也不必对 XML 做任何事情，它们都是 MySQL 驱动的查询。

我的输出应该是这样的形式:

<PCBDatabaseReply>
  <SearchResult>
    <SBE_PCB_Data PCBID="53">
      <Termination ActionID="97DF" User="UName:192.168.255.255" Date="2012-09-26T13:15:51" PCBID="53">
        <Reason>Other</Reason>
      </Termination>
    </SBE_PCB_Data>
  </SearchResult>
</PCBDatabaseReply>

我的查询结果如下所示:

EventType   User    Date                PCBID   Reason 
Termination UName   2012-09-26T13:15:51 53      Other 

我的输出 XML 目前看起来像这样:

<PCBDatabaseReply>
  <Termination User="UName" Date="2012-09-26T13:15:51" PCBID="53">
    <EventType>Termination</EventType>
    <Reason>Other</Reason>
  </Termination>
</PCBDatabaseReply>

使用此代码:

string mysqlConnection = "server=server;\ndatabase=database;\npassword=password;\nUser ID=user;";
MySqlConnection connection = new MySqlConnection(mysqlConnection);
connection.Open();
string command = "SELECT eventtypes.EventType, events.User, DATE_FORMAT(events.DateTime,'%Y-%m-%dT%T') AS Date, pcbid.PCBID, getReasons.ItemValue AS Reason " +
                "FROM events " +
                "INNER JOIN pcbid ON events.PCBID = pcbid.PCBID " +
                "INNER JOIN eventtypes " +
                "ON events.EventType_ID = eventtypes.EventType_ID " +
                "LEFT JOIN getReasons " + 
                "ON getReasons.Event_ID = events.Event_ID " +
                "WHERE eventtypes.EventType = 'termination'";
//create fake "ActionID"
var random = new Random();
string ActionID = String.Format("{0}\"{1:X4}\"", "ActionID=", random.Next(0xffff));

MySqlDataAdapter adapter = new MySqlDataAdapter(command, connection);
DataSet dataSet = new DataSet();
adapter.Fill(dataSet);
//change upper level node name to what's expected in client-speak
dataSet.DataSetName = "PCBDatabaseReply";

//change first child node name to client-speak eventType
dataSet.Tables[0].TableName = dataSet.Tables[0].Rows[0][0].ToString();
StringWriter writer = new StringWriter();

var ds1 = dataSet.Tables[0];
DataColumn dcEventType = ds1.Columns[0];
DataColumn dcUser = ds1.Columns[1];
DataColumn dcDate = ds1.Columns[2];
DataColumn dcPCBID = ds1.Columns[3];

dcEventType.ColumnMapping = MappingType.Element;
dcUser.ColumnMapping = MappingType.Attribute;
dcDate.ColumnMapping = MappingType.Attribute;
dcPCBID.ColumnMapping = MappingType.Attribute;

dataSet.Tables[0].WriteXml(writer, true);
Console.WriteLine(writer.ToString());

我需要注入(inject)一些东西

在 下方的顶部:

<SearchResult>
  <SBE_PCB_Data PCBID="53">

在 Termination 标签中:(来自代码中的假 ActionID)

ActionID="0xnnnn"  & append ":192.168.255.255" to the end of the user name

然后用适当的标签结束:

  </SBE_PCB_Data>
</SearchResult>

我曾尝试为“SBE_PCB_Data”标签添加一个虚拟列，但没有成功。

DataColumn dcSBE_PCB_Data = new DataColumn("SBE_PCB_Data", System.Type.GetType("System.String"), "SBE_PCB_Data", MappingType.Element);
dcSBE_PCB_Data.DefaultValue = "SBE_PCB_Data";
//add to the dataset
dataSet.Tables[0].Columns.Add(dcSBE_PCB_Data);
//move it to the zeroth position
dcSBE_PCB_Data.SetOrdinal(0);

这只是让它显示为:

<SBE_PCB_Data>SBE_PCB_Data</SBE_PCB_Data>

我需要它作为祖先节点环绕 XML 的其余部分。

如何最好地将我需要的 XML 注入(inject)到结果中？

编辑:根据下面的优秀示例重构**编辑:用最终代码更新

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Xml.Linq;
using MySql.Data.MySqlClient;

namespace TerminationResults
{
public class SearchResult
{
    //all possible event detail tags (test items are excluded)
    public string EventType { get; set; }
    public string User { get; set; }
    public string Date { get; set; }
    public string PCBID { get; set; }
    public string EAReason { get; set; }
    public string ETReason { get; set; }
    public string Notes { get; set; }
    public string Reason { get; set; }
    public string SBEJobNumber { get; set; }
    public string SBEModelNumber { get; set; }
    public string SBEPN { get; set; }
    public string SBESerialNumber { get; set; }
    //create fake IP address since we no longer track it
    public string UserAndIP
    {
        get { return String.Format("{0}:192.168.255.255", User); }
        set {}
    }
    //create fake actionID since the originals weren't inserted into the database because they weren't unique.
    public string ActionId
    {
        get { return String.Format("{0:X4}", new Random().Next(0xffff)); }
        set {}
    }
}

internal class Program
{
    private static void Main(string[] args)
    {
        var searchResults = GetSearchResults();
        var xml = TransformList(searchResults);
        Console.WriteLine(xml);
        Console.ReadLine();
    }

    public static IEnumerable<SearchResult> GetSearchResults()
    {
        List<SearchResult> searchResults = new List<SearchResult>();
        try
        {
            const string mysqlConnection = @"server=server;
                                        database=database;
                                        password=password;
                                        User ID=username;";
            MySqlConnection conn = new MySqlConnection(mysqlConnection);
            conn.Open();
            using (conn)
            {
                string cmd = @"SELECT eventtypes.EventType, events.User, 
                    DATE_FORMAT(events.DateTime,'%Y-%m-%dT%T') AS Date,
                    pcbid.PCBID, 
                    getEAReasons.ItemValue AS EAReason,
                    getETReasons.ItemValue AS ETReason,
                    getReasons.ItemValue AS Reason, 
                    getNotes.ItemValue AS Notes,
                    getSBEJobNumbers.ItemValue AS SBEJobNumber,
                    getSBEModelNumbers.ItemValue AS SBEModelNumber,
                    getSBEPNs.ItemValue as SBEPN,
                    getSBESerialNumbers.ItemValue as SBESerialNumber
                    FROM events 
                    INNER JOIN pcbid ON events.PCBID = pcbid.PCBID 
                    INNER JOIN eventtypes 
                    ON events.EventType_ID = eventtypes.EventType_ID 
                    LEFT JOIN getEAReasons
                    ON getEAReasons.Event_ID = events.Event_ID
                    LEFT JOIN getETReasons
                    ON getETReasons.Event_ID = events.Event_ID
                    LEFT JOIN getReasons
                    ON getReasons.Event_ID = events.Event_ID
                    LEFT JOIN getNotes
                    ON getNotes.Event_ID = events.Event_ID
                    LEFT JOIN getSBEJobNumbers
                    ON getSBEJobNumbers.Event_ID = events.Event_ID
                    LEFT JOIN getSBEModelNumbers
                    ON getSBEModelNumbers.Event_ID = events.Event_ID
                    LEFT JOIN getSBEPNs
                    ON getSBEPNs.Event_ID = events.Event_ID
                    LEFT JOIN getSBESerialNumbers
                    ON getSBESerialNumbers.Event_ID = events.Event_ID
                    WHERE eventtypes.EventType = 'termination'";
                try
                {
                    using (MySqlDataAdapter adapter = new MySqlDataAdapter(cmd, conn))
                    {
                        DataSet dataSet = new DataSet();
                        adapter.Fill(dataSet);
                        DataTable ds = dataSet.Tables[0];
                        for (int row = 0; row < ds.Rows.Count; row++ )
                        {
                            SearchResult result = new SearchResult()
                                {
                                    EventType = ds.Rows[row]["EventType"].ToString(),
                                    User = ds.Rows[row]["User"].ToString(),
                                    Date = ds.Rows[row]["Date"].ToString(),
                                    PCBID = ds.Rows[row]["PCBID"].ToString(),
                                    EAReason = ds.Rows[row]["EAReason"].ToString().Any() ? ds.Rows[row]["EAReason"].ToString() : null,
                                    ETReason = ds.Rows[row]["ETReason"].ToString().Any() ? ds.Rows[row]["ETReason"].ToString() : null,
                                    Notes = ds.Rows[row]["Notes"].ToString().Any() ? ds.Rows[row]["Notes"].ToString() : null,
                                    Reason = ds.Rows[row]["Reason"].ToString().Any() ? ds.Rows[row]["Reason"].ToString() : null,
                                    SBEJobNumber = ds.Rows[row]["SBEJobNumber"].ToString().Any() ? ds.Rows[row]["SBEJobNumber"].ToString() : null,
                                    SBEModelNumber = ds.Rows[row]["SBEModelNumber"].ToString().Any() ? ds.Rows[row]["SBEModelNumber"].ToString() : null,
                                    SBEPN = ds.Rows[row]["SBEPN"].ToString().Any() ? ds.Rows[row]["SBEPN"].ToString() : null,
                                    SBESerialNumber = ds.Rows[row]["SBESerialNumber"].ToString().Any() ? ds.Rows[row]["SBESerialNumber"].ToString() : null
                                };
                            searchResults.Add(result);
                        }

                    }
                }
                catch (MySqlException ex)
                {
                    Console.WriteLine(ex);
                }
                catch(Exception ex)
                {
                    Console.WriteLine(ex);
                }

            }
        }
        catch (MySqlException ex)
        {
            Console.WriteLine(ex);
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex);
        }
        return searchResults;
    }

    public static XElement TransformSearchResult (SearchResult result)
    {
        return new XElement("SBE_PCB_Data", 
            new XAttribute("PCBID", result.PCBID),
            new XElement(result.EventType,
            new XAttribute("ActionID", result.ActionId),
            new XAttribute("User", result.UserAndIP),
            new XAttribute("Date", result.Date),
            new XAttribute("PCBID", result.PCBID),
            result.EAReason == null ? null : new XElement("EAReason", result.EAReason),
            result.ETReason == null ? null : new XElement("ETReason", result.ETReason),
            result.Reason == null ? null : new XElement("Reason", result.Reason),
            result.Notes == null ? null : new XElement("Note", result.Notes),
            result.SBEJobNumber == null ? null : new XElement("SBEJobNumber", result.SBEJobNumber),
            result.SBEModelNumber == null ? null : new XElement("SBEModelNumber", result.SBEModelNumber),
            result.SBEPN == null ? null : new XElement("SBEPN", result.SBEPN),
            result.SBESerialNumber == null ? null : new XElement("SBESerialNumber", result.SBESerialNumber)
            )
        );
    }

    public static XElement TransformList (IEnumerable<SearchResult> listOfResults)
    {
        return new XElement("PCBDatabaseReply",
            new XElement("SearchResult", 
                            from r in listOfResults
                            select TransformSearchResult(r)));
    }
}

必须做一些调整才能让它运行，但这个概念是合理的，我喜欢它的可扩展性。它还没有完全给出正确的输出，但我也可以对其进行调整。

Answer 1

好的，让我们重构它。

我们不要尝试直接从您的数据集执行此操作，您正在尝试在此处的方法中执行许多操作，这很麻烦，难以维护，也很难进行单元测试。

我们应该做的第一件事是创建一个我们可以更轻松地使用的 SearchResult 类，这也是一个方便的地方来放置我们的业务规则(Ip 添加到 User 和随机 ActionId)这也意味着我们可以轻松地将数据模拟到此类中而无需访问数据库，然后我们可以将转换逻辑作为单元测试而不是集成测试(速度较慢，并且具有更多依赖性)进行测试

public class SearchResult
{
    public string EventType {get ;set;}
    public string User {get ; set;}
    public DateTime Date {get;set;}
    public int PCBID {get;set;}
    public string Reason {get;set;}

    public string UserAndIP
    {
        get
        {
            return String.Format("{0}:192.168.255.255",User);
        }
    }

    public string ActionId
    {
        get
        {
            return String.Format("{0:X4}", new Random().Next(0xffff));
        }
    }
}

因此让我们重写查询以填充搜索结果列表而不是数据集

public IEnumerable<SearchResult> GetSearchResults()
{
    using(var conn = GetYourConnection())
    {
        conn.open();
        using(var cmd = conn.CreateCommand())
        {
            cmd.CommandText = GetYourQueryString();
            using(var reader = cmd.ExecuteReader())
            {
                while(reader.Read())
                {
                    var result = new SearchResult
                    {
                        .... populate from reader...
                    }
                    yield return result;
                }
            }           
        }
    }
}

现在我们有了一个 SearchResult 类和一个为我们提供它们列表的查询方法，让我们将其转换为您需要的 XML。首先，我会根据您的问题做出一些并非 100% 清楚的假设。 (如果这些不对，修改起来很容易)

1. 我假设我们正在为每个搜索创建一个搜索结果标签从我们的查询返回的结果。这些将包含在PCBDatabaseReply 标签。

2. xml 标记“Termination”是事件类型的值，所以我将 假设该标签应该是 EventType 值。

让我们使用 Linq to XML 从 SearchResults 列表中创建 XML

首先，我们将创建一个方法来转换单个 SearchResults(SearchResult 标记的内容)

public XElement TransformSearchResult(SearchResult result)
{
    return new XElement("SearchResult",
        new XElement("SBE_PCB_Data", new XAttribute("PCBID", result.PCBID)),
        new XElement(result.EventType,
            new XAttribute("ActionID", result.ActionId),
            new XAttribute("User", result.UserAndIP),
            new XAttribute("Date", result.Date),
            new XAttribute("PCBID", result.PCBID)),
        new XElement("Reason", result.Reason));
}

然后我们将创建转换列表的方法

public XElement TransformList(IEnumerable<SearchResult> listOfResults)
{
    return new XElement("PCBDatabaseReply", 
            from r in listOfResults
            select TransformSearchResult(r));
}

现在我们的主要调用方法就变成了...

var searchResults = GetSearchResults();
var xml = TransformList(searchResults);