将API调用Json URL字符串(Json对象)转换为C#数据表(不使用任何带有getter、setter或poco的

Question

我是API调用字符串解析C#的新手，我需要从API调用中获取Json字符串，并将其转换为C#数据表，然后使用Asp.NetGridView在网页中显示结果。

我试了好几种方法。我在每种方法中都面临着不同类型的问题，只有我的方法4(如下所述)才成功。但我不想创建一个单独的类(带有getter和setters)并将Json字符串转换为该类的对象。由于Json文件中的每条记录都有150多个字段，而且对象名称是动态的，所以它们是随机生成的。我不想要一对一映射(Class字段到Json字段)。

这是我的Json文件格式，

{
"R_aabdcDgjZwp0ch":{ Record 1 information key value pair } // Here R_aabdcDgjZwp0ch are randomly generated value
"R_lkYnksdY6qXaPb":{ Record 2 information key value pair } //R_lkYnksdY6qXaPb random
………
"R_7GhjsnB29xWBjp":{ Record n information key value pair }//R_7GhjsnB29xWBjp not fixed value
}

Json字符串中的示例记录：

{
"R_3dSKpqkb0JuH0TW":{"ResponseSet":"Default Response Set","Name":"John, Smith","ExternalDataReference":"811221273","EmailAddress":"[e-mail]","IPAddress":"123.232.12.21","Status":"","StartDate":"2015-07-06 11:10:26","EndDate":"2015-07-06 11:10:55","Finished":"1","RecipientEmail":"[e-mail]","RecipientLastName":"John","RecipientFirstName":"Smith","MI":"Mia","EntryTerm":"","Classification":"","Type":"","MajorCode":"","Major":"","DeptCode":"","Dept":"","College":"","Age":"","Ethnicity":"","Gender":"","CB1":"","PIDM":"71121027","Military":"","OrientationDate":"4/7/2016","H1":1,"H2":1,"H3":2,"H4":2,"H5":"","Q1":"","Q2":"","Q3_1":"","Q3_2":"","Q3_3":"","Q3_4":"","Q3_5":"","Q3_6":"","Q3_7":"","Q3_7_TEXT":"","Q4_1":"","Q4_2":"","Q4_3":"","Q4_4":"","Q4_5":"","Q4_6":"","Q4_7":"","Q4_8":"","Q4_9":"","Q4_10":"","Q4_11":"","Q4_12":"","Q4_12_TEXT":"","Q5":"","Q5_TEXT":"","Q6_1":"","Q6_2":"","Q6_3":"","Q7":"","Q8":"","Q9":"","Q10_1":"","Q10_2":"","Q10_3":"","Q10_4":"","Q11_1":"","Q11_2":"","Q11_3":"","Q11_4":"","Q12_1":"","Q12_2":"","Q12_3":"","Q12_4":"","Q13":"","Q13_TEXT":"","Q14":"","Q14_TEXT":"","Q15_1":"","Q15_2":"","Q15_3":"","Q15_4":"","Q15_5":"","Q15_6":"","Q15_7":"","Q15_8":"","Q16_1":"","Q16_2":"","Q16_3":"","Q16_4":"","Q16_5":"","Q16_6":"","Q16_7":"","Q16_8":"","Q17_1":"","Q17_2":"","Q17_3":"","Q17_4":"","Q17_5":"","Q17_6":"","H6":"","Q18_1":"","Q18_2":"","Q18_3":"","Q19_1":"","Q19_2":"","Q19_3":"","Q19_4":"","Q20":"","Q21_1":"","Q21_2":"","Q22_1":"","Q22_2":"","Q23_1":"","Q23_2":"","Q23_3":"","Q23_4":"","Q24":"","Q24_TEXT":"","Q25":"","Q26":"","Q27":"","Q28":"","H7":"","Q29_1":"","Q29_2":"","Q29_3":"","Q29_4":"","Q29_5":"","Q30":"","Q30_TEXT":"","Q31":"","Q31_TEXT":"","Q32":"","Q33_1":"","Q33_2":"","Q33_3":"","Q33_4":"","Q33_5":"","Q33_6":"","Q33_6_TEXT":"","Q34":"","Q34_TEXT":"","Q35":"","Q35_TEXT":"","Q36_1":"","Q36_2":"","Q36_3":"","Q36_4":"","Q36_5":"","Q36_6":"","Q36_7":"","Q36_7_TEXT":"","Q37":"","H8":1,"H9":1},
"R_1kYrTV300hwdvPP":{"ResponseSet":"Default Response Set","Name":"priya, Sam","ExternalDataReference":"8901212","EmailAddress":"[e-mail]","IPAddress":"123.232.12.21","Status":"","StartDate":"2015-07-06 11:14:18","EndDate":"2015-07-06 11:14:59","Finished":"1","RecipientEmail":"[e-mail]","RecipientLastName":"sam","RecipientFirstName":"priya","MI":"","EntryTerm":"","Classification":"","Type":"","MajorCode":"","Major":"","DeptCode":"","Dept":"","College":"","Age":"","Ethnicity":"","Gender":"","CB1":"","PIDM":"71121028","Military":"","OrientationDate":"6/27/2016","H1":1,"H2":1,"H3":2,"H4":2,"H5":"","Q1":"","Q2":"","Q3_1":"","Q3_2":"","Q3_3":"","Q3_4":"","Q3_5":"","Q3_6":"","Q3_7":"","Q3_7_TEXT":"","Q4_1":"","Q4_2":"","Q4_3":"","Q4_4":"","Q4_5":"","Q4_6":"","Q4_7":"","Q4_8":"","Q4_9":"","Q4_10":"","Q4_11":"","Q4_12":"","Q4_12_TEXT":"","Q5":"","Q5_TEXT":"","Q6_1":"","Q6_2":"","Q6_3":"","Q7":"","Q8":"","Q9":"","Q10_1":"","Q10_2":"","Q10_3":"","Q10_4":"","Q11_1":"","Q11_2":"","Q11_3":"","Q11_4":"","Q12_1":"","Q12_2":"","Q12_3":"","Q12_4":"","Q13":"","Q13_TEXT":"","Q14":"","Q14_TEXT":"","Q15_1":"","Q15_2":"","Q15_3":"","Q15_4":"","Q15_5":"","Q15_6":"","Q15_7":"","Q15_8":"","Q16_1":"","Q16_2":"","Q16_3":"","Q16_4":"","Q16_5":"","Q16_6":"","Q16_7":"","Q16_8":"","Q17_1":"","Q17_2":"","Q17_3":"","Q17_4":"","Q17_5":"","Q17_6":"","H6":"","Q18_1":"","Q18_2":"","Q18_3":"","Q19_1":"","Q19_2":"","Q19_3":"","Q19_4":"","Q20":"","Q21_1":"","Q21_2":"","Q22_1":"","Q22_2":"","Q23_1":"","Q23_2":"","Q23_3":"","Q23_4":"","Q24":"","Q24_TEXT":"","Q25":"","Q26":"","Q27":"","Q28":"","H7":"","Q29_1":"","Q29_2":"","Q29_3":"","Q29_4":"","Q29_5":"","Q30":"","Q30_TEXT":"","Q31":"","Q31_TEXT":"","Q32":"","Q33_1":"","Q33_2":"","Q33_3":"","Q33_4":"","Q33_5":"","Q33_6":"","Q33_6_TEXT":"","Q34":"","Q34_TEXT":"","Q35":"","Q35_TEXT":"","Q36_1":"","Q36_2":"","Q36_3":"","Q36_4":"","Q36_5":"","Q36_6":"","Q36_7":"","Q36_7_TEXT":"","Q37":"","H8":1,"H9":1}
}

