Converting a DataTable to JSON with columns having arrays of values

Question

I have a DataTable like this: Sample DataTable

The JSON I require as output is in the below format:

{
    "Column1": ["value1","value2"],
    "Column2": ["value3"],
    "Column3": ["value4","value5"]
},
{
    "Column1": ["value1","value2"],
    "Column2": ["value2","value3"],
    "Column3": ["value4","value5"]
}

So basically I have a list of comma separated values in every row. It can be a single value as well. If multi value, I want the JSON to contain the array in the format as specified.

I am in total control of formatting the rows in the DataTable. I tried to build the format in the SQL Server query itself but as you know, when JsonConvert.SerializeObject(Datatable) executes, it escapes the double quotes which is not what I want.

Please let me know how I can achieve this. I am using SQL Server 2008 and NewtonSoft Json on the C# side of it.

Edit -

The DataTable contains more than 10 rows and might add more later on. So I want to avoid custom logic on a particular column.

you have to do code to convert comma separated values in array. — Hitesh Thakor, Jun 23 '17 at 05:57
Your json is incorrectly formed with extra commas at the end of these lines `"Column3": ["value4","value5"],` — Turophile, Jun 23 '17 at 06:22
What you need to do is refactor your database. Read [Is storing a delimited list in a database column really that bad?](http://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad), where you will see a lot of reasons why the answer to this question is **Absolutly yes!**. — Zohar Peled, Jun 23 '17 at 06:35

score 0 · Answer 1 · answered Jun 23 '17 at 06:16

0

You could create dynamic type with array of strings instead of string:

var json = table.AsEnumerable().Select(r => new
{
    Column1 = r["Column1"].ToString().Split(','),
    Column2 = r["Column2"].ToString().Split(','),
    Column3 = r["Column3"].ToString().Split(','),
});

answered Jun 23 '17 at 06:16

Pablo notPicasso

3,031
3
17
22

OP uses `JsonConvert.SerializeObject(..)` so he knows how to serialize object to JSON. The main problem was he has DataTable and needs Array. – Pablo notPicasso Dec 14 '18 at 14:31

score 0 · Accepted Answer · answered Jun 23 '17 at 16:26

You can use a custom JsonConverter to allow you to serialize the DataTable into the format you want. Something like this should work:

class CustomDataTableConverter : JsonConverter
{
    public override bool CanConvert(Type objectType)
    {
        return objectType == typeof(DataTable);
    }

    public override void WriteJson(JsonWriter writer, object value, JsonSerializer serializer)
    {
        DataTable table = (DataTable)value;
        JArray array = new JArray();
        foreach (DataRow row in table.Rows)
        {
            JObject obj = new JObject();
            foreach (DataColumn col in table.Columns)
            {
                if (col.ColumnName == "Id") continue;  // skip Id column

                if (row[col] != null && row[col] != DBNull.Value)
                {
                    string[] values = row[col].ToString().Split(',');
                    obj.Add(col.ColumnName, JArray.FromObject(values));
                }
                else
                {
                    obj.Add(col.ColumnName, JValue.CreateNull());
                }
            }
            array.Add(obj);
        }
        array.WriteTo(writer);
    }

    public override bool CanRead
    {
        get { return false; }
    }

    public override object ReadJson(JsonReader reader, Type objectType, object existingValue, JsonSerializer serializer)
    {
        throw new NotImplementedException();
    }
}

To use the converter, add an instance to the Converters collection on JsonSerializerSettings and pass the settings to SerializeObject:

JsonSerializerSettings settings = new JsonSerializerSettings();
settings.Converters.Add(new CustomDataTableConverter());
settings.Formatting = Formatting.Indented;

string json = JsonConvert.SerializeObject(table, settings);

Here is a working demo: https://dotnetfiddle.net/D0fw5w

Although a bit of customization needed, thanks for the fiddle, this solves the issue at hand. — Vishwas, Jun 24 '17 at 04:09

Narendra kumawat · Answer 3 · 2017-07-27T09:18:16.347

-1

Do like this

 var jsonString = JsonConvert.SerializeObject(someObjet);

 var myObject = JsonConvert.DeserializeObject<MyType>(jsonString);

convert srting into json and do use myObject data

edited Jul 27 '17 at 09:18

answered Jun 23 '17 at 06:47

Narendra kumawat

137
1
6

this is C# solution – Narendra kumawat Jul 29 '17 at 04:03

Converting a DataTable to JSON with columns having arrays of values

3 Answers3