Home Library Learn Samples Downloads Support Community Forums
10 out of 21 rated this helpful - Rate this topic

Walkthrough: Creating a LocalDB Database

Visual Studio 2012

By following this walkthrough, you can create a SQL Server database file based on the Customers and Orders tables in the Northwind Sample database. You can then use the file that you created to learn more about local database files by completing other how-to and walkthrough tasks in this Help system.

During this walkthrough, you will learn how to:

When creating a new service-based database in Visual Studio 2012, the SQL Server Express LocalDB engine is used to access a SQL Server 2012 database file (.mdf). In earlier versions of Visual Studio, the SQL Server Express engine is used to access a database file (.mdf). For more information, see Local Data Overview.

In order to complete this walkthrough, you will need:

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

To create the new Windows project

  1. In Visual Studio, choose File, New, Project.

  2. Name the project SampleDatabaseWalkthrough.

  3. Select Windows Forms Application and choose the OK button. For more information, see Developing Client Applications.

    The SampleDatabaseWalkthrough project is created and added to Solution Explorer.

To add a new database to the project

  1. Choose Project, Add New Item.

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

  3. In the Name area, type SampleDatabase.

  4. Choose the Add button. The Data Source Configuration Wizard opens.

  5. On the Choose a Database Model page, select Dataset, and then choose the Next button.

    On the Choose Your Database Objects page, no database objects are available because the database is new.

  6. Choose the Finish button to create the database and dataset and add them to the project.

To display the connection string and the location of the primary .mdf file for the database in Server Explorer, open the shortcut menu for the database, and then choose Properties.

NoteNote

A new version of Table Designer appears for databases in the SQL Server 2012 format. This topic describes the old version of Table Designer, which you use with databases in earlier formats of SQL Server.

In the new version, you can change a table definition through a graphical interface or directly in a script pane. If you use the graphical interface, the table’s definition is automatically updated in the script pane. To apply the SQL code in the script pane, choose the Update button. For more information about the new version, see How to: Create Database Objects Using Table Designer.

To add tables to the database

  1. Open Server Explorer/Database Explorer by choosing View, Server Explorer/Database Explorer.

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

  3. Open the shortcut menu for Tables and choose 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 Table Designer, Set Primary Key.

  6. Choose File, Save Table1.

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

  8. Choose the OK button.

  9. Open the shortcut menu for Tables and choose 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 Table Designer, Set Primary Key.

  12. Choose File, Save Table2.

  13. In the Enter a name for the table area, type Orders.

  14. Choose the OK button.

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

  1. In Server Explorer/Database Explorer, in the SampleDatabase.mdf node, open the shortcut menu for Database Diagrams, and then choose Add New Diagram.

  2. If a dialog box opens asking to create the required database objects for diagramming, choose the Yes button.

  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 then verify that the CustomerID column is selected for both tables.

  7. Choose the OK button to close the Tables and Columns dialog box.

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

  9. Choose File, Save Diagram1.

  10. Leave the default name and choose OK button.

  11. In the Save dialog box, choose the Yes button.

To populate the Customers table with data

  1. Create a new connection to the Northwind sample database in Server Explorer/Database Explorer. For more information, see How to: Install Sample Databases and How to: Connect to Data in a Database.

  2. In Server Explorer/Database Explorer, expand the Northwind database node.

  3. Open the shortcut menu for the Northwind Customers table, and choose Show Table Data.

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

  5. In Server Explorer/Database Explorer, expand the SampleDatabase.mdf database.

  6. Open the shortcut menu for 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. Open the shortcut menu for the Northwind Orders table, and choose Show Table Data.

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

  3. In Server Explorer/Database Explorer, expand the SampleDatabase.mdf database.

  4. Open the shortcut menu for the SampleDatabase.mdf Orders table, and choose Show Table Data.

  5. Paste the Northwind Orders table data from the Clipboard.

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 File, Save All.

  2. Choose File, Close Solution.

  3. In your project folder, browse to the SampleDatabase.mdf file, and Copy it.

  4. Browse to a folder where you want to save the database and paste the copy into the folder.

Now that you have a local database file with some sample data you can complete the following walkthrough, as well as other tasks in the Help system:

Did you find this helpful?
(1500 characters remaining)
© 2013 Microsoft. All rights reserved.
|
Site Feedback
© 2013 Microsoft. All rights reserved.