Serialize List<T> into google charts json datatable

I'm looking for a generic way of serializing a List of objects into the google charts json data format.

This example is quite close, but it's using a datatable..

I expect this would involve some reflection and some maybe attrbutes on the model properties. Can anyone point me to a library or something?

Even better would be if I开发者_开发知识库 could serialize a query like this into the google charts format:

var results = from m in be.cmsMember
      where m.FirstLogin != null
      && m.FirstLogin >= BitCoinGoLive
      group m by

      new { Year = m.FirstLogin.Value.Year, Month = m.FirstLogin.Value.Month, Day =           m.FirstLogin.Value.Day } into grp
      select new
      {
                              Year = grp.Key.Year,
                              Month = grp.Key.Month,
                              Day = grp.Key.Day,
                              Count = grp.Count()
      };

I would create my own class hierarchy that matches Google's API and then use JSON.NET to serialize it. The possible data model:

public class Graph {
    public ColInfo[] cols { get; set; }
    public DataPointSet[] rows { get; set; }
    public Dictionary<string, string> p { get; set; }
}

public class ColInfo {
    public string id { get; set; }
    public string label { get; set; }
    public string type { get; set; }
}

public class DataPointSet {
    public DataPoint[] c { get; set; }
}

public class DataPoint {
    public string v { get; set; } // value
    public string f { get; set; } // format
}

Then an example usage:

var graph = new Graph {
    cols = new ColInfo[] {
        new ColInfo { id = "A", label = "set A", type = "string" },
        new ColInfo { id = "B", label = "set B", type = "string" },
        new ColInfo { id = "C", label = "set C", type = "string" }
    },
    rows = new DataPointSet[] {
        new DataPointSet {
            c = new DataPoint[] {
                new DataPoint { v = "a" },
                new DataPoint { v = "b", f = "One" }
            }
        }
    },
    p = new Dictionary<string, string>()
};

string json;
//var s = new JsonSerializer();
var s = new JavaScriptSerializer();
/*using (var sw = new StringWriter()) {
    s.Serialize(sw, graph);
    json = sw.ToString();
}*/
var sw = new StringBuilder();
s.Serialize(graph, sw);
json = sw.ToString();

You can use Linq's Select() to transform your data into Google's data model, then serialize it to JSON.

Here's a full working funciton which also works with anonymous types.

Carefull with dates though: these need to parsed on the client as well:e.g.

for (var i = 0; i < data.rows.length;i++ ) {
   data.rows[i].c[0].v = new Date(data.rows[i].c[0].v);
}



private string getGetJsonString<T>(IEnumerable<dynamic> list, dynamic row) {

    string header = "{\"cols\":[";
    PropertyInfo[] props = row.GetType().GetProperties();
    foreach (PropertyInfo p in props)
    {

        header += "{\"id\":\"" + p.Name + "\", \"label\":\"" + p.Name + "\",";
        switch (p.PropertyType.Name)
        {
            case "Int32":
                header += "\"type\":\"number\"";
                break;
            case "DateTime":
                header += "\"type\":\"date\"";
                break;
            default:
                header += "\"type\":\"string\"";
                break;
        }
        header += "},";
    }
    header = header.Substring(0, header.Length - 1);
    header += "]";

    StringBuilder json = new StringBuilder();
    json.Append(header + ",\"rows\":[");

    bool first = true;
    foreach (dynamic a in list)
    {
        string jRow = "{\"c\":[";
        if (first)
            first = false;                    
        else
            jRow = "," + jRow;

        foreach (PropertyInfo p in props)
        {

            // todo get other fieldtypes from http://code.google.com/apis/chart/interactive/docs/reference.html#dataparam
            switch (p.PropertyType.Name)
            {
                case "Int32":
                    jRow += "{\"v\":";
                    jRow += p.GetValue(a,null).ToString();
                    jRow += "},";
                    break;
                case "DateTime":
                    jRow += "{\"v\":\"";
                    DateTime d = ((DateTime)p.GetValue(a, null));
                    //jRow += d.DayOfYear;
                    //jRow += "\\/Date("+d.Ticks+")\\/";
                    jRow += d.ToString("yyyy-MM-dd");
                    //jRow += "new Date(" + d.Ticks+ ")";
                    jRow += "\"},";
                    break; 
                default:
                    jRow += "{\"v\":\"";
                    jRow += p.GetValue(a,null).ToString();
                    jRow += "\"},";
                    break;
            }

        }
        jRow = jRow.Substring(0, jRow.Length - 1);
        json.Append(jRow + "]}");
    }

    json.Append("]}");


    return json.ToString() ;
}

Have a look at JSON.NET. It's a great library to generate json based on your class, this page will give you examples of how to serialise : http://james.newtonking.com/pages/json-net.aspx.

Hopefully this will point you in the right direction,

I recommend using a combination of classes matching google's JSON format for the charts, combined with Json.net. Essential steps are.

1. Specify your columns
2. Select your data from whatever source (using linq) into row cell values matching the correct columns
3. serialise out to json with JSON.net

This is a combination of the two suggestions by @emfurry and @TWith2Sugars.

So with classes similar to @emfurrys in place (maybe add some constructors to remove need for object initialiser) Something a bit like :

Table table = new Table();

ColInfo[] cols = new ColInfo[4]; 
cols.Add("year", "Year", "string");
cols.Add("month", "Month", "string");  
cols.Add("day", "Day", "string"); 
cols.Add("count", "Count", "number"); 

table.rows = cmsMembersWithCount.Select(row => new DataPointSet(){ 
{new DataPoint(row.Year)} 
{new DataPoint(row.Month)} 
{new DataPoint(row.Day)} 
{new DataPoint(row.Count)} 
}).ToArray();


var json = JsonConvert.SerializeObject(table);

bob's your uncle. N.B Bob is untested, he's here just as a little example, he assumes a fair bit, but he hopefully gives the outline of how to go quickly from any data source to Google's chart JSON format.

I use Google DataTable .Net Wrapper, available on Nuget. I have a little article about it in my blog at http://nicholasbering.ca/dot-net/2014/10/24/google-data-table-dot-net-wrapper/, but to sum it up you could do something like the following.

var list = new[]
                 {
                     new {Name = "Dogs", Count = 5},
                     new {Name = "Cats", Count = 2}
                 };

 var json = list.ToGoogleDataTable()
                .NewColumn(new Column(ColumnType.String, "Name"), x => x.Name)
                .NewColumn(new Column(ColumnType.Number, "Count"), x => x.Count)
                .Build()
                .GetJson();

The example above is working on an array, but it should work on any IEnumrable<T>.