将JSON数据转换为dataTable [英] Converting JSON data into dataTable

问题描述

我正在尝试将嵌套的Json数组字符串转换为dataTable.我的代码运行正常，可以创建良好的数据表.但是现在客户需求发生了变化，我正在努力寻找一种方法来获得以下结构.

I am trying to convert the nested Json array string into a dataTable. My code is working fine, creating fine Data Table. But now customer requirement changes and I am struggling to find a way out to get the below structure.

要求是，只要Json值是一个数组，数据就应该作为单独的行出现.

The requirement is whenever the Json Value is an array the data should come as seperate row.

我们非常感谢您的帮助！

Any help is really appreciated !!

示例JSON结构:

{"A":"A0"，"B":{"B2":"B2- Val"，"B3":[{"B30":"B30-Val1"，"B31":"B31- Val1}]}，" C:[" C0，" C1]}

{ "A": "A0" , "B" : { "B2" : "B2- Val", "B3" : [{"B30" : "B30 - Val1" ,"B31" : "B31 - Val1"}]}, "C": ["C0", "C1"]}

当前数据表的输出结构:

Current DataTable output structure:

所需的数据表结构:

原始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;
    }

推荐答案

在这里-它并不漂亮，需要进一步测试和清理(例如，将单独的关注点放到类中并摆脱这些全局变量！)，但是它给了您你在追求什么.将下面的代码粘贴到新的控制台应用程序中(粘贴Program.cs的内容)，并添加System.Web.Extensions作为参考.

Here you go - its not pretty and needs further testing and clean up (e.g. separate concerns in to classes and get rid of those globals!) but it gives you what you are after. Paste the code below in to a new console application (paste over the content of Program.cs) and add System.Web.Extensions as a reference.

祝你好运！

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()));
                }
            }
        }
    }
}

这篇关于将JSON数据转换为dataTable的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！