Archive for the ‘Databases’ Category

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

Wednesday, March 12th, 2008

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.

Create a SQL Server 2005 Compact Edition database visually by using the Data Source Configuration Wizard in Microsoft Visual Studio 2005

Monday, January 28th, 2008

Since developers spend a lot of their time within the Visual Studio 2005 IDE it is only natural that this tool has many features designed to create and maintain the contents of SQL Server 2005 Compact Edition databases.

NOTE: If any of the dialogs within your copy of Visual Studio 2005 refer to “SQL Mobile Database” or something similar you have an older version of SQL Server CE installed. See my Installing SQL Server Compact Edition blog entry for details on how to upgrade.

Creating a database

Screenshot showing Add New Item dialog

The quickest way to add a blank SQL Server Compact Edition database to a smart device project is to right click on the project within Solution Explorer and select the “New Item…” option within the “Add” submenu. This allows you to select the “Database File” item template, as shown in the above screenshot.

Once you have named the database file and clicked the Add button, the last step of the “Data Source Configuration Wizard” appears. You can press the cancel button to skip this wizard since our database is currently empty.

Another approach to creating a database file is to use the complete Data Source Configuration Wizard. This is accessible in a number of different places within the Visual Studio IDE, perhaps the easiest being via the “Add New Data Source…” menu item found within the main Data menu.

Screenshot showing first step of Data Source Configuration Wizard

The first step of the wizard asks what type of data source you would like to configure. For a SQL Server Compact Edition database we select “Database” and select the “Next” button to move to the second step of the wizard.

Second step of the Data Source Configuration Wizard

Since we are creating a new database our database will not be listed in the database combo box, so we press “New Connection…” to display the “Add Connection” dialog.

Screenshot showing the Add Connection dialog

This dialog can be used to configure a wide range of database products. If the read-only text box at the top of the dialog does not mention “Microsoft SQL Server 2005 Compact Edition” click the “Change…” button and select it from a small popup dialog that appears.

To create a database select the “Create…” button. This will display a dialog similar to the one seen within SQL Server Management Studio as discussed earlier.

Screenshot of Create New SQL Server Compact Edition Database dialog

Specifying the location we want the new database file to be stored in and pressing the OK button twice will return us to step 2 of the Data Source Configuration Wizard. Pressing the Next button takes us to the third and final step of the wizard. If the database file isn’t located within a subdirectory of the project you will get a prompt “the connection you selected uses a local data file that is not in the current project, would you like to copy the file to your project and modify the connection?”. Selecting yes is probably the best option, and will move a copy of the newly created database file into the project folder.

Screenshot showing third step of the Data Source Configuration Wizard

This last wizard step enables us to create a type-safe dataset for the database. Since we have just created a blank database there is not much use for this, so we can press the Cancel button to dismiss this step.

Opening a database

Double clicking on a SQL Server 2005 Compact Edition database file (*.sdf) within Solution Explorer will automatically open the Server Explorer pane and provide a view similar to the Object Explorer pane found within SQL Server Management Studio.

If the database file is not currently part of our solution we can use the “Connect To Database” toolbar button within the Server Explorer window to display the Data Source Configuration Wizard (as discussed above) to connect to the existing database file.

Creating tables

Screenshot of Server Explorer windowTo create a Posts table that looks like the example table we created in the previous tutorial we can use the Server Explorer window.

Right clicking on the “Tables” node underneath our database file will allow us to select a “Create Table” menu option. This will display a “New Table” dialog that allows us to graphically specify the details of our new table.

Screenshot showing the New Table dialog within Visual Studio 2005

Installing SQL Server Compact Edition (v3.1)

Monday, January 28th, 2008

SQL Server 2005 Compact Edition is available via a number of Microsoft downloads including:

Knowing which components are required and need to be installed for a given situation is important but can be confusing due to the various ways the SQL Server Compact Edition components can find their way onto your machine.

Installing onto a PDA

If your application makes use of a SQL Server Compact Edition database you must ensure that every PDA it runs on has the SQL Server Compact Edition database engine installed. If your target device is running Windows Mobile 6.0 it should have SQL Server 2005 Compact Edition in ROM, meaning nothing needs to be manually installed.

If your target device does not have a version of SQL Server Compact Edition installed or you would like to ensure that the latest service pack is installed you will need to manually install the database engine via one or more CAB files as part of your application installation process.

There are three different CAB files that make up the SQL Server 2005 Compact Edition product.

  • sqlce30.platform.processor.cab – The actual database engine.
  • sqlce30.repl.platform.processor.cab – An optional component that is required if your application makes use of the Merge Replication or Remote Data Access (RDA) APIs.
  • sqlce30.dev.lang.platform.processor.cab – Additional features for development purposes such as improved error messages for exceptions, and a query analyzer utility that runs directly on a PDA.

Once SQL Server 2005 Compact Edition support has been installed into Visual Studio 2005 (see below) you can 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 replaced with values specific to the version of Windows Mobile your target device is utilising.

