Wednesday, February 17, 2010

Microsoft Sql Server CE 3.5 provider for DataObjects.Net 4

The first version of Microsoft Sql Server CE 3.5 provider for DataObjects.Net 4 is going to be officially shipped in the upcoming DataObjects.Net 4.2 release.

Let me shortly introduce Sql server CE’s limitations. So it doesn’t support:

Feature Where is used Workaround, if any
Temporary tables Local collections support Regular tables are created and dropped automatically
Full-text search Full-text queries -
Batches Batch database operations -
CROSS APPLY SQL operator “First”, “Single”, “ElementAt”, etc. LINQ methods inside sub queries;
”SelectMany” LINQ method without “DefaultIfEmpty” specifying
UPDATE FROM [Table] Upgrade procedure Another, but analogous SQL statement is used
INSERT DEFAULT VALUES Insert operation Default values are explicitly specified
Savepoints Nested transactions -
ROW NUMBER SQL operator “Skip” LINQ methods -
INTERSECT, EXCEPT SQL operators “Except”, “Intersect” LINQ methods -
nvarchar(max), varchar(max), varbinary(max) datatypes   text, ntext, image are used instead
COUNT_BIG function “Count” LINQ method Result of “Count” is explicitly casted to bigint type

But despite of all these limitations there is one huge advantage of CE usage: it doesn’t require separate installation. This is significant feature for developers who works on desktop applications for personal use, for example. Just ship, install and it works. No more problems with database server installation, connections, firewalls, network configurations, etc. Another case is running tests on your business model. Actually, you don’t need big database server for this.

In order to use DataObjects.Net 4 with Sql Server CE instead of his big brother, use the following connection url:

<domain connectionUrl="sqlserverce://user:password@localhost/<Path to your database file>"/>

Another option is to use native connection string:

<domain provider="sqlserverce"
        connectionString="Data Source=<Path to your database file>"/>

Sql Server CE 3.5 provider for DataObjects.Net 4 is already available in DataObjects.Net 4.2 RC.


  1. Hi, how could I use DataObjects.NET 4.2 with a windows mobile or smartphone application? I ask because in DataObjects.NET 4.2 exists only templates for Console, Web Application and WPF.

    Thanks in advance.

  2. Hello Francisco,
    Actually, we've never tested DO 4.2 on .NET compact framework because of some known annoying limitations, mainly in System.Reflection implementation. But its seems that in the last .NET version they are gone so we are going to deliver the compact version soon, may be this summer.

  3. Thank you for your help Dmitri :D

    Today I was tested if I could use 4.2 with a SmartDevice Application with .Net Framework 3.5, and I was copied the code that generate the templates from 4.2 thinking to implement in a mobile application, but I have'nt success because a missing Interface was found "System.ComponentModel.IDataErrorInfo".

    I have no choice, I must wait until the release of support for mobile devices.

    Thank you Dmitri.

  4. Actually, I haven't provided any meaningful help, so I'm suspecting that you are joking =)

    Anyway, thanks for sharing your experience.
    I've heard some news that IDataErrorInfo interface was added to Silverlight 4. Don't you want give .NET framework 4.0 a go?

  5. Hahah, no Dmitri I'm not joking :D

    I just have copied the code into a SmartDevice application Template that belongs to a Console Template from

    The problem seems that the implementation of ComponentModel, is different for a .net compact framework.

    At the moment I'm going to work with Web Application Template of, for testing purposes :D

  6. OK, I see.

    I'm looking forward to hear the news about the web application template and its possible problems. anyway, this will definitely help us in the process of DO4 porting in the future.