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

Question

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?

Answer 1

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,

Answer 2

Looking at the code, I believe this is what you need. You are newing up a DataRow and adding it to the data table in the wrong place.

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);
    }
}

Can you try this and let me know. And please don't forget to mark as answer if it helps.

Answer 3

Error CS0029 Cannot implicitly convert type 'string' to 'System.Data.DataRow'

 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++)
            {
                    row = dataloop.series[0].values[x][y].ToString();

            }
            table.Rows.Add(row);
            }

        }

I think the problem is in public List<List<object>> values { get; set; }, because I can write the columns values in the datatable because this is a List<string>.

Answer 4

Hello @Eric Bezemer

First off, the following is not a drop-in solution but instead bits and pieces to learn from.

• Creates a DataTable to work from with three columns of different data types
• Add several rows
• Get column names
• Clone first DataTable, this is for showing how to import rows (not what I believe you want but good to know)
• Creates otherTable where all columns are of type string.
• Show how to populate second and third DataTable containers

Code

Other than the "is not null" which is C# 9, all the remaining code is pre C# 9

using System;
using System.Data;
using System.Linq;

namespace ItemArrayToStringArray
{
    class Program
    {
        static void Main(string[] args)
        {
            
            var dt = new DataTable();
            dt.Columns.Add(new DataColumn() {ColumnName = "Id", DataType = typeof(int), AutoIncrement = true});
            dt.Columns.Add(new DataColumn() {ColumnName = "FullName", DataType = typeof(string)});
            dt.Columns.Add(new DataColumn() {ColumnName = "BirthDate", DataType = typeof(DateTime)});

            var columnName = 
                dt.Columns
                    .Cast<DataColumn>()
                    .Select(col => col.ColumnName)
                    .ToList();

            var otherTable = new DataTable();

            foreach (var colName in columnName)
            {
                otherTable.Columns.Add(colName);
            }

           
            var clone = dt.Clone();

            dt.Rows.Add(null, "Karen Payne", new DateTime(1956,9,24));
            dt.Rows.Add(null, "Jim Beam", new DateTime(1822,3,4));
            dt.Rows.Add(new object[] { null});

            foreach (DataRow dataRow in dt.Rows)
            {
                var itemArrayData = dataRow.ItemArray.Where(item => item is not null)
                    .Select(x => x.ToString())
                    .ToArray();

                Console.WriteLine(string.Join(",", itemArrayData));

                clone.ImportRow(dataRow);

                otherTable.Rows.Add(itemArrayData);
            }

            foreach (DataRow otherTableRow in otherTable.Rows)
            {
                Console.WriteLine(string.Join(",", otherTableRow.ItemArray));
            }
            
            Console.ReadLine();
        }
    }
    
}

Or

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Threading.Channels;

namespace ItemArrayToStringArray
{
    class Program
    {
        static void Main(string[] args)
        {
            List<Item> items = new();
            
            var dt = new DataTable();
            dt.Columns.Add(new DataColumn() {ColumnName = "Id", DataType = typeof(int), AutoIncrement = true});
            dt.Columns.Add(new DataColumn() {ColumnName = "FullName", DataType = typeof(string)});
            dt.Columns.Add(new DataColumn() {ColumnName = "BirthDate", DataType = typeof(DateTime)});

            var columnName = 
                dt.Columns
                    .Cast<DataColumn>()
                    .Select(col => col.ColumnName)
                    .ToList();

          

            dt.Rows.Add(null, "Karen Payne", new DateTime(1956,9,24));
            dt.Rows.Add(null, "Jim Beam", new DateTime(1822,3,4));
            dt.Rows.Add(new object[] { null});

            foreach (DataRow dataRow in dt.Rows)
            {
                var itemArrayData = dataRow.ItemArray.Where(item => item is not null)
                    .Select(x => x.ToString())
                    .ToArray();
                
                items.Add(new Item(itemArrayData));
            }

            items.ForEach(Console.WriteLine);
            
            Console.ReadLine();
        }
    }

    public class Item
    {
        public Item(string[] data)
        {
            Column1 = data[0];
            Column2 = data[1];
            Column3 = data[2];
        }
        public string Column1 { get; set; }
        public string Column2 { get; set; }
        public string Column3 { get; set; }

        public override string ToString() => $"{Column1},{Column2},{Column3}";

    }
    
}

Answer 5

Based on the information you provided so far, I guess that the column lists of all Series objects are the same, right?
If this is the case, then the following code cannot be written into foreach, otherwise, you are repeatedly adding existing columns:

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

Moving it outside and using JaliyaUdagedara's code should solve your problem.

If they are not the same, then the current datatable structure will look like this:

And your expectation might be this:

I have to say that this looks a little strange, so could you please provide some json data so that we can understand its real format?

---

If the response is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.