Walkthrough: Creating a Local Database File in Visual Studio
You can explore basic tasks, such as adding tables and defining columns, by using Visual Studio to create a local database file in SQL Server Express LocalDB. You perform these tasks by using the Table Designer, which was significantly changed in 2012, and this topic reflects those updates. After you finish this walkthrough, you can discover more advanced capabilities by using your local database as a starting point for other walkthroughs that require it.
During this walkthrough, you'll explore the following tasks:
When you create a service-based database in Visual Studio, 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). See Local Data Overview.
Creating a project and a local database file
To create a project and a database file
Create a Windows Forms project that's named SampleDatabaseWalkthrough.
On the menu bar, choose Project, Add New Item.
The Add New Item dialog box appears so that you can add items that are appropriate in a Windows Form project.
In the list of templates, scroll down until Service-based Database appears, and then choose it.
Name the database SampleDatabase, and then choose the Add button.
The Data Source Configuration Wizard opens.
On the Choose a Database Model page, choose the Dataset icon, and then choose the Next button.
On the Choose Your Database Objects page, no database objects are available because the database is new.
Choose the Finish button to create the database and dataset and add them to the project.
The properties window for the database shows the connection string and the location of the primary .mdf file for the database. To display this window, choose the Server Explorer tab, expand the Data Connections node, open the shortcut menu for the database, and then choose Properties.
Creating Tables, Columns, Primary Keys, and Foreign Keys
To create the Customers table
In Server Explorer/Database Explorer, expand the Data Connections node, and then expand the SampleDatabase.mdf node.
If Server Explorer doesn't appear, you can display it by choosing View, Server Explorer on the menu bar.
Open the shortcut menu for Tables, and then choose Add New Table.
The Table Designer opens and shows a grid with one default row, which represents a single column in the table that you're creating. By adding rows to the grid, you'll define additional columns in the table.
In the grid, add a row for each entry in the following table:
Column name
Data type
Allow nulls
CustomerID
nchar(5)
False (cleared)
CompanyName
nvarchar(40)
False (cleared)
ContactName
nvarchar (30)
True (selected)
Phone
nvarchar (24)
True (selected)
Open the shortcut menu for the CustomerID row, and then choose Set Primary Key.
Open the shortcut menu for the default row, and then choose Delete.
Name the Customers table by updating the first line in the script pane to match the following sample:
CREATE TABLE [dbo].[Customers]In the upper-left corner of the Table Designer, choose the Update button.
In the Preview Database Updates dialog box, choose the Update Database button.
Your changes are saved to the local database file.
To create the Orders table
In Server Explorer/Database Explorer, open the shortcut menu for Tables, and then choose Add New Table.
In the grid, add a row for each entry in the following table:
Column name
Data type
Allow nulls
OrderID
int
False (cleared)
CustomerID
nchar(5)
False (cleared)
OrderDate
datetime
True (selected)
OrderQuantity
int
True (selected)
Open the shortcut menu for the OrderID column, and then choose Set Primary Key.
Open the shortcut menu for the default row, and then choose Delete.
Name the Orders table by updating the first line in the script pane to match the following sample:
CREATE TABLE [dbo].[Orders]In the upper-left corner of the Table Designer, choose the Update button.
In the Preview Database Updates dialog box, choose the Update Database button.
Your changes are saved to the local database file.
To create a foreign key
In the context pane on the right side of the grid, open the shortcut menu for Foreign Keys, and then choose Add New Foreign Key.
In the text box that appears, replace ToTable with Customers.
In the script pane, update the last line to match the following sample:
CONSTRAINT [FK_Orders_Customers] FOREIGN KEY ([CustomerID]) REFERENCES [Customers]([CustomerID])In the upper-left corner of the Table Designer, choose the Update button.
In the Preview Database Updates dialog box, choose the Update Database button.
Your changes are saved to the local database file.
Populating the Tables with Data
To populate the tables with data
In Server Explorer/Database Explorer, expand the node for the sample database, and then expand the Tables node.
Open the shortcut menu for the Customers table, and then choose Show Table Data.
Add whatever data you want for at least three customers.
Open the shortcut menu for the Orders table, and then choose Show Table Data.
Add data for at least three orders.
Important
Make sure that each value in the CustomerID column of the Orders table matches a value in the CustomerID column of the Customers table.
On the menu bar, choose File, Save All.
On the menu bar, choose File, Close Solution.
Note
As a best practice, you can back up the database file that you just created by copying it and then either pasting the copy in another location or giving the copy a different name.
Next Steps
Now that you have a local database file with some sample data, you can complete Walkthrough: Connecting to Data in a Local Database File (Windows Forms), in addition to other walkthroughs that demonstrate database tasks.
See Also
Tasks
How to: Manage Local Data Files in Your Project
Concepts
Binding Windows Forms Controls to Data in Visual Studio
Preparing Your Application to Receive Data
Fetching Data into Your Application
Binding Controls to Data in Visual Studio
Editing Data in Your Application