Udostępnij za pośrednictwem


Walkthrough: Creating a SQL Server Express Database

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:

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

  1. In Visual Studio, from the File menu, create a new Project.

  2. Name the project SampleDatabaseWalkthrough.

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

  1. From the Project menu, choose Add New Item.

  2. Select Service-based Database from the list of available templates.

  3. Type SampleDatabase in the Name area.

  4. Click Add.

    The Data Source Configuration Wizard opens, but no database objects are available because this is a new database.

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

  1. Open Server Explorer/Database Explorer by selecting Server Explorer/Database Explorer from the View menu.

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

  3. Right-click Tables and select Add New Table.

    The Table Designer opens.

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

  5. Select the CustomerID column, and then choose Set Primary Key from the Table Designer menu.

  6. Choose Save Table1 from the File menu.

  7. Type Customers in the Enter a name for the table area.

  8. Click OK.

  9. Right click Tables and select Add New Table.

    The Table Designer opens.

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

  11. Select the OrderID column, and then choose Set Primary Key from the Table Designer menu.

  12. Choose Save Table2 from the File menu.

  13. Type Orders in the Enter a name for the table area.

  14. Click OK.

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

  1. Right-click the Database Diagrams node in the SampleDatabase.mdf node in Server Explorer/Database Explorer and choose Add New Diagram.

  2. Click Yes if a dialog box opens asking to create the required database objects for diagramming.

  3. Add the Customers and Orders tables to the diagram.

  4. Close the Add Table dialog box.

  5. Drag the CustomerID column from the Customers table onto the Orders table.

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

  7. Click OK to close the Tables and Columns dialog box.

  8. Click OK to close the Foreign Key Relationship dialog box and create the relationship.

  9. Select Save Diagram1 from the File menu.

  10. Leave the default name and click OK.

  11. Click Yes in the Save dialog box.

Populating the Sample Tables with Data

To populate the Customers table with data

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

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

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

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

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

  6. Right-click the SampleDatabase.mdf Customers table, and choose 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 select Show Table Data.

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

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

  4. Right click SampleDatabase.mdf Orders table, and select 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 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

  1. Choose Save All from the File menu.

  2. Choose Close Solution from the File menu.

  3. Browse to the SampleDatabase.mdf file in your project 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 with some sample data you can complete the following pages:

See Also

Tasks

How to: Manage Local Data Files in Your Project

Concepts

Local Data Overview

Displaying Data Overview

Other Resources

Getting Started with Data Access

Connecting to Data in Visual Studio

Preparing Your Application to Receive Data

Fetching Data into Your Application

Displaying Data on Forms in Windows Applications

Editing Data in Your Application

Validating Data

Saving Data