Simple answer… you can’t! :o)
You can open up to SQL CE 3.5 SP1 in 2008 Management Studio, but CE 4.0 is not supported. Have a quick read of this stackoverflow.com article for more info.
Can you open SQL CE 4.0 files in Visual Studio 2010? Yes you can.
But the problem I’ve come across is that when I want to work in code and write say a POCO class (Plain Old C# Class) I can’t. While the SQL Table property sheet is open so I can see the column data types – it’s Modal, so I can’t easily jump back and forth updating my code while reading the types. Not a major problem really, just an annoyance.
SQL Server Compact Toolbox Stand Alone. You can download it here on codeplex.com. It is a great little tool that you can run – no installation – and connect to your SQL CE 4.0 file. So now I can have the toolbox open on one monitor and happily code away in VS 2010 in the other.
Life is good again.
ps: For all the information you could need on SQL CE, check out ErikEJ’s excellent blog, ironically named Everything SQL Server Compact.
Okay… I want to do some SQL queries that are wrapped in transactions and not just simple CRUD statements. So I pulled out my trusty DAL code generation library (EntitySpaces) and began integrating it into my WebMatrix project.
After installing EntitySpaces 2011 I referenced all the DLL’s that I needed in my project (I’m using a separate “Core” project in Visual Studio 2010 that is linked to my WebMatrix project), including the EntitySpaces.SqlServerCe4Provider:
WebMatrix uses SQL Server Compact Edition v4.0, and due to the way the EntitySpaces references the different CE versions, you need to supply this in the connection string for code generation:
Code generation went fine and I have my Generated and Custom classes all set to go. Next I needed to reference the EntitySpaces libraries in my Web.config file. Be sure to reference the correct provider for the Compact Edition:
Note, that we specify Â “Data Source=|DataDirectory|yot.sdf;” where the Data Directory is going to default to the App_Data folder under your website root directory. This is handy too if you deploy the compact database to your intranet or production site. Because it’s all relative to your App_Data folder, it will still work without having to remember to change the path etc…
Lastly, we need to configure the EntitySpaces library on startup. This is done by adding the necessary code to the _AppStart.cshtml file:
Now, all that’s left to do is to load up a collection and populate a web grid to display it:
The rendered WebGrid looks something like this:
I am now ready to start putting together some more complex multi-step commits using EntitySpaces.