Share via


Walkthrough: Creating a SQL Server Compact 3.5 Database

This walkthrough demonstrates how to create a new SQL Server Compact 3.5 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:

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

  1. In Visual Studio, on the File menu, point to New and then click Project.

  2. Name the project SampleDatabaseWalkthrough.

  3. Click Windows Forms Application and then click OK. For more information, see Creating Windows-Based 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

  1. On the Project menu, click Add New Item.

  2. Select Local Database in the list of available templates.

  3. Type SampleDatabase in the Name box.

  4. Click Add.

    The Data Source Configuration Wizard opens with the new SampleDatabase.sdf file chosen as the data connection.

  5. Click Next.

  6. Click Next again to save the connection string to the application configuration file.

  7. 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

  1. Open Server Explorer/Database Explorer by clicking Server Explorer/Database Explorer on the View menu.

  2. Expand the SampleDatabase.sdf node under the Data Connections node.

  3. Right-click Tables and then click Create Table.

    The New Table dialog box opens.

  4. 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

  5. Select the CustomerID column, and then set Primary Key to Yes.

  6. Type Customers in the Name box.

  7. Click OK.

  8. In Server Explorer/Database Explorer, right-click Tables and then click Create Table.

    The New Table dialog box opens.

  9. 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

  10. Select the OrderID column, and then set Primary Key to Yes.

  11. Type Orders in the Name area.

  12. Click OK.

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

  1. In Server Explorer/Database Explorer, expand Tables.

  2. Right-click the Orders table and then click Table Properties.

  3. Click Add Relations.

  4. Type FK_Orders_Customers in the Relation Name box.

  5. Select CustomerID in the Foreign Key Table Column list.

  6. Click Add Columns.

  7. Click Add Relation.

  8. Click OK to complete the process and create the relationship in the database.

  9. Click OK again to close the Table Properties dialog box.

Populating the Sample Tables with Data

To populate the Customers table with data

  1. 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.

  2. Expand the Northwind database node in Server Explorer/Database Explorer.

  3. Right-click the Northwind Customers table and then click Show Table Data.

  4. Select all the records and copy them to the Clipboard.

  5. Expand the SampleDatabase.sdf database in Server Explorer/Database Explorer.

  6. Right-click the SampleDatabase.sdf Customers table and then click Show Table Data.

  7. Paste the Northwind Customers table data from the Clipboard.

To populate the Orders table with data

  1. Right-click the Northwind Orders table and then click Show Table Data.

  2. Select all the records and copy them to the Clipboard.

  3. Expand the SampleDatabase.sdf database in Server Explorer/Database Explorer.

  4. Right-click the SampleDatabase.sdf Orders table and then click Show Table Data.

  5. 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

  1. On the File menu, click Save All.

  2. On the File menu, click Close Solution.

  3. Browse to the SampleDatabase.sdf file in your Projects folder, and copy it.

  4. 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:

See Also

Tasks

How to: Manage Local Data Files in Your Project

Walkthrough: Adding a SQL Server Compact 3.5 Database to an Application and Deploying it

Concepts

Local Data Overview

Binding Windows Forms Controls to Data in Visual Studio

SQL Server Compact 3.5 and Visual Studio

Other Resources

Overview of Data Applications in Visual Studio

Occasionally Connected Applications (Local Database Caching)