Create a SQL Server 2005 Compact Edition database by using Query Analyzer 3.0

Screenshot of Query Analyzer about dialog boxSQL Server 2005 Compact Edition Query Analyzer is a small application that can run directly on a Windows Mobile PDA and allows you to create and manipulate SQL Server Compact Edition databases. It is a handy tool to add to your toolbox, as it can help diagnose and correct small database errors while you are away from your development PC.

First a word of warning… Query Analyzer is prehaps the worst application with respect to conforming to the “Designed For WIndows Mobile” logo certification requirements. The application is not supported on Windows Mobile Standard (smartphone) devices, and many dialogs are unusable on small or square resolution Windows Mobile Professional (PocketPC) devices. The application appears to be more at home on devices running the standard Windows CE shell as evidenced by its heavy reliance on non fullscreen dialogs.

Installing Query Analyzer

If your .NET Compact Framework based application makes reference to the System.Data.SqlServerCe assembly Visual Studio will automatically install the query analyser tool when you deploy your project via the IDE.

If you are a native developer or want to utilise the tool without deploying via Visual Studio you will need to manually install the Query Analyzer tool which is part of SQL Server Compact Edition’s developer support cab file.

The SQL Server Compact Edition Books Online documentation has a “How to: Install Query Analyzer (SQL Server Compact Edition)” section that covers this process.

Essentially you need to install the following CAB files on your device:

  • .NET Compact Framework – NETCFversion.platform.processor.cab
  • SSCE runtime – sqlce30.platform.processor.cab
  • SSCE dev tools – sqlce30.dev.ENU.platform.processor.cab

You should be able to find the required CAB files within the %programs%\Microsoft Visual Studio 8\SmartDevices\SDK\SQL Server\Mobile\v3.0\platform\processor\ folder. One tricky bit is the fact that references to “platform” and “processor” in file and directory names above need to be replaced with values specific to the version of Windows Mobile your target device is utilising.

In most cases you can use wce500\armv4i\sqlce30.ppc.wce5.armv4i.CAB and wce500\armv4i\sqlce30.dev.ENU.ppc.wce5.armv4i.cab for Windows Mobile 5 Pocket PC or higher devices. Further instructions can be found in the “How to: Install SQL Server Compact Edition on a Device” article available on MSDN.

One tip to correctly determine the correct CAB files to install is to hard reset a PDA and have Visual Studio deploy your project to the PDA. After the deployment you should be able to refer to the output window to see which CAB files Visual Studio deployed and installed to configure SQL Server Compact Edition.

Opening a database

After installing the SQL Server Compact Edition developer tools CAB file you should be able to find a new Query Analyzer application within the start menu of your Windows Mobile device.

The initial window that appears after this application is started should show a series of tabs with the one labelled “Objects” selected. This tab is similiar to the Object Explorer of SQL Server 2005 Management Studio on the desktop and shows each database you have connected to in the past and the database objects contained within them.

Screenshot showing main screen of Query Analyzer application

To open a connection to an existing database you need to click the toolbar button at the bottom of the screen. This will display the following “Connect to SQL Server Compact Edition Database” dialog.

Screenshot showing Connect To Sql Server Compact Edition dialog

You need to enter the full path to your database (i.e. \Program Files\MyApplication\test.sdf) and a password if the database is password protected. Selecting the [...] button will display a file open dialog that allows you to graphically select a database file. This dialog unfortunately is restricted to database files located within the My Documents folder structure. If your database is not located within this hierarchy you will have to manually type it in.

Once the database connection details have been entered you can select the Connect button. This will return the user to the main Query Analyzer window with the specified database added to the tree view.

When you have completed your tasks with the database you should select the database within the treeview and select the toolbar button to disconnect from the database. If you do not do this it may not be possible for other applications to access the database due to Query Analyzer having a lock on the database file.

Creating a database

A new database can also be created directly on the PDA. To do this you follow the same process to open an existing database. However when the Connect to SQL Server Compact Edition database dialog appears you select the “New Database” button which will expand the connection window to include a couple of additional fields.

Screenshot showing Connect To Sql Server Compact Edition dialog after Create Database button has been pressed

The details you can enter via this dialog include:

  • Path – The full path to where the *.sdf database file should be created.
  • Password – An optional password that must be provided whenever an attempt is made to connect to the new database.
  • Sort – The sort order applied to indexes on tables within the database.
  • Encrypt – If encryption is enabled data within the database file will be encrypted. Enabling this feature will make a password mandatory since the password is the key used to decrypt the database.

After the new database has been configured clicking the Create button will return the user to the main Query Analyzer window with the newly created database added to the treeview.

Creating Tables

Screenshot showing main window of Query Analyzer after database has been created (or opened)

There are two ways to create a table within Query Analyzer. The first is to select the toolbar button while the Tables node of a database is selected. This will bring up the following Table Definition dialog that enables the user to specify a table name and define the columns within the table. These dialogs in particular are difficult to utilise on devices with small square screens.

Screenshot showing Table Definition dialog used to create a new database table graphically

An alternative to the GUI dialogs is to switch to the SQL tab of the main Query Analyzer window and enter a T-SQL data definition language (DDL) statement to create the table programatically, as shown in the screenshot below:

Screenshot showing creating a table via TSQL query execution

Once you have entered the CREATE TABLE statement you can select the toolbar button to execute the query. Query Analyzer will automatically switch to the Notes tab and inform you of the duration and outcome of the operation.

3 Responses to “Create a SQL Server 2005 Compact Edition database by using Query Analyzer 3.0”

  1. mithun says:

    hello,
    i am currently working on a project that is involved with interfacing a pda with the sql database. pls can u suggest me a way that how i can query the server database from my pda. I have used the merge replication architecture and have coded my application in Visual C#. I am currently not getting of how to query the database from my application. Hope that i will get a reply urgently.
    thanking you,
    mithun

  2. Claudio Weiler says:

    Thank you for your article.
    I like to add a comment about my installation. I have to instal sqlce30.repl.platform.processor.cab too. Without it, Query Analyzer cant connet to sql engine.
    (PPC2003SE)

  3. Anandhu says:

    Hello,
    Actually am learning to develop Windows Mobile Application (6.0) in that application am try to add my own Toolbar .It comes fine on bottom of the Application. I need to bring top of my application.
    So can you suggest me please.
    send me mail to my id

    Thank you in advance

Leave a Reply