# Preparing tabular data to be sent as json

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?

## 1 Answer

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

• I like these suggestions especially the one about making colums typesafe ;-) When I now look at it, the only T for all data isn't that useful :-| What was I thinking? ;-] – t3chb0t Oct 28 '18 at 12:08
• The problem with a DataTable is that it's a single data structure and thus it's difficult to keep track of the head, body and foot separately - I had it this way before - as mentioned in my question ;-) but I didn't like it as it doesn't represent the html table too well - especially if the foot or head can have more than one row - which I'm going to implement next - also each part will support different data-types e.g. strings for the head, various other types for the body and again numerics or strings for the foot that can be some summaries of the body. Let's see how this works :-] – t3chb0t Oct 28 '18 at 21:40
• E.g. this line will be splitted into two or three foot rows for better display effect. It's from the old times when I was working with the classic data-table. – t3chb0t Oct 28 '18 at 21:45
• @t3chb0t: OK, I won't argue about it, I'm just thinking along while trying to keep things simple, but you obviously have been there :-). My concern is often that when trying to encapsulate problems or complexity on one (abstraction) level, I just meet a different (but not lesser) complexity on that higher level as well – Henrik Hansen Oct 29 '18 at 6:49
• Sure, I'm always open to all perspectives and I highly value every suggestion ;-) I'm on your side and I actually didn't want to implement anything on my own (that's why I used a data-table at first) but this time it was too difficult to bend it for what I wanted to do with it. – t3chb0t Oct 29 '18 at 8:19