Convert objects to JSON in C#

Question

I have a class:

public class StatististikaByCustomer
{
    public string data { get; set; } // Array or String?
    public string xkey {get;set;}
    public Array ykey {get;set;}
    public Array labels {get;set;}

}

How can I get JSON like this?

{
    "data":[
        {
            "timeinterval":"2015-10-22T00:00:00",
            "Firm":4,
            "Firm1":4,
            "Firm2":22,
            "Firm3":30,
            "Firm4":19
        },
        {
            "timeinterval":"2015-10-23T00:00:00",
            "Firm":2,
            "Firm1":5,
            "Firm2":29,
            "Firm3":34,
            "Firm4":219
        }
    ],
    "xkey":"timeinterval",
    "ykey":["Firm","Firm1","Firm2","Firm3","Firm4"],
    "labels":[" Firm","Firm1","Firm2","Firm3","Firm4"]
}

Firm cannot be hard coded. It all must be dynamic.

My Controller Action:

public JsonResult StatistikaJson()
{
    ArrayList arrayList = new ArrayList();
    StatistikaByCustomer statisikaObject = new StatistikaByCustomer();           
    List<Data> listData = new List<Data>();
    string jsonTemp = null;
    DbDataReader reader = null;

    using (var cmd = db.Database.Connection.CreateCommand())
    {
        if (db.Database.Connection.State == ConnectionState.Closed)
        {
            db.Database.Connection.Open();
        }

        cmd.CommandText = "EXEC GetClientConnectsByCustomer @start='" + DateTime.Now.AddMonths(-1).Date.ToString("yyyy-MM-dd") + "',@end='" + DateTime.Now.Date.ToString("yyyy-MM-dd") + "',@interval = 24";
        reader = cmd.ExecuteReader();

        var tempYkey = Enumerable.Range(1, reader.FieldCount - 1).Select(reader.GetName).ToArray();
        statisikaObject.ykey = tempYkey.Select(x => x.Replace(" ", "")).ToArray();

        if (reader.HasRows)
        {
            while (reader.Read())
            {
                string name = reader.GetName(0);
                object value = reader.GetDateTime(0);
                listData.Add(new Data() { Name = name, Value = value });

                for (int i = 1; i < reader.FieldCount - 1; i++)
                {
                    name = reader.GetName(i).Replace(" ", "");
                    value = reader.GetInt32(i);
                    listData.Add(new Data() { Name = name, Value = value });
                }

                //arrayList.Add(JsonConvert.SerializeObject(listData.ToDictionary(x => x.Name, y => y.Value)));
                jsonTemp += JsonConvert.SerializeObject(listData.ToDictionary(x => x.Name, y => y.Value));  
                jsonTemp += ",";                             
                listData.Clear();
                //Debug.WriteLine("{0}\t{1}\t{2}\t{3}\t{4}", reader.GetName(0), reader.GetDateTime(0), reader.GetInt32(1), reader.GetInt32(2), reader.GetInt32(3), reader.GetInt32(4));   
            }
        }
        else
        {
            Debug.WriteLine("No rows found.");
        }
        statisikaObject.xkey = reader.GetName(0);
        statisikaObject.labels = Enumerable.Range(1, reader.FieldCount - 1).Select(reader.GetName).ToArray();
        reader.Close();
        //the number of affected records, if the query returns it var result = cmd.ExecuteNonQuery(); 
        //or a single scalar value //var result = cmd.ExecuteScalar(); 
        //or even a data reader var result = cmd.ExecuteReader(); 
        db.Database.Connection.Close();
    }
    statisikaObject.data =  oSerializer.Serialize(arrayList);

    //string json = JsonConvert.SerializeObject(statisikaObject);
    //string json = JsonConvert.SerializeObject(l);

    return Json(statisikaObject, JsonRequestBehavior.AllowGet);
}

I get JSON, but it's escaped and morris.js doesn't like it.

In my view, I would like to use it like this:

<script type="text/javascript">
    $.getJSON('@Url.Action("StatistikaJson")', function (result)
    {
        new Morris.Line({
            element: 'line-example',
            data: result.data,
            xkey: "timeinterval",
            ykeys: result.ykey,
            labels: result.labels
        });
    });
</script>

I can use Json.NET if necessary. If posible, I would like to get ride off the JSON string appending. I would like to have an array and call serialize to get data: json objects in array format that morris.js needs: http://jsbin.com/uqawig/441/embed?js,output

