18 out of 48 rated this helpful - Rate this topic

Walkthrough: Creating a SQL Server Express Database File

.NET Framework 2.0

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 SQL 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:

Did you find this helpful? Yes No

Not accurate

Not enough depth

Need more code examples

(1500 characters remaining)

Community Additions

ADD