Can't iterate a JSON array

Question

I am trying to use different way of putting data in to SQL using "SqlBulkCopy", and I am getting some error.

Bottom is what currently working without issue for now.

static void Main(string[] args)
{
    ILogger logger = Bootstrap.Logger("");                     
    string paramValue = SayHello(logger);

    string connString = @"Data Source=net;USER id=admin;Password=ess;Initial Catalog=fin";

    string sprocname = "InsertPerfCounterData3";
    string paramName = "@json";

    using (SqlConnection conn = new SqlConnection(connString))
    {
        conn.Open();
        using (SqlCommand cmd = new SqlCommand(sprocname, conn))
        {
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add(new SqlParameter(paramName, paramValue));

            cmd.ExecuteReader();
        }
    }

Currently, paramValue has this output:

"[{\"GLDETAIL\":{\"RECORDNO\":\"264378-1756289-919567--accrual\",\"BATCH_DATE\":\"02/01/2022\"}},
{\"GLDETAIL\":{\"RECORDNO\":\"264378-1756290-919568--accrual\",\"BATCH_DATE\":\"02/01/2022\"}}]"

I am trying to use bottom code, instead:

var table = new DataTable();
table.Columns.Add("RECORDNO", typeof(int));
table.Columns.Add("BATCH_DATE", typeof(DateTime));

foreach (var r in paramValue.Data)
    table.Rows.Add(r.RECORDNO, r.BATCH_DATE);

const string connString1 = @"Data Source=net;USER id=admin;Password=ess;Initial Catalog=fin";

using (var conn = new SqlConnection(connString))
using (var bulk = new SqlBulkCopy(conn))
{
    bulk.DestinationTableName = "PerfCounter3";
    conn.Open();
    bulk.WriteToServer(table);
}

This is area where I am getting error, and I am not sure how to modify:

Update (5/10/2022):

I need some help regards to how to insert data properly as I am getting an error like shown below:

I did not include all other Class codes.

I need some help to iterate each line to database.

static void Main(string[] args)
{
    ILogger logger = Bootstrap.Logger("");                     
    string paramValue = SayHello(logger);

    const string connString = @"Data Source=net;USER id=admin;Password=ess;Initial Catalog=fin";

    var data = JsonConvert.DeserializeObject<Wossit[]>(paramValue);

    var table = new DataTable();
    table.Columns.Add("RECORDNO", typeof(Char));
    table.Columns.Add("BATCH_DATE", typeof(DateTime));

    foreach (var record in data)
        table.Rows.Add($"{record.Detail.RecordNumber}, {record.Detail.BatchDate}");

    using (var conn = new SqlConnection(connString))
    using (var bulk = new SqlBulkCopy(conn))
    {
        bulk.DestinationTableName = "PerfCounter4";
        conn.Open();
        bulk.WriteToServer(table);
    }
}    

HResult=0x80070057
Message=String must be exactly one character long.Couldn't store 
<264378-1756289-919567--accrual, 2/1/2022 12:00:00 AM> in RECORDNO 
Column. Expected type is Char.
Source=System.Data.Common
StackTrace:
at System.Data.DataColumn.set_Item(Int32 record, Object value)
at System.Data.DataTable.NewRecordFromArray(Object[] value)
at System.Data.DataRowCollection.Add(Object[] values)
at Sage0413.Program.Main(String[] args) in C:\..\Main.cs:line 65

This exception was originally thrown at this call stack:

[External Code]

Inner Exception 1:
FormatException: String must be exactly one character long.

Answer 1

Your exception shows you what is happening. You are trying to insert a VARCHAR into a CHAR field in your database (the one for RECORDNO that you initially has as an INT).

A CHAR is one single character, your RECORDNO is a string of variable characters -- VARCHAR, which most closely matches a c# string.

Try changing table.Columns.Add("RECORDNO", typeof(Char)); to table.Columns.Add("RECORDNO", typeof(String)); to see if it fixes your issue.

EDIT:

It seems you are also passing in a single string into the table row here:

table.Rows.Add($"{record.Detail.RecordNumber}, {record.Detail.BatchDate}");

Which may not be what you wanted.

It may help to add the data to the table using the Add(Object[]) method. I think you are currently passing in a character array to that method -- $"{record.Detail.RecordNumber}, {record.Detail.BatchDate}" may be interpreted as a character array and accepted as a parameter for the add method without issue.

Perhaps changing

table.Rows.Add($"{record.Detail.RecordNumber}, {record.Detail.BatchDate}");

to

table.Rows.Add(new object[]{record.Detail.RecordNumber, record.Detail.BatchDate});

will solve your issue.

Answer 2

You seem to be missing a JSON parse step. You can't magically get from a string to data - you need to interpret the data; perhaps something like:

using Newtonsoft.Json;
using System;

string json = "[{\"GLDETAIL\":{\"RECORDNO\":\"264378-1756289-919567--accrual\",\"BATCH_DATE\":\"02/01/2022\"}},{\"GLDETAIL\":{\"RECORDNO\":\"264378-1756290-919568--accrual\",\"BATCH_DATE\":\"02/01/2022\"}}]";

var data = JsonConvert.DeserializeObject<Wossit[]>(json);
foreach (var record in data)
{
    Console.WriteLine($"{record.Detail.RecordNumber}, {record.Detail.BatchDate}");
}


class Wossit
{
    [JsonProperty("GLDETAIL")]
    public Somet Detail { get; set; }
}
class Somet
{
    [JsonProperty("RECORDNO")]
    public string RecordNumber { get; set; }
    [JsonProperty("BATCH_DATE")]
    public DateTime BatchDate { get; set; }
}