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?
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.
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:
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);