JSON.NET: How to serialize just one row from a DataTable object without it being in an array?

Question

I have a database class that calls into a database and retrieves data. The data is loaded into a DataTable object with an SqlDataAdapter. I then want to take only the first row of data (in truth, the database will only ever return one row anyway) and convert it to a JSON string.

The problem is this: If I simply pass the DataTable into the JsonConvert.SerializeObject method, e.g. JsonConvert.SerializeObject(dt), I get a JSON string but it is an array with one object. For example:

[ {"id":"1","value":"hi"} ]

The code on the other end that expects to receive this JSON does not want an array, it simply wants an object. So basically I need the above, but without the square brackets [ and ]. So the desired output is:

{"id":"1","value":"hi"}

The naive approach I am using right now is to just return result.Substring(1,result.Length - 2) to the caller. Assuming that the JSON object exists in a single array of just one object, this has the effect of stripping the [ and ] off the return text. But it seems like there should be a "proper" way to achieve this.

The most obvious thing would seem to be using the Rows property of the DataTable. However, if I try to do JsonConvert.SerializeObject(dt.Rows[0]), I don't get just the row, I get an entire collection of data about the state of the row, with one item being Table but which simply references the entire table, so in the event that more than one row occurred I would get all the rows, not just the one I desired.

The other approach I have tried is to round-trip into JSON from the entire DataTable. I serialize the DataTable, then parse the result into into a JArray so I can get the first value, then reserialize again. But this seems unnecessary.

Here's what trying to serialize a Row object gives me:

{
    "RowError":"",
    "RowState":2,
    "Table":[
        {
            "id":"1",
            "value":"hello"
        }
    ],
    "ItemArray":[
        "1","hello"
    ],
    "HasErrors":false
}

Can I do this the "correct" way without just stripping the [ and ] off the string? In other words, can I get the data from one row of a DataTable as a single JSON object, not in an array, without roundtripping through JSON and back again just to pick it off the one-item array?

Answer 1

You could use the LINQ-to-JSON API to build up a JObject from the DataRow. For example:

DataTable dt = new DataTable();
dt.Columns.Add("id");
dt.Columns.Add("value");
dt.Rows.Add("1", "hi");

string json = new JObject(
    dt.Columns.Cast<DataColumn>()
      .Select(c => new JProperty(c.ColumnName, JToken.FromObject(dt.Rows[0][c])))
).ToString(Formatting.None);

Console.WriteLine(json);

Output:

{"id":"1","value":"hi"}

Fiddle: https://dotnetfiddle.net/ndL1xu