Walkthrough: Creating a SQL Server Compact Database
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:
See Also
Tasks
How to: Manage Local Data Files in Your Project
Walkthrough: Adding a SQL Server Compact Database to an Application and Deploying it
Concepts
Binding Windows Forms Controls to Data in Visual Studio
SQL Server Compact 4.0 and Visual Studio