Wednesday, January 13, 2010

SQL Server Compact Bulk Insert Library

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

        using ErikEJ.SqlCe;

private static void DoBulkCopy(bool keepNulls, IDataReader reader)
{
SqlCeBulkCopyOptions options = new SqlCeBulkCopyOptions();
if (keepNulls)
{
options = options = SqlCeBulkCopyOptions.KeepNulls;
}
using (SqlCeBulkCopy bc = new SqlCeBulkCopy(connectionString, options))
{
bc.DestinationTableName = "tblDoctor";
bc.WriteToServer(reader);
}
}


Hope you will find it useful, and please provide any comments here.

24 comments:

RichB said...

How does this handle constraints?

ErikEJ said...

It does not, just performs the inserts. To get max performance, drop all constraints before and create again afterbulk insert.

Unknown said...

Do it function can import a text file formated table into a Table?

ErikEJ said...

No, it accepts a IDataReader or DataTable object as input (as does the SqlBulkCopy api)

Unknown said...

Thank you a lot for this library! It's really cool, kind of treasure for me!

The only question is why it is compiled for desctop? It's not a big deal to make a new project for smart device but I'm inquiring to know (:

ErikEJ said...

Nuts: You are welcome to submit a pacth with a Windows Mobile 5 project.

etfairfax said...

Hi Erik. 1st of all thanks for sharing your blog with the outside world. I stumbled across it the other day and it has already proved a great help.

How does "SQL Server Compact Bulk Insert Library" deal with someone attempting to insert the same data twice? Can I tell it to Update OR Insert, depending on the presence of an Id value.

Thanks

ETFairfax.

ErikEJ said...

etfairfax: It does not, and neither does the SqlBulkCopy api.

Ian said...

I posted an issue on the codeplex page for this library (http://sqlcebulkcopy.codeplex.com/workitem/20796). It's way to long to post here but in short I am having a namespace reference issue with "SqlCeBulkCopy." thanks for any help in advance.

angel said...

Hello ErikEJ i got this error : "{The operation completed successfully.}" when i do :

bc.WriteToServer(TABLE);

Do you know how to fix it?

angel said...

i got this error :"{The operation completed successfully.}"

Do you know how to fix it?
bc.WriteToServer(reader);

ErikEJ said...

Please post at repro at http://sqlcebulkcopy.codeplex.com - Issue list

Airfoil said...

Great api but I am having a bit of trouble in one area.

When I attempt to pass in a DataTable Object, I get the following error:

Argument 2: cannot convert from 'System.Data.DataTable' to 'System.Data.IDataReader'

Do I have to do something other than just pass in the DataTable?

ErikEJ said...

Airfoil: DataTable Works fine, something else must be wrong- could you go to the codeplex site issue tracker and provide repro sample code.

Unknown said...

I used this API to overcome slow insertions with SQLServerCompact. However, this broke database independence for the code. So now I defined a IBulkCopy interface through MEF for which I can write implementations for other databases, like SQLExpress. Your API supports IENumerables which makes it possible to bulkcopy collections of Entities as long as they do not have Complex Types in them. The BulkCopy implementations for SQLExpress and SQLServer do not support IENumerables however. Is there a simple way to convert an IENumerable to a DataTable?

ErikEJ said...

Andre:I use the Salient.Data library to convert a IEnumerable to a DataReader:
var reader = new Salient.Data.EnumerableDataReader(collection)

Unknown said...

Thanks, that is very convenient! However: I keep running in a specified argument is outside the range of valid values while using SQLBulkCopy, using SQLCEBulkCopy it all works without a hiccup.

ErikEJ said...

Andre: If this is an tech issue with SqlCeBulkCopy, maybe would should continue the discussion here: http://sqlcebulkcopy.codeplex.com/workitem/list/basic

Pavan said...

Hi ErikEj

I am trying to use your API in my windows mobile 6 app. I am getting an error after deploying the app on device and running it. I have seen this post http://stackoverflow.com/questions/17118021/copying-datatable-to-sql-server-ce-table

I was trying to download the SQL Compact SP2 for devices, but that microsoft link is no more working. I think your API doesnt work for SQL compact SP1 on devices. If you have any solution let me know. IF I could find the SQL compact SP2 for device will be very heplful

ErikEJ said...

Pavan: Pls email me, and I can help you with the package

Unknown said...

Hi ErikEj,

Thank you for great library. However i am getting following error in one table:
"Source column 'ID' does not exist and destination does not allow nulls." Do i have to have column "ID" ?

I am using guid primary key, and my table structure is like this, and i am filling all columns in bulk data.

CREATE TABLE [customer] (
[id] binary(16) NOT NULL
, [name] nvarchar(50) NOT NULL
, [type] tinyint NOT NULL
, [no] int IDENTITY (1000,1) NOT NULL
, [email] nvarchar(50) NULL
, [phone] nvarchar(12) NULL
, [mobile] nvarchar(12) NULL
, [identity_no] nvarchar(11) NULL
, [tax_address] nvarchar(50) NULL
, [city] nvarchar(30) NOT NULL
, [town] nvarchar(30) NOT NULL
, [address] nvarchar(100) NOT NULL
, [location] nvarchar(30) NULL
, [record_owner] nvarchar(40) NOT NULL
, [active] bit DEFAULT 1 NOT NULL
, [created_on] datetime NULL
, [created_by] nvarchar(40) NULL
, [last_modified_on] datetime NULL
, [global_id] nvarchar(50) NULL
, [city_id] int NULL
);

ErikEJ said...

Please post a repro project on CodePlex or github, and I will tale a closer look - it looks a bit strange with both id and identity columns, your table

Unknown said...

Thank you for quick reply. I will reproduce it for you.
Btw, I changed the structure, created an integer id primary key. This time it throws error for each NOT NULL columns like this:

{"Source column 'NAME' does not exist and destination does not allow nulls."}
{"Source column 'CITY' does not exist and destination does not allow nulls."}
etc.

There is no empty value that i try to insert.

ErikEJ said...

@arda: It sounds like the source and target tables do not have the same structure, in that case you must use a mapping