Add columns to dataset to be used as XML parent no

问题:

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.

My output should be in a form like this:

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

The results from my query look like this:

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

My output XML currently looks like this:

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

Using this code:

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

I need to inject several things

At the top beneath <PCBDatabaseReply>:

<SearchResult>
  <SBE_PCB_Data PCBID="53">

In the Termination tag: (from the fake ActionID in the code)

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

And then close with the appropriate tags:

  </SBE_PCB_Data>
</SearchResult>

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

This just makes it show up as:

<SBE_PCB_Data>SBE_PCB_Data</SBE_PCB_Data>

I need it to wrap around the rest of the XML as an ancestor node.

How best to inject the XML I need into the results?

EDIT: refactored according to excellent example below **EDIT: 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.

回答1:

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.

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

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

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)

1. 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.

2. The xml tag "Termination" is the value of the Event Type, so I'll assume that tag should be the EventType value.

Lets use Linq to XML to create the XML from the list of SearchResults

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