PetaPoco - Support for SQL Server Compact Edition

This post is about PetaPoco - "a tiny ORMish thing for your POCOs". Read more on the PetaPoco Project Page.

I've not used SQL Server CE before so I thought adding support to PetaPoco would be bit of an effort. In the end it turned out to be not too bad with most problems relating to my lack of understanding of the tooling. Here's some things I discovered:

• There's two main versions of CE - 3.5 and 4.0. I have both installed.
• To use 4.0 needed to install Visual Studio 2010 SP1 and these tools.
• I also needed to install the runtime from here.
• Even with all of the above, Visual Studio always seems to create v3.5 sdf files - which CE 4.0 won't open.
• There's this handy command line tool which can be used to upgrade a sdf file to 4.0.
• The provider names for the two engines are System.Data.SqlServerCe.3.5 and System.Data.SqlServerCe.4.0.

And a typical config file settings for a CE database look like this:

    <add
        name="sqlserverce"
        connectionString="Data Source=C:\Users\bradr\dev\Source\PetaPoco\PetaPoco.Tests\petapoco.sdf"
        providerName="System.Data.SqlServerCe.4.0"
        />

So once I got past all that there were a few fixes required.

Support for Detecting the Database Provide Type

PetaPoco already had code to detect the type of database it was talking too - either MySql of SQL Server. This used to work by guessing from the providerName string. I thought I'd tighten this up and now it looks at the type name of the actual provider factory... and I added support for detecting CE.

Retrieving the Identity of Inserted Records

SQL Server CE doesn't support multiple SQL statements in a single command invocation. PetaPoco used this in one place - as part of inserting a record it used a subsequent SELECT @@IDENTITY statement to retrieve the new record's ID.

To work around this when talking to a CE database PetaPoco will invoke this statement in a separate command.

Paged Requests

PetaPoco can automatically generate the statements required to do a paged request and return the total number of pages/records. The syntax for this is very specific to the database in question so it's not surprising that this didn't just work.

Implementing this in CE 4.0 is trivial as it supports a syntax like this: OFFSET @0 ROWS FETCH NEXT @1 ROWS ONLY

Implementing this for CE 3.5 is not trivial... in fact it can't even be done in SQL and the only suggestion I've seen is to manually skip records in the data reader. Hrm... I'm not going to do that so PetaPoco doesn't support paged requests in CE 3.5.

T4 Template Support

The T4 templates needed a few little tweaks to get working... mostly just removing unsupported stuff from the schema queries.

Unit Tests

I didn't need to update the unit tests except to run the tests a third time for CE.

As usual, available in GitHub and NuGet now.