asked on

DataSet to JSON file and back from JSON file to DataSet

Hi all, I'm trying to send a dataset to a JSON file and than back to a dataset in C#. The file results are close but the structure of the dataset name and the two table names are not included, so when I try importing it back into the same structure it errors out.

The DataSet is called "CONTACTS"
The two tables are called "tblMAIN" and "tblDETAILS".

Below is the code creating the JSON string and subsequently I'm saving it to a .json file.

public static string dataSetToJSON(DataSet ds)
        {
            string json = null;

            foreach (DataTable dt in ds.Tables)
            {
                if (dt.TableName == "tblMAIN" || dt.TableName == "tblDETAILS")
                {
                    json += JsonConvert.SerializeObject(dt, Formatting.Indented);
                }
                
            }
            return json;
        }

Open in new window

The below data is what it file looks like. As you can see those structural names are not defined.

[
  {
    "SID": "84c7ad88-cd30-491a-ad8d-053ff6b6d5d4",
    "sITEM_NAME": "John Doe",
    "sNOTES": "",
    "sGROUPS": "fea44695-8319-48fd-a49d-c13aeaf21e02,a1d080cc-61af-4e3c-996d-4c389650f352,376b5a99-5b37-4582-855a-b1eab05309bf",
    "sFAVORITE": false,
    "sMODIFIED_DATE": "2021-03-03T15:10:04.480499",
    "sIMAGE": 0,
    "sINACTIVE": false
  },
  {
    "SID": "039607b4-0271-4c89-bda5-9809a55d6b4b",
    "sITEM_NAME": "Eric Doe",
    "sNOTES": "",
    "sGROUPS": "fea44695-8319-48fd-a49d-c13aeaf21e02,ca79ce6b-bac9-49bc-837c-f2c89bdb688f,376b5a99-5b37-4582-855a-b1eab05309bf",
    "sFAVORITE": false,
    "sMODIFIED_DATE": "2021-03-03T15:10:04.480499",
    "sIMAGE": 0,
    "sINACTIVE": false
  },
  {
    "SID": "b2f6ebb1-bdb3-4d18-a386-ac6d431eb429",
    "sITEM_NAME": "Maggie Doe",
    "sNOTES": "",
    "sGROUPS": "fea44695-8319-48fd-a49d-c13aeaf21e02,a1d080cc-61af-4e3c-996d-4c389650f352,376b5a99-5b37-4582-855a-b1eab05309bf",
    "sFAVORITE": false,
    "sMODIFIED_DATE": "2021-03-03T15:10:04.480499",
    "sIMAGE": 0,
    "sINACTIVE": true
  }
][
  {
    "DID": "b755620c-e53d-4558-a05d-cc3c213d5c13",
    "SID": "84c7ad88-cd30-491a-ad8d-053ff6b6d5d4",
    "dCAPTION": "Order ID",
    "dVALUE": "*************",
    "dTYPE": "TEXT",
    "dSECURED": false
  },
  {
    "DID": "8e78563d-cfaa-41d8-b683-e2946d413409",
    "SID": "84c7ad88-cd30-491a-ad8d-053ff6b6d5d4",
    "dCAPTION": "Registration Key",
    "dVALUE": "****************",
    "dTYPE": "REGISTRATION KEY",
    "dSECURED": false
  },
  {
    "DID": "839c3db4-2124-4c27-ac67-ff9348417ef7",
    "SID": "84c7ad88-cd30-491a-ad8d-053ff6b6d5d4",
    "dCAPTION": "Web Site",
    "dVALUE": "https://www.bing.com",
    "dTYPE": "WEB ADDRESS",
    "dSECURED": false
  },

Open in new window

I also tried the code below that I found on the internet; but this does not include the structural elements either. Converting Datatable And Dataset To JSON String And Vice Versa (c-sharpcorner.com)

{  
    ArrayList root = new ArrayList();  
    List<Dictionary<string, object>> table;  
    Dictionary<string, object> data;  
  
    foreach (DataTable dt in ds.Tables)  
    {  
        table = new List<Dictionary<string, object>>();  
        foreach (DataRow dr in dt.Rows)  
        {  
            data = new Dictionary<string, object>();  
            foreach (DataColumn col in dt.Columns)  
            {  
                data.Add(col.ColumnName, dr[col]);  
            }  
            table.Add(data);  
        }  
        root.Add(table);  
    }  
    JavaScriptSerializer serializer = new JavaScriptSerializer();  
    return serializer.Serialize(root);  
}

Open in new window

it_saige

You can simply serialize the dataset. If your dataset contains more tables than you want to serialize, you could create a new dataset with just the tables and/or rows you want to serialize.

Proof of concept:

using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Linq;

namespace EE_Q29209956
{
    class Program
    {
        static void Main(string[] args)
        {
            var dataset = GenerateDataSet();
            var json = JsonConvert.SerializeObject(dataset, Formatting.Indented);
            Console.WriteLine(json);
            Console.ReadLine();
        }

        static DataSet GenerateDataSet()
        {
            var dataset = new DataSet("CONTACTS");
            dataset.Tables.Add(new[]
            {
                new Item
                {
                    SID = Guid.Parse("84c7ad88-cd30-491a-ad8d-053ff6b6d5d4"),
                    sITEM_NAME = "John Doe",
                    sNOTES = "",
                    sGROUPS = "fea44695-8319-48fd-a49d-c13aeaf21e02,a1d080cc-61af-4e3c-996d-4c389650f352,376b5a99-5b37-4582-855a-b1eab05309bf",
                    sFAVORITE = false,
                    sMODIFIED_DATE = DateTime.Parse("2021-03-03T15:10:04.480499"),
                    sIMAGE = 0,
                    sINACTIVE = false
                },
                new Item
                {
                    SID = Guid.Parse("039607b4-0271-4c89-bda5-9809a55d6b4b"),
                    sITEM_NAME = "Eric Doe",
                    sNOTES = "",
                    sGROUPS = "fea44695-8319-48fd-a49d-c13aeaf21e02,ca79ce6b-bac9-49bc-837c-f2c89bdb688f,376b5a99-5b37-4582-855a-b1eab05309bf",
                    sFAVORITE = false,
                    sMODIFIED_DATE = DateTime.Parse("2021-03-03T15:10:04.480499"),
                    sIMAGE = 0,
                    sINACTIVE = false
                },
                new Item
                {
                    SID = Guid.Parse("b2f6ebb1-bdb3-4d18-a386-ac6d431eb429"),
                    sITEM_NAME = "Maggie Doe",
                    sNOTES = "",
                    sGROUPS = "fea44695-8319-48fd-a49d-c13aeaf21e02,ca79ce6b-bac9-49bc-837c-f2c89bdb688f,376b5a99-5b37-4582-855a-b1eab05309bf",
                    sFAVORITE = false,
                    sMODIFIED_DATE = DateTime.Parse("2021-03-03T15:10:04.480499"),
                    sIMAGE = 0,
                    sINACTIVE = true
                }
            }.ConvertToDataTable("tblMAIN"));
            dataset.Tables.Add(new[]
            {
                new Detail
                {
                    DID = Guid.Parse("b755620c-e53d-4558-a05d-cc3c213d5c13"),
                    SID = Guid.Parse("84c7ad88-cd30-491a-ad8d-053ff6b6d5d4"),
                    dCAPTION = "Order ID",
                    dVALUE = "*************",
                    dTYPE = "TEXT",
                    dSECURED = false
                },
                new Detail
                {
                    DID = Guid.Parse("8e78563d-cfaa-41d8-b683-e2946d413409"),
                    SID = Guid.Parse("84c7ad88-cd30-491a-ad8d-053ff6b6d5d4"),
                    dCAPTION = "Registration Key",
                    dVALUE = "*************",
                    dTYPE = "REGISTRATION KEY",
                    dSECURED = false
                },
                new Detail
                {
                    DID = Guid.Parse("839c3db4-2124-4c27-ac67-ff9348417ef7"),
                    SID = Guid.Parse("84c7ad88-cd30-491a-ad8d-053ff6b6d5d4"),
                    dCAPTION = "Web Site",
                    dVALUE = "https://www.bing.com",
                    dTYPE = "WEB ADDRESS",
                    dSECURED = false
                }
            }.ConvertToDataTable("tblDETAILS"));

            return dataset;
        }
    }

    class Item
    {
        public Guid SID { get; set; }
        public string sITEM_NAME { get; set; }
        public string sNOTES { get; set; }
        public string sGROUPS { get; set; }
        public bool sFAVORITE { get; set; }
        public DateTime sMODIFIED_DATE { get; set; }
        public int sIMAGE { get; set; }
        public bool sINACTIVE { get; set; }
    }

    class Detail
    {
        public Guid DID { get; set; }
        public Guid SID { get; set; }
        public string dCAPTION { get; set; }
        public string dVALUE { get; set; }
        public string dTYPE { get; set; }
        public bool dSECURED { get; set; }
    }

    static class Extensions
    {
        public static DataTable ConvertToDataTable<T>(this IEnumerable<T> source, string name = "")
        {
            var table = new DataTable(name);
            var properties = TypeDescriptor.GetProperties(typeof(T));
            foreach (var property in properties.Cast<PropertyDescriptor>())
            {
                if (property.PropertyType.IsGenericType && Equals(property.PropertyType.GetGenericTypeDefinition(), typeof(Nullable<>)))
                {
                    table.Columns.Add(property.Name, property.PropertyType.GetGenericArguments()[0]);
                }
                else
                {
                    table.Columns.Add(property.Name, property.PropertyType);
                }
            }

            var values = new object[properties.Count];
            foreach (var item in source)
            {
                for (int i = 0; i < properties.Count; i++)
                {
                    values[i] = properties[i].GetValue(item);
                }
                table.Rows.Add(values);
            }

            return table;
        }
    }
}

Open in new window

Produces the following output:

HTH,

-saige-

CodeJunky

ASKER

I'm not understanding why I would want to create a new dataset and load all the records from my current dataset? Is there something I'm missing from my current code that is prohibiting the table names form being added, as it seems to be done with the code you provided?

it_saige

Because I serialized the dataset instead of trying to pull the individual tables/rows. Your attempts above seem to indicate that your dataset contains more than Main and Details. If this is not true then you can simply serialize the dataset you have; e.g -

public static string dataSetToJSON(DataSet ds)
{
    return JsonConvert.SerializeObject(ds, Formatting.Indented);
}

Open in new window

HTH,

-saige-

CodeJunky

ASKER

yes you are correct that there are more than two tables. And in my code, first one, I believe that I'm doing that very thing?

it_saige

But you are not. You are pulling just the table itself and serializing each individual table. The serialization won't include the table name (it is also invalid json in the current structure).

That's why what you *need* to do is create a new or filtered dataset; e.g. -

using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Linq;

namespace EE_Q29209956
{
    class Program
    {
        static void Main(string[] args)
        {
            var dataset = GenerateDataSet();
            var ds1 = GetTables(dataset);
            var json = JsonConvert.SerializeObject(ds1, Formatting.Indented);
            Console.WriteLine(json);
            Console.ReadLine();
        }

        static DataSet GetTables(DataSet source)
        {
            var dataset = new DataSet();
            dataset.Tables.AddRange(source.Tables.Cast<DataTable>().Where(t => t.TableName == "tblMAIN" || t.TableName == "tblDETAILS").Select(x => x.Copy()).ToArray());

            return dataset;
        }

        static DataSet GenerateDataSet()
        {
            var dataset = new DataSet("CONTACTS");
            dataset.Tables.Add(new[]
            {
                new Item
                {
                    SID = Guid.Parse("84c7ad88-cd30-491a-ad8d-053ff6b6d5d4"),
                    sITEM_NAME = "John Doe",
                    sNOTES = "",
                    sGROUPS = "fea44695-8319-48fd-a49d-c13aeaf21e02,a1d080cc-61af-4e3c-996d-4c389650f352,376b5a99-5b37-4582-855a-b1eab05309bf",
                    sFAVORITE = false,
                    sMODIFIED_DATE = DateTime.Parse("2021-03-03T15:10:04.480499"),
                    sIMAGE = 0,
                    sINACTIVE = false
                },
                new Item
                {
                    SID = Guid.Parse("039607b4-0271-4c89-bda5-9809a55d6b4b"),
                    sITEM_NAME = "Eric Doe",
                    sNOTES = "",
                    sGROUPS = "fea44695-8319-48fd-a49d-c13aeaf21e02,ca79ce6b-bac9-49bc-837c-f2c89bdb688f,376b5a99-5b37-4582-855a-b1eab05309bf",
                    sFAVORITE = false,
                    sMODIFIED_DATE = DateTime.Parse("2021-03-03T15:10:04.480499"),
                    sIMAGE = 0,
                    sINACTIVE = false
                },
                new Item
                {
                    SID = Guid.Parse("b2f6ebb1-bdb3-4d18-a386-ac6d431eb429"),
                    sITEM_NAME = "Maggie Doe",
                    sNOTES = "",
                    sGROUPS = "fea44695-8319-48fd-a49d-c13aeaf21e02,ca79ce6b-bac9-49bc-837c-f2c89bdb688f,376b5a99-5b37-4582-855a-b1eab05309bf",
                    sFAVORITE = false,
                    sMODIFIED_DATE = DateTime.Parse("2021-03-03T15:10:04.480499"),
                    sIMAGE = 0,
                    sINACTIVE = true
                }
            }.ConvertToDataTable("tblMAIN"));
            dataset.Tables.Add(new[]
            {
                new Detail
                {
                    DID = Guid.Parse("b755620c-e53d-4558-a05d-cc3c213d5c13"),
                    SID = Guid.Parse("84c7ad88-cd30-491a-ad8d-053ff6b6d5d4"),
                    dCAPTION = "Order ID",
                    dVALUE = "*************",
                    dTYPE = "TEXT",
                    dSECURED = false
                },
                new Detail
                {
                    DID = Guid.Parse("8e78563d-cfaa-41d8-b683-e2946d413409"),
                    SID = Guid.Parse("84c7ad88-cd30-491a-ad8d-053ff6b6d5d4"),
                    dCAPTION = "Registration Key",
                    dVALUE = "*************",
                    dTYPE = "REGISTRATION KEY",
                    dSECURED = false
                },
                new Detail
                {
                    DID = Guid.Parse("839c3db4-2124-4c27-ac67-ff9348417ef7"),
                    SID = Guid.Parse("84c7ad88-cd30-491a-ad8d-053ff6b6d5d4"),
                    dCAPTION = "Web Site",
                    dVALUE = "https://www.bing.com",
                    dTYPE = "WEB ADDRESS",
                    dSECURED = false
                }
            }.ConvertToDataTable("tblDETAILS"));
            dataset.Tables.Add((from i in Enumerable.Range(1, 6) select new Something { ID = i, Name = $"{nameof(Something)}{i}" }).ConvertToDataTable(nameof(Something)));
            dataset.Tables.Add((from i in Enumerable.Range(1, 6) select new SomethingElse { ID = i, Name = $"{nameof(SomethingElse)}{i}" }).ConvertToDataTable(nameof(SomethingElse)));

            return dataset;
        }
    }

    class Item
    {
        public Guid SID { get; set; }
        public string sITEM_NAME { get; set; }
        public string sNOTES { get; set; }
        public string sGROUPS { get; set; }
        public bool sFAVORITE { get; set; }
        public DateTime sMODIFIED_DATE { get; set; }
        public int sIMAGE { get; set; }
        public bool sINACTIVE { get; set; }
    }

    class Detail
    {
        public Guid DID { get; set; }
        public Guid SID { get; set; }
        public string dCAPTION { get; set; }
        public string dVALUE { get; set; }
        public string dTYPE { get; set; }
        public bool dSECURED { get; set; }
    }

    class Something
    {
        public int ID { get; set; }
        public string Name { get; set; }
    }

    class SomethingElse
    {
        public int ID { get; set; }
        public string Name { get; set; }
    }

    static class Extensions
    {
        public static DataTable ConvertToDataTable<T>(this IEnumerable<T> source, string name = "")
        {
            var table = new DataTable(name);
            var properties = TypeDescriptor.GetProperties(typeof(T));
            foreach (var property in properties.Cast<PropertyDescriptor>())
            {
                if (property.PropertyType.IsGenericType && Equals(property.PropertyType.GetGenericTypeDefinition(), typeof(Nullable<>)))
                {
                    table.Columns.Add(property.Name, property.PropertyType.GetGenericArguments()[0]);
                }
                else
                {
                    table.Columns.Add(property.Name, property.PropertyType);
                }
            }

            var values = new object[properties.Count];
            foreach (var item in source)
            {
                for (int i = 0; i < properties.Count; i++)
                {
                    values[i] = properties[i].GetValue(item);
                }
                table.Rows.Add(values);
            }

            return table;
        }
    }
}

Open in new window

Produces the same output as above.

-saige-

CodeJunky

ASKER

Thanks I'll give it a try. One other question about this though. The static DataSet GenerateDataSet() where you are adding records; is that something I have to do for each record?

it_saige

GenerateDataSet is creating the original dataset by mocking it. It's not needed by you. The second method where I *filter* the tables is the one you need to use to generate your JSON or return the filtered dataset, your choice.

-saige-

CodeJunky

ASKER

Ok, I will include the code that I used, from your example you gave me. Started out ok; but the end of the file is not correct, and I've seen this in other iterations of code I've tried as well.

public static string dataSetToJSON()
        {
            var dataset = clDATA.myDS_main;
            var ds1 = GetTables(dataset);
            var json = JsonConvert.SerializeObject(ds1, Formatting.Indented);

            return json.ToString();

        }
        static DataSet GetTables(DataSet source)
        {
            var dataset = new DataSet();
            dataset.Tables.AddRange(source.Tables.Cast<DataTable>().Where(t => t.TableName == "tblMAIN" || t.TableName == "tblDETAILS").Select(x => x.Copy()).ToArray());

            return dataset;
        }

Open in new window

it_saige

How are you writing your file? Did you confirm that all the data was in the dataset?

-saige-

CodeJunky

ASKER

Yes. All the data was pulled directory from my DataSet, and that obviously has all the data in it.

it_saige

But did you validate that all the data was in the filtered dataset? Did you validate that the json string ends properly? And how are you writing your file?

-saige-

CodeJunky

ASKER

So, that's the problem. the json string does not end properly. I tested with just one table, also, and that did not end correctly either. I also did a query to see if I was missing any main data records as related to the detailed records, and I am not. I'm returning the var json = JsonConvert.SerializeObject(ds1, Formatting.Indented); as a string, and writing it to a file.

ASKER CERTIFIED SOLUTION

it_saige

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

CodeJunky

ASKER

I removed the ToString(); but the end still gets cut off.

{
      "DID": "3df13fd5-122a-49d9-82b1-2f26aa473995",
      "SID": "1c6e407c-5ab0-4c8d-8a0c-0a21a24ff343",
      "dCAPTION": "Login Name",
      "dVALUE": "****************",
      "dTYPE": "LOGIN NAME",
      "dSECURED": false
    },
    {
      "DID": "068e8fe5-39d8-47ab-9f6b-c54eda033038",
      "SID": "1c6e407c-5ab0-4c8d-8a0c-0a21a24ff343",
      "dCAPTION": "Password",
      "dVALUE": "***********",
      "dTYPE

Open in new window

it_saige

You want to setup a teamviewer session?

-saige-

CodeJunky

ASKER

I just thought of something that may help you help me. Before even trying your method, I gave it a go doing something a little different and adding the table names and proper json constructs to the string results. The table parts were added just fine; but when I went to append
}
]
}
to the end of the string, nothing would be appended. Very odd.

CodeJunky

ASKER

That maybe a good idea; but I have somethings I need to do today. Thanks for the offer.

it_saige

Send me a private message when you want to setup the session. I don't think it should take too long.

I myself have something to do in about 6 hours.

-saige-

CodeJunky

ASKER

Also, I tried a completely different dataset of records. The same thing as far as the ending record being cut off.

CodeJunky

ASKER

I figured the issue out. I was not closing the file. At least I got a good chuckle out that one. Thanks very much for your help.

it_saige

Glad you got it sorted. :)

-saige-