Connecting to a SQLLite db with SqlBulkCopy

c# sqlbulkcopy sqlite unit-testing

Question

In C#, I'm developing a unit test for a method that uses System. Data.SqlClient. To copy a DataTable to a database server, use SqlBulkCopy.

I wanted to connect to my SQLLite in memory database using SqlBulkCopy and bulk transfer the test data into the SQLLite db since I use SQLLite for unit testing.

I am unable to correctly configure the connection string, however.

When I first attempted

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

Then

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

which didn't acknowledgeCache I then tried, so

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

out of despair, but the attempt to connect to the database just timed out.

Is what I'm attempting to do here even possible? If so, could anybody kindly assist me with the connection string?

2
1
11/8/2017 9:42:57 PM

Accepted Answer

The response to this was that SqlBulkCopy cannot be connected to a SQLite instance.

I created a wrapper around SqlBulkCopy that is implemented using SqlBulkCopy for production code and with a dummy bulk copy in test code in order to address my issue (unit test a section of the code that utilized SqlBulkCopy). removing the dependence on SqlBulkCopy itself in a practical way.

More specifically, I

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

I then put this into practice 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);
    }

}

Additionally, I faked out "bulk copy" using just inserts in my test utilities:

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 As auxiliary functions, I created.

0
3/2/2020 1:37:57 PM

Expert Answer

You cannot useSqlBulkCopy SQLite for.SqlBulkCopy been carried out for SQL Server.

Typically, using a transaction is the key to significantly enhancing SQLite's speed.


I own Bulk Operations in.NET, which is Disclaimer.

Although this library costs money, it makes it simple to carry out and personalize any bulk operations:

  • a mass insert
  • Bulk Delete
  • Mass Update
  • mass 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: Comment for response

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

This scenario is conceivable but calls for two connections.

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);
1
5/4/2018 1:38:14 PM


Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow