JSON.NET：如何在不使用数组的情况下从DataTable对象中仅序列化一行？

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.

我有一个数据库类,它调用数据库并检索数据。使用SqlDataAdapter将数据加载到DataTable对象中。然后我想只获取第一行数据(事实上,数据库只会返回一行)并将其转换为JSON字符串。

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:

问题是:如果我只是将DataTable传递给JsonConvert.SerializeObject方法,例如JsonConvert.SerializeObject(dt),我得到一个JSON字符串,但它是一个带有一个对象的数组。例如:

[ {"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:

期望接收此JSON的另一端的代码不需要数组,它只需要一个对象。所以基本上我需要上面的,但没有方括号[和]。所以期望的输出是:

{"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.

我现在使用的天真方法是将result.Substring(1,result.Length - 2)返回给调用者。假设JSON对象存在于仅包含一个对象的单个数组中,这具有剥离[和]关闭返回文本的效果。但似乎应该有一种“适当”的方式来实现这一目标。

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.

最明显的事情似乎是使用DataTable的Rows属性。但是,如果我尝试做JsonConvert.SerializeObject(dt.Rows [0]),我不会只得到行,我得到一个关于行状态的整个数据集合,其中一个项目是Table但是只是引用整个表,所以如果出现多行,我会获得所有行,而不仅仅是我想要的行。

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.

我尝试过的另一种方法是从整个DataTable往返JSON。我序列化DataTable,然后将结果解析成JArray,这样我就可以获得第一个值,然后再次重新序列化。但这似乎没必要。

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

以下是尝试序列化Row对象的原因:

{
    "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?

我可以用“正确”的方式做到这一点,而不只是剥离[和]字符串吗?换句话说,我可以将DataTable的一行中的数据作为单个JSON对象获取,而不是在数组中,而不是通过JSON进行往返,而只是将其从一项数组中取出来吗?

2 个解决方案

#1

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

您可以使用LINQ-to-JSON API从DataRow构建JObject。例如:

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

#2

As long as you are only going to receive one row from the query/DataTable, in JSON.Net you can use a private setter that accepts the Table structure as a List and a public property that returns only the first row as an object like this:

只要你只从查询/ DataTable接收一行,在JSON.Net中你可以使用一个私有的setter接受Table结构作为List和一个公共属性,它只返回第一行作为像这样的对象:

using Newtonsoft.Json;
using System.Collections.Generic;
using System.Linq;

public class DataTableToModel
{
    /// <summary>
    /// Deserialize from DataTable, set ModelProperty from first row
    /// </summary>
    [JsonProperty("Table")]
    private List<Model> ModelSetter { set { this.ModelProperty = value.FirstOrDefault(); } }

    /// <summary>
    /// Serialize Model
    /// </summary>
    [JsonProperty("Model")]
    public Model ModelProperty { get; set; }
}

/// <summary>
/// Model that has id and value properties expected from DataTable
/// </summary>
public class Model 
{
    [JsonProperty("id")]
    public string id { get; set; }

    [JsonProperty("value")]       
    public string value { get; set; }
}

To utilize this, you need to add your DataTable to a DataSet, and JsonConvert.SerializeObject the DataSet, then JsonConvert.DeserializeObject on the ModelProperty object like this:

要利用它,您需要将DataTable添加到DataSet,将JsonConvert.SerializeObject添加到DataSet,然后将ModelProperty对象上的JsonConvert.DeserializeObject添加到:

DataSet ds = new DataSet();
DataTable dt = new DataTable();
dt.TableName = "Table";
dt.Columns.Add("id");
dt.Columns.Add("value");
dt.Rows.Add("1", "hi");
ds.Tables.Add(dt);

var dsString = JsonConvert.SerializeObject(ds);     
var model = JsonConvert.DeserializeObject<DataTableToModel>(dsString);              
var json = JsonConvert.SerializeObject(model.ModelProperty);

Here is a dotnetfiddle example.

这是一个dotnetfiddle示例。

#1