{"data":"{"timeinterval":"2015-10-22T00:00:00","Firm":4,...},{"timeinterval":"2015-10-22T00:00:00","Firm":5,...},...}

Answer 1

ViewModel:

public class StatistikaByCustomer
{
    public ArrayList data { get; set; }
    public string xkey { get; set; }
    public List<string> ykey { get; set; }
    public List<string> labels { get; set; }
}

Controller:

public JsonResult StatistikaJson()
{
    StatistikaByCustomer statisikaObject = new StatistikaByCustomer();           
    ArrayList arrayList = new ArrayList();                  
    List<Data> listData = new List<Data>();
    DbDataReader reader = null;
    using (var cmd = db.Database.Connection.CreateCommand())
    {
        if (db.Database.Connection.State == ConnectionState.Closed)
        {
            db.Database.Connection.Open();
        }

        cmd.CommandText = "EXEC GetClientConnectsByCustomer @start='" + DateTime.Now.AddMonths(-1).Date.ToString("yyyy-MM-dd") + "',@end='" + DateTime.Now.Date.ToString("yyyy-MM-dd") + "',@interval = 24";
        reader = cmd.ExecuteReader();

        if (reader.HasRows)
        {
            while (reader.Read())
            {                  
                string name = reader.GetName(0);
                object value = reader.GetDateTime(0).ToString("yyyy-MM-dd");

                listData.Add(new Data() { Name = name, Value = value });

                for (int i = 1; i <= reader.FieldCount - 1; i++)
                {
                    name = reader.GetName(i).Replace(" ", "");
                    value = reader.GetInt32(i);                                               
                    listData.Add(new Data() { Name = name, Value = value });
                }

                arrayList.Add(JsonConvert.SerializeObject(listData.ToDictionary(x => x.Name, y => y.Value)));
                listData.Clear();
                //Debug.WriteLine("{0}\t{1}\t{2}\t{3}\t{4}", reader.GetName(0), reader.GetDateTime(0), reader.GetInt32(1), reader.GetInt32(2), reader.GetInt32(3), reader.GetInt32(4));   
            }               
        }                           
        statisikaObject.data = arrayList;
        statisikaObject.xkey = reader.GetName(0);
        statisikaObject.labels = Enumerable.Range(1, reader.FieldCount - 1).Select(reader.GetName).ToList();
        var tempYkey = Enumerable.Range(1, reader.FieldCount - 1).Select(reader.GetName).ToArray();
        statisikaObject.ykey = tempYkey.Select(x => x.Replace(" ", "")).ToList();
        reader.Close();    
        db.Database.Connection.Close();
    }

    return Json(statisikaObject, JsonRequestBehavior.AllowGet);
}

View:

<script type="text/javascript">    
    $.getJSON('@Url.Action("StatistikaJson")', function (result)
    {
        new Morris.Line({
            element: 'line-example',
            data: $.parseJSON("[" + result.data + "]"),
            xkey: result.xkey,
            ykeys: result.ykey,
            labels: result.labels
    });
    });
</script>

Answer 2

public class Datum
{
    public string timeinterval { get; set; }
    public int Firm { get; set; }
    public int Firm1 { get; set; }
    public int Firm2 { get; set; }
    public int Firm3 { get; set; }
    public int Firm4 { get; set; }
}

public class RootObject
{
    public List<Datum> data { get; set; }
    public string xkey { get; set; }
    public List<string> ykey { get; set; }
    public List<string> labels { get; set; }
}

By putting this code get Serialize json string

var collection = new List<RootObject>();

dynamic collectionWrapper = new
{
    people = new RootObject()
    {
        xkey = "timeinterval",
        data = new List<Datum> 
        { 
            new Datum() 
            { 
                timeinterval = "2015-10-22T00: 00: 00", 
                Firm = 4,
                Firm1= 4,
                Firm2= 22,
                Firm3= 30,
                Firm4= 19
            },
            new Datum() 
            { 
                timeinterval = "2015-10-23T00: 00: 00", 
                Firm = 2,
                Firm1= 5,
                Firm2= 29,
                Firm3= 34,
                Firm4= 219
            }
        },
        ykey = new List<string> { "Firm", "Firm1", "Firm2", "Firm3", "Firm4" },
        labels = new List<string> { "Firm", "Firm1", "Firm2", "Firm3", "Firm4" }
    }
};
var output = JsonConvert.SerializeObject(collectionWrapper);

Edit:

var collection = new StatististikaByCustomer
{
    xkey = "timeinterval",
    ykey = new List<string>{"Firm","Firm1","Firm2","Firm3","Firm4"},
    labels = new List<string> { "Firm", "Firm1", "Firm2", "Firm3", "Firm4" },
    data = new Dictionary<string, string>
    {
        { "timeinterval", "2015-10-22T00: 00: 00" },
        { "Firm", "4" },
        { "Firm1", "4" },
        { "Firm2", "22" },
        { "Firm3", "30" },
        { "Firm4", "19" }
    }
};

string json = JsonConvert.SerializeObject(collection);

Class:

public class StatististikaByCustomer
{
    public string xkey { get; set; }
    public Dictionary<string, string> data { get; set; }
    public List<string> ykey { get; set; }
    public List<string> labels { get; set; }

}

Update:

var collection = new StatististikaByCustomer
{
    xkey = "timeinterval",
    ykey = new List<string> { "Firm", "Firm1", "Firm2", "Firm3", "Firm4" },
    labels = new List<string> { "Firm", "Firm1", "Firm2", "Firm3", "Firm4" },
    data = new List<Dictionary<string,string>>
    {
        new Dictionary<string, string>
        {
            { "timeinterval", "2015-10-22T00: 00: 00" },
            { "Firm", "4" },
            { "Firm1", "4" },
            { "Firm2", "22" },
            { "Firm3", "30" },
            { "Firm4", "19" }
        },
        new Dictionary<string, string>
        {
            { "timeinterval", "2015-10-23T00: 00: 00" },
            { "Firm", "2" },
            { "Firm1", "5" },
            { "Firm2", "29" },
            { "Firm3", "34" },
            { "Firm4", "219" }
        }
    }
};

string json = JsonConvert.SerializeObject(collection);

Class:

public class StatististikaByCustomer
{
    public string xkey { get; set; }
    public List<Dictionary<string, string>> data { get; set; }
    public List<string> ykey { get; set; }
    public List<string> labels { get; set; }

}