Walkthrough: Creating a SQL Server Express Database

Article
11/16/2012

This walkthrough creates a new SQL Server database file based on the Customers and Orders tables in the Northwind Sample database. This database file can then be used as a sample database for completing additional how-to and walkthrough pages in this Help system that reference local database files.

During this walkthrough, you will learn how to:

Create a new Windows Application.
Create a new local database file and add it to a project.
Create database tables and relationships using Visual Database Tools.

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 Application

Because this walkthrough creates a database based on the empty database template, a project is needed to create the database in.

To create the new Windows project

In Visual Studio, from the File menu, create a new Project.
Name the project SampleDatabaseWalkthrough.
Select Windows Application and click OK. For more information, see Creating Windows-Based Applications.

The SampleDatabaseWalkthrough project is created and added to Solution Explorer.

Creating a New SQL Server Database

To add a new database to the project

From the Project menu, choose Add New Item.
Select Service-based Database from the list of available templates.
Type SampleDatabase in the Name area.
Click Add.

The Data Source Configuration Wizard opens, but no database objects are available because this is a new database.
Click Finish to create the database and dataset and add them to the project.

Creating New Tables in the Database

To add tables to the database

Open Server Explorer/Database Explorer by selecting Server Explorer/Database Explorer from the View menu.
Expand the SampleDatabase.mdf node under the Data Connections node.
Right-click Tables and select Add New Table.

The Table Designer opens.

Create columns with the following information:

Column Name	Data Type	Allow Nulls
CustomerID	nchar(5)	False (not checked)
CompanyName	nvarchar(40)	False (not checked)
ContactName	nvarchar (30)	True (checked)
ContactTitle	nvarchar (30)	True (checked)
Address	nvarchar (60)	True (checked)
City	nvarchar (15)	True (checked)
Region	nvarchar (15)	True (checked)
PostalCode	nvarchar (10)	True (checked)
Country	nvarchar (15)	True (checked)
Phone	nvarchar (24)	True (checked)
Fax	nvarchar (24)	True (checked)

Select the CustomerID column, and then choose Set Primary Key from the Table Designer menu.
Choose Save Table1 from the File menu.
Type Customers in the Enter a name for the table area.
Click OK.
Right click Tables and select Add New Table.

The Table Designer opens.

Create columns with the following information:

Column Name	Data Type	Allow Nulls
OrderID	int	False (not checked)
CustomerID	nchar(5)	True (checked)
EmployeeID	int	True (checked)
OrderDate	datetime	True (checked)
RequiredDate	datetime	True (checked)
ShippedDate	datetime	True (checked)
ShipVia	int	True (checked)
Freight	money	True (checked)
ShipName	nvarchar(40)	True (checked)
ShipAddress	nvarchar(60)	True (checked)
ShipCity	nvarchar(15)	True (checked)
ShipRegion	nvarchar(15)	True (checked)
ShipPostalCode	nvarchar(10)	True (checked)
ShipCountry	nvarchar(15)	True (checked)

Select the OrderID column, and then choose Set Primary Key from the Table Designer menu.
Choose Save Table2 from the File menu.
Type Orders in the Enter a name for the table area.
Click OK.

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

Right-click the Database Diagrams node in the SampleDatabase.mdf node in Server Explorer/Database Explorer and choose Add New Diagram.
Click Yes if a dialog box opens asking to create the required database objects for diagramming.
Add the Customers and Orders tables to the diagram.
Close the Add Table dialog box.
Drag the CustomerID column from the Customers table onto the Orders table.
Verify that the Customers table is the Primary key table and the Orders table is the Foreign key table, and verify that the CustomerID column is selected for both tables.
Click OK to close the Tables and Columns dialog box.
Click OK to close the Foreign Key Relationship dialog box and create the relationship.
Select Save Diagram1 from the File menu.
Leave the default name and click OK.
Click Yes in the Save dialog box.

Populating the Sample Tables with Data

To populate the Customers table with data

Create a new connection in Server Explorer/Database Explorer 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 choose Show Table Data.
Select all the records and copy them to the Clipboard.
Expand the SampleDatabase.mdf database in Server Explorer/Database Explorer.
Right-click the SampleDatabase.mdf Customers table, and choose 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 select Show Table Data.
Select all the records and copy them to the clipboard.
Expand the SampleDatabase.mdf database in Server Explorer/Database Explorer.
Right click SampleDatabase.mdf Orders table, and select 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 with data you should make a copy of the database in its original state so you can restore the data if needed.

To save the sample database

Choose Save All from the File menu.
Choose Close Solution from the File menu.
Browse to the SampleDatabase.mdf file in your project 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 with some sample data you can complete the following pages: