Download SqlCeViewer.v1.0.zip (900 KB)
You can open and edit Microsoft SQL Server Compact Edition 3.5 databases with SQL Server Management Studio 2008 – however, it is quite a large download and at least on my machine SQL Server Management Studio 2008 Express refused to install, as I had full version of MS SQL Server 2005 installed. Blech, truly!
However, I need to see the data in SQL CE databases quite often – and writing queries into the code constantly just to see the data… not the greatest idea, y’know.
As I had some unexpected free time, I decided to write a small application to browse the SQL CE database – relying on INFORMATION_SCHEMA to get the info. It works with SQL CE version 3.5 databases – but you can update the databases from older versions as well (see File –> Tools) or read below.
It does not require SQL CE to be installed, as it has CE dll’s in the .zip file. Just extract all files into one folder and call it good. However, note that including Microsoft dll’s makes the legalese stuff murky. MS gives SQL server CE freely, but I am unsure about the redistribution rights. But, the viewer is a tool that makes it easier to work with MS product and therefore makes choosing the MS product more appealing to users – so I hope that Bill won’t mind.
SQL CE database viewer requires .NET 2.0 framework – and as with all .NET programs, first time you start it, it will take much longer to start then subsequent times, as the native image is created on the first run.
The viewer supports both encrypted and unencrypted databases; in case the database is encrypted you will be prompted for password.
As you can see from the screenshot, SQL CE viewer lists all the tables in the database. If you choose treenode Tables, then you will get detailed info about all the tables. If you select treenode presenting an individual table, you can see all the data in it, if you expand table columns, then you will see detailed column info. In column hint you can see data type and some additional information.
As with Tables, clicking on Indexes will show you info about all the indexes in the database, selecting individual index node will show you information about that index only. There are no child nodes for Keys and Constraints.
Menu item File –> Tools opens following dialog:
These are all normal SqlCeEngine operations, it is just simpler to use them from here. Like it says, Repair will attempt to recover corrupted rows first, then remove those it was unable to recover. Verify will report that the database is corrupt if you’ve specified a wrong password.
Database will be backed up for all operations, including Verify.
Unlike I normally do, I won’t post the source code this time, sorry. Not because I want to keep it closed source – quite the opposite – but simply out of shame. I started with one direction and kept adding and changing things, thinking and planning as I coded. As you can expect, the result is quite a bad mix of spaghetti code, with SQL queries as concated as strings in it (don’t do that! Ever!). So… I’d rather not have anyone seeing the code right now, tywm.
I plan to do version 2 pretty soon… hopefully. Some ideas for the next version:
- Clean up and optimize the code
- Add functionality to use manual queries
- Use descriptive column icons (key image for primary key etc)
- Allow changing the data (maybe)
- Open database from commandline (i.e. integration with Windows Explorer). Sorry, I just forgot about that in the current version.
- Better support for image column type – when clicking on a cell with image, pop up an image window with full image, resized to fit the screen if needed.
- Better user interface – use toolbar instead of menu.
- See about using SqlMetadata
If you have more suggestions, please leave them to the comments of this post.