将JSON数据转换为数据表

Question

我试图将嵌套的Json数组字符串转换为dataTable。我的代码工作正常，创建精美的数据表。但是现在客户需求发生了变化，我正努力寻找出路来获得以下结构。

要求是每当Json值是一个数组时数据应该作为单独的行来。

任何帮助真的很感激！

样品JSON结构：

{ “A”： “A0”， “B”：{ “B2”： “B2- VAL”， “B3”：[{ “B30”：“B30 - VAL1 “” B31" ： “B31 - VAL1”}]}， “C”：[ “C0”， “C 1”]}

电流数据表输出结构：

必需的DataTable结构：

原始的C＃代码：

public DataTable JsonStringToDataTable(string jsonString) 
    { 
     LoggingUtil.LogMessage("GetReceiver :: JsonStringToDataTable :: Enters", LogLevel.Debug); 

     DataTable dt = new DataTable(); 
     List<string> lstColumnName = new List<string>(); 
     List<string> lstRowData = new List<string>(); 
     try 
     { 
      lstColumnName = ConvertJsonToList(jsonString, false); 
      foreach (string AddColumnName in lstColumnName) 
      { 
       DataColumnCollection columns = dt.Columns; 
       string colName = AddColumnName.ToLower(); 
       if (!columns.Contains(colName)) 
       { 
        dt.Columns.Add(colName); 
       }     
      } 
      lstRowData = ConvertJsonToList(jsonString, true); 
      DataRow nr = dt.NewRow(); 
      for (int i = 0; i < lstRowData.Count; i++) 
      { 
       try 
       { 
        string RowColumns = lstColumnName[i]; 
        string RowDataString = lstRowData[i]; 
        nr[RowColumns] = RowDataString; 
       } 
       catch (Exception ex) 
       { 
        //continue; 
        throw ex; 
       } 
      } 
      dt.Rows.Add(nr); 
     } 
     catch (Exception ex) 
     { 
      LoggingUtil.LogMessage("GetReceiver :: JsonStringToDataTable :: Error while creating datatable from JSON string :: " + ex.Message, LogLevel.Debug); 
      throw ex; 
     } 
     finally 
     { 
      LoggingUtil.LogMessage("GetReceiver :: JsonStringToDataTable :: Exits", LogLevel.Debug); 
     } 
     return dt; 
    } 
    public static List<string> ConvertJsonToList(string jsonString, bool isValue) 
    { 
     LoggingUtil.LogMessage("GetReceiver :: ConvertJsonToList :: Enters", LogLevel.Debug); 
     DataTable dt = new DataTable(); 
     var jObj = JObject.Parse(jsonString); 
     List<string> lstData = new List<string>(); 

     try 
     { 
      if (isValue) 
      { 
       lstData = AddJsonObjects(jObj, "JSON", lstData, true, false); 
      } 
      else 
      { 
       lstData = AddJsonObjects(jObj, "JSON", lstData, false, false); 
      } 
     } 
     catch (Exception ex) 
     { 
      LoggingUtil.LogMessage("GetReceiver :: ConvertJsonToList :: Error :: " + ex.Message, LogLevel.Debug); 
      throw ex; 
     } 
     finally 
     { 
      LoggingUtil.LogMessage("GetReceiver :: ConvertJsonToList :: Exits", LogLevel.Debug); 
     } 
     return lstData; 
    } 
    public static List<string> AddJsonObjects(JObject jObj, string name, List<string> ColumnsName, bool isValue, bool isArrayObject) 
    { 
     foreach (var property in jObj.Properties()) 
     { 
      string strName = name + "." + property.Name; 
      if (isArrayObject && !isValue) 
      { 
       ColumnsName = AddTokenValues(property.Value, strName, ColumnsName, isValue, true); 
      } 
      else 
      { 
       ColumnsName = AddTokenValues(property.Value, property.Name, ColumnsName, isValue, false); 
      } 
     } 
     return ColumnsName; 
    } 
    public static List<string> AddTokenValues(JToken token, string name, List<string> ColumnsName, bool isValue, bool isArrayObject) 
    { 
     if (token is JValue) 
     { 
      if (isValue) 
      { 
       string value = string.Empty; 
       if (token.Type != JTokenType.Null) 
       { 
        value = ((JValue)token).Value.ToString(); 
       } 
       ColumnsName.Add(value); 
      } 
      else 
      { 
       ColumnsName.Add(name); 
      } 
     } 
     else if (token is JArray) 
     { 
      ColumnsName = AddArrayValues((JArray)token, name, ColumnsName, isValue); 
     } 
     else if (token is JObject) 
     { 
      ColumnsName = AddJsonObjects((JObject)token, name, ColumnsName, isValue, true); 
     } 
     return ColumnsName; 
    } 
    public static List<string> AddArrayValues(JArray array, string name, List<string> dataList, bool isValue) 
    { 
     for (var i = 0; i < array.Count; i++) 
     { 
      dataList = AddTokenValues(array[i], string.Format("[{0}]", name + "[" + i.ToString() + "]"), dataList, isValue, true); 
     } 
     return dataList; 
    } 

Answer 1

在这里你去 - 它并不漂亮，需要进一步的测试和清理（例如关注课程并摆脱这些全局变量！），但它给了你后来的东西。将下面的代码粘贴到新的控制台应用程序中（粘贴Program.cs的内容）并添加System.Web.Extensions作为参考。

祝你好运！

using System; 
using System.Collections.Generic; 
using System.Data; 
using System.Web.Script.Serialization; 

namespace ConsoleApplication1 
{ 
    class Program 
    { 
     private static DataTable dt; 
     private static Dictionary<string, int> columnRowManager; 

     static void Main(string[] args) 
     { 
      //var json = "[{'firstName':'John', 'lastName':'Doe'},{'firstName':'Anna', 'lastName':'Smith'},{'firstName':'Peter','lastName': 'Jones'} ]"; 
      //var json = "{ 'glossary': { 'title': 'example glossary','GlossDiv': { 'title': 'S','GlossList': { 'GlossEntry': { 'ID': 'SGML','SortAs': 'SGML','GlossTerm': 'Standard Generalized Markup Language','Acronym': 'SGML','Abbrev': 'ISO 8879:1986','GlossDef': { 'para': 'A meta-markup language, used to create markup languages such as DocBook.','GlossSeeAlso': ['GML', 'XML'] },'GlossSee': 'markup' } } } } }"; 
      var json = "{ 'A': 'A0' , 'B' : { 'B2' : 'B2 - Val', 'B3' : [{'B30' : 'B30 - Val1' ,'B31' : 'B31 - Val1'}]}, 'C': ['C0', 'C1']}"; 
      var jss = new JavaScriptSerializer(); 
      dt = new DataTable(); 
      columnRowManager = new Dictionary<string, int>(); 

      try 
      { 
       // Deal with an object root 
       var dict = jss.Deserialize<Dictionary<string, object>>(json); 
       GetColumnsAndRowsFromJsonDictionary(dict); 
      } 
      catch (InvalidOperationException ioX) 
      { 
       // Deal with an Array Root 
       var dictionaries = jss.Deserialize<Dictionary<string, object>[]>(json); 
       foreach (var dict in dictionaries) 
       { 
        GetColumnsAndRowsFromJsonDictionary(dict); 
       } 
      } 

      DumpTableToConsole(); 
     } 

     private static void DumpTableToConsole() 
     { 
      WriteColumnsToConsole(); 
      WriteRowsToConsole(); 
      Console.ReadKey(); 
     } 

     private static void WriteRowsToConsole() 
     { 

      // Write out the Rows 
      foreach (DataRow row in dt.Rows) 
      { 
       foreach (DataColumn col in dt.Columns) 
       { 
        Console.Write(row[col.ColumnName].ToString().PadRight(12) + ","); 
       } 
       Console.WriteLine(); 
      } 
     } 

     private static void WriteColumnsToConsole() 
     { 
      foreach (DataColumn col in dt.Columns) 
      { 
       Console.Write(col.ColumnName.PadRight(12) + ","); 
      } 
      Console.WriteLine(); 
      Console.WriteLine("-------------------------------------------------------------------------------"); 
     } 

     private static void AddDataToTable(string column, string cellValue) 
     { 
      AddColumnIfNew(column); 
      int targetRowPosition = DetermineTargetRow(column); 
      AddRowIfRequired(targetRowPosition); 
      dt.Rows[targetRowPosition - 1][column] = cellValue; 
     } 

     private static void AddRowIfRequired(int targetRowPosition) 
     { 
      if (dt.Rows.Count < targetRowPosition) 
      { 
       dt.Rows.Add(); 
      } 
     } 

     private static int DetermineTargetRow(string column) 
     { 
      int targetRowPosition; 
      columnRowManager.TryGetValue(column, out targetRowPosition); 
      targetRowPosition++; 
      columnRowManager[column] = targetRowPosition; 
      return targetRowPosition; 
     } 

     private static void AddColumnIfNew(string column) 
     { 
      if (!dt.Columns.Contains(column)) 
      { 
       dt.Columns.Add(new DataColumn(column, typeof(String))); 
       columnRowManager.Add(column, 0); 
      } 
     } 

     private static void GetColumnsAndRowsFromJsonDictionary(Dictionary<string, object> dictionary) 
     { 
      // Catch the curse of recursion - null is your friend (enemy!) 
      if (dictionary == null) return; 

      foreach (var kvp in dictionary) 
      { 
       if (kvp.Value.GetType() == typeof(Dictionary<string, object>)) 
       { 
        // Process an embedded dictionary (hierarchy) 
        var subDictionary = kvp.Value as Dictionary<string, object>; 
        GetColumnsAndRowsFromJsonDictionary(subDictionary); 
       } 
       else if (kvp.Value.GetType() == typeof(System.Collections.ArrayList)) 
       { 
        ProcessArrayList(kvp); 
       } 
       else if (kvp.Value.GetType() == typeof(String)) 
       { 
        AddDataToTable(kvp.Key, kvp.Value.ToString()); 
       } 
       else 
       { 
        throw new NotSupportedException(string.Format("Err2: Type '{0}' not supported", kvp.Value.GetType().ToString())); 
       } 
      } 
     } 

     private static void ProcessArrayList(KeyValuePair<string, object> kvp) 
     { 
      // Process each independant item in the array list 
      foreach (var arrItem in kvp.Value as System.Collections.ArrayList) 
      { 
       if (arrItem.GetType() == typeof(String)) 
       { 
        AddDataToTable(kvp.Key, arrItem.ToString()); 
       } 
       else if (arrItem.GetType() == typeof(Dictionary<string, object>)) 
       { 
        var subArrDictionary = arrItem as Dictionary<string, object>; 
        GetColumnsAndRowsFromJsonDictionary(subArrDictionary); 
       } 
       else 
       { 
        throw new NotSupportedException(string.Format("Err1: Type '{0}' not supported", arrItem.GetType().ToString())); 
       } 
      } 
     } 
    } 
}