C# converting value for each column into array

Question

I want to convert a C# object or datatable such that, each column becomes a key & values becomes a array

year    productOne     ProductTwo      ProductThree
2010    1              2                 3
2011    10             20                30
2012    100            200               300
2013    1000           2000              3000

I am looking to generate following JSON output. Please keep in mind, no of rows and columns are dynamic.

[{
    name: productOne ,
    data: [1,10,100,1000]
},{
    name: productTwo ,
    data: [2,20,200,2000]
},{
    name: productThree ,
    data: [3,30,300,3000]
}]

how is it stored - not sorted (ps - dont say 'in a database' - what c# data structure is it in) — pm100
– pm100, Commented Mar 28, 2016 at 22:21
@pm100: As i mentioned in my questions, data can be stored in list of C# object or a datatable. — SharpCoder
– SharpCoder, Commented Mar 28, 2016 at 22:43
@JleruOHeP: I don't know why, but I kept reading it as sorted and not stored :( — SharpCoder
– SharpCoder, Commented Mar 28, 2016 at 22:44

OpenStack · Accepted Answer · 2016-03-28 22:32:31Z

3

DataTable dt = new DataTable();
            dt.Columns.Add("col1");
            dt.Columns.Add("col2");
            dt.Columns.Add("col3");

            DataRow dr = dt.NewRow();
            dr[0] = "10";
            dr[1] = "20";
            dr[2] = "30";
            dt.Rows.Add(dr);

            dr = dt.NewRow();
            dr[0] = "100";
            dr[1] = "200";
            dr[2] = "300";
            dt.Rows.Add(dr);

            dr = dt.NewRow();
            dr[0] = "1000";
            dr[1] = "2000";
            dr[2] = "3000";
            dt.Rows.Add(dr);

            List<dynamic> list = new List<dynamic>();

            for (int i = 0; i < dt.Columns.Count; i++)
            {
                dynamic result = new ExpandoObject();
                result.name = dt.Columns[i].ColumnName;
                result.data = dt.Rows.Cast<DataRow>()
                               .Select(row => row[i])
                               .ToArray();
                list.Add(result);
            }

            var op = JsonConvert.SerializeObject(list);

answered Mar 28, 2016 at 22:32

OpenStack

5,71611 gold badges48 silver badges94 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

OpenStack Over a year ago

@pm100: I don't understand. What is the issue here?

pm100 Over a year ago

becuase your result is hard coded- he says "Please keep in mind, no of rows and columns are dynamic.".

OpenStack Over a year ago

@pm100 Please correct me if I am wrong but this solution is dynamic. It will grow based on no of rows and columns available in datatable. I am just using datatable as source.

Jeb50 Over a year ago

@OpenStack i is the count index of Columns, if dt has one more row, then row[i] won't retrieve all the vertical cells in a column into array.

Saleem · Accepted Answer · 2016-03-28 23:31:23Z

You can definitely do this with help of Lists and Dictionaries.

public static IDictionary<string, List<object>> TransposeDataTable(DataTable table)
{

    if (table == null) {
        return null;
    }

    var dicData = new Dictionary<string, List<object>> ();
    var lstRows = new List<object>[table.Columns.Count];

    foreach (DataRow item in table.Rows) {
        for (var i = 0; i < table.Columns.Count; i++) {
            if (lstRows [i] == null) {
                lstRows [i] = new List<object> ();
            }
            lstRows [i].Add (item [i]); 
        }
    }

    for (var i = 0; i < table.Columns.Count; i++) {
        dicData.Add (table.Columns [i].ColumnName, lstRows [i]);
    }

    return dicData;
}

Code above will create keys for dictionary from Column names of tables and data for each column will go into corresponding list. It's sort of tuple.

Let's assume your DataTable is a valid dt

var dataObject = TransposeDataTable (dt);
var jsonString = JsonConvert.SerializeObject (dataObject, Formatting.Indented);

After successful conversion, jsonString will contain our good looking Json object. In my case, dt as dummy data so will produce something like.

{
    "year": [
        "2010",
        "2011",
        "2012",
        "2013",
        "2014"
    ],
    "produceOne": [
        "1",
        "11",
        "21",
        "31",
        "41"
    ],
    "ProductTwo": [
        "2",
        "12",
        "22",
        "32",
        "42"
    ],
    "ProductThree": [
        "3",
        "13",
        "23",
        "33",
        "43"
    ]
}

Collectives™ on Stack Overflow

C# converting value for each column into array

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related