How to create an in-memory database with SQL CE 4.0?

Question

Question

How do I create an in-memory database with SQL CE 4.0?

Context

I'd like to do some unit testing (or automated integration testing) with a real database, however, one that is in-memory. That'll make the tests run fast, plus, the database will vanish in thin air once the test are finished running.

According to Scott Guthrie's blog post "VS 2010 SP1 and SQL CE" the new SQL CE 4.0 is capable of doing just that: providing an in-memory database.

However, I couldn't find any tutorials or code examples on the web showing how it is done. I only found this connection string example in this blog post. But that also hits the harddrive.

Answer 1

SQL Server CE 4.0 does not allow for memory-only databases.

That blog posts says that CE runs in-memory of your application.

A comment by Scott says that CE maps the file into memory, but that still requires the file.

Answer 2

@linkgoron,

Are you considering adding a in memory DB feature? This can help a lot with unit testing (Create/Drop a in memory DB using Code-First for every test) which should be much faster than a file based DB (I've used this approach with other DBs, and would love to actually use SQL Server CE for it). This also reduces the need to mock the Data access/Repository when unit testing.

SQL CE itself runs in-memory - and so is quite fast to create and use for unit test projects. I believe it does require the DB file on disk - but immediately maps it in to memory (so it as fast as in-memory). You would still create/delete the file as part of your setup/teardown logic - but this should be as easy as deleting the file (there is no explicit drop or cleanup required).

Hope this helps,

Scott

~ From the linked blog post.