SQLCE Database Tutorial

You want to develop SQLCE databases that store data using SDF Viewer. You already know some things about databases. Here are some instructions you can follow to start working with SQLCE databases, using SDF Viewer.

SQLCE database introduction

SQLCE is a powerful client-side database, and it is fairly easy to use. We will make a client-side SQL database hosted on the desktop computer. It will store simple data. Let's look at the steps required to create the SQLCE database. Here is how this will look on your screen.

Getting started. Download the trial edition of SDF Viewer and install.

How can I make a new SQLCE database?

Use the menu at the top of SDF Viewer select New - Database

Click the Database Name button and select a name for the SQLCE database in this example "Staff.sdf".

You can add a password to protect the database if you wish.

You can select SQLCE database version 3.1, 3.5 or 4.0. Use SQLCE 4.0 if the database is to be used for a website or SQLCE 3.5 if replication with SQL Server will be required. SQLCE 3.1 can be used for compatability with legacy programs.

Specify which sorting options you require using the Case Sensitive option and selected Language.

Click OK and an empty SQLCE database will be created.

How do I create a table?

From the top menu select New - Table OR right click the Tables symbol and select Add New Table.

New table dialog

Now, there is the New Table dialog box. Click in the name box and type "StaffNames" or whatever you need your table to be named. Next, we need to add some columns to the Table before we can save it. Here we have added "FirstName"

How can I add columns to a table?

By clicking Add the Column Designer will be shown

Select a Data Type in this case we have selected "nchar" and specified a maximum length of 25 characters. Click OK to add the Column to the table.

A number of columns can be added to the table, we also added LastName nchar(25); StartDate datetime; StaffNo int Unique Primary Key

When the columns have been added click OK on the Table Designer to save the table.

Your database should now look like this screenshot.

How can I insert values into a table?

Select the "Table View" tab, and type the data on a new row.

Click the Update button to save the data.

How can I read data from the database?

Using a SELECT command. Finally, we have a database, data in the table. Let's get some filtered data out of the table. Use a SELECT query, now, which you will be familiar with. We want the columns named "FirstName" and "LastName" where the "StaffNo" is less than 2050; your column names will be different. Enter the following into the SQL text box "SELECT FirstName, LastName FROM StaffNames where StaffNo<2050"

Click the "Run SQL" button and view the reults in the "Table View" tab.

Summary

Here we looked at using SQLCE databases in SDF Viewer, created a new database, added a table, columns and data as well as filtering the data.