In this article I show you how to easily convert arbitrary SQL query results in Enterprise Architect to native C# objects.

The Enterprise Architect object model provides several methods which you can use to make direct SQL queries against the underlying database. And while Repository.GetElementSet really returns a Collection of native EA.Elements (but not connectors) the more versatile Repository.SQLQuery only returns an XML string that we normally have to parse ourselves – until now …

Introductiion

This is in short how to use the deserialiser (and I will explain the steps later on in more detail):

// define your SQL query
var query = @"SELECT [Connector_ID] AS [Id], [Connector_Type] AS [Type], 
    [Start_Object_ID] AS [ClientId], [End_Object_ID] AS [SupplierId]
  FROM t_connector";

// run the query against the database
var resultAsXmlString = repository.SQLQuery("query");

// deserialise the returned XML string into a collection of POCO elements
var conns = EaQueryXml<ConnectorInfo>.Deserialise(resultAsXmlString);

// do whatever you have to do with the elements
foreach(var conn in conns)
{
  // ...
}

First let’s have a look at the returned XML string:

<?xml version="1.0"?>
<EADATA version="1.0" exporter="Enterprise Architect">
  <Dataset_0>
    <Data>
      <Row>
        <ElementId>50</ElementId>
        <ElementType>Generalization</ElementType>
        <ClientId>33</ClientId>
        <SupplierId>39</SupplierId>
      </Row>
      <Row>
        <ElementId>16476</ElementId>
        <ElementType>Realisation</ElementType>
        <ClientId>16516</ClientId>
        <SupplierId>33</SupplierId>
      </Row>
      <Row>
        <ElementId>16477</ElementId>
        <ElementType>Realisation</ElementType>
        <ClientId>16515</ClientId>
        <SupplierId>33</SupplierId>
      </Row>
    </Data>
  </Dataset_0>
</EADATA>

As it seems every column in the SELECT statement is represented as a single XML element (ElementId, …) and every row in the query result is represented as a Row element. As the contents of the Row element depends on the SELECT statement we use a generic deserialisation class so we can change the type at design time.

The class structure

The main class structure is quite straightforward once we know the preferences of the DataContractSerializer. As we can see the Data class is actually just a Collection T and T is the actual class we want to deserialise from the Row elements. The reason for realising IEnumerable will be explained later.

[DataContract(Name = "EADATA", Namespace = "")]
public class EaQueryXml<T> : IEnumerable<T>
  where T : class
{
  [DataMember(Name = nameof(Dataset_0))]
  public Dataset_0 Dataset0 { get; set; }

  [DataContract(Name = "Dataset_0", Namespace = "")]
  public class Dataset_0
  {
    [DataMember]
    public Data Data { get; set; }
  }

  [CollectionDataContract(ItemName = "Row", Namespace = "")]
  public class Data : Collection<T>
  {
    public Data() { }

    public Data(IList<T> list) : base(list) { }
  }
}

In our case we create a ConnectorInfo class which will deserialise from the Row elements. It does not matter how we name the class as long as we use Name = "Row" on the DataContractAttribute.
As it seems sometimes the order of the XML elements confuses the deserialiser which is why I used the Order property on the DataMember attribute. In addition the deserialiser sometimes has problems converting numbers in string form to native ints, therefore I added some wrapper properties ~AsString. (I am happy to hear from you a more elegant solution.)

[DataContract(Name = "Row", Namespace = "")]
public class ConnectorInfo
{
  [DataMember(IsRequired = true, Order = 0)]
  public int ElementId { get; set; }

  [DataMember(IsRequired = true, Order = 1)]
  public string ElementType { get; set; }

  [DataMember(IsRequired = true, Name = nameof(ClientId), Order = 2)]
  public string ClientIdAsString
  {
    get => ClientId.ToString();
    set => ClientId = int.Parse(value);
  }
  public int ClientId { get; set; }

  [DataMember(IsRequired = true, Name = nameof(SupplierId), Order = 3)]
  public string SupplierIdAsString
  {
    get => SupplierId.ToString();
    set => SupplierId = int.Parse(value);
  }
  public int SupplierId { get; set; }
}

Serialisation and Deserialisation

Serialisation is as easy as a to call Serialise on the instance of the EaQueryXml class. But to make things a little bit easier I added a static Serialise that accepts a IList T objects that will be added to the EaQueryXml class:

  public static string Serialize(IList<T> list)
  {
    return new EaQueryXml<T>
    {
      Dataset0 = new Dataset_0
      {
        Data = new Data(list)
      }
    }.Serialize();
  }

  public string Serialize()
  {
    using (var memoryStream = new MemoryStream())
    using (var reader = new StreamReader(memoryStream))
    {
      var serializer = new System.Runtime.Serialization.DataContractSerializer(typeof(EaQueryXml<T>));
      serializer.WriteObject(memoryStream, this);
      memoryStream.Position = 0;

      var result = reader.ReadToEnd();
      return result;
    }
  }

