using System;

using System.Collections;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.IO;

using System.Data;

using System.ComponentModel.DataAnnotations;

using System.Globalization;

using System.Reflection;

using System.Collections.ObjectModel;

using System.ComponentModel;

using System.Diagnostics;

​

using NUnit.Framework; // Throws NUnit.Framework.AssertionException

​

using CsvHelper;

using CsvHelper.Configuration;

using CsvHelper.Configuration.Attributes;

using CsvHelper.Expressions;

using CsvHelper.TypeConversion;

​

using Newtonsoft.Json;

​

using CSVLoader;

​

//https://stackoverflow.com/questions/68028093/csvhelper-populating-datatable-slowish

​

namespace CSVLoader

{

     // B

     public class B

     {

         [Index(0)]

         public string A { get; set; }

         [Index(1)]

         public string BString { get; set; } // Fixed

         [Index(2)]

         public int? C { get; set; }

         [Index(3)]

         public string D { get; set; }

         [Index(4)]

         public string E { get; set; }

         [Index(5)]

         public DateTime? F { get; set; }

         [Index(6)]

         public decimal? G { get; set; }

         [Index(7)]

         public decimal? H { get; set; }

         [Index(8)]

         public decimal? I { get; set; }

         [Index(9)]

         public decimal? J { get; set; }

         [Index(10)]

         public int? K { get; set; }

         [Index(11)]

         public string L { get; set; }

         [Index(12)]

         public DateTime? M { get; set; }

     }

​

     // B

     public sealed class BMap : ClassMap<B> // Fixed

     {

         public BMap()

         {

             // AutoMap(CultureInfo.InvariantCulture);

             Map(m => m.A).Index(0);

             Map(m => m.BString).Index(1); // Fixed

             Map(m => m.C).Index(2);

             Map(m => m.D).Index(3);

             Map(m => m.E).Index(4);

             Map(m => m.F).Index(5).TypeConverterOption.Format("yyyyMMdd");

             Map(m => m.G).Index(6);

             Map(m => m.H).Index(7);

             Map(m => m.I).Index(8);

             Map(m => m.J).Index(9);

             Map(m => m.K).Index(10);

             Map(m => m.L).Index(11);

             Map(m => m.M).Index(12).TypeConverterOption.Format("yyyy-MM-dd-hh.mm.ss.ffffff");

         }

     }

}

​

    public class FileLoader

    {

        public System.Diagnostics.Stopwatch csvTableTimer { get; } = new();

​

        public long Load<TClass, TClassMap>(string rFile, int dtbatchSize) where TClassMap : ClassMap<TClass>, new()

        {

            bool isBadRecord = false;

            long badCount = 0;

            long runningCount = 0;

            long goodCount = 0;

            long batchCount = 0;

​

            var rconfig = CreateCsvConfiguration(

                x => 

                {

                    isBadRecord = true;

                    //ErrRecords.Add(x.RawRecord);

                    ++badCount;

                });

            // Create datatable using class as definition.

            var dt = UtilExtensions.CreateDataTable(typeof(TClass));

​

            using (var reader = new StreamReader(rFile))

            {

                //reader.ReadLine();  FIXED - THIS SKIPPED THE FIRST LINE AND CAUSED A RECORD TO BE OMITTED.

                using (var csv = new CsvReader(reader, rconfig))

                {

                    csv.Context.RegisterClassMap<TClassMap>();

​

                    dt.BeginLoadData();

                    while (csv.Read())

                    {

                        isBadRecord = false;

                        //csvReadTimer.Start();

                        var record = csv.GetRecord<TClass>();

                        //csvReadTimer.Stop();

​

                        runningCount++;

                        if (!isBadRecord)

                        {

                            csvTableTimer.Start();

                            // Process row and populate datatable

                            DataRow dr = dt.NewRow();

                            foreach (DataColumn dc in dt.Columns)

                            {

                                var value = record.GetType().GetProperty(dc.ToString()).GetValue(record);

                                if (value != null)

                                {

                                    dr[dc.ToString()] = value;

                                }

                            }

                            dt.Rows.Add(dr);

                            csvTableTimer.Stop();

                            goodCount++;

                            if (++batchCount >= dtbatchSize)

                            {

                                // Flush the data table

                                FlushTable(dt);

                                batchCount = 0;

                            }

                        }

                    }

                    dt.EndLoadData();

                    FlushTable(dt);

                    Commit();

                }

            }

            return goodCount;

        }

​

        protected virtual void FlushTable(DataTable dt) => dt.Clear();  // Replace with SqlBulkCopy 

        protected virtual void Commit() {} // Replace with transaction.Commit();

        public static CsvConfiguration CreateCsvConfiguration(BadDataFound badDataFound) => 

            new CsvHelper.Configuration.CsvConfiguration(CultureInfo.InvariantCulture)

            {

                BufferSize = 1024,

                Delimiter = ",",

                AllowComments = true,

                HasHeaderRecord = false,

                HeaderValidated = null,

                IgnoreBlankLines = true,

                MissingFieldFound = null,

                Comment = '#',

                Escape = '"',

                TrimOptions = TrimOptions.Trim,

                BadDataFound = badDataFound,

            };

    }

