Json / Xml to DataSet

Json / Xml 문자열을 DataSet 에 넣는 코드

(5년전 작성한 블로그글에서 발췌한것으로 코드가 지저분하고 Json 이나 Xml 문자열에 따라 오류발생 가능)

 

JObject인경우와 JArray 인 경우는 재귀되도록 구현

 

(하나의 테이블에 담아야 하는 기능이 필요하여 개발한것으로 여러 JObject 나 JArray 에서 명칭이 같은 것이 있으면 오류)

-> 수정

eachTable 이 true 이면 각 Object 별로 테이블 생성

false 이면 기존과 동일하게 하나의 테이블에 삽입

(맨 윗단의 Object 까지만 각각 테이블 생성하도록 한것으로 내부에 있는 Object 중복은 오류)

 

JsonToDataSet.zip

0.23MB

 

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using System.Xml.Linq;
using System.IO;


namespace JsonToDataSet
{
    public partial class Form1 : Form
    {
        private bool m_eachTable = false;
        public Form1()
        {
            InitializeComponent();
        }

        private void textBox1_TextChanged(object sender, EventArgs e)
        {

        }

        private void label1_Click(object sender, EventArgs e)
        {

        }

        private void button1_Click(object sender, EventArgs e)
        {
            textBox2.Text = "";
            this.PrintRows(JsonToDataSet(textBox1.Text, m_eachTable));
        }
        private void button2_Click(object sender, EventArgs e)
        {
            textBox2.Text = "";
            XElement xEle = XElement.Parse(textBox1.Text);
            DataSet ds = new DataSet();
            ds.ReadXml(new StringReader(new XElement("a", xEle).ToString()));
            this.PrintRows(ds);
        }

        private void PrintRows(DataSet dataSet)
        {
            foreach (DataTable table in dataSet.Tables)
            {
                textBox2.Text += table.TableName + "\r\n";
                foreach (DataRow row in table.Rows)
                {
                    foreach (DataColumn column in table.Columns)
                    {
                        textBox2.Text += column.ColumnName + " : " + row[column].ToString() + "\r\n";
                    }
                    textBox2.Text += "\r\n";
                }
                textBox2.Text += "\r\n";
            }

        }
        public DataSet JsonToDataSet(string jsonString)
        {
            return JsonToDataSet(jsonString, false);
        }

        public DataSet JsonToDataSet(string jsonString, bool eachTable)
        {
            DataSet ds = new DataSet("ROOT");
            DataTable dt = new DataTable();
            JArray trgArray = null;
            jsonString = "{\"row\":" + jsonString + "}";
            var jsonLinq = JObject.Parse(jsonString);

            if (jsonLinq.Descendants().First() != null)
            {
                try
                {
                    JToken srcArrayStatus = jsonLinq.Descendants().First();
                    trgArray = new JArray();
                    foreach (JObject row in srcArrayStatus.Children<JObject>())
                    {
                        var cleanRow = new JObject();

                        foreach (JProperty column in row.Properties())
                        {
                            JsonConvert.SerializeObject(ds, Formatting.Indented);
                            cleanRow = JTokenIntoDataSet(column, cleanRow);
                            if (eachTable && cleanRow.Count != 0) //각 Obejct 별로 Table 생성
                            {
                                trgArray.Add(cleanRow);
                                DataTable dt2 = new DataTable();
                                dt2 = JsonConvert.DeserializeObject<DataTable>(trgArray.ToString());
                                dt2.TableName = column.Name;
                                ds.Tables.Add(dt2);
                                cleanRow.RemoveAll();
                                trgArray.RemoveAll();
                            }
                        }
                        trgArray.Add(cleanRow);
                    }
                    if (!eachTable && trgArray.Count != 0)
                    {
                        dt = JsonConvert.DeserializeObject<DataTable>(trgArray.ToString());
                        dt.TableName = "DataBlock1";
                        ds.Tables.Add(dt);
                    }
                }
                catch (Exception ex)
                { }
            }
            return ds;
        }
        public JObject JTokenIntoDataSet(JToken jToken, JObject cleanRow)
        {  
            if ((jToken as JProperty).Value != null) // "name" : "value"
            {
                if ((jToken as JProperty).Value is JValue)
                {
                    cleanRow.Add((jToken as JProperty).Name, (jToken as JProperty).Value);
                }
                else if ((jToken as JProperty).Value is JObject) // "name" : { "name" : "value", ... }
                {
                    foreach (JToken row in (jToken as JProperty).Value.Children<JToken>())
                    {
                        cleanRow = JTokenIntoDataSet(row, cleanRow);
                    }
                }
                else if ((jToken as JProperty).Value is JArray)
                {
                  
                    JToken srcArrayData = ((jToken as JProperty).Value as JArray).Descendants().First();
                    while (srcArrayData.Next == null)
                    {
                        foreach (JToken row in srcArrayData.Children<JToken>())
                        {
                            JToken jtk = row;
                            cleanRow = JTokenIntoDataSet(jtk, cleanRow);
                        }
                        srcArrayData = srcArrayData.Next;
                    }
                }
            }
            return cleanRow;
        }

