Convert SQLDataReader results to JSON, with nested JSON objects

Question

I have a C# application which retrieves an SQL result set in the following format:

customer_id     date_registered     date_last_purchase      loyalty_points
1               2017-01-01          2017-05-02              51
2               2017-01-23          2017-06-21              124
...

How can I convert this to a JSON string, such that the first column (customer_id) is a key, and all other subsequent columns are values within a nested-JSON object for each customer ID?

Example:

{
        1: {
            date_registered: '2017-01-01',
            date_last_purchase: '2017-05-02',
            loyalty_points: 51,
            ...
        },
        2: {
            date_registered: '2017-01-23',
            date_last_purchase: '2017-06-21',
            loyalty_points: 124,
            ...
        },
        ...
}

Besides date_registered, date_last_purchase, and loyalty_points, there may be other columns in the future so I do not want to refer to these column names specifically. Therefore I have already used the code below to fetch the column names, but am stuck after this.

SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();

var columns = new List<string>();

for (var i = 0; i < sqlDataReader.FieldCount; i++)
{
    columns.Add(sqlDataReader.GetName(i));
}

while (sqlDataReader.Read())
{
    rows.Add(columns.ToDictionary(column => column, column => sqlDataReader[column]));
}

Answer 1

You could use something like this to convert the data reader to a Dictionary<object, Dictionary<string, object>> and then use Json.NET to convert that to JSON:

var items = new Dictionary<object, Dictionary<string, object>>();
while (sqlDataReader.Read())
{
    var item = new Dictionary<string, object>(sqlDataReader.FieldCount - 1);
    for (var i = 1; i < sqlDataReader.FieldCount; i++)
    {
        item[sqlDataReader.GetName(i)] = sqlDataReader.GetValue(i);
    }
    items[sqlDataReader.GetValue(0)] = item;
}
var json = Newtonsoft.Json.JsonConvert.SerializeObject(items, Newtonsoft.Json.Formatting.Indented);

Update: JSON "names" are always strings, so used object and GetValue for the keys.