将JSON字符串转换为数据表? [英] convert json String to datatable?
问题描述
在转换json字符串数据表时，值字段中的，(逗号)值存在问题.
While converting json string datatable facing an issue with , (comma) value in value field.
准确地说，我的json字符串是[{"BNo":"345","GNo":"3453","FirstName":"fjai","LastName":"ljai","Address":"BARETI,CEVO, 13/2","Telephone":"051682247","BirthDate":"23-Jan-1981","Email":""}]
actualy my json string is [{"BNo":"345","GNo":"3453","FirstName":"fjai","LastName":"ljai","Address":"BARETI,CEVO, 13/2","Telephone":"051682247","BirthDate":"23-Jan-1981","Email":""}]
在这种情况下，请查看地址方案"Address":"BARETI,CEVO, 13/2"
In that please look at the address scenario "Address":"BARETI,CEVO, 13/2"
在值字段中具有.将字符串转换为数据库时出现错误.这是我使用的代码将json字符串转换为datatable
It has the , in the values field. While converting the string to data base i got error. Here the code which i used convert json string to datatable
public DataTable JsonStringToDataTbl(string jsonString)
{
DataTable dt = new DataTable();
string[] jsonStringArray = Regex.Split(jsonString.Replace("[", "").Replace("]", ""), "},{");
List<string> ColumnsName = new List<string>();
foreach (string jSA in jsonStringArray)
{
string[] jsonStringData = Regex.Split(jSA.Replace("{", "").Replace("}", ""), ",");
foreach (string ColumnsNameData in jsonStringData)
{
try
{
int idx = ColumnsNameData.IndexOf(":");
string ColumnsNameString = ColumnsNameData.Substring(0, idx - 1).Replace("\"", "");
if (!ColumnsName.Contains(ColumnsNameString))
{
ColumnsName.Add(ColumnsNameString);
}
}
catch (Exception ex)
{
throw new Exception(string.Format("Error Parsing Column Name : {0}", ColumnsNameData));
}
}
break;
}
foreach (string AddColumnName in ColumnsName)
{
dt.Columns.Add(AddColumnName);
}
foreach (string jSA in jsonStringArray)
{
string[] RowData = Regex.Split(jSA.Replace("{", "").Replace("}", ""), ",");
DataRow nr = dt.NewRow();
foreach (string rowData in RowData)
{
try
{
int idx = rowData.IndexOf(":");
string RowColumns = rowData.Substring(0, idx - 1).Replace("\"", "");
string RowDataString = rowData.Substring(idx + 1).Replace("\"", "");
nr[RowColumns] = RowDataString;
}
catch (Exception ex)
{
continue;
}
}
dt.Rows.Add(nr);
}
return dt;
}
代码必须在value字段中省略..我该怎么办
The code must omit the , in the value field.. what can i do
推荐答案
If your keys are unknown at the time of being read, then you can use the JObject and the JProperty classes from JSON.Net to retrieve the keys and their values like this:
private void printKeysAndValues(string json)
{
var jobject = (JObject)((JArray)JsonConvert.DeserializeObject(json))[0];
foreach (var jproperty in jobject.Properties())
{
Console.WriteLine("{0} - {1}", jproperty.Name, jproperty.Value);
}
}
应用于两个不同的JSON输入字符串，检索键/值对:
Applied to two different JSON input string, retrieves the key/value pair:
var json1 = @"[{""BNo"":""345"",""GNo"":""3453"",""FirstName"":""fjai"",""LastName"":""ljai"",""Address"":""BARETI,CEVO, 13/2"",""Telephone"":""051682247"",""BirthDate"":""23-Jan-1981"",""Email"":""""}]";
var json2 = @"[{""Test"": ""A"", ""Text"":""some text"", ""Numbers"":""123""}]";
printKeysAndValues(json1);
Console.WriteLine("-------------------");
printKeysAndValues(json2);
输出为:
BNo - 345
GNo - 3453
FirstName - fjai
LastName - ljai
Address - BARETI,CEVO, 13/2
Telephone - 051682247
BirthDate - 23-Jan-1981
Email -
-------------------
Test - A
Text - some text
Numbers - 123
一种可能性是使用 dynamic 关键字.您可以像这样直接访问该字段:
One possibility would be to use the dynamic keyword. You can directly access the field like this:
var json = @"[{""BNo"":""345"",""GNo"":""3453"",""FirstName"":""fjai"",""LastName"":""ljai"",""Address"":""BARETI,CEVO, 13/2"",""Telephone"":""051682247"",""BirthDate"":""23-Jan-1981"",""Email"":""""}]";
dynamic data = JsonConvert.DeserializeObject(json);
// take the first element of the array
string address = data[0].Address;
Console.WriteLine(address.Replace(",", " "));
输出为:
BARETI CEVO 13/2
请注意，String.Replace不会失败，如果当前不存在应替换的符号，则"test".Replace(",", " ");将返回test.
Note that String.Replace does not fail, if the symbol that should be replaced is not currently present, so "test".Replace(",", " "); will return test.
另一种可能性是使用在ASP.NET中内置的JSON转换器(serializer/deserializer)- NewtonSoft JSON.Net .您可以使用它来重新获得结构化数据.您需要创建一个表示JSON结构的类:
Another possibility is to use the in ASP.NET build in JSON converter (serializer/deserializer) - NewtonSoft JSON.Net. You can use it in order to regain the structured data. You need to create a class that represents the JSON structure:
public class Data
{
public string BNo { get; set; }
public string GNo { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public string Address { get; set; }
public string Telephones { get; set; }
public string BirthDates { get; set; }
public string Emails { get; set; }
}
然后可以使用Data的对象> JsonConvert.DeserializeObject 方法:
Then the current JSON can be converted to an object of type Data using the JsonConvert.DeserializeObject method:
var json = @"[{""BNo"":""345"",""GNo"":""3453"",""FirstName"":""fjai"",""LastName"":""ljai"",""Address"":""BARETI,CEVO, 13/2"",""Telephone"":""051682247"",""BirthDate"":""23-Jan-1981"",""Email"":""""}]";
// remove square braces [ and ] at the start resp. end
var data = JsonConvert.DeserializeObject<Data>(json.Substring(1).Substring(0, json.Length - 2));
现在您可以访问Address字段，例如替换,符号:
Now you can access the Address field and for example replace the , symbol:
Console.WriteLine(data.Address.Replace(",", " "));
输出为:
BARETI CEVO 13/2
我认为您的服务还会返回错误的JSON格式. JSON始终以对象开头(如果不是在JavaScript中)，这意味着必须将所有顶级内容括在大括号{和}中.如果服务应返回一个数组，则其外观应类似于此{"results": [{"BNo":"...},{...}]}.如果您无法更改服务，则可以修改/更正返回的字符串.为数组添加类型化的模型:
I think your service returns also the wrong JSON format. JSON always starts with an object (when not in JavaScript), meaning that everything at the top level must be enclosed within curly braces { and }. If the service should return an array, then it should look like this {"results": [{"BNo":"...},{...}]}. If you can't change the service, then you can adapt / correct the returned string. Add a typed model for the array:
public class DataHolder
{
public Data[] data { get; set; }
}
，然后创建一个包含数组的正确JSON对象:
and then create a correct JSON object holding an array:
var data = JsonConvert.DeserializeObject<DataHolder>("{\"data\":" + json + "}");
Console.WriteLine(data.data[0].Address.Replace(",", " "));
输出再次相同.
这篇关于将JSON字符串转换为数据表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
