Connecting to a SQLLite db with SqlBulkCopy

Question

Connecting to a SQLLite db with SqlBulkCopy

c# sqlbulkcopy sqlite unit-testing

Question

I'm writing a unit test in c# for a function that is responsible for using System.Data.SqlClient.SqlBulkCopy to copy a DataTable to a database server.

I use SQLLite for unit tests, and wanted to connect to my SQLLite in memory database with SqlBulkCopy, and then bulk copy that test data into the SQLLite db.

However, I can't seem to get the connection string right.

I originally tried

var bcp = new SqlBulkCopy("FullUri=file::memory:?cache=shared")

Then

var bcp = new SqlBulkCopy("Data Source=:memory:;Cache=Shared")

Which didn't recognize Cache So then I tried

var bcp = new SqlBulkCopy("Data Source=:memory:")

out of desperation, which simply timed out when attempting to connect to the database.

Is what I'm trying to accomplish here possible? If it is, can someone please help me with the connection string?

Mattkwish

View more on Stack Overflow

Mattkwish · Accepted Answer · 3/2/2020 1:37:57 PM

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.

Jonathan Magnan · Accepted Answer · 5/4/2018 1:38:14 PM

You cannot use SqlBulkCopy for SQLite. SqlBulkCopy has been done for SQL Server.

Normally the trick to dramatically improve performance for SQLite is making sure a transaction is used.

Disclaimer: I'm the owner of .NET Bulk Operations

This library is not free but allows you to easily perform and customize all bulk operations:

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge

Example

// Easy to use
var bulk = new BulkOperation(connection);
bulk.BulkInsert(dt);
bulk.BulkUpdate(dt);
bulk.BulkDelete(dt);
bulk.BulkMerge(dt);

// Easy to customize
var bulk = new BulkOperation<Customer>(connection);
bulk.BatchSize = 1000;
bulk.ColumnInputExpression = c => new { c.Name,  c.FirstName };
bulk.ColumnOutputExpression = c => c.CustomerID;
bulk.ColumnPrimaryKeyExpression = c => c.Code;
bulk.BulkMerge(customers);

EDIT: Answer comment

I want to load a data table from SQLite then "bulk copy" it in other databases

This situation is possible but requires 2 connection

DbConnection sourceConnection = // connection from the source
DbConnection destinationConnection = // connection from the destination

// Fill the DataTable using the sourceConnection
dt = ...;

// BulkInsert using the destinationConnection
var bulk = new BulkOperation(destinationConnection);
bulk.BulkInsert(dt);

Connecting to a SQLLite db with SqlBulkCopy

Question

Fastest Entity Framework Extensions

Accepted Answer

Expert Answer

Performance

Expression Evaluator