Preparing tabular data to be sent as json

Question

I query a database and pass the data for processing as a DataTable. After all computations (grouping, filtering, formatting etc.) I need to send it to a Web-Service as json that will generate emails. This means I need to convert it to a more json and html friendly structure because I need a header, body and footer parts separated. I was using a DataTable and a flat structure before but it's a pain to debug it as you never really know where the header, body or footer begin or end. Passing indexes around is very error prone.

To make this conversion process more reliable and debug-friendly I created a couple of helpers.

At the top there is the TripleTableDto that should resemble a html-table with its three parts.

internal class TripleTableDto
{
    public TripleTableDto(IEnumerable<SoftString> columns, bool areHeaders = true)
    {
        Head = new TableDto<object>(columns);
        if (areHeaders)
        {
            Head.NewRow();
            foreach (var column in columns)
            {
                Head.LastRow[column] = column;
            }
        }

        Body = new TableDto<object>(columns);
        Foot = new TableDto<object>(columns);
    }

    public TripleTableDto(IEnumerable<string> columns, bool areHeaders = true)
        : this(columns.Select(SoftString.Create), areHeaders)
    {
    }

    [NotNull]
    public TableDto<object> Head { get; }

    [NotNull]
    public TableDto<object> Body { get; }

    [NotNull]
    public TableDto<object> Foot { get; }

    [NotNull]
    public IDictionary<string, IEnumerable<IEnumerable<object>>> Dump()
    {
        return new Dictionary<string, IEnumerable<IEnumerable<object>>>
        {
            [nameof(Head)] = Head.Dump(),
            [nameof(Body)] = Body.Dump(),
            [nameof(Foot)] = Foot.Dump(),
        };
    }
}

This is made of three TableDto<T>s that hold the actual data.

internal class TableDto<T>
{
    private readonly IDictionary<SoftString, ColumnDto> _columnByName;
    private readonly IDictionary<int, ColumnDto> _columnByOrdinal;

    private readonly List<RowDto<T>> _rows = new List<RowDto<T>>();

    public TableDto(IEnumerable<SoftString> names)
    {
        var columns = names.Select((name, ordinal) => new ColumnDto { Name = name, Ordinal = ordinal }).ToList();

        _columnByName = columns.ToDictionary(x => x.Name);
        _columnByOrdinal = columns.ToDictionary(x => x.Ordinal);
    }

    public TableDto(params string[] names) : this(names.Select(SoftString.Create))
    {
    }

    [NotNull]
    public RowDto<T> LastRow => _rows.LastOrDefault() ?? throw new InvalidOperationException("There are no rows.");

    [NotNull]
    public RowDto<T> NewRow()
    {
        var newRow = new RowDto<T>
        (
            _columnByName.Values,
            name => _columnByName.GetItemSafely(name),
            ordinal => _columnByOrdinal.GetItemSafely(ordinal)
        );
        _rows.Add(newRow);
        return newRow;
    }

    [NotNull, ItemNotNull]
    public IEnumerable<IEnumerable<T>> Dump() => _rows.Select(row => row.Dump());
}

I wanted to make sure that I'm not introducing new column names or change their order by mistake. This is taken care of by two more dtos. The ColumnDto that stores information about a single column...

internal class ColumnDto
{
    public static readonly IComparer<ColumnDto> Comparer = ComparerFactory<ColumnDto>.Create
    (
        isLessThan: (x, y) => x.Ordinal < y.Ordinal,
        areEqual: (x, y) => x.Ordinal == y.Ordinal,
        isGreaterThan: (x, y) => x.Ordinal > y.Ordinal
    );

    public SoftString Name { get; set; }

    public int Ordinal { get; set; }

    public override string ToString() => $"{Name}[{Ordinal}]";
}

...and the RowDto that keeps an eye on the correct column order. It can work either with names or indexes and retrieves the actual column used as a key for the SortedDictionary in one of two other mapping dictionaries. I'm using here one of my dictionary extensions GetItemSafely that provides a wrapper exception with the name of the missing key for easier debugging.

