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?
Expert Answer
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);