Walkthrough: Creating a SQL Server Compact Database

Article
10/26/2012

This walkthrough demonstrates how to create a new SQL Server Compact database file that is based on the Customers and Orders tables in the Northwind sample database.

This walkthrough illustrates the following tasks:

Creating a new Windows-based application.
Creating a new local database file and adding it to a project.
Creating database tables and relationships by using the data design tools in Visual Studio.

Prerequisites

In order to complete this walkthrough, you will need:

Access to the Northwind sample database. For more information, see How to: Install Sample Databases.

Creating a Windows-Based Application

Because you will create a database that is based on the empty database template, you need a project in which to create the database.

Note

Your computer might show different names or locations for some of the Visual Studio user interface elements in the following instructions. The Visual Studio edition that you have and the settings that you use determine these elements. For more information, see Visual Studio Settings.

To create a new Windows-based application

In Visual Studio, on the File menu, point to New and then click Project.
Name the project SampleDatabaseWalkthrough.
Click Windows Forms Application and then click OK. For more information, see Developing Client Applications.

The SampleDatabaseWalkthrough project is created and added to Solution Explorer/Database Explorer.

Creating a New SQL Server Database

To add a new database to the project

On the Project menu, click Add New Item.
Select Local Database in the list of available templates.
Type SampleDatabase in the Name box.
Click Add.

The Data Source Configuration Wizard opens with the new SampleDatabase.sdf file chosen as the data connection.
Click Next.
Click Next again to save the connection string to the application configuration file.
Click Finish on the Choose Your Database Objects page. (No database objects are available because this is a new database.)

SampleDatabase.sdf and SampleDatabaseDataSet.xsd are added to the project.

Creating New Tables in the Database

To add tables to the database

Open Server Explorer/Database Explorer by clicking Server Explorer/Database Explorer on the View menu.
Expand the SampleDatabase.sdf node under the Data Connections node.
Right-click Tables and then click Create Table.

The New Table dialog box opens.

Create columns as described in the following table:

Column Name	Data Type	Length	Allow Nulls
CustomerID	nchar	5	No
CompanyName	nvarchar	40	No
ContactName	nvarchar	30	Yes
ContactTitle	nvarchar	30	Yes
Address	nvarchar	60	Yes
City	nvarchar	15	Yes
Region	nvarchar	15	Yes
PostalCode	nvarchar	10	Yes
Country	nvarchar	15	Yes
Phone	nvarchar	24	Yes
Fax	nvarchar	24	Yes

Select the CustomerID column, and then set Primary Key to Yes.
Type Customers in the Name box.
Click OK.
In Server Explorer/Database Explorer, right-click Tables and then click Create Table.

The New Table dialog box opens.

Create columns as described in the following table:

Column Name	Data Type	Length	Allow Nulls
OrderID	int	4	No
CustomerID	nchar	5	Yes
EmployeeID	int	4	Yes
OrderDate	datetime	8	Yes
RequiredDate	datetime	8	Yes
ShippedDate	datetime	8	Yes
ShipVia	int	4	Yes
Freight	money	19	Yes
ShipName	nvarchar	40	Yes
ShipAddress	nvarchar	60	Yes
ShipCity	nvarchar	15	Yes
ShipRegion	nvarchar	15	Yes
ShipPostalCode	nvarchar	10	Yes
ShipCountry	nvarchar	15	Yes

Select the OrderID column, and then set Primary Key to Yes.
Type Orders in the Name area.
Click OK.

To create a relationship between the tables created in the previous procedure

In Server Explorer/Database Explorer, expand Tables.
Right-click the Orders table and then click Table Properties.
Click Add Relations.
Type FK_Orders_Customers in the Relation Name box.
Select CustomerID in the Foreign Key Table Column list.
Click Add Columns.
Click Add Relation.
Click OK to complete the process and create the relationship in the database.
Click OK again to close the Table Properties dialog box.

Populating the Sample Tables with Data

To populate the Customers table with data

In Server Explorer/Database Explorer, create a new connection to the Northwind sample database. For more information, see How to: Install Sample Databases and How to: Connect to Data in a Database.
Expand the Northwind database node in Server Explorer/Database Explorer.
Right-click the Northwind Customers table and then click Show Table Data.
Select all the records and copy them to the Clipboard.
Expand the SampleDatabase.sdf database in Server Explorer/Database Explorer.
Right-click the SampleDatabase.sdf Customers table and then click Show Table Data.
Paste the Northwind Customers table data from the Clipboard.

To populate the Orders table with data

Right-click the Northwind Orders table and then click Show Table Data.
Select all the records and copy them to the Clipboard.
Expand the SampleDatabase.sdf database in Server Explorer/Database Explorer.
Right-click the SampleDatabase.sdf Orders table and then click Show Table Data.
Paste the Northwind Orders table data from the Clipboard.

Creating a Copy of the Database

Now that you have created a sample database containing data, you should make a copy of the database in its original state so that you can restore the data if you need to.

To save the sample database

On the File menu, click Save All.
On the File menu, click Close Solution.
Browse to the SampleDatabase.sdf file in your Projects folder, and copy it.
Browse to a folder where you want to save the database, and paste the copy into the folder.

Next Steps

Now that you have a local database file containing some sample data, you can complete the following procedure:

How to: Deploy a SQL Server Compact 4.0 Database with an Application