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:
How does this handle constraints?
It does not, just performs the inserts. To get max performance, drop all constraints before and create again afterbulk insert.
Do it function can import a text file formated table into a Table?
No, it accepts a IDataReader or DataTable object as input (as does the SqlBulkCopy api)
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 (:
Nuts: You are welcome to submit a pacth with a Windows Mobile 5 project.
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.
etfairfax: It does not, and neither does the SqlBulkCopy api.
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.
Hello ErikEJ i got this error : "{The operation completed successfully.}" when i do :
bc.WriteToServer(TABLE);
Do you know how to fix it?
i got this error :"{The operation completed successfully.}"
Do you know how to fix it?
bc.WriteToServer(reader);
Please post at repro at http://sqlcebulkcopy.codeplex.com - Issue list
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?
Airfoil: DataTable Works fine, something else must be wrong- could you go to the codeplex site issue tracker and provide repro sample code.
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?
Andre:I use the Salient.Data library to convert a IEnumerable to a DataReader:
var reader = new Salient.Data.EnumerableDataReader(collection)
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.
Andre: If this is an tech issue with SqlCeBulkCopy, maybe would should continue the discussion here: http://sqlcebulkcopy.codeplex.com/workitem/list/basic
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
Pavan: Pls email me, and I can help you with the package
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
);
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
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.
@arda: It sounds like the source and target tables do not have the same structure, in that case you must use a mapping
Post a Comment