In general you can use:

  • wce500\armv4i\sqlce30.ppc.wce5.armv4i.CAB – For Windows Mobile 5 Pocket PC or above devices.
  • wce500\armv4i\sqlce30.phone.wce5.armv4i.CAB – For Windows Mobile 5 Smartphone or above devices.

Although you will find support for a range of platforms and processor types within the specified folder.

Installing onto a desktop (or TabletPC, or UMPC etc)

One advance of SQL Server CE with the SQL Server 2005 Compact Edition release is the ability to run the database engine on desktop PCs.

If your target machine does not have Visual Studio 2005 or SQL Server 2005 installed you need to install the SQL Server Compact Edition Runtime (SQLServerCE31-EN.msi) on each machine your application is deployed to. This may be able to be done automatically if you are using Click-Once deployment functionality for your product. Alternatively the MSI can be directly downloaded, or found on your development machine within the %programfiles%\Microsoft Visual Studio 8\SDK\v2.0\BootStrapper\Packages\SQL Server Compact Edition folder.

Developer Tool Support

Different versions of Visual Studio support different versions of SQL Server CE.

Visual Studio version SQL Server CE version
VS2003 SQL Server 2000 Windows CE Edition (v2.0)
VS2005 (RTM) SQL Server 2005 Mobile Edition (v3.0)
VS2005 (SP1 + update) SQL Server 2005 Compact Edition (v3.1)
VS2008 SQL Server Compact 3.5 (v3.5)

As João Paulo Figueira pointed out SQL Server 2005 Mobile Edition (v3.0) and SQL Server 2005 Compact Edition (v3.1) are both compatible at the file level. The major changes between these two releases related to the high level SQL query processor and the number of platforms supported by the product.

Using the supported version of SQL Server CE for a particular version of Visual Studio means that you get access to all the graphical designers and database manipulation tools within Visual Studio such as Server Explorer and the Data Source Configuration Wizard. It is generally possible to utilise an older or newer version of the SQL Server CE database engine with a given version of Visual Studio, but in doing so you will loose the ability to use the graphical tools.

When Visual Studio 2005 shipped it orignally supported Mobile Edition. In order to update your copy of Visual Studio 2005 to support Compact Edition you need to perform the following steps:

  1. Install Visual Studio 2005 Service Pack 1.
  2. Install SQL Server Compact Edition Runtime.
  3. Install SQL Server Compact Edition Tools for VS2005 SP1.
  4. Optionally install SQL Server Compact Edition Books Online.

The second step of these instructions installs the updated (SQL Server 2005 Compat Edition) database engine, while the third step updates the Visual Studio 2005 user interface. You need to be careful, if you only install Visual Studio 2005 SP1, some elements of the user interface will have been updated, but this is only a change of a couple of strings within the dialog resources. Even though Visual Studio will refer to SQL Server Compact Edition, underneath the covers it is in fact still utilising the SQL Server 2005 Mobile Edition database product. For a more detailed coverage including solutions to the most common problems you may find along the way please review Steve Lasker’s Configuring Visual Studio 2005 for use with SQL Server 2005 Compact Edition blog post.

If you are using a development environment other than Visual Studio, or are looking for a way to obtain the CAB files without needing to install Visual Studio 2005 you could download the SQL Server Compact Edition Developer SDK. This package contains the installers for the SQL Server Compact Edition Runtime, the CAB files required for installation onto a PDA, a local copy of the SQL Server Compact edition Books Online documentation, as well as an example of how to access SQL Server Compact Edition databases in native C++ code.

Create a SQL Server 2005 Compact Edition database visually by using SQL Server Management Studio

Saturday, January 26th, 2008

Microsoft SQL Server 2005 Management Studio (commonly also referred to as SSMS) is a replacement for the Enterprise Manager tool found in earlier versions of SQL Server. As well as administering SQL Server 2005 (or earlier) database instances the tool can be used to administer SQL Server 2005 Compact Edition databases that live on a PDA.

If you don’t have a version of the SQL Server 2005 developer tools installed on your desktop, you can download and install the free SQL Server 2005 Management Studio Express edition (service pack 2 or later), as this product also supports Compact Edition databases.

Creating a database

When SQL Server Management Studio is started on your desktop PC the “Connect to Server” dialog will appear.

Screenshot of SQL Server 2005 Management Studio's Connect To Server dialog

To create a new SQL Server 2005 Compact Edition database,

  1. Select “SQL Server Compact Edition” as the server type
  2. Select the <New database…> option within the Database File combo box

This will display a “Create New SQL Server Compact Edition Database” dialog, as shown below:

Screenshot of Create New SQL Server Compact Edition Database dialog

The most important option to specify is the location of the new database file. Selecting the browse button allows you to graphically select a location on your local harddrive, or an ActiveSync connected PDA. Alternatively you could type in the path manually. To create a database on the PDA you use the prefix “Smart Device” instead of a drive letter. For example “Smart Device\Program Files\MyApplication\MyDatabase.sdf”.

