I'm writing an API client from a spec that came from Nswag studio. I am able to retrieve that data using the supplied client.PlansAsync(apikey).GetAwaiter().GetResult(), but I'm struggling to turn the returned ICollection into something that I'm able to bulk insert into an Oracle database table.

I have attempted to create a DataTable but during the transformation into a dataTable, an exception is being thrown. I'm suspect it has something to do with nullable types in the collection.

My guess is that I should be attempting to do the inserts using Entity Framework, but it seems like adding all of the extra EF Core stuff is overkill for this particular client.

I feel like the Oracle Bulk copy methods are perfect for what I'm intending, but I've been running to the issue listed above.

Any help would be greatly appreciated.

TIA

EDIT: Here's the code in question.

//first in the calling class
ICollection<Plans> plansFromApi = client_.PlansAsync(apiKey).GetAwaiter().GetResult();

ListToDatTable listToDt = new();
List<Plans> ps = plansFromApi.ToList();
DataTable dt = listToDt.ToDataTable<Plans>(ps);


//second the List to Datatable class

public class ListToDataTable
{
    public DataTable ToDataTable<T>(List<T> items)
    {
        DataTable dataTable = new(typeof(T).Name);
        PropertyInfo[] Props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
        foreach(T item in items)
        {
            var values = new object[Props.Length];
            for(int i = 0; i < Props.Length; i  )
            {
                values[i] = Props[i].GetValue(item);
            }
            //line where the exception is thrown
            //System.ArgumentException: 'Input array is longer than the number of columns in this table.'
            dataTable.Rows.Add(values);
         }
     }
}

EDIT 2: Here is what came out of Nswag studio. This is just one of 15 datasets that I need to retrieve. This isn't the one I'm currently testing, as that one has 25 properties, so for brevity I'm including one of the smaller ones. In the end they all will be the same, since they are all going to be processed the exact same way, and yes, I have tested with this dataset as well, and received the same exception.

[System.CodeDom.Compiler.GeneratedCode("NJsonSchema", "10.5.2.0 (Newtonsoft.Json v12.0.0.0)")]
    public partial class ContactGroupedManufacturer
    {
        [Newtonsoft.Json.JsonProperty("lastContacted", Required = Newtonsoft.Json.Required.Default, NullValueHandling = Newtonsoft.Json.NullValueHandling.Ignore)]
        public System.DateTimeOffset? LastContacted { get; set; }
    
        [Newtonsoft.Json.JsonProperty("vendorContactId", Required = Newtonsoft.Json.Required.DisallowNull, NullValueHandling = Newtonsoft.Json.NullValueHandling.Ignore)]
        public int VendorContactId { get; set; }
    
        [Newtonsoft.Json.JsonProperty("ManufacturerId", Required = Newtonsoft.Json.Required.DisallowNull, NullValueHandling = Newtonsoft.Json.NullValueHandling.Ignore)]
        public int ManufacturerId { get; set; }
    
        [Newtonsoft.Json.JsonProperty("website", Required = Newtonsoft.Json.Required.Default, NullValueHandling = Newtonsoft.Json.NullValueHandling.Ignore)]
        public string Website { get; set; }
    
    
    }

Here area few rows of data:

CodePudding user response：

You probably have problem with quantity of columns in the DataTable. Try this code:

public class ListToDataTable
{
    public DataTable ToDataTable<T>(List<T> items)
    {
        DataTable dataTable = new DataTable();
        PropertyInfo[] Props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
        bool columnsAlreadyCreated=false;
        foreach(T item in items)
        {
            if (columnsAlreadyCreated==false)
            {
              for(int i = 0; i < Props.Length; i  )
              {
                dataTable.Columns.Add(Props[i].Name,Props[i].PropertyType);
              }
              columnsAlreadyCreated=true;
            }
            var values = new object[Props.Length];
            for(int i = 0; i < Props.Length; i  )
            {
                values[i] = Props[i].GetValue(item);
            }
            //line where the exception is thrown
            //System.ArgumentException: 'Input array is longer than the number of columns in this table.'
            dataTable.Rows.Add(values);
         }
     }
}

CodePudding user response：

You can wrap the IEnumerable in a IDataReader to pass to the BulkCopy method. See eg: ObjectDataReader

Once you have an IDataReader, pass it to OracleBulkCopy.WriteToServer(IDataReader) See OracleBulkCopy.