Connecting to SQL CE 3.5 databases from C#

I’ve been scratching my head for a long time about how I should be connecting to my .sdf files from c#.  I’ve had a couple of data based projects on the go for some time now, and continually find myself bumping up against some problem as the project progresses.

I tried creating the data with the wizard and an Entity Data Model/Entity Framework.

This leads to pain, as your tables cannot have an server generated identity field.

I tried creating the database using EDM and code first

But for the life of me, I couldn’t work out how to bind it to tables in the database.

I tried using OLE DB

And then had to struggle with code to fetch the identity like this:

_lastIndexCmd = _sqlConn.CreateCommand();
_lastIndexCmd.CommandText = "SELECT @@IDENTITY";

and had to be very careful about inserting only one record at a time.

 

This spring, I went to TechDays in London, and Andy Wigley gave a talk http://blogs.msdn.com/b/mikeormond/archive/2011/07/12/tech-days-live-video-sql-server-compact-and-user-data-access-in-mango.aspx
on using SQL CE in mango.  I asked a bunch of questions in the talk (yes, that’s me in the orange jumper at the front) about server generated keys.

The reason I asked, was because I had misunderstood the comment earlier in the talk that mentioned EDM 4.1, and didn’t realize that we were actually talking about LINQ to SQL, not EF.  In other projects I had problems with inserting multiple server generated keys as described in the OLE DB section above.

Today, I watched the talks again, very carefully, and realized that I should have been using Linq to SQL, and that that will let me work on the phone too.

To create your objects from an already existing database file, run the following command:

sqlmetal /dbml:mydb.dbml mydb.sdf /pluralize

Afterwards, simply add the mydb.dbml file to your project.

Don’t forget the pluralize switch if you’re trying to replace your EDM implementation, or you may be in for a lot of member renaming.

The other gotchas?  AddObject in EF is replaced with InsertOnSubmit on the tables, and  SaveChanges on the DataContext becomes SubmitChanges.

After these changes, I was pleasantly surprised that my query time dropped from 95msec for 11520 records to 78, and improvement of  18%.

I hope I’ve got this right, but if not, hope ErikEJ over at http://erikej.blogspot.com/ will put me right.