For deserialisation we have to invoke the static method and specify the type T into which to convert the given XML value.

  public static EaQueryXml<T> Deserialize(string value)
  {
    using (Stream stream = new MemoryStream())
    {
      var data = Encoding.UTF8.GetBytes(value);
      stream.Write(data, 0, data.Length);
      stream.Position = 0;

      var deserializer = new System.Runtime.Serialization.DataContractSerializer(typeof(EaQueryXml<T>));
      var result = deserializer.ReadObject(stream) as EaQueryXml<T>;
      Contract.Assert(null != result);

      return result;
    }
  }

So after a successful conversion we end up with something like this:

var result = EaQueryXml<ConnectorInfo>.Deserialise(xmlString);
var elementId = result.Dataset_0.Data.Row.First().ElementId;

And now we see the reeaon for implementing IEnumerable T on the base object: with it we can access the actual result much faster:

var result = EaQueryXml<ConnectorInfo>.Deserialise(xmlString);
var elementId = result.First().ElementId;

Implementing that interface is simple as this:

  public IEnumerator<T> GetEnumerator()
  {
    return Dataset0.Data.GetEnumerator();
  }

  IEnumerator IEnumerable.GetEnumerator()
  {
    return GetEnumerator();
  }

Examples and Testing

Below you find some examples on how to use the methods. The actual call to SQLQuery has been omitted and only the resulting XML is shown here:

// ReSharper disable once IdentifierTypo
public const string XML_EADATA = @"<?xml version=""1.0""?><EADATA version=""1.0"" exporter=""Enterprise Architect""><Dataset_0><Data>
<Row><ElementId>50</ElementId><ElementType>Generalization</ElementType><ClientId>33</ClientId><SupplierId>39</SupplierId></Row>
<Row><ElementId>16476</ElementId><ElementType>Realisation</ElementType><ClientId>16516</ClientId><SupplierId>33</SupplierId></Row>
<Row><ElementId>16477</ElementId><ElementType>Realisation</ElementType><ClientId>16515</ClientId><SupplierId>33</SupplierId></Row>
</Data></Dataset_0></EADATA>";

// ReSharper disable once IdentifierTypo
public const string XML2_EADATA = @"<?xml version=""1.0""?><EADATA version=""1.0"" exporter=""Enterprise Architect""><Dataset_0><Data>
<Row><Id>50</Id></Row><Row><Id>16476</Id></Row><Row><Id>16477</Id></Row></Data></Dataset_0></EADATA>";

[TestMethod]
public void DeserialiseEadataSucceeds()
{
  // Arrange

  // Act
  var result = Deserialize<EaQueryXml<Row>>(XML_EADATA);

  // Assert
  Assert.AreEqual(50, result.Dataset0.Data.First().ElementId);
}

[TestMethod]
public void StaticDeserialiseEadataSucceeds()
{
  // Arrange

  // Act
  var result = EaQueryXml<Row>.Deserialize(XML_EADATA);

  // Assert
  Assert.AreEqual(50, result.Dataset0.Data.First().ElementId);
}

[DataContract(Name = "Row", Namespace = "")]
public class ConnectorId
{
  [DataMember(IsRequired = true)]
  public int Id { get; set; }
}

[TestMethod]
public void StaticDeserialiseXmlData2Succeeds()
{
  // Arrange

  // Act
  var result = EaQueryXml<ConnectorId>.Deserialize(XML2_EADATA);

  // Assert
  Assert.AreEqual(50, result.Dataset0.Data.First().Id);
}

[TestMethod]
public void StaticSerialiseEadataSucceeds()
{
  // Arrange
  var input = EaQueryXml<Row>.Deserialize(XML_EADATA);
  var list = input.ToList();

  // Act
  var result = EaQueryXml<Row>.Serialize(list);

  // Assert
  Assert.IsTrue(result.Contains("50"));
  Assert.IsTrue(result.Contains("16476"));
  Assert.IsTrue(result.Contains("16477"));
}

[TestMethod]
public void SerialiseEadataSucceeds()
{
  // Arrange
  var input = EaQueryXml<Row>.Deserialize(XML_EADATA);

  // Act
  var result = input.Serialize();

  // Assert
  Assert.IsTrue(result.Contains("50"));
  Assert.IsTrue(result.Contains("16476"));
  Assert.IsTrue(result.Contains("16477"));
}

[TestMethod]
public void EnumerateResultSucceds()
{
  // Arrange
  var result = Deserialize<EaQueryXml<Row>>(XML_EADATA);

  // Act
  ;

  // Assert
  Assert.AreEqual(50, result.First().ElementId);
  Assert.AreEqual(16476, result.Take(2).Last().ElementId);
  Assert.AreEqual(16477, result.Last().ElementId);
}

Summary

With this deserialiser we now can easily convert results from SQLQuery into native C# objects without using a complete XmlDocument. In addition we can use small classes that only contain the relevant information and properties (so we do not create too much burden on the database).

I hope you can use it as much as I do. Till next time.

ps For easy invocation I created an extension method the IDualRepository so that I am now able to call this directly whenever I have a regular repository instance at hand:

public static IEnumerable<T> SQLQuery<T>(this IDualRepository source, string query)
  where T : class
{
  var result = source.SQLQuery(query);
  return EaQueryXml<T>.Deserialize(result).Dataset0.Data;
}