using System;
using System.Data;
using System.Data.SqlClient;
using System.IO;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            var xyz = @"Data Source=(local)\SQL17DEV;Initial Catalog=DemoDB;Integrated Security=SSPI;";
            var x = @"C:\Sifiso\pl_leading_goalscorers.csv";
            GetDataTabletFromCSVFile(x, xyz);
        }

        static DataTable GetDataTabletFromCSVFile(string csv_file_path, string connname)
        {
            DataTable csvData = new DataTable();
            try
            {
                using (TextReader csvReader = File.OpenText(csv_file_path))
                {
                    string line;
                    while ((line = csvReader.ReadLine()) != null)
                    {
                        string[] items = line.Trim().Split(',');
                        if (csvData.Columns.Count == 0)
                        {
                            for (int i = 0; i < items.Length; i++)
                            {
                                csvData.Columns.Add(new DataColumn(items[i], typeof(string)));
                            }
                        }
                        else
                        {
                            csvData.Rows.Add(items);
                        }
                    }
                }

                using (SqlConnection dbConnection = new SqlConnection(connname))
                {
                    dbConnection.Open();
                    using (SqlBulkCopy s = new SqlBulkCopy(dbConnection))
                    {
                        s.DestinationTableName = "dbo.[PLGoalScorers]";
                        foreach (var column in csvData.Columns)
                        {
                            s.ColumnMappings.Add(column.ToString(), column.ToString());
                        }
                        s.WriteToServer(csvData);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return csvData;
        }
    }
}
using System;
using System.Data;
using System.Data.SqlClient;
using System.IO;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            var xyz = @"Data Source=(local)\SQL17DEV;Initial Catalog=DemoDB;Integrated Security=SSPI;";
            var x = @"C:\Sifiso\pl_leading_goalscorers.csv";
            GetDataTabletFromCSVFile(x, xyz);
        }

        static DataTable GetDataTabletFromCSVFile(string csv_file_path, string connname)
        {
            DataTable csvData = new DataTable();
            try
            {
                using (TextReader csvReader = File.OpenText(csv_file_path))
                {
                    string line;
                    while ((line = csvReader.ReadLine()) != null)
                    {
                        string[] items = line.Trim().Split(',');
                        if (csvData.Columns.Count == 0)
                        {
                            for (int i = 0; i < items.Length; i++)
                            {
                                csvData.Columns.Add(new DataColumn(items[i], typeof(string)));
                            }
                        }
                        else
                        {
                            csvData.Rows.Add(items);
                        }
                    }
                }

                using (SqlConnection dbConnection = new SqlConnection(connname))
                {
                    dbConnection.Open();
                    using (SqlBulkCopy s = new SqlBulkCopy(dbConnection))
                    {
                        s.DestinationTableName = "dbo.[PLGoalScorers]";
                        foreach (var column in csvData.Columns)
                        {
                            s.ColumnMappings.Add(column.ToString(), column.ToString());
                        }
                        s.WriteToServer(csvData);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return csvData;
        }
    }
}