我已经尝试了几种将Json url字符串解析/使用到Datatable的方法。我收到以下错误。

方法1:使用Json.Net并使用Newtonsoft.Json.JsonConvert.DeserialeObject 直接从Json字符串转换为数据表

样本代码：

string url ="test.com/json...etc"; //Here actual url to call api
var json_data = string.Empty;
// attempt to download JSON data as a string
json_data = w.DownloadString(url); // we are passing API url here
DataTable items = JsonConvert.DeserializeObject<DataTable>(json_data); // Exception coming here

异常：Newtonsoft.Json.JsonSerializationException：读取DataTable时出现意外的Json令牌。应为StartArray，实际为StartObject。路径"，第1行，位置1。

方法2:我使用过http://json2csharp.com/#(将Json输入文件/Json url转换为构造类，该构造类将具有getter和setter以及Root Object来访问子类中的数据)。但我最后有个例外。但是我不想要这种方法。

异常：Newtonsoft.Json.JsonSerializationException：无法将当前Json对象(例如｛"name"："value"｝)反序列化为类型"System.Collections.Generic.List`1[matric+RANFpZfdGjZwp0ch]"，因为该类型需要Json数组(例如[1，2，3])才能正确反序列化。要修复此错误，请将JSON更改为JSON数组(例如[1，2，3])，或者更改反序列化的类型，使其成为可以从JSON对象反序列化的普通.NET类型(例如，不是像integer这样的基元类型，也不是像array或List这样的集合类型)。JsonObjectAttribute也可以添加到类型中，以强制它从JSON对象反序列化。路径"R_ANFpZfdGjZwp0ch"，第1行，位置21。

方法3:我尝试使用以下方法。但以一个例外结束。

DataTable dt = (DataTable)JsonConvert.DeserializeObject(json, (typeof(DataTable)));

异常：Newtonsoft.Json.JsonSerializationException：读取DataTable时出现意外的Json令牌。应为StartArray，实际为StartObject。路径"，第1行，位置1。下面尝试解决方法3的问题。但没用。