public class RowDto<T>
{
    private readonly IDictionary<ColumnDto, T> _data;
    private readonly Func<SoftString, ColumnDto> _getColumnByName;
    private readonly Func<int, ColumnDto> _getColumnByOrdinal;

    internal RowDto(IEnumerable<ColumnDto> columns, Func<SoftString, ColumnDto> getColumnByName, Func<int, ColumnDto> getColumnByOrdinal)
    {
        // All rows need to have the same length so initialize them with 'default' values.
        _data = new SortedDictionary<ColumnDto, T>(columns.ToDictionary(x => x, _ => default(T)), ColumnDto.Comparer);
        _getColumnByName = getColumnByName;
        _getColumnByOrdinal = getColumnByOrdinal;
    }

    [CanBeNull]
    public T this[SoftString name]
    {
        get => _data.GetItemSafely(_getColumnByName(name));
        set => _data[_getColumnByName(name)] = value;
    }

    [CanBeNull]
    public T this[int ordinal]
    {
        get => _data.GetItemSafely(_getColumnByOrdinal(ordinal));
        set => _data[_getColumnByOrdinal(ordinal)] = value;
    }

    [NotNull, ItemCanBeNull]
    public IEnumerable<T> Dump() => _data.Values;
}

The order of the columns is supported by the ComparerFactory<T> that is a helper for creating IComparer<T>s without worrying about the null-checks or returning the correct number in each case.

internal static class ComparerFactory<T>
{
    private class Comparer : IComparer<T>
    {
        private readonly Func<T, T, bool> _isLessThan;
        private readonly Func<T, T, bool> _areEqual;
        private readonly Func<T, T, bool> _isGreaterThan;

        public Comparer(Func<T, T, bool> isLessThan, Func<T, T, bool> areEqual, Func<T, T, bool> isGreaterThan)
        {
            _isLessThan = isLessThan;
            _areEqual = areEqual;
            _isGreaterThan = isGreaterThan;
        }

        public int Compare(T x, T y)
        {
            if (ReferenceEquals(x, y)) return 0;
            if (ReferenceEquals(x, null)) return -1;
            if (ReferenceEquals(y, null)) return 1;

            if (_isLessThan(x, y)) return -1;
            if (_areEqual(x, y)) return 0;
            if (_isGreaterThan(x, y)) return 1;

            // Makes the compiler very happy.
            return 0;
        }
    }

    public static IComparer<T> Create(Func<T, T, bool> isLessThan, Func<T, T, bool> areEqual, Func<T, T, bool> isGreaterThan)
    {
        return new Comparer(isLessThan, areEqual, isGreaterThan);
    }
}

Example

When I'm done filling this with data I call .Dump() on the TripleTableDto and pass it to the json-serializer to do the rest (it uses one more helper for converting my SoftString to json).

void Main()
{
    var table = new TripleTableDto(new[] { "Timestamp", "Level", "Message" });

    // add other data in different order

    table.Body.NewRow();
    table.Body.LastRow["timestamp"] = DateTime.UtcNow;
    table.Body.LastRow["message"] = "Hallo!";
    table.Body.LastRow["level"] = "Debug";

    table.Body.NewRow();
    table.Body.LastRow["level"] = "Warning";
    table.Body.LastRow["timestamp"] = DateTime.UtcNow;
    table.Body.LastRow["message"] = "Hi!";

    table.Foot.NewRow();
    //table.Foot.LastRow["message"] = 4;
    //table.Foot.LastRow["timestamp"] = 2;
    table.Foot.LastRow["level"] = 3;
    //table.Foot.LastRow["levels"] = 3; // Boom!

    // TableDto.Dump() + LINQPad.Dump()
    table.Dump().Dump();

    JsonConvert.SerializeObject(table.Dump(), new JsonSerializerSettings
    {
        Converters = { new SoftStringConverter() },
        Formatting = Newtonsoft.Json.Formatting.Indented
    }).Dump();
}

I'm sequentially adding data to this dto so there are not many APIs. Just as many as required to support this simple workflow: add a row, set its values, next... dump.

It produces the following json-ouput from

