An easy way to use SqlBulkCopy is by making your own implementation of BulkCopyImporter.

Here is an example of a fully testable implementation. Using dependency injection the ExampleImporter class is passed an instance of IBulkCopy, which can easily be substituted using a mocking framework. It is now a simple task to verify the setup performed in the constructor, and that column values are set as expected.

  1. ExampleData
    public sealed class ExampleData
    {
        public int Value1 { get; set; }
        public DateTime Value2 { get; set; }
    }
    
  2. ExampleDataImporter
    public sealed class ExampleDataImporter : BulkCopyImporter<ExampleData>
    {
        private readonly IBulkCopy _bulkCopy;
        private ITypedBulkCopyBoundColumn<int> _column1;
        private ITypedBulkCopyBoundColumn<DateTime> _column2;
    
        private const int BatchSize = 100;
    
        public ExampleDataImporter(IBulkCopy bulkCopy)
            : base(bulkCopy, BatchSize)
        {
            _bulkCopy = bulkCopy;
        }
    
        public void Initialize(string connectionString)
        {
            if (string.IsNullOrEmpty(connectionString)) throw new ArgumentNullException("connectionString");
    
            _bulkCopy.Connect(connectionString);
    
            _bulkCopy.ExecuteDirect("pre-import SQL to execute goes here");
    
            _column1 = _bulkCopy.BindInt();
            _column2 = _bulkCopy.BindDateTime2();
        }
    
        protected override void SetRowValues(ExampleData data)
        {
            if (data == null) throw new ArgumentNullException("data");
    
            _column1.SetValue(data.Value1);
            _column2.SetValue(data.Value2);
        }
    
        public override bool Done()
        {
            var success = base.Done();
    
            if (success)
            {
                _bulkCopy.ExecuteDirect("post-import SQL to execute goes here");
            }
    
            return success;
        }
    }
    
  3. ExampleDataImporterTests
    [TestClass]
    public class ExampleDataImporterTests
    {
        private Mock<IBulkCopy> _bulkCopy;
    
        [TestInitialize]
        public void TestInitialize()
        {
            _bulkCopy = new Mock<IBulkCopy>();
        }
    
        [TestMethod]
        public void InitializeBehavesAsExpected()
        {
            const string anyConnectionStringWillDo = "x";
    
            var column1 = new Mock<ITypedBulkCopyBoundColumn<int>>();
            var column2 = new Mock<ITypedBulkCopyBoundColumn<DateTime>>();
    
            _bulkCopy
                .Setup(m => m.BindInt())
                .Returns(column1.Object);
    
            _bulkCopy
                .Setup(m => m.BindDateTime2())
                .Returns(column2.Object);
    
            var sut = new ExampleDataImporter(_bulkCopy.Object);
    
            sut.Initialize(anyConnectionStringWillDo);
    
            _bulkCopy.Verify(m => m.Connect(anyConnectionStringWillDo), Times.Once());
            _bulkCopy.Verify(m => m.ExecuteDirect("pre-import SQL to execute goes here"), Times.Once());
            _bulkCopy.Verify(m => m.BindInt(), Times.Once);
            _bulkCopy.Verify(m => m.BindDateTime2(), Times.Once);
        }
    
        [TestMethod]
        public void SendRowBehavesAsExpected()
        {
            const string anyConnectionStringWillDo = "x";
    
            var column1 = new Mock<ITypedBulkCopyBoundColumn<int>>();
            var column2 = new Mock<ITypedBulkCopyBoundColumn<DateTime>>();
    
            _bulkCopy
                .Setup(m => m.BindInt())
                .Returns(column1.Object);
    
            _bulkCopy
                .Setup(m => m.BindDateTime2())
                .Returns(column2.Object);
    
            _bulkCopy
                .Setup(m => m.SendRow())
                .Returns(true);
    
            var sut = new ExampleDataImporter(_bulkCopy.Object);
    
            sut.Initialize(anyConnectionStringWillDo);
    
            var data = new ExampleData { Value1 = 1, Value2 = DateTime.Now };
    
            sut.SendRow(data);
    
            column1.Verify(m => m.SetValue(data.Value1), Times.Once);
            column2.Verify(m => m.SetValue(data.Value2), Times.Once);
        }
    
        [TestMethod]
        public void DoneBehavesAsExpected()
        {
            _bulkCopy
                .Setup(m => m.Done())
                .Returns(0);
    
            var sut = new ExampleDataImporter(_bulkCopy.Object);
    
            sut.Done();
    
            _bulkCopy.Verify(m => m.ExecuteDirect("post-import SQL to execute goes here"), Times.Once);
        }
    }
    
Data Transfer