The encryption and sort order properties will not be discussed at this point in time, as I am planning to cover these in greater depth in future blog entries. In the mean time they can be left at their default values.

To complete creating the database we need to select the OK button. Since no password has been specified for the database, a warning that the “password is either not provided or blank” will be displayed. This can be safely ignored by pressing the Yes button.

After SQL Server 2005 Management Studio has created the database you will be returned to the “Connect to Server” dialog, however this time the Database File field is populated with the name of the newly created database.

Opening a database

Once a SQL Server 2005 Compact Edition database has been created it can be opened within SQL Server Management Studio 2005 by using the same “Connect to Server” dialog discussed above (also accessible via the File, Connect Object Explorer… menu option).

To open an existing SQL Server 2005 Compact Edition database,

  1. Select “SQL Server Compact Edition” as the server type
  2. Specify the database filename within the Database File combo box

Recently accessed databases are available in the Database File drop down combo box. To browse the file system to graphically select another database file you can select the <Browse for more…> option within the combo box.

Creating Tables

One of the advantages of using SQL Server Management Studio is its rich support for the management and manipulation of databases and their contents. When targeting SQL Server 2005 Compact Edition databases many Management Studio features are disabled, but the subset provided is still pretty useful.

Screenshot showing how to create a new table via the Object Explorer pane

Within the Object Explorer pane you can view the various objects within your database. To create a new table, you can right click on the Tables node within the Object Explorer and select “New Table”. This will display a dialog which allows you to specify the details of the new table, such as its name and the properties of the columns it contains. Using this view also allows you to rename and delete database objects.

Alternatively you can interact with the database via Transact-SQL queries. The SQL Server 2005 Compact Edition database engine supports a subset of the query language supported by SQL Server 2005 on the desktop. To create a new query select the “New Query” toolbar button (Ctrl + N is the keyboard shortcut), and enter the query in the new window that appears. Pressing F5 or the execute toolbar button will then execute the query and show its results.

To create a new database table we could execute the following query (for more details see the CREATE TABLE (SQL Server Compact Edition) section of SQL Server 2005 Compact Edition Books Online).

CREATE TABLE Photos
(
  id INT NOT NULL IDENTITY PRIMARY KEY,
  name NVARCHAR(100) NULL,
  filename NVARCHAR(256) NOT NULL,
  description NVARCHAR(2048) NULL
)

By default after executing such Data Definition Language (DDL) statements changes in the database schema are not reflected in the Object Explorer pane. In order for the new table to show up you need to right click and select “Refresh”.

The many versions of SQL Server CE

Tuesday, January 22nd, 2008

If you refer to the 70-540 Exam study guide you will see references to a database product called SQL Server 2005 Everywhere Edition. This product technically doesn’t exist!

Logo of SQL Server 2000 Windows CE Edition product

Originally there was a database product for Pocket PC devices called SQL Server CE (aka SQL CE). For similiar reasons to those mentioned in my blog post about Windows Mobile naming conventions, the product name for the SQL Server CE database engine has changed over time.

This leads to a confusing mismatch of names through out the various developer tools and documentation, especially when Visual Studio and SQL Server CE releases were not in sync with each other. As a simple example the ADO.NET data provider for SQL Server 2005 Mobile Edition databases still lives within the System.Data.SqlServerCe namespace for backwards compatability reasons.

A brief version history

There have been atleast 5 major releases of the SQL Server CE database engine as follows.

Offical Name Nickname
SQL Server 2000 Windows CE Edition SQL CE 1.0
SQL Server 2000 Windows CE Edition SQL CE 1.1
SQL Server 2000 Windows CE Edition SQL CE 2.0
SQL Server 2005 Mobile Edition SQL CE 3.0
SQL Server 2005 Compact Edition* SQL CE 3.1
SQL Server Compact 3.5 SQL CE 3.5

* Was also called SQL Server 2005 Everywhere Edition.

Visual Studio 2005 originally came with support for SQL Server 2005 Mobile Edition. However with Service Pack 1 (and an optional download) it is possible to update it to support development against SQL Server Compact Edition.

The 70-540 Study Guide was obviously produced before SQL Server Compact Edition was officially released. Prior to final release the product was scheduled to be named SQL Server Everywhere edition.

There is little difference between SQL Server 2005 Mobile Edition (v3.0) and SQL Server 2005 Compact Edition (v3.1) on PDAs, the major difference is that SQL Server 2005 Compact Edition was the first release to support running the database engine on an X86 desktop PC. Prior to this release SQL Server CE only supported running on Windows CE based devices such as Pocket PC PDAs.

I am about to renew my effort to post blog entries for my 70-540 Study Guide. This blog post was designed to setup the stage for a series of posts about database access within the .NET Compact Framework environment.