C# insert List<List<object>> in datatable and then to sql

Eric Bezemer 196

Background story:

I want to get data from influxdb and copy this data to SQL table and I want to do this with a datatable.

structure of the JSON file:

public class Series
{
    public string name { get; set; }
    public List<string> columns { get; set; }
    public List<List<object>> values { get; set; }
    public bool partial { get; set; }
}

public class Result
{
    public int statement_id { get; set; }
    public Series[] series { get; set; }
}

public class Root
{
    public List<Result> results { get; set; }
}

And this is the loop Iam building to get the columns and values:

foreach (var dataloop in root.results)
{
    for (int i = 0; i < countColumns; i++)
    {
        TestConsole.çolumn = dataloop.series[0].columns[i].ToString();
        table.Columns.Add(TestConsole.çolumn);
    }

    for (int x = 0; x < countRows; x++)
    {
        DataRow row;

        for (int y = 0; y < countColumns; y++)
        {
            row = table.NewRow();
            row[table.Columns[y]] = (string) dataloop.series[0].values[x][y].ToString();
            table.Rows.Add(row);
        }
    }
}

I can get the columns, but I am stuck with the values. I try a lot of things but I can't understand why this won't work. Can someone give me a tip to solve this?

Accepted answer

Jaliya Udagedara 2,821 MVP

I am sorry, your code still has a couple of mistakes. I just updated your code, fixed issues and tried with sample test data. Can you try this code.

class Program  
{  
    static void Main(string[] args)  
    {  
        var root = new Root  
        {  
            results = new List<Result>  
            {  
                new Result  
                {  
                    statement_id = 10,  
                    series = new Series[]  
                    {  
                        new Series  
                        {  
                            name = "some name",  
                            columns = new List<string> { "some column1", "some column2" },  
                            partial = true,  
                            values = new List<List<object>>  
                            {  
                                new List<object> { "some value 1", "some value 2" },  
                                new List<object> { "some value x", "some value y" },  
                            }  
                        }  
                    }  
                }  
            }  
        };  
  
        var countColumns = root.results.First().series.First().columns.Count;  
        var countRows = root.results.First().series.First().values.Count;  
  
        var table = new DataTable();  
  
        foreach (Result dataloop in root.results)  
        {  
            for (int i = 0; i < countColumns; i++)  
            {  
                var çolumn = dataloop.series[0].columns[i].ToString();  
                table.Columns.Add(çolumn);  
            }  
  
            for (int x = 0; x < countRows; x++)  
            {  
                DataRow row = table.NewRow();  
                for (int y = 0; y < countColumns; y++)  
                {  
                    row[table.Columns[y]] = dataloop.series[0].values[x][y].ToString();  
                }  
                table.Rows.Add(row);  
            }  
        }  

        // some helper method to print the data  
        PrintDataTable(table);  
    }  
}

Output is like this,

Jaliya Udagedara 2,821 Reputation points MVP

2020-12-30T07:42:54.847+00:00

PrintDataTable method just in case, got it from StackOverflow
Eric Bezemer 196 Reputation points

2020-12-30T08:10:30+00:00

I will try it, thank you!
Eric Bezemer 196 Reputation points

2020-12-30T13:41:41.003+00:00

Thanks for your help, great!

6 additional answers

Eric Bezemer 196

When I run the orginal code, I get the columns that I want they are in a List in the datatable.

The list for rows stays empty(I run trough the loop 4 times to get alle the rows I have to do this 271)

 using Newtonsoft.Json;  
    using System;  
    using System.Collections.Generic;  
    using System.Data;  
    using System.Data.SqlClient;  
    using System.Net;  
      
    //using Newtonsoft.Json;  
      
    namespace GetInfluxdbData  
    {  
        public class TestConsole  
        {  
            public static string çolumn;  
            public static string row;  
      
            public static void Main(string[] args)  
            {  
      
                string json;  
      
                //string url = //THIS IS NORMALLY THE JSON STRING, BUT FOR NOW I LEFT THIS INFORMATION OUT  
                  
      
      
                json = DownloadJson(url);  
      
                //Making object from the url as json file  
                Root root = JsonConvert.DeserializeObject<Root>(json);  
      
                int countColumns = root.results[0].series[0].columns.Count;  
                int countRows = root.results[0].series[0].values.Count;  
                DataTable table = new DataTable();  
      
      
      
                foreach (var dataloop in root.results)  
                {  
                    for (int i = 0; i < countColumns; i++)  
                    {  
                        TestConsole.çolumn = dataloop.series[0].columns[i].ToString();  
                        table.Columns.Add(TestConsole.çolumn);  
                      
                    }  
                    for (int x = 0; x < countRows; x++)  
                    {  
      
                        DataRow row = table.NewRow();  
      
                    for (int y = 0; y < countColumns; y++)  
                    {  
                            TestConsole.row = dataloop.series[0].values[x][y].ToString();  
                            table.Rows.Add(TestConsole.row);  
                    }  
                    table.Rows.Add(row);  
                    }  
                      
                }  
      
      
            }  
      
            public static string DownloadJson(string downloadURL)  
            {  
                using (WebClient client = new WebClient())  
                {  
                    return client.DownloadString(downloadURL);  
                }  
            }  
            // Converting the json file in classes with this site https://json2csharp.com/ for converting json to C# classes  
            public class Series  
            {  
                public string name { get; set; }  
                public List<string> columns { get; set; }  
                public List<List<object>> values { get; set; }  
                public bool partial { get; set; }  
            }  
      
            public class Result  
            {  
                public int statement_id { get; set; }  
                public Series[] series { get; set; }  
            }  
      
            public class Root  
            {  
                public List<Result> results { get; set; }  
            }  
      
      
        }  
    }

Eric Bezemer 196 Reputation points

2020-12-30T13:40:33.74+00:00

It works, thanks a lot guys! I learn a lot :-)
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

C# insert List<List<object>> in datatable and then to sql

6 additional answers

Your answer