Some developers are faced with issues when it comes to loading data into their SQL Server Compact databases. The current synchronization solutions like Merge and Sync Framework do not always fit the bill. In order to ease the pain, I have developed a SQL Server Compact bulk insert library, which mimics the well-know SqlBulkCopy API – the name of the library is of course SqlCeBulkCopy .
The initial beta release is now available on CodePlex as open source, currently ColumnMappings are not implemented (any volunteers?).
Some timings from testing - load 2 column table with no constraints/indexes:
1000000 (1 million) rows: 15 seconds = 66666 rows/second
5000000 (5 million) rows: 82 seconds = 60975 rows/second
Sample usage of the API: - Currently source and destination column ordinals must match
private static void DoBulkCopy(bool keepNulls, IDataReader reader)
SqlCeBulkCopyOptions options = new SqlCeBulkCopyOptions();
options = options = SqlCeBulkCopyOptions.KeepNulls;
using (SqlCeBulkCopy bc = new SqlCeBulkCopy(connectionString, options))
bc.DestinationTableName = "tblDoctor";
Hope you will find it useful, and please provide any comments here.