How to serialize specific Json object from C#

Question

I have a requirement where i need to serialize json object in below format

[{
    "columns": [{
        "title": "NAME"
    }, {
        "title": "COUNTY"
    }],
    "data": [
        ["John Doe", "Fresno"],
        ["Billy", "Fresno"],
        ["Tom", "Kern"],
        ["King Smith", "Kings"]
    ]
}]

Here i need to get this json object from two different source, one is Columns and other is data. Columns would come from a string which will be comma separated as

string columnNames = "Name, County";

and data would come from .net Datatable like

DataTable dt = new DataTable();

I tried with below code using JavaScriptSerializer but i am not able to format it in the required format. Actually, shared format is required to dynamically create jquery datatable. Here is my raw code in C#.

[WebMethod]
public static string ConvertDatadttoString(string appName)
{

    DataTable dt = new DataTable();

    dt.Columns.Add("Name", typeof(string));
    dt.Columns.Add("County", typeof(string));

    dt.Rows.Add("vo", "go.com");
    dt.Rows.Add("pa", "pa.com");

    System.Web.Script.Serialization.JavaScriptSerializer serializer = new System.Web.Script.Serialization.JavaScriptSerializer();
    List<Dictionary<string, object>> rows = new List<Dictionary<string, object>>();
    Dictionary<string, object> row;
    foreach (DataRow dr in dt.Rows)
    {
        row = new Dictionary<string, object>();
        foreach (DataColumn col in dt.Columns)
        {
            row.Add(col.ColumnName, dr[col]);
        }
        rows.Add(row);
    }
    return serializer.Serialize(rows);
}

Above code is only serializing the DataTable and is not able to create in the required format. Thanks.

What exactly is your code outputting? How does it differ from what you were aiming for? — Matt Burland
– Matt Burland, Commented Aug 1, 2016 at 19:43
Use json2csharp.com to figure out what your model should look like. Use JSON.NET to serialize it. — Ian Mercer
– Ian Mercer, Commented Aug 1, 2016 at 19:53

Sam I am says Reinstate Monica · Accepted Answer · 2016-08-01 19:48:42Z

What I usually do, is I build a model based off of the data, and serialize that model.

This is how I'd imagine your model would look.

public class SampleClass
{
    public IEnumerable<SampleItem> columns { get; set; }
    public IEnumerable<IEnumerable<string>> data { get; set; }
}

public class SampleItem
{
    public string title { get; set; }
}

And this is how I'd imagine you'd get the sample json

var sample = new List<SampleClass>
{
    new SampleClass()
    {
        columns = new List<SampleItem>()
        {
            new SampleItem() {title = "NAME" },
            new SampleItem() {title = "COUNTY" },
        },
        data = new List<List<string>>()
        {
            new List<string> { "John Doe", "Fresno" },
            new List<string> { "Billy", "Fresno" },
            new List<string> { "Tom", "Kern" },
            new List<string> { "King Smith", "Kings" },
        }
    }
};

var serializer = new JavaScriptSerializer();
var json = serializer.Serialize(sample);

I'm sure you can figure out how to create that model based off of your real data. It's not that hard.

Matt Burland · Accepted Answer · 2016-08-01 20:24:54Z

1

It's probably easier to create a class, but if you want to work with a Dictionary<string,object> then you need to first add an entry for your columns:

rows["columns"] = dt.Columns.Cast<DataTableColumn>()
                            .Select(c => new { title = c.ColumnName }).ToList();

And then you can add your data with something like:

rows["data"] = dt.Rows.Cast<DataRow>.Select(r => r.ItemArray).ToList();

Now you have a Dictionary<string,object> with two items columns and rows. columns contains a collection of objects with a property title and rows just contains an array of arrays for each row.

But this is a quick and dirty solution. I think creating a class as per @Sam I am's answer is cleaner and easier to maintain in the long run.

If you are starting with a comma separated list of column names, it really shouldn't be much harder to do. Something like:

var columns = columnNames.Split(",");    // beware of column names that include commas!

row["columns"] = columns.Select(c => new { title = c });

row["data"] = dt.Rows.Cast<DataRow>.Select(r => columns.Select(c => r[c]).ToList());

edited Aug 1, 2016 at 20:24

answered Aug 1, 2016 at 19:52

Matt Burland

45.3k18 gold badges110 silver badges182 bronze badges

7 Comments

Lara Over a year ago

Could you please guide me on adding the column names from string which is separated with comma as shared in the post. Datatable should give only rows, Column names need to be custom and User specific rather than database table name

Matt Burland Over a year ago

@Lara: It shouldn't be hard to Split your string and pick out the column names. Then for your rows you just have to be sure to only take the values you need.

Lara Over a year ago

Tried to implement the shared code as of now but getting error at columns.Select(c => new { title: c }); do i need to define title somewhere ?

Matt Burland Over a year ago

@Lara: Sorry, my bad. I always get javascript and C# mixed up with annoymous types. It should be title = c.

Matt Burland Over a year ago

@Lara: That would be a question about populating an datatable and nothing to do with serialization. If you have another question, ask another question. In your update you never added any columns to your datatable - hence the error.

|

Collectives™ on Stack Overflow

How to serialize specific Json object from C#

2 Answers 2

Comments

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related