question

Protecteric avatar image
0 Votes"
Protecteric asked ·

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

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?

dotnet-csharp
10 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

JaliyaUdagedara avatar image
0 Votes"
JaliyaUdagedara answered ·

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,

52214-image.png






image.png (8.4 KiB)
· 3
10 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

PrintDataTable method just in case, got it from StackOverflow


0 Votes 0 ·

I will try it, thank you!

0 Votes 0 ·

Thanks for your help, great!

0 Votes 0 ·
JaliyaUdagedara avatar image
0 Votes"
JaliyaUdagedara answered ·

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.

10 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Protecteric avatar image
0 Votes"
Protecteric answered ·

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>.

· 2
10 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Why are you doing,

  row = dataloop.series[0].values[x][y].ToString();

It needs to be,

 row[table.Columns[y]] = dataloop.series[0].values[x][y].ToString();


0 Votes 0 ·

Thanks for your advice, but it wont work. I posted the script at the bottom of this page(https://docs.microsoft.com/answers/answers/213736/view.html).

  • I already get the columns in the datatable list.

  • I want to get the rows in the datatable list.

0 Votes 0 ·
karenpayneoregon avatar image
0 Votes"
karenpayneoregon answered ·

Hello @Protecteric

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




· 1
10 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks Karen, but if I want to loop through a public List<List<object>> and I want the values in a datatable how can I fix that?

I have now the columns in the datatable list, see below(https://docs.microsoft.com/answers/answers/213950/view.html) But I also want the rows in the datatable list, but when I try this with the original code the list stays empty.

eventually when I get all the information in the datatable I want to copy it to a mssql table.



0 Votes 0 ·
TimonYang-MSFT avatar image
0 Votes"
TimonYang-MSFT answered ·

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:
52067-2.png
And your expectation might be this:
52181-1.png
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.


2.png (2.0 KiB)
1.png (2.1 KiB)
· 1
10 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi Timon, thanks for your input. The columns are in the list and this works for me. I want to get the rows in the datatable list so I can use them for a mssql table.

https://docs.microsoft.com/answers/answers/213950/view.html

0 Votes 0 ·
Protecteric avatar image
0 Votes"
Protecteric answered ·

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

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



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



image.png (27.7 KiB)
image.png (8.1 KiB)
10 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Protecteric avatar image
0 Votes"
Protecteric answered ·

It works, thanks a lot guys! I learn a lot :-)

10 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.