• Download codebase for CE3.5 - 572 KB
• Download codebase for CE4 - 1.45 MB
• Download help file - 433 KB
• Download installer for CE3.5 - 827 KB
• Download installer for CE4 - 1.59 MB

Introduction

CEQuery was written in C# with the help of the Visual Studio 2008 platform. The new version has been upgraded to VS2010. SQL CE version 4 has been supported in this release. Some of the features of this tool are described below:

• User is able to create a CE database. If the schema is provided, the tool is able to create the tables and columns.
• User is able to overview an existing CE database with its table and column structure in tree format.
• User can design a query by dragging and dropping tables and columns from the database tree. The query pane in the tool is able to hold multiple queries provided, that only one of those queries is active [as the SQL Server CE engine does not support multiple queries in a single statement]. The tool is able to handle multiple query panes in the form of tabs. The query designer of the tool is able to highlight the keywords. The tool is able to handle the execution of the user's selected query.
• User can manipulate [Insert/Update/Delete] records in a selected table.
• User is able to generate scripts from a single table or multiple selected tables. [Both for SQL CE and SQL Server.]
• User is able to open multiple SQL Server CE files simultaneously.
• User is able to convert a SQL Server database to an SDF file with schema and data.
• User is able to change the password of a SQL CE DB. The tool is now capable of handling a database with no password.
• User is able to design CE tables with Visual Interface.
• User is able to design a query with Visual Interface.
• User is able to execute DB related operations like Validate, Repair, Compact, etc.
• User is able to export a query result.

Background

In one of my projects, I have to use a SQL Server CE database rigorously as the application interacts with the local version of the SQL Server CE file for displaying and manipulating data. SQL Server CE 3.5 version has been used in the application. The application is built on the Visual Studio 2005 platform with support for .NET Framework 2.0. I was initially faced with some problem with querying SQL Server CE files. Following are the reasons for creating a separate tool for querying a CE database:

• SQL Server 2005 Management Studio does not support querying a SQL Server CE 3.5 or SP1 database for displaying contents and querying data.
• There are no free tools for querying a CE database available on the internet.
• The only option to view the content of a CE database file is to install VS 2008 which has built-in support for viewing and querying a CE database. But the UI provided for querying a CE database in Visual Studio 2008 is not a user friendly one. We need to type hell of a lot of things for querying a table. Users who are using SQL Server Management Studio will be very uncomfortable with Visual Studio 2008 for querying a CE database.

So to manage the queries for a CE database, the concept of writing a new tool came to mind. As the SQL Server CE architecture is completely written in C#, there should not be any problem writing a small new tool in a short time period. The main aspects of the tool are as follows:

• The tool should be able to view the table and column structure in a CE database.
• User should be able to design a query in less time by dragging and dropping table names and column names in the query pane from the DB tree like in SQL Server Management Studio.
• The tool should be able to create a new blank CE database. If a schema file is provided, the tool should be able to create tables and columns in the database.
• The user should be able to manipulate data [like Insert/Update/Delete] in any table using the tool.
• The user should be able to generate a script for an individual table as well as the whole database.
• The user should be able to place multiple queries in a single query pane. Also she/he should be able to use multiple query panes.
• The query editor should highlight the keywords in the query so that the user should feel like using SQL Server Management Studio.

Using the Code

A brief description of how to use the article or code has been provided in the help file [CEQuery-Help.zip] attached with this article.

Points of Interest

The following articles were very helpful in terms of learning for me:

• ICSharpCode.TextEditor. I took help from this CodeProject article - Using ICSharpCode.TextEditor.
• IE7 like Tab View in Windows. I have used the following CodeProject article in my application: A tab control similar to IE7's.
• SQL CE script generator framework. I took help from the following blog: Generate Data and Export Data from SQL CE (Compact Edition 3.5). I have customized the framework to my requirements.

You need to have SQL Server CE 3.5 SP2 installed to run the code.

Improvement in the Latest Version

The latest version of CEQuery has some enhancements as well as bug fixes. Suggestions from users have been the main reason for posting the new release. Also, a new version of the codebase and installer which points to SQL Server CE 4 has been attached here, with the same functionality. For the CE4 installer, the user does not have to install CE4 separately.

Enhancements

• New: Enhanced SQL Editor with undo and redo
• New: Export SQL Server DB with Windows Authentication support
• New: Visual Table Editor
• New: Visual Key Editor
• New: Visual Query Designer
• New: Validate and repair DB
• New: Compact and shrink DB
• New: Separate codebase and installer for supporting SQLCE 4; automatically upgrades DB to CE4

Bug Fixes

• Bug 1: Bug fix for byte array, pointed out by "Digitalbeach"
• Bug 2: Bug fix for IDENTITY attribute, pointed out by "Tony Vaughan"

History

• 28^th February, 2011: Updated code base and installer.
• 30^th April, 2009: Updated code base and installer.
• 27^th April, 2009: Third release.
• 18^th March, 2009: Second release.
• 16^th February, 2009: First release.