…meie igapäevast IT’d anna meile igapäev…

2008-09-18

Small utils, part four: SQL CE database viewer

Filed under: Isiklikud,Programmeerimine — Sander @ 10:08:00

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.

SqlCeViewer1

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:

SqlCeViewer2

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:

Todo:
  • 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.

9 kommentaari »

  1. Hi, I have downloaded and extracted the application but I get the following error when I try to run it:

    “An unhandled exception (‘System.IO.FileLoadException’) occured in SqlCeDbViewer.exe [1540].”

    Any ideas?

    Stefan

    kommentaar kirjutas Stefan Pienaar — 2008-09-25 @ 10:58:06 | Vasta

  2. When exactly do you get the error? Also, what version of Windows – I’ve only tested it on XP.

    kommentaar kirjutas dukelupus — 2008-09-25 @ 14:06:59 | Vasta

  3. Great job, would love to be able to edit the database data.

    kommentaar kirjutas Robert Mirenzi — 2008-10-12 @ 10:19:10 | Vasta

  4. […] Uncategorized — dukelupus @ 15:38:19 A bit more than a month ago, I released SQL CE Database Viewer. I promised in that post I would do a version 2 – with ability to edit the data and some other […]

    Pingback-viide kirjutas SQL CE Database Editor v2 - BETA « …meie igapäevast IT’d anna meile igapäev… — 2008-10-28 @ 17:01:00 | Vasta

  5. Beautiful work – I’d love to see the code. Is it posted?

    kommentaar kirjutas Don Robins — 2009-01-08 @ 04:20:20 | Vasta

  6. I’m on Vista and I get a “System.BadImageFormatException” when I try to start the program: (works fine on XP)

    Description:
    Stopped working

    Problem signature:
    Problem Event Name: CLR20r3
    Problem Signature 01: sqlcedbviewer.exe
    Problem Signature 02: 1.0.0.0
    Problem Signature 03: 48d0f2c3
    Problem Signature 04: System.Data.SqlServerCe
    Problem Signature 05: 3.5.1.0
    Problem Signature 06: 486140a5
    Problem Signature 07: 11b
    Problem Signature 08: 2d
    Problem Signature 09: System.BadImageFormatException
    OS Version: 6.0.6000.2.0.0.256.6
    Locale ID: 2057

    kommentaar kirjutas Scott — 2009-02-23 @ 13:21:21 | Vasta

  7. Would your marvellous little V1 program work on a Windows 7 (64.bit) laptop if installed in the Program Files(86) directory

    kommentaar kirjutas Ken Beale — 2010-04-26 @ 18:54:50 | Vasta

  8. Thanks Buddy you are a life saver!!!!!!!!!!!!

    kommentaar kirjutas Winlight Solutions — 2011-09-21 @ 10:04:27 | Vasta

  9. Free open source viewer/editor:
    https://sourceforge.net/p/compactview/

    kommentaar kirjutas axtu — 2011-09-23 @ 00:05:42 | Vasta


RSS feed for comments on this post. TrackBack URI

Lisa kommentaar

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Muuda )

Twitter picture

You are commenting using your Twitter account. Log Out / Muuda )

Facebook photo

You are commenting using your Facebook account. Log Out / Muuda )

Google+ photo

You are commenting using your Google+ account. Log Out / Muuda )

Connecting to %s

Blog at WordPress.com.