CsvHelper - write to SQL Database with mapping

Question

The CSVHelper .NET library seems fantastic so far, but the documentation is a little lacking for a pseudo-beginner like myself.

I need to read a csv file and write the results to our SQL Server database. For the table I'm writing to, I need to map to its columns from the CSV columns, including some concatenation of multiple fields to one.

This is what I have for reading the csv file:

public static void Main(string[] args)
{
    using (var reader = new StreamReader(@"C:\Users\me\Documents\file.csv"))
    using (var csv = new CsvReader(reader))          
    {
        csv.Configuration.PrepareHeaderForMatch = (string header, int index) =>
            header.Replace(" ", "_").Replace("(", "").Replace(")", "").Replace(".", "");
        var records = csv.GetRecords<EntityCsv>().ToList();
    }
}

My EntityCsv class contains property names for all columns of the csv file.

Then, I also have a class called TaskEntity which contains the property names and types for the destination database table (although I'm unclear as to whether I need this).

Finally, per advice from a colleague, I have a method set up to make use of SQLBulkCopy as thus:

public void AddBulk(List<TaskEntity> entities)
{
    using (var con = GetConnection())
    {
        SqlBulkCopy bulk = new SqlBulkCopy(con);
        bulk.BatchSize = 2000;
        bulk.BulkCopyTimeout = 0;
        bulk.DestinationTableName = "dbo.CsvExports";
        bulk.WriteToServer(entities.AsDataTable());
        bulk.Close();
    }
}

I borrowed that code block from him and would theoretically run that method as the final step.

But I know I'm missing a step in between, and that is mapping the fields from the csv to the SQL server field. I'm scratching my head at how to implement this step.

So let's say for simplicity's sake I have 3 columns in the csv file, and I want to map them to 2 columns of the SQL table as follows:

CsvColumn1 -> SQLtableColumn1
CsvColumn2 + CsvColumn3 -> SQLtableColumn2

How would I go about accomplishing this with CsvReader and C#? I have explored the Mapping section of the CSVReader documentation but everything I'm seeing in there seems to refer to mapping column names from an input file to names in an output file. I don't see anything there (nor anywhere on the Google) that speaks specifically to taking the input file and exporting its rows to a SQL database.

Answer 1

You can use a ClassMap to map the csv columns to the sql table columns and skip the CsvEntity class.

public static void Main(string[] args)
{
    using (var reader = new StreamReader(@"C:\Users\me\Documents\file.csv"))
    using (var csv = new CsvReader(reader))          
    {
        csv.Configuration.PrepareHeaderForMatch = (string header, int index) =>
            header.Replace(" ", "_").Replace("(", "").Replace(")", "").Replace(".", "");

        csv.Configuration.RegisterClassMap<TaskEntityMap>();

        var records = csv.GetRecords<TaskEntity>().ToList();
    }
}

public class TaskEntity
{
    public int Id { get; set; }
    public string SqlTableColumn1 { get; set; }
    public string SqlTableColumn2 { get; set; }
}

public sealed class TaskEntityMap : ClassMap<TaskEntity>
{
    public TaskEntityMap()
    {
        Map(m => m.SqlTableColumn1).Name("CsvColumn1");
        Map(m => m.SqlTableColumn2).ConvertUsing(row => row.GetField<string>("CsvColumn2") + " " + row.GetField<string>("CsvColumn3"));
    }
}

Answer 2

I have used the SqlBulkCopy along with the csvhelper to dump data in to the sql server.

SqlBulkCopy is an awesome utility that writes data to the sql server from nearly any data source that can be loaded into a DataTable instance.

var lines = File.ReadAllLines(file);
if (lines.Count() == 0) 
    return;

var tableName = GetTableName(file);
var columns = lines[0].Split(',').ToList();
var table = new DataTable();
sqlBulk.ColumnMappings.Clear();

foreach (var c in columns)
{
    table.Columns.Add(c);
    sqlBulk.ColumnMappings.Add(c, c); 
}

for (int i = 1; i < lines.Count() - 1; i++)
{
    var line = lines[i];
    // Explicitly mark empty values as null for SQL import to work
    var row = line.Split(',')
        .Select(a => string.IsNullOrEmpty(a) ? null : a).ToArray();
    table.Rows.Add(row);
}

sqlBulk.DestinationTableName = tableName;
sqlBulk.WriteToServer(table);