        private void Form1_Load(object sender, EventArgs e)
        {

        }

        private void label2_Click(object sender, EventArgs e)
        {

        }

        private void checkBox1_CheckedChanged(object sender, EventArgs e)
        {
            if(m_eachTable)
            {
                m_eachTable = false;
            }
            else
            {
                m_eachTable = true;
            }
           
        }
    }
}

 

 

--

리팩토링

 

using System;
using System.Data;
using System.IO;
using System.Linq;
using System.Windows.Forms;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using System.Xml.Linq;

namespace JsonToDataSet
{
    public partial class Form1 : Form
    {
        private bool m_eachTable = false;

        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            textBox2.Clear();
            var ds = JsonToDataSet(textBox1.Text, m_eachTable);
            PrintRows(ds);
        }

        private void button2_Click(object sender, EventArgs e)
        {
            textBox2.Clear();
            XElement xEle = XElement.Parse(textBox1.Text);
            DataSet ds = new DataSet();
            ds.ReadXml(new StringReader(new XElement("Root", xEle).ToString()));
            PrintRows(ds);
        }

        private void PrintRows(DataSet dataSet)
        {
            foreach (DataTable table in dataSet.Tables)
            {
                textBox2.AppendText(table.TableName + Environment.NewLine);
                foreach (DataRow row in table.Rows)
                {
                    foreach (DataColumn column in table.Columns)
                    {
                        textBox2.AppendText($"{column.ColumnName} : {row[column]}{Environment.NewLine}");
                    }
                    textBox2.AppendText(Environment.NewLine);
                }
                textBox2.AppendText(Environment.NewLine);
            }
        }

        public DataSet JsonToDataSet(string jsonString, bool eachTable = false)
        {
            var ds = new DataSet("ROOT");

            try
            {
                // JSON 루트 보정
                var wrappedJson = "{ \"row\": " + jsonString + " }";
                var root = JObject.Parse(wrappedJson);

                var rows = root["row"] as JArray ?? new JArray(root["row"]);
                var table = new DataTable("DataBlock1");

                foreach (var row in rows.Children<JObject>())
                {
                    var cleanRow = ExtractFields(row);

                    if (eachTable && cleanRow.Count > 0)
                    {
                        var dt = JsonConvert.DeserializeObject<DataTable>(new JArray(cleanRow).ToString());
                        dt.TableName = row.Path.Split('.').LastOrDefault() ?? "Table";
                        ds.Tables.Add(dt);
                    }
                    else
                    {
                        table = EnsureTableSchema(table, cleanRow);
                        var newRow = table.NewRow();
                        foreach (var col in cleanRow.Properties())
                        {
                            newRow[col.Name] = col.Value.ToString();
                        }
                        table.Rows.Add(newRow);
                    }
                }

                if (!eachTable && table.Rows.Count > 0)
                {
                    ds.Tables.Add(table);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("JSON 변환 실패: " + ex.Message);
            }

            return ds;
        }

        /// <summary>
        /// JSON 객체 → Key-Value 평탄화
        /// </summary>
        private JObject ExtractFields(JToken token, JObject result = null, string prefix = "")
        {
            result ??= new JObject();

            if (token is JObject obj)
            {
                foreach (var prop in obj.Properties())
                {
                    ExtractFields(prop.Value, result, prefix + prop.Name + "_");
                }
            }
            else if (token is JArray arr)
            {
                int idx = 0;
                foreach (var item in arr)
                {
                    ExtractFields(item, result, prefix + idx + "_");
                    idx++;
                }
            }
            else if (token is JValue val)
            {
                result[prefix.TrimEnd('_')] = val.ToString();
            }

            return result;
        }

        /// <summary>
        /// DataTable 스키마 자동 확장
        /// </summary>
        private DataTable EnsureTableSchema(DataTable table, JObject row)
        {
            foreach (var prop in row.Properties())
            {
                if (!table.Columns.Contains(prop.Name))
                {
                    table.Columns.Add(prop.Name, typeof(string));
                }
            }
            return table;
        }

        private void checkBox1_CheckedChanged(object sender, EventArgs e)
        {
            m_eachTable = checkBox1.Checked;
        }
    }
}