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?

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);


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why