将列添加到要用作XML父节点的数据集 [英] Add columns to dataset to be used as XML parent nodes
问题描述
我正在尝试从MySQL查询格式化XML，以模拟客户端前端预期的输入。我无法控制客户需要什么，所以我必须匹配从Wireshark捕获的内容。我没有结合添加列到数据集来做到这一点的想法，我可能只是对XML的添加进行搜索和替换，但是，我有大量非常相似但不同的查询和输出来写，我更喜欢做一些比较好的事情。不幸的是，它会丢弃代码，因为当我为此编写新的前端客户端时，我们不会跟踪当前遗留系统的客户端IP地址的大量数据，或者据称是唯一的ActionID您将看到以下引用的内容，也不需要对XML做任何事情，它们都是MySQL驱动的查询。
I'm trying to format XML from a MySQL query to emulate what a client frontend is expecting for input. I have no control over what the client requires, so I have to match what I've gotten from Wireshark captures. I am not married to the idea of adding columns to the dataset to do this, and I can probably just do a search and replace for the additions to the XML, however, I have a large number of very similar, yet different queries & outputs to write, and I'd prefer to do something that scales well. Unfortunately it'll be throw away code because when I write the new front end client for this, we won't be tracking a lot of the data the current legacy system does like client IP address, or the supposedly unique "ActionID" both of which you'll see referenced below, nor will I have to do anything with XML, it'll all be MySQL driven queries.
我的输出应该在一个这样的形式：
<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());
我需要注入几件事
在< PCBDatabaseReply>下方的顶部：
At the top beneath <PCBDatabaseReply>:
<SearchResult>
<SBE_PCB_Data PCBID="53">
在终止标签中：（从代码中的假ActionID）
ActionID="0xnnnn" & append ":192.168.255.255" to the end of the user name
然后关闭相应的标签：
</SBE_PCB_Data>
</SearchResult>
我已经尝试为SBE_PCB_Data标签添加了一个虚拟列，这是不起作用的。 / p>
I have tried adding a dummy column for the "SBE_PCB_Data" tag, which didn't work.
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。
I need it to wrap around the rest of the XML as an ancestor node.
如何最好地将XML需要注入到结果中？
How best to inject the XML I need into the results?
编辑：根据
**编辑：用最终代码更新
refactored according to excellent example below ** updated with final code
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)));
}
}
}
有做一些调整让它运行，但这个概念是健全的，我喜欢它是可扩展的。它不是很正确的输出，但我也可以调整。
}
Had to do some tweaking to get this to run, but the concept is sound, and I like that it's extensible. It doesn't quite give the right output yet, but I can tweak that as well.
推荐答案
好的，让我们重构一下。
Ok, Let's refactor this.
让我们不要直接从您的数据集中尝试这样做，您正在尝试在这里的方法中做许多事情，难以维护，并且非常难以进行单元测试
Lets not try and do this directly from your dataset, you are trying to do to many things in your method here, it's messy hard to maintain and very hard to unit test.
我们应该做的第一件事是创建一个可以更容易使用的SearchResult类，这也是放置我们的业务规则的便利位置（Ip已添加到用户和随机ActionId），这也意味着我们可以轻松地将数据模拟到这个类中，而不必打到数据库，然后我们可以测试我们的变换逻辑作为单元测试，而不是集成测试（更慢，更多依赖关系）
The first thing we should do is create a SearchResult class that we can work with more easily, this is also a convenient place to put in our Business rules (Ip added to User and random ActionId) it also means that we can easily mock up data into this class without having to hit the database, we can then test our transform logic as a unit test, not an integration test (which are slower, and have more dependencies)
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));
}
}
}
所以让我们重写查询现在填充一个SearchResult的列表而不是数据集
So lets rewrite the query to now populate a list of SearchResult's instead of a dataset
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％清楚。 （如果这些是不正确的，这将很容易修改）
So now that we have a SearchResult class and a query method that gives us a list of them, lets transform that to your required XML. Firstly, I'll make some assumtions that are not 100% clear from your question. (if these are not correct, it will be easy enough to modify)
-
我假设我们正在创建一个搜索从我们的查询返回的每个搜索
结果的结果标签。并且这些将包含在
PCBDatabaseReply标签中。
I'll assume that we are creating a search result tag for each search result returned from our query. And that these will be contained in the PCBDatabaseReply tag.
xml标签Termination是事件类型的值， ll
假定标签应该是EventType值。
The xml tag "Termination" is the value of the Event Type, so I'll assume that tag should be the EventType value.
让Linq使用XML来创建XML从搜索结果列表中
Lets use Linq to XML to create the XML from the list of SearchResults
首先我们将创建一个方法来转换单个SearchResults（SearchResult标签的内容）
Firstly We'll create a method that transforms individual SearchResults (the contents of the SearchResult tag)
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));
}
其次，我们将创建转换列表的方法
Secondly we'll create the method to transform the list
public XElement TransformList(IEnumerable<SearchResult> listOfResults)
{
return new XElement("PCBDatabaseReply",
from r in listOfResults
select TransformSearchResult(r));
}
现在我们的主调用方法简单地变成...
Now our main calling method simply becomes...
var searchResults = GetSearchResults();
var xml = TransformList(searchResults);
这篇关于将列添加到要用作XML父节点的数据集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
