If have the following code that via an SQL query retrieves data from a database in the form:
Id subschemaName locusName
MLST MLST LMO0558
MLST MLST LMO0563
MLVST MLVST LMO1305
MLVST MLVST LMO1089
And I want to turn this into an XML like:
<ResponseSubschemas>
<organism>LMO</organism>
<Subschemas>
<Subschema>
<id>MLST</id>
<name>MLST</name>
<loci>
<locus>LMO0558</locus>
<locus>LMO0563</locus>
</loci>
</Subschema>
<Subschema>
<id>MLVST</id>
<name>MLVST</name>
<loci>
<locus>LMO1305</locus>
<locus>LMO1089</locus>
</loci>
</Subschema>
</Subschemas>
</ResponseSubschemas>
I do this by using a dictionary but it is the first time I'm using C# and I'm sure there must be a more efficient way of doing this in one go?
string subschemaSQL = "SELECT subschema.Id, subschema.name as subschemaName, locus.Name as locusName " +
"FROM subschema " +
"LEFT JOIN subschemamembers ON subschemamembers.SubSchemaID = subschema.PrimKey " +
"LEFT JOIN locus ON subschemamembers.LocusID = locus.ID " +
"WHERE subschema.OrganismID = ? " +
"ORDER BY subschema.Id, locusName";
XElement rootNode = new XElement("ResponseSubschemas",
new XElement("organism", organismID),
new XElement("Subschemas"));
XElement subschemasNode = rootNode.Element("Subschemas");
DbDataReader subschemaReader = conn.Query(subschemaSQL, organismID);
Dictionary<string, List<string>> dictionary = new Dictionary<string, List<string>>();
while (subschemaReader.Read())
{
string subschemaDbID = (string)subschemaReader["Id"];
XElement subschemaNode = new XElement("Subschema",
new XElement("id", subschemaDbID),
new XElement("name", subschemaReader["subschemaName"])
);
subschemasNode.Add(subschemaNode);
string locusName = (string)subschemaReader["locusName"];
if (dictionary.ContainsKey(subschemaDbID))
{
dictionary[subschemaDbID].Add(locusName);
}
else
{
dictionary.Add(subschemaDbID, new List<string> { locusName });
}
}
IEnumerable<XElement> subschemaNodes = from element in
subschemasNode.Elements("Subschema")
select element;
foreach (XElement subschemaNode in subschemaNodes)
{
string subschemaID = subschemaNode.Element("id").Value;
XElement lociNode = new XElement("loci");
foreach (string locusName in dictionary[subschemaID])
{
XElement locusNode = new XElement("locus", locusName);
lociNode.Add(locusNode);
}
subschemaNode.Add(lociNode);
}
responseXml = rootNode.ToString();