{
  "Head": [
    [
      "Timestamp",
      "Level",
      "Message"
    ]
  ],
  "Body": [
    [
      "2018-10-27T08:17:33.8372643Z",
      "Debug",
      "Hallo!"
    ],
    [
      "2018-10-27T08:17:33.8372643Z",
      "Warning",
      "Hi!"
    ]
  ],
  "Foot": [
    [
      null,
      3,
      null
    ]
  ]
}

---

The server is stupid and doesn't require much logic (yet) so it parses everything into something simpler:

internal class TableDto
{        
    public List<List<object>> Head { get; set; } = new List<List<object>>();
    public List<List<object>> Body { get; set; } = new List<List<object>>();
    public List<List<object>> Foot { get; set; } = new List<List<object>>();
}

---

In what way would you improve any of these classes? My main goal is to provide as much debugging help as possible when a mistake happens. I'd like to know primarily what went wrong and where without much effort. Preprocessing the data is tricky enough so I don't want to add any more logic on the user-side. This table-dto should be able to take care of itself. What do you think?

Answer 1

This

        Head.NewRow();
        foreach (var column in columns)
        {
            Head.LastRow[column] = column;
        }

can be optimized a bit:

        var newRow = Head.NewRow();
        foreach (var column in columns)
        {
            newRow[column] = column;
        }

---

This

table.Body.NewRow();
table.Body.LastRow["timestamp"] = DateTime.UtcNow;
table.Body.LastRow["message"] = "Hallo!";
table.Body.LastRow["level"] = "Debug";

could be replaced by a call like this:

table.Body.AddRow(("timestamp", DateTime.UtcNow), ("message", "Hallo!"), (level", "Debug"));

where AddRow looks like:

RowDto<T> AddRow(params (string Key, T Value)[] row) {...}

---

Wouldn't it be better if Head, Body and Foot shared the columns?:

public TripleTableDto(IEnumerable<SoftString> columns, bool areHeaders = true)
{
    var dtoColumns = columns.Select((name, ordinal) => new ColumnDto { Name = name, Ordinal = ordinal }).ToList(); 
    Head = new TableDto<object>(dtoColumns);
    if (areHeaders)
    {
        Head.NewRow();
        foreach (var column in columns)
        {
            Head.LastRow[column] = column;
        }
    }

    Body = new TableDto<object>(dtoColumns);
    Foot = new TableDto<object>(dtoColumns);
}

...

public TableDto(IEnumerable<ColumnDto<T>> columns)
{
    _columnByName = columns.ToDictionary(x => x.Name);
    _columnByOrdinal = columns.ToDictionary(x => x.Ordinal);
}

---

Wouldn't it be better if the columns where typesafe in a way: ColumnDto<T>? Or it's maybe too complicated and unnecessary in the context.

---

Update

I'm not sure, I really understand what you want to achieve by this, that you can't get with a simple DataTable extension:

  public static class TableExtensions
  {
    public static object Dump(this DataTable table)
    {
      return new
      {
        Headers = table.Columns.Cast<DataColumn>().Select(c => c.Caption),
        Body = table.Rows.Cast<DataRow>().Take(table.Rows.Count - 1).Select(r => r.ItemArray),
        Footer = table.Rows[table.Rows.Count - 1].ItemArray,
      };
    }
  }

void TestDataTable()
{
  DataColumn[] columns = new[]
  {
    new DataColumn("Date"),
    new DataColumn("Message", typeof(string)),
    new DataColumn("Level", typeof(int))
  };

  DataTable table = new DataTable();
  table.Columns.AddRange(columns);

  DataRow row = table.NewRow();
  row[columns[0]] = DateTime.UtcNow;
  row[columns[1]] = "Hello!";
  row[columns[2]] = 1;
  table.Rows.Add(row);

  row = table.NewRow();
  row[columns[0]] = DateTime.UtcNow + TimeSpan.FromHours(2);
  row[columns[1]] = "Goodbye!";
  //row[columns[2]] = 25;
  table.Rows.Add(row);

  row = table.NewRow();
  row[columns[0]] = "Date Footer";
  row[columns[1]] = 46;
  row[columns[2]] = "45";
  table.Rows.Add(row);

  string json = JsonConvert.SerializeObject(table.Dump(), Formatting.Indented);
  Console.WriteLine(json);
}