When I was writing my introductory post about the newly released version of SQL Server or more precisely about just a CTP (Community Technology Preview) version, I complained about an apparent lack of graphical tools similar to SQL Server Management Studio or at least similar to Visual Studio Server Explorer. Such a lack made an exploration of indeed promising features of SQL Server CE 4.0 a slightly tricky task. However, as I pointed out in my previous post that shouldn’t actually prevent programmers from playing with this database, because in spite of non-existence of GUI tools there was already an API released, thus it was possible to interact with the database system by just issuing SQL statements from C# or any other .NET language.
However, I was wrong in my statement about a lack of GUI tools, in fact they do exists.
The first tool to write about is surprisingly WebMatrix that is not only a simplified web page editor, albeit with a support of a new ASP.NET MVC template engine called Razor, but also a tool for managing SQL CE 4.0 databases. Furthermore, if you haven’t installed CE 4.0, you can get one bundled with WebMatrix, plus a whole bunch of other useful applications that can be easily installed with Web Platform Installer.
At the first glance WebMatrix may scare serious developers with its slightly childish simplicity, but anyway in order to start working with SQL CE 4.0 it is necessary to either create a web site or use a folder as a website or whatsoever in WebMatrix to make it create a some kind of a project. Fortunately, it just takes a few second and then it rewards us with an option called “Databases”.
On clicking “Add a Database to your site” or “New Database” in the toolbar it creates a new .sdf file that is meant to contain your CE database. Then, you can start creating tables and populating them with data, as well as issuing SQL queries.
Moreover, WebMatrix allows performing a migration from CE server to a regular version of SQL Server, in that case it still can be kept as a database tool, since it also works with SQL Server and even MySQL , plus supports dealing with views and stores procedures, which is pretty good for WebMatrix that has an image of a toy.
There is one thing that might be a little bit confusing at first. How do I open a database that wasn’t created by WebMatrix? Fortunately, there is a simple workaround, just copy to the App_Data folder of your website and WebMatrix will hook it automatically.
WebMatrix is available here.
SQL Server Compact Toolbox
It is a free tool that was recommended me by its author Erik Ejlskov Jensen who told me about it in his comment to my previous post. This tool is both available as a standalone application and a Visual Studio add-in. Frankly, I don’t see a point of having it as a standalone application because I can use above mentioned WebMatrix instead that also has more features, however having it as an add-in in Visual Studio is indeed a good thing that doesn’t force you to switch a context between two development environments.
Moreover, it looks pretty similar Server Explorer that is available in Visual Studio by default, however it lacks some features, for instance, it’s possible to create or modify a table by using graphical tools, instead you have to write SQL statements, on the other hand you can at least view all the tables and perform queries against them.
SQL Server toolbox and its source code are available on CodePlex.