​

    public static partial class UtilExtensions

    {

        static IEnumerable<PropertyInfo> GetSerializableProperties(this Type type) => 

            type.GetProperties().Where(p => p.GetIndexParameters().Length == 0 && p.CanRead && p.CanWrite && p.GetGetMethod() != null && p.GetSetMethod() != null);

        public static DataTable CreateDataTable(Type type)

        {

            var dt = new DataTable();

            foreach (var p in type.GetSerializableProperties())

                dt.Columns.Add(p.Name, Nullable.GetUnderlyingType(p.PropertyType) ?? p.PropertyType);

            return dt;

        }

    }

​

    class TestClass

    {

        public static void Test()

        {

            var rFile = "Question68028093.csv";

            int count = 5555;

            int repeats = 20;

            var dateTimeStart = new DateTime(2021, 6, 18);

            var records = Enumerable.Range(0, count).Select(i => new B

                                                           {

                                                               A = "A " + i.ToString(),

                                                               BString = "BString",

                                                               C = i,

                                                               D = "D " + i.ToString(),

                                                               E = "E String",

                                                               F = dateTimeStart,

                                                               G = i,

                                                               H = i,

                                                               I = i / 100m,

                                                               J = null,

                                                               K = i,

                                                               L = "L",

                                                               M = dateTimeStart.AddSeconds(i),

                                                           });

            var config = FileLoader.CreateCsvConfiguration(x => { });

            using (var writer = new StreamWriter(rFile))

            using (var csv = new CsvWriter(writer, config))

            {

                csv.Context.RegisterClassMap<BMap>();

                csv.WriteRecords(records);              

            }

            var loader = new FileLoader();

​

            foreach (var i in Enumerable.Range(0, repeats))

            {

                var nRead = loader.Load<B, BMap>(rFile, 1000);

                Assert.AreEqual(nRead, count);

            }

            Console.WriteLine("Number of rows={0}, Repeats={1}, total ElapsedMilliseconds={2}", count, repeats, loader.csvTableTimer.ElapsedMilliseconds);

            File.Delete(rFile);

        }

    }

​

    public class Program

    {

        public static void Main()

        {

            Console.WriteLine("Environment version: {0} ({1})", System.Runtime.InteropServices.RuntimeInformation.FrameworkDescription , GetNetCoreVersion());

            Console.WriteLine("OS Version: {0}, NewLine: {1}", System.Environment.OSVersion, JsonConvert.SerializeObject(Environment.NewLine));

            Console.WriteLine("{0} version: {1}", typeof(CsvReader).Assembly.GetName().Name,  typeof(CsvReader).Assembly.FullName);

            Console.WriteLine();

​

            try

            {

                TestClass.Test();

            }

            catch (Exception ex)

            {

                Console.WriteLine("Failed with unhandled exception: ");

                Console.WriteLine(ex);

                throw;

            }

        }

        public static string GetNetCoreVersion()

        {

            //https://techblog.dorogin.com/how-to-detect-net-core-version-in-runtime-ecd65ad695be

            //

            var assembly = typeof(System.Runtime.GCSettings).GetTypeInfo().Assembly;

            var assemblyPath = assembly.Location.Split(new[] { '/', '\\' }, StringSplitOptions.RemoveEmptyEntries);

            int netCoreAppIndex = Array.IndexOf(assemblyPath, "Microsoft.NETCore.App");

            if (netCoreAppIndex > 0 && netCoreAppIndex < assemblyPath.Length - 2)

                return assemblyPath[netCoreAppIndex + 1];

            return null;

        }

    }

​