1. Newtonsoft.Json Json转换为数据表
2. http://www.codeproject.com/Questions/817608/Newtonsoft-Json-JsonConvert-To-Datatable
3. 将JSON字符串转换为DataTable
4. 使用Json.NET进行解析："；意外的标记：StartObject">

方法4:通过这种方法，我成功地使用了带有getter和setter的构造类。

string url ="test.com/json...etc"; //Here actual url to call api
using (var w = new WebClient())
{
var json = string.Empty;
// attempt to download JSON data as a string
try
{
json = w.DownloadString(url);
}
catch (Exception) { }

User obj = new User(json);
Response.Write(obj.name);
}

public class User
{
/********* Used from https://stackoverflow.com/questions/2246694/how-to-convert-json-object-to-custom-c-sharp-object **********/
public User(string json)
{
JObject jObject = JObject.Parse(json);
JToken jUser = jObject["R_XYZanOp0ch"]; //R_XYZanOp0ch this value is randomly generated. I gave it constant to check for one record. In actual Json file there are so many randomly generated object names exist.
name = (string)jUser["Name"];
email = (string)jUser["Email"];
ExternalDataReference = (string)jUser["NumberReference"];
}
public string name { get; set; }
public string ExternalDataReference { get; set; } // student ID
public string email { get; set; }

}

我使用方法4得到了一些结果。但问题是，我们有150多个字段，我们不想为每个字段设置getter和setter，对象名称是动态的，而不是固定的。不幸的是，我们需要所有字段的数据。

以下是我用于上述方法的参考资料。

1. 将Json字符串转换为C#对象列表
2. 将JSON转换为DataTable
3. 如何将json转换为datatable

感谢阅读。抱歉我发了很长的短信。不要认为这篇文章是重复的，因为我尝试了所有的方法，但仍然没有得到想要的结果，所以在这里发帖。有人能根据我的Json字符串结构帮助我或指导我吗？我需要更改任何代码吗？任何样本或阅读笔记都会有所帮助。

谢谢。

更新：我同时尝试了这两种方法，得到了System.FormatException：数组中的输入字符串格式不正确。ToObject()；如果我错过了中间的任何一步，请告诉我。

var obj = JObject.Parse(json);
var array = new JArray(obj.Values());
//Response.Write("<br/>array[0]" + array[0].ToString()); // I could able to view the record 1 data 
var dt = array.ToObject<DataTable>(); // Having issue here.

Answer 1

Json.NET有一个用于DataTable的内置转换器。它将表格格式化为数组，如下所示：

[
{
"Column1Name" : value11,
"Column2Name" : value21
},
{
"Column1Name" : value12,
"Column2Name" : value22
}, 
// And so on
}

您所拥有的是一个带有随机键的字典，而不是数组，因此您需要在反序列化之前将JSON转换为数组。这可以通过LINQ到JSON来完成。如果你不需要随机密钥名称，你可以做：

var obj = JObject.Parse(json);
var array = new JArray(obj.Values());
var dt = array.ToObject<DataTable>();

如果您需要随机密钥名称，可以将它们作为列添加到DataTable中，如下所示：

var obj = JObject.Parse(json);
string keyColumnName = "__key";
var query = from p in obj.Properties()
select new JObject(p.Value.OfType<JProperty>().Concat(new [] { new JProperty(keyColumnName, p.Name) }));
var array = new JArray(query);
var dt = array.ToObject<DataTable>();

样品小提琴。

Answer 2

谢谢dbc。我很感激您的投入，它帮助我找到了Json到DataTable之间的中间层(将Json转换为Array)。

我稍微修改了您的代码，并使用了来自的数据表转换方法调用https://stackoverflow.com/a/24339121/4425471

在移动时，我们忽略了随机键。最后我得到了我想要的结果。我可以将Json转换为DataTable，然后再转换为GridView。

这是我的最终代码。谢谢你。

string json = "test.com/json..."; // actual API call url
DataTable dt = toDataTable(json);
GridView1.DataSource = dt;
GridView1.DataBind();

public static DataTable toDataTable(string json)
{
var result = new DataTable();
var obj = JObject.Parse(json);
var jArray = new JArray(obj.Values());
//Initialize the columns
foreach (var row in jArray)
{
foreach (var jToken in row)
{
var jproperty = jToken as JProperty;
if (jproperty == null) continue;
if (result.Columns[jproperty.Name] == null)
result.Columns.Add(jproperty.Name,typeof(string));
}
}
foreach (var row in jArray)
{
var datarow = result.NewRow();
foreach (var jToken in row)
{
var jProperty = jToken as JProperty;
if (jProperty == null) continue;
datarow[jProperty.Name] = jProperty.Value.ToString();
}
result.Rows.Add(datarow);
}
return result;
}