Connecting to a SQLLite db with SqlBulkCopy

The answer to this was that you cannot connect SqlBulkCopy to a SQLite instance.

What I did to solve my problem (unit test a part of the code that used SqlBulkCopy) was to create a wrapper around SqlBulkCopy that is implemented using SqlBulkCopy for production code, and with a mock bulk copy in test code. Effectively decoupling the dependency on SqlBulkCopy itself.

Specifically, I created

public interface IBulkCopy : IDisposable {
    string DestinationTableName { get; set; }
    void CreateColumnMapping(string from, string to);
    Task WriteToServerAsync(DataTable dt);
}

Then, I implemented this as

public class SQLBulkCopy : IBulkCopy { 

    private SqlBulkCopy _sbc;

    public string DestinationTableName {
        get { return _sbc.DestinationTableName; }
        set { _sbc.DestinationTableName = value; }
    }

    public SQLBulkCopy(IDBContext ctx) { 
        _sbc = new SqlBulkCopy((SqlConnection)ctx.GetConnection());
    }

    public void CreateColumnMapping(string from, string to) {
        _sbc.ColumnMappings.Add(new SqlBulkCopyColumnMapping(from, to));
    }

    public Task WriteToServerAsync(DataTable dt) {
        return _sbc.WriteToServerAsync(dt);
    }

}

And in my test utilities I mocked out "bulk copy" with just inserts:

class MockBulkCopy : IBulkCopy {

    private IDBContext _context;

    public MockBulkCopyHelper(IDBContext context) {
        _context = context;
    }

    public string DestinationTableName { get; set; }

    public void CreateColumnMapping(string fromName, string toName) {
        //We don't need a column mapping for raw SQL Insert statements.
        return;
    }

    public virtual Task WriteToServerAsync(DataTable dt) {
        return Task.Run(() => {
            using (var cn = _context.GetConnection()) {
                using (var cmd = cn.CreateCommand()) {
                    cmd.CommandText = $"INSERT INTO {DestinationTableName}({GetCsvColumnList(dt)}) VALUES {GetCsvValueList(dt)}";
                    cmd.ExecuteNonQuery();
                }
            }
        });
    }

Where GetCsvColumnList and GetCsvValueList I implemented as helper functions.