Create a tree node when serializing data

Question

I have some data queried from a SQL database and I use this code to serialize them:

List<Dictionary<string, object>> rows = new List<Dictionary<string, object>>();
DataTable dt = new DataTable();
...

SqlDataAdapter adapt = new SqlDataAdapter();
adapt.Fill(dt);

Dictionary<string, object> row;
foreach (DataRow dr in dt.Rows)
{
    row = new Dictionary<string, object>();
    foreach (DataColumn col in dt.Columns)
    {
        row.Add(col.ColumnName, dr[col]);
    }
    rows.Add(row);
}
return JsonSerializer.Serialize(rows);

It gave me this result when I serialize them:

{
    "operator": "Unknown",
    "extrainfo": "potential client",
    "Name": "John Doe",
    "ID": 568910,
    "LastUpdate": "2021-07-22T00:00:00",      
    "Interested?": "Yes",
    "Does it have a valid contract?": "No",
    "Contract type": "Prepaid",
    "Client willing to pay more?": "Yes, up to 20%",       
    "Comments": {}
}

I want all data that comes after lastUpdate column to be serialized inside another node, which is simply called interview. Here is how I want to serialize them:

{
    "operator": "Unknown",
    "extrainfo": "potential client",
    "Name": "John Doe",
    "ID": 568910,
    "LastUpdate": "2021-07-22T00:00:00",          
    "interview": [
        {
            "question" : "Interested?",
            "answer": "Yes"
        },
        {
            "question" : "Does it have a valid contract?",
            "answer": "No"
        },
        {
            "question" : "Contract type",
            "answer": "Prepaid"
        },
        {
            "question" : "Client willing to pay more?",
            "answer": "Yes, up to 20%"
        },           
        {
            "question" : "Comments",
            "answer": ""
        }
    ]
}

Here it's how a database row looks like:

I want some help on how to do this.

Answer 1

All data that comes after lastUpdate column to be serialized inside another node

After is relative:

• Your DataTable might define the columns in a different order then they should present in the json
• Serializer might use different ordering then your database schema

Filtering

I would suggest an approach where you list those fields that should be serialized as properties and treat the rest of them as interview question-answer pairs.

var propertyFields = new[] { "operator", "extrainfo", "Name", "ID", "LastUpdate" };

Capturing data

In order to create the required output (for interview) you might need to introduce a class or a struct. I've introduced a named ValueTuple to avoid creating such. But depending on your runtime environment it may or may not available. UPDATE: ValueTuples are not supported by System.Text.Json.JsonSerializer

struct Interview
{
    [JsonPropertyName("question")]
    public string Question { get; set; }
    [JsonPropertyName("answer")]
    public string Answer { get; set; }
}

Wire up

Let's put all this things together

static readonly string[] propertyFields = new[] { "operator", "extrainfo", "Name", "ID", "LastUpdate" };

...
Dictionary<string, object> row;
foreach (DataRow dr in dt.Rows)
{
    row = new Dictionary<string, object>();
    var interview = new List<Interview>();

    foreach (DataColumn col in dt.Columns)
    {
        string name = col.ColumnName;
        object value = dr[col];
        if (propertyFields.Contains(col.ColumnName))
            row.Add(name, value);
        else
            interview.Add(new Interview { Question = name, Answer = value.ToString() });

    }
    row.Add("interview", interview);
    rows.Add(row);
}