VSTO & .NET & Excel

October 28, 2010

CEQuery Tool for SQL Server Compact Edition Databases

Filed under: .NET & Excel, Excel, SQL Server, VSTO & Excel — Dennis M Wallentin @ 4:35 pm

Background
If You suspect that I, at present, work a lot with SQL Server Compact Edition (SSCE) databases than You are absolutely right about it. I have developed two larger solutions where SSCE databases are central parts of the respectively solution. In both projects data is exported to SSCE databases and then the data is acquired from these two sources to populate Excel reports with.

In one of the projects the client also wanted a simple tool to query the database with. I had two alternatives to choose between; build a query tool from scratch or look for a free/commercial query tool that could work with SSCE databases. For several reasons the client wanted in the end me to build a new tool. However, at that time I had already made some investigations and among the few tools I have found CEQuery Tool quickly became a favorite.

The CEQuery Tool
Working with the CEQuery Tool is very simple and straightforward. The tool provides us with several tools:

  • Create new SSCE databases and tables from scratch.
  • Open existing SSCE databases and acquire data from them.
  • The acquired data can be exported to Excel, i.e. saved to Excel files.
  • Created queries can be saved for later use and are written to Query files.
  • Export data from a SQL Server database, either to an existing SSCE database or to a new SSCE database. The main window for the Export/Import tool is showed below after the list.
  • A Script Generator with which we can create scripts, for instance to create or drop tables. These scripts can also be saved for later use and are written to Query files. The main window for the Script Generator is also showed below after the list.
  • The CEQuery tool is free and the source code is available which allow us to customize and update the tool, if desirable.

The below screen shot shows CEQuery’s main menu, i.e for the available tools:

For some unknown reasons, at least to me, the Script Generator tool is available via CEQuery’s Command bar and via the right click menu in the Database window but not in the main menu.

The Excel Export tool is only available via the right click menu in the Datagrid’s window. Also commands to create and delete tables are only available via the right click menu in the Database’s windows and not via its main menu.

The screen shot of the Export/Import tool’s main window.

The screen shot of the Script Generator’s main window:

The next screen shot below shows CEQuery’s main window where we have opened a SSCE database, created the SQL expression and also acquired the data:

If we right click in the Database window we get access to additional commands which the below screen shot also shows:

After we have executed the Script Generator the Query file is generated. The following screen shot shows how its content can look like:

I have been using the tool for some time now and all its main tools and functions works as expected. The tool feels reliable and stable. Of course, it has some shortcomings like:

  • The location of its main tools and commands.
  • Issues with the UI, especially when resizing the windows.
  • The size and the location of the tool’s main windows are not saved between the sessions.
  • The help file is not embedded in the tool. It’s only available as a standalone file.
  • The source code is available so if we find anything annoying or less good implemented we can actually change it.

If You work extensively with SSCE databases and are looking for a tool the CEQuery tool should be among the tools that should be evaluated.

Kind regards,
Dennis

About these ads

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

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 / Change )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

The Silver is the New Black Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 36 other followers

